Template for HEUG Presentations at HEUG 2005, Las Vega

1
Oracle BI (XML) Publisher Installation

• John Chemers
• AOL LLC
• TimeWarner Inc.
• NCOAUG/MWRUG/ORACLE CONFERENCE
• Monday, March 12, 2007

2
Types of Paths in this Presentation
3
Your Presenters

• John Chemers
• Technology Architect/DBA
• johnchemers06_at_aol.com
• [phone] cell
• Marybeth Hinkel
• PeopleSoft Support
• mhink44_at_aol.com
• [phone]

4
Overview

• Oracle BI (XML) Publisher Installation

5
Agenda

• Database Configuration
• XML Publisher User, Grants, Enable
• Database Links
• XML Publisher Server Configuration
• Quartz Scheduler Configuration
• Database Configuration
• XML Publisher Client Configuration
• Internet Explorer Configurations
• Enable Java, JavaScript
• XML Publisher Presentations

6

• Database Architecture

7
Database Architecture

• Oracles Database Architecture
• My Database Architecture

8
Oracles Database Architecture
9
My Database Architecture
10

• Database Configuration

11
Database Configuration

• Oracle 9i Configuration
• Oracle 9i 10g Sample Schemas
• Database Configuration
• XML Publisher User
• Enable Report User and Grant Privileges
• Enable Sample Report User
• Database Links
• Database Link to Oracle DB
• Database Link to Microsoft DB

12
Oracle 9i Configuration

• Activate Oracle JServer/JVM option
• Increase INIT.ORA param values
• java_pool_size (gt20M)
• shared_pool_size (gt50M)
• Run ORACLE_HOME/javavm/install/initjvm.sql as
  SYS AS SYSDBA to install JServer Option
• Grant JAVAUSERPRIV to users that use Java
• SQLgt GRANT JAVAUSERPRIV TO SCOTT
• Use rmjvm.sql to deinstall JServer option

13
Oracle 9i 10g Sample Schemas

• Sample Schemas
• Human Resources (HR)
• Order Entry (OE)
• Online Catalog (OC) subschema for OE
• Multimedia datatypes (Product Media, PM)
• Queued Shipping (QS) and subschemas
• Sales History (SH)

14
Create Tablespace for Sample Schemas

• CREATE TABLESPACE "EXAMPLE"
• LOGGING DATAFILE 'C\ORACLE\PRODUCT\10.2.0\ORADATA
  \REPT\example01.dbf'
• SIZE 50M REUSE
• AUTOEXTEND ON NEXT 50M MAXSIZE 1000M
• EXTENT MANAGEMENT LOCAL
• SEGMENT SPACE MANAGEMENT AUTO

15
Create Sample Schemas-Oracle 9i

• sqlplus /nolog
• SQLgt _at_?/demo/schema/mksample manager
  change_on_install hr oe pm ix sh bi example temp
  ORACLE_HOME/demo/schema/log/
• The entire process takes about 30 to 45 minutes
  to complete.

16
Create Sample Schemas-Oracle 10g

• Use DCA Database Configuration Assistant
• The entire process takes about 30 to 45 minutes
  to complete.

17
Database Configuration

• Configure tnsnames.ora
• Configure listener.ora
• Reload listner
• TNSPING

18
Configure tnsnames.ora

• REPT
• (DESCRIPTION
• (ADDRESS_LIST
• (ADDRESS (PROTOCOL TCP)
• (HOST ltYOUR HOSTgt)
• (PORT 1521))
• )
• (CONNECT_DATA
• (SERVER DEDICATED)
• (SERVICE_NAME REPT)
• )
• )

19
Configure listener.ora

