Oracle9i Reports 2002 5
Oracle9i Reports...3...4...4...9...14...18 Oracle9i Forms...19...19...20 A...22 B...24 Oracle9i Reports 2
Oracle9i Reports Oracle9i Reports Oracle9i Oracle9i Reports 3
Oracle9i Reports 1 SQL SQL SQL SQL XML rdf jsp A B Oracle9i Reports 4
rwrun module=emp.rdf userid=scott/tiger@orcl destype=file desformat=pdf desname=emp_pdf.pdf traceopts=trace_prf tracemode=trace_replace tracefile=emp_tr.txt emp_tr.txt +------------------------------------+ Report Builder Profiler statistics +------------------------------------+ Total Elapsed Time: 8.00 seconds Reports Time: 7.00 seconds (87.50% of TOTAL) ORACLE Time: 1.00 seconds (12.50% of TOTAL) UPI: SQL: 0.00 seconds 1.00 seconds TOTAL CPU Time used by process: N/A Total Elapsed Time: Reports Time: ORACLE Time: ORACLE SQL UPI SQL SQL SRW.DO_SQL() EXEC_SQL PL/SQL Cursors XML SQL ORACLE Time UPI SQL Oracle9i SQL Oracle9i Reports Structured Query Language SQL SQL SQL Oracle9i Reports 5
Oracle9i Reports SQL SQL SQL SQL Oracle SQL SQL SQL TKPROF EXPLAIN PLAN Oracle Optimizer EXPLAIN PLAN SQL-TRACE Reports Builder SQL SRW.DO_SQL('ALTER SESSION SET SQL_TRACE=TRUE'); return(1); : SQL TRACE Before Report Before Parameter Form EXPLAIN PLAN SQL Oracle9i SQL Language Select e.ename, d.dname From emp e, dept d Where e.deptno (+) = d.deptno EXPLAIN PLAN OPERATION OPTIONS OBJECT_NAME POSITION ------------------ ----------- --------------- ----------- SELECT STATEMENT MERGE JOIN OUTER 1 SORT JOIN 1 TABLE ACCESS FULL DEPT 1 SORT JOIN 2 TABLE ACCESS FULL EMP 1 Oracle9i Reports Oracle9i RDBMS 2 Oracle9i Reports 6
SQL SQL DBMS_STATS COMPUTE STATISTICS ANALYZE SQL Oracle9i Server PL/SQL PL/SQL ORA_PROF ORA_PROF PROCEDURE timed_proc (test VARCHAR2) IS i PLS_INTEGER; BEGIN Ora_Prof.Create_Timer('loop2'); Ora_Prof.Start_Timer('loop2'); ColorBand_Program_Unit; Ora_Prof.Stop_Timer('loop2'); Text_IO.Putf('Loop executed in %s seconds. n', Ora_Prof.Elapsed_Time('loop2')); Ora_Prof.Destroy_Timer('loop2'); END; Oracle9i Reports 7
ORA_PROF Reports Builder PL/SQL Oracle PL/SQL PL/SQL SQL PL/SQL PL/SQL Object Navigator PL/SQL PL/SQL PL/SQL PL/SQL SRW.DO_SQL() SRW.DO_SQL() SRW.DO_SQL() PL/SQL SRW.DO_SQL() 100 100 SQL DDL 1 SRW.DO_SQL() SRW.DO_SQL DDL Runtime Parameter Form SRW.DO_SQL SRW.DO_SQL (`CREATE TABLE' :tname `(ACCOUNT NUMBER NOT NULL PRIMARY KEY, COMP NUMBER (10,2))'); Oracle9i Reports 8
WITH Oracle9i SQL Java Oracle9i PL/SQL Java SQL PL/SQL Oracle Java Java Java Java Oracle9i Java Java Importer Oracle PL/SQL Java Oracle9i Reports Oracle Java Importer Java Java Importer PL/SQL PL/SQL Java Java PL/SQL PL/SQL Java PL/SQL Java Importer PL/SQL Java JOBJECT JVM Java Object persistence Oracle9i Reports 9
SQL Oracle9i Reports (Pluggable) Oracle9i Reports XML JDBC SQL XML URL URL XML XML XML Query XML DTD XML XML XML XML DTD XML DTD XML XSL extensible style sheet language XSL JDBC JDBC Thin Thick JDBC Oracle9i Reports 10
SQL WHERE 1 SQL Oracle9i JDBC PL/SQL Oracle9i CREATE OR REPLACE FUNCTION CityState ( p_location_id world_cities.location_id%type) RETURN VARCHAR2 is v_result VARCHAR2(100); BEGIN SELECT city ',' state INTO v_result FROM world_cities WHERE location_id = p_location_id; RETURN v_result; END CityState; SQL Select location_id, citystate(location_id)"city & State" from world_cities Oracle9i Reports 11
LOCATION_ID CITY & STATE ----------- ------------------------- 1 Redwood Shores, California 2 Seattle, Washington 3 Los Angeles, California 4 New York, New York 1 2 1 SRW.SET_MAXROW() Query_Emp Query_Dept Before Report IF :Parameter_1 = 'A' then SRW.SET_MAXROW('Query_Emp',0); ELSE SRW.SET_MAX_ROW('Query_Dept',0); END IF; SRW.SET_MAXROW() Before Report SRW.SET_MAXROW() SRW.MAXROW_UNSET XML Oracle9i Reports 12
Oracle9i Reports Oracle9i SQL ORDER BY ORDER BY ORDER BY ORDER BY Reports Builder 1 1 Oracle9i Reports 5 WHERE dept emp Oracle9i Reports 13
Select d.dname, e.ename From emp e, dept d Where e.deptno(+) = d.deptno 2 deptno Select deptno, dname from dept Select deptno, ename from emp Oracle9i Reports 1 2 Oracle9i Reports Oracle9i Reports Web Web JSP ' ' Oracle9i Reports Oracle9i Reports 14
Oracle9i Reports Oracle9i Reports Reports Builder 1 SUBSTR Reports Builder 1 Report Builder SUBSTR Oracle9i Reports 15
.RDF.REP.JSP.RDF.REP.REP.RDF 2 1 1 Oracle9i Reports 1 1 2 DML INSERT 2 Oracle9i Reports 16
Oracle9i Reports PL/SQL SRW.SET_TEXT_COLOR PL/SQL PL/SQL Web JSP Oracle9i Reports JavaServer Pages JSP Web Web Web Oracle9i Reports Web 1 Oracle9i Reports Web Web Java JSP Java JSP Web Oracle9i Reports Oracle9i Reports 17
Java PL/SQL.JSP Web JSP Web <rw:include> Oracle9i Reports JSP SUPPRESLAYOUT JSP Oracle Technology Network (http://technet.oracle.com Oracle Technology Network http://otn.oracle.co.jp/ Oracle9iAS Oracle9i Reports 3 1 3 1 3 Oracle9i Reports 1 1 Oracle9i Reports 18
Oracle9i Forms Forms Builder Reports Builder Oracle9i Forms Oracle9i Forms Oracle9i Reports Oracle9i Reports Oracle9i Forms Reports Run_Report_Object Oracle9i Forms Oracle Technology Network (http://technet.oracle.com Oracle9i Forms Server Report Integration Reports Builder RUNDEBUG NO JSP Reports Builder Reports Services http VALIDATTAG YES RWRUN RWSERVLET RECURSIVE_LOAD Oracle9i Reports 19
YES PL/SQL Oracle9i SQL Oracle9i Reports Oracle 1 ARRAYSIZE LONG CLOB BLOB LONGCHUNK Oracle9i Reports Oracle8i Oracle9i Server LONG LONG RAW CLOB BLOB PARAMFORM NO PostScript COPIES PostScript COPIES 1 Oracle9i Reports Oracle9i Reports / Web Reports Server Oracle9i Reports 20
Oracle9i Reports 21
A Reports Builder Builder rwbuilder.conf <trace tracefile="trace_file_name" traceopts="trace_all" tracemode="trace_replace"/> <ORACLE_HOME>/reports/log <hostname>-rwbuilder.trc RWBUILDER RWRUN tracefile=trace_file_name traceopts=trace_all tracemode=trace_replace rwbuilder.conf RWSERVER <servername.conf> <trace tracefile="trace_file_name" traceopts="trace_all" tracemode="trace_replace"/> <ORACLE_HOME>/reports/log <servername.trc> <severname>-<enginename>-<engineno>.trc RWSERVLET rwservlet.properties TRACEOPTS=TRACE_ALL TRACEFILE=rwservlet.trc TRACEMODE=TRACE_REPLACE TRACEFILE TRACEMODE TRACEOPTS TRACEFILE=<tracefile> tracefile Oracle9i Reports 22
TRACEMODE=<TRACE_APPEND TRACE_REPLACE> TRACE_APPEND TRACEOPTS=<TRACE_APP TRACE_BRK TRACE_DBG TRACE_DST TRACE ERR TRACE INF TRACE LOG TRACE_PLS TRACE_PRF TRACE_SQL TRACE_STA TRACE_TMS TRACE_WRN TRACE_ALL> TRACE_ERR - TRACE_PRF - TRACE_APP - TRACE_PLS - PL/SQL TRACE_SQL - SQL TRACE_TMS - TRACE_DST - TRACE_ALL - TRACEOPTS=(TRACE_APP, TRACE_PRF) Oracle9i Reports 23
B traceopts=all 14 emp LOG : Report: MODULE2 Logged onto server: Username: scott 16:15:59 APP ( Frame 16:15:59 APP. ( Text Boilerplate B_OR$BODY_SECTION 16:15:59 APP. ) Text Boilerplate B_OR$BODY_SECTION 16:15:59 APP. ( Generic Graphical Object B_1_SEC2 16:15:59 APP. ) Generic Graphical Object B_1_SEC2 16:15:59 APP. ( Text Boilerplate B_DATE1_SEC2 16:15:59 APP. ) Text Boilerplate B_DATE1_SEC2 16:15:59 APP. ( Text Boilerplate B_PAGENUM1_SEC2 16:15:59 APP. ) Text Boilerplate B_PAGENUM1_SEC2 16:15:59 APP. ( Text Field F_DATE1_SEC2 16:15:59 APP.. ( Database Column Name unknown 16:15:59 APP.. ) Database Column Name unknown 16:15:59 APP. ) Text Field F_DATE1_SEC2 16:15:59 APP ) Frame 16:15:59 APP ( Frame 16:15:59 APP. ( Frame M_G_DNAME_GRPFR 16:15:59 APP.. ( Repeating Frame R_G_DNAME 16:15:59 APP... ( Group G_dname Local Break: 0 Global Break: 0 16:15:59 APP... ( Query Q_1 16:15:59 SQL EXECUTE QUERY : select d.dname, e.ename from emp e, dept d where e.deptno(+)=d.deptno 16:15:59 APP... ) Query Q_1 16:15:59 APP... ) Group G_dname 16:15:59 APP... ( Text Field F_ENAME 16:15:59 APP... ( Database Column ename 16:15:59 APP... ) Database Column ename 16:15:59 APP... ) Text Field F_ENAME 16:15:59 APP... ( Text Field F_DNAME 16:15:59 APP... ( Database Column dname 16:15:59 APP... ) Database Column dname 16:15:59 APP... ) Text Field F_DNAME 16:15:59 APP... ( Group G_dname Local Break: 1 Global Break: 1 16:15:59 APP... ) Group G_dname 16:15:59 APP... ( Text Field F_ENAME 16:15:59 APP... ( Database Column ename 16:15:59 APP... ) Database Column ename 16:15:59 APP... ) Text Field F_ENAME Oracle9i Reports 24
+------------------------------------+ Report Builder Profiler statistics +------------------------------------+ Total Elapsed Time: 8.00 seconds Reports Time: 7.00 seconds (87.50% of TOTAL) ORACLE Time: 1.00 seconds (12.50% of TOTAL) UPI: 0.00 seconds SQL: 1.00 seconds TOTAL CPU Time used by process: N/A Oracle9i Reports 25
Oracle9i Reports 2002 5 : Christian Bauwens : Barry Hiern Danny Richardson James Safcik Oracle Corporation Tuning Oracle9i Reports Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. : : +1.650.506.7000 : +1.650.506.7200 www.oracle.com Oracle Copyright 2002 Oracle Corporation All rights reserved.