• SID_LIST_LISTENER
• (SID_LIST
• (SID_DESC
• (SID_NAME PLSExtProc)
• (ORACLE_HOME C\oracle\product\10.2.0\db_1
  )
• (PROGRAM extproc)
• )
• (SID_DESC
• (GLOBAL_DBNAMErept)
• (ORACLE_HOME C\oracle\product\10.2.0\
  db_1)
• (SID_NAME rept)
• )
• )
• LISTENER
• (DESCRIPTION_LIST
• (DESCRIPTION
• (ADDRESS (PROTOCOL TCP)
• (HOST ltYOUR HOSTgt)

20
Reload Listener and TNSPING

• lsnrctl reload
• tnsping rept

21

• Sample User Configuration

22
Enable Sample Report User

• C\gt sqlplus sys/password_at_REPT as SYSDBA
• SQLgt alter user OE account unlock
• SQLgt ALTER USER OE IDENTIFIED BY password

23

• Custom User Creation

24
Identify location of Data Files

• C\gtsqlplus sys/password_at_REPT as sysdba
• SQLgt select file, substr(name,1,60) from
  vdatafile order by file

25
Create Tablespace

• SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
• SQLgt CREATE TABLESPACE SYSADM
• DATAFILE 'C\ORACLE\PRODUCT\10.2.0\ORADATA\REPT\sy
  sadm01.dbf'
• SIZE 20M EXTENT MANAGEMENT LOCAL AUTOALLOCATE

26
Create Custom Report User

• SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
• SQLgt CREATE USER sysadm IDENTIFIED BY sysadm
• TEMPORARY TABLESPACE temp
• DEFAULT TABLESPACE sysadm
• QUOTA UNLIMITED ON sysadm

27
Grant Connect Access

• SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
• SQLgt GRANT connect TO sysadm

28
Grant Create/Select Privileges

• SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
• SQLgt GRANT SELECT ANY TABLE TO SYSADM
• SQLgt GRANT CREATE TABLE TO SYSADM
• SQLgt GRANT CREATE VIEW TO SYSADM
• SQLgt GRANT CREATE SEQUENCE TO SYSADM

29
Grant Java Privileges

• SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
• SQLgt GRANT JAVAUSERPRIV TO SYSADM

30

• Database Link Creation

31
Database Link Creation

• Grant Privileges
• Oracle DB to Oracle DB Configuration
• Create Database Link
• Create Views to Oracle-DB Tables/Views
• Oracle DB to Microsoft DB Configuration
• Oracle Heterogeneous Services ODBC agent
• Create Database Link
• Create Views to Microsoft DB Tables/Views

32

• Grant Privileges

33
Grant Privileges

• SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
• SQLgt GRANT CREATE DATABASE LINK TO SYSADM
• SQLgt GRANT CREATE SYNONYM TO SYSADM
• SQLgt GRANT EXECUTE ANY PROCEDURE TO SYSADM

34

• Database Link Creation-Oracle DB

35
My Database Architecture
36
Create Database Link-Oracle DB

• SQLgt CONNECT SYSADM/SYSADM_at_REPT
• SQLgt CREATE DATABASE LINK SAPDB
• CONNECT TO SAPADM IDENTIFIED BY password
  USING 'SAPDB'

37
Create Views-Oracle DB

• create force view SAP_TABLE_NAME
• as select from SAP_TABLE_NAME_at_sapdb
• create force view SAP_VIEW_NAME
• as select from SAP_VIEW_NAME_at_sapdb

38
My Database Architecture
39

• Database Link Creation-Microsoft SQL Server DB

40
My Database Architecture
41
Oracle Heterogeneous Services ODBC
agent-Microsoft DB

• Make a connection from Oracle to SQL Server using
  Oracle Heterogeneous Services ODBC agent

42
Define a Data Source Name (DSN) for SQL Server

• Open Data Sources (ODBC) in Control Panel
• Add a new DSN Data Source and use the SQL Server
  driver
• Choose a name for the Data Source i.e MYAWDSN
• Accept (local) for SQL server
• Change default database from master to database
  like AdventureWorks

43
Create a Oracle Heterogeneous Services
Initialization File-Microsoft DB

• Copy sample HS init file
• Copy ORACLE_HOME/hs/admin/inithsodbc.ora sample
  file to initMYAWDSN.ora
• Modify parameters
• HS_FDS_CONNECT_INFO MYAWDSN
• HS_FDS_TRACE_LEVEL OFF

44
Configure listener.ora - Microsoft DB - Part 1

• Set Listener name to LISTENERMYAWDSN
• Set Port number to 1522
• Set SID_NAME to MYAWDSN
• Set ORACLE_HOME location
• Set PROGRAM hsodbc

45
Configure listener.ora Microsoft DB - Part 2

• LISTENERMYAWDSN
• (ADDRESS_LIST
• (ADDRESS(PROTOCOLtcp)(HOSTlocalhost)(PORT
  1522))
• (ADDRESS(PROTOCOLipc)(KEYPNPKEY)))
• SID_LIST_LISTENERMYAWDSN
• (SID_LIST
• (SID_DESC
• (SID_NAMEMYAWDSN)
• (ORACLE_HOME c\oracle\product\10.2.0\d
  b_1)
• (PROGRAMhsodbc)
• )
• )

46
Configure tnsnames.ora Microsoft DB - Part 1

• Create TNS entry named MYAWDSN
• Set Port number to 1522
• Set SID to MYAWDSN
• Set HS parameter to OK

47
Configure tnsnames.ora Microsoft DB - Part 2

• MYAWDSN
• (DESCRIPTION (ADDRESS(PROTOCOLtcp)(HOSTlo
  calhost)(PORT1522)) (CONNECT_DATA(SIDMYAWDSN
  ))
• (HSOK)
• )

48
Reload listener Microsoft DB

• Lsnrctl reload

49
Create a Database Link Microsoft DB

• SQLgt CONNECT SYSADM/SYSADM_at_REPT
• SQLgt create database link myawdsn
• connect to sa identified by password using
  'MYAWDSN'

50
Create Views-Microsoft DB

• create force view MS_TABLE_NAME
• as select from MS_TABLE_NAME_at_myawdsn
• create force view MS_VIEW_NAME
• as select from MS_VIEW_NAME_at_myawdsn

51
My Database Architecture
52

• XML Publisher Enterprise Installation

53
XML Publisher Enterprise Installation

• Specify Home Details
• Name XMLP_HOME_1
• Path C\Oracle\product\XMLP\5.6.2
• XMLP Setup Information
• C\oracle\product\XMLP\5.6.2\xmlpserver\setupinfo.
  txt
• XMLP URL
• http//ltYOUR HOSTgt15101/xmlpserver
• Start/Stop XMLP
• C\oracle\product\XMLP\5.6.2\xmlpserver\xmlpserver
  start.bat
• C\oracle\product\XMLP\5.6.2\xmlpserver\xmlpserver
  stop.bat

54
Modify xml-server-config.xml FileVerify File Path

• The original entry is
• ltfile path"s_Reports_Path"/gt.
• The value for the entry should be
• ltfile path"C\oracle\product\XMLP\5.6.2\xmlpserve
  r\"/gt

55
Modify xml-server-config.xml FileModify
Scheduler Section

• ltschedulergt
• ltconnectiongt
• ltconnectionTypegtjdbclt/connectionTypegt
• lturlgtjdbcoraclethin_at_ltYOUR
  HOSTgt1521reptlt/urlgt
• ltusernamegtoelt/usernamegt
• ltpasswordgtpasswordlt/passwordgt
• ltdrivergtoracle.jdbc.driver.OracleDriverlt/dri
  vergt
• lt/connectiongt
• lt/schedulergt

56
Run oracle_tables.sql Script for Quartz Scheduler

• From the SQL script directory
  \manual\SQL_Scripts, run the oracle_tables.sql
  script with sqlplus
• C\gt sqlplus SYSADM/SYSADM_at_REPT
• SQLgt _at_tables_oracle.sql
• C\gt sqlplus oe/password_at_REPT
• SQLgt _at_tables_oracle.sql

57
Setup Quartz Scheduler for storing job information

• cd C\oracle\product\XMLP\5626AD1.2\oc4j\j2ee\hom
  e\applib
• notepad quartz.properties
• Update hostname ltYOUR-HOSTgt for
  org.quartz.dataSource.myDS.URL in the
  quartz.properties file.

58
See Quartz Website for Configuration Detail

• http//www.opensymphony.com/quartz/wikidocs/Config
  uration.html

59

• Start XML Publisher Enterprise

60
Start XML Publisher Enterprise

• Start
• C\oracle\product\XMLP\5.6.2\xmlpserver\xmlpserver
  start.bat
• Login
• http//ltYOUR HOSTgt15101/xmlpserver

61
Set Connection Values

• Set Connection Values
• Select the Admin tab
• In the Data Sources list, choose JDBC Database
  Connection.
• Choose xmlpdemo connection.
• Connection Values
• Data Source Name xmlpdemo
• URL jdbcoraclethin_at_ltYOUR HOSTgt1521rept
• Username oe
• Password password
• Database Driver Class oracle.jdbc.driver.OracleDr
  iver

62

• Client Configuration

63
Client Configuration

• Install Internet Explorer
• In Control Panel, disable Internet Explorer
  Enhanced Security Configuration
• In IE, enable Java and JavaScript
• Install Adobe Acrobat Reader

64

• Install XML Publisher Desktop

65
Install XML Publisher Desktop

• Run Oracle Universal Installer to install Oracle
  XML Publisher Desktop for Microsoft Word.
• \XMLP_DESKTOP\setup.exe

66

• Connecting to SQL Server with XMLP Enterprise

67
Oracles Database Architecture
68
Connecting to SQL Server with XMLP
Enterprise-Part 1

• Download the SQL Server 2000 Driver for JDBC
  Service Pack 3
• some issues with the 2005 jdbc driver Oracle or
  MS?
• you can access the SQL Server 2005 edition with
  the 2000 driver
• There will be 3 jars in the ltltINSTALL DIRgtgt/lib
  directory
• mssqlserver.jar
• msbase.jar
• msutil.jar

69
Connecting to SQL Server with XMLP
Enterprise-Part 2

• Copy them into your OC4J_HOME/j2ee/home/applib
  if you have installed XMLP under OC4J otherwise
  put them in the common apps libraries directory
  of your J2EE container.
• Bounce your server, the jdbc libraries will be
  loaded automatically for all applications on the
  instance. If you only want them loaded for the
  xmlpserver application then copy the jars to
  OC4J_HOME/j2ee/home/applications/xmlpserver/xmlpse
  rver/WEB-INF/lib and bounce.

70
Connecting to SQL Server with XMLP
Enterprise-Part 3

• Login as Administrator in the Enterprise server,
  go to the Admin -gt JDBC page and enter the
  details required for the data connection
• Data Source Name - give the connection a name
• URL the url to connect to the db, standard MSSQL
  stuff, basically jdbcsqlserver//serverportda
  tabaseNamedbname e.g. jdbcsqlserver//111.11.1.1
  111133databaseNameAdventureWorks
• Username - simple stuff
• Password - even simpler
• Database Driver Class - com.microsoft.jdbc.sqlserv
  er.SQLServerDriver
• Check the connection.
• If it fails check your connection string is valid
  and the class definition above.
• The data source will now be available in the
  report builder to builder queries against.

71

• Creating XML Publisher Applications

72
Creating XML Publisher Applications

• Copy the XML Publisher Java libraries
  (\manual\lib) on the Server CD to the library
  directory of your J2EE project or Java Runtime
  Environment.
• Copy the fonts in the fonts directory
  (\manual\fonts) on the Server CD to the fonts
  directory of your Java Runtime Environment.
• Consult "Setting Runtime Properties" in the
  Oracle XML Publisher Enterprise User's Guide
  (found under \doc\XMLPublisher562.pdf) regarding
  the configuration file. Please define the
  temporary directory!

73

• Oracle OC4J Deployment Notes

74
Oracle OC4J Deployment Notes

• The version of the XML Parser shipped with Oracle
  OC4J is incompatible with XML Publisher. When you
  create an XML Publisher application for OC4J you
  need to configure the system to use the XML
  Publisher version of the XML Parser. This can be
  accomplished by setting an orion-web.xml
  configuration file with the following content
• lt?xml version '1.0' encoding 'windows-1252'?gt
• ltorion-web-appgt
• ltweb-app-class-loader search-local-classes-firs
  t"true" /gt
• lt/orion-web-appgt

75

• Getting Started with XML Publisher

76
Getting Started with XML Publisher

• Getting Started
• Viewing and Scheduling Reports
• Creating a New Report
• Translating Reports
• Administration
• Creating an RTF Template
• XSL, SQL, and XSL-FO Support
• Creating a PDF Template
• Building a Data Template
• Setting Runtime Properties
• Using the XML Publisher APIs
• Supported XSL-FO Elements

77

• XML Publisher Presentations

78
XML Publisher Presentations

• Implementing Oracle XML Publisher for PeopleSoft
  Enterprise
• Shawn Zerby, Oracle Corporation
• 845-925 AM, Room 48
• Oracle BI (XML) Publisher Installation
• John Chemers, AOL LLC, TimeWarner Inc.
• 935-1015 AM, RM 57
• Fax and Email from Oracle E-Business Suite using
  XML Publisher
• Mike Piland, STR Software
• 935-1015 AM, RM 43
• XML Publisher Reports - E-Business Suite
• Kiran Kumar Akkiraju, eAlliance Corporation
• 1245-125 PM, RM 52
• XML Publisher in PeopleSoft Easier Than You
  Think
• John Jaent, Emerging Solutions
• 135-215 PM, Room 48

79

• Questions

80
Questions

• John Chemers
• Technology Architect/DBA
• johnchemers06_at_aol.com
• [phone] cell
• Marybeth Hinkel
• PeopleSoft Support
• mhink44_at_aol.com
• [phone]