Leveraging Oracle8i with Oracle Reports 6i An Oracle Technical White Paper January 2001

Leveraging Oracle8i with Oracle Reports 6i January 2001

INTRODUCTION

Oracle8i™ is the database for Internet computing. More than just a simple relational data-source, it has many advanced features that allow application developers to build and deploy advanced Internet and OLTP/data-warehousing applications that scale. Business today, of course, moves at Internet speed and application developers are being pushed to produce these advanced applications quickly but at the same time make them flexible enough to cope with constantly changing requirements.

Oracle Reports is Oracle’s enterprise reporting tool. It’s wizard driven, declarative development environment permits IT developers to easily and rapidly build any kind of highly complex enterprise report for high fidelity publishing of Oracle8i data – the kinds of reports that organizations depend upon. It’s middle-tier services available through iAS provides a scalable and performant reporting solution for deployment of these reports to the web.

This paper will demonstrate how application developers may leverage the functionality of Oracle8i from within Oracle Reports to publish data to the Internet. The paper is broken up into five sections focusing on five key Oracle8i technologies:

•

Materialized Views

•

Extended Aggregate Operations and Analytic Functions

•

Java Stored Procedures

•

interMedia Text Support

•

Oracle8i Object Support

Leveraging Oracle8i with Oracle Reports 6i January 2001

For each technology, a brief explanation will be given. However, it is beyond the scope of this paper to discuss these technologies in great detail. After the description an example with Oracle Reports integrating these technologies will be shown.

MATERIALIZED VIEWS Materialized views, introduced in Oracle 8.0 initially and enhanced in subsequent releases, allow DBA’s the ability to define summary tables on the data to increase query performance. A summary table (which is also called an aggregate table) stores the pre-computed results of aggregated data, such as sums, counts, and averages. These tables are built to enhance query performance in a data warehouse. It is often much more efficient to access a pre-aggregated result, instead of computing an aggregate from detail data every time a query is executed.

Consider a data warehouse containing detailed sales information. The warehouse administrator may choose to build a summary table containing the total sales per region per month. When a user requests East-region sales for Q1, Oracle8i will not calculate the total sales from individual transaction records. Instead, Oracle8i retrieves the pre-aggregated data from the sales-per-regionper-month materialized view. The use of the materialized view is entirely transparent to the end-user. Oracle8i automatically rewrites a SQL request to use a materialized view. By using the materialized view, query performance may be improved by orders of magnitude. For example, a materialized view can be created in the following manner:

create materialized view emp_view BUILD IMMEDIATE REFRESH COMPLETE START WITH ROUND((SYSDATE + 1) + 11/24) NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY')+15/24 ENABLE QUERY REWRITE AS select e.EMPNO, e.ENAme,e.JOB,e.MGR,e.HIREDATE,e.SAL,e.COMM,e.DEPTNO, d.DNAME, d.LOC from emp e, dept d where d.deptno = e.deptno

Leveraging Oracle8i with Oracle Reports 6i January 2001

This will (immediately, as opposed to at some point in the future) create a materialized view containing the information of the employees and departments tables (joined), will then automatically re-write queries to use this view if the tables are queried, and finally, will automatically refresh the results in the materialized view tomorrow at 11:00 am, and subsequently every Monday at 3:00 pm. Note: for query re-write to occur, the privileges of ‘create materialized view’ and ‘query rewrite’ need to be granted to the user creating the materialized view, and the following database initialization parameters need to be set: COMPATIBLE = 8.1.0 (or greater) QUERY_REWRITE_ENABLED = TRUE OPTIMIZER_MODE = ALL_ROWS|FIRST_ROWS|CHOOSE This now means that a query such as: select job, sal, loc from emp, dept where dept.deptno = emp.deptno

will transparently use the materialized view emp_view to fetch the data. This is because as part of query optimization (which occurs before execution) every query is analyzed and, where appropriate, rewritten to use a materialized view. This summary-rewrite facility has previously been available with other products such as Oracle Discoverer, however this is now available to all SQL queries that are issued from any end-user environment.

So, what must the Report developer do in addition to the report they have just developed, to allow Oracle Reports to take advantage of this powerful feature? Quite simply, nothing! When executing the report, Oracle Reports parses the query, adds any bind variables, order-by clauses, etc. and then passes it down to the database for execution. As with any query handed to the database, it is analyzed and thus automatically directed (if necessary) to the materialized view. This can be verified by looking at a SQL_TRACE which can be optionally generated by the Reports session.

Oracle8i manages definitions of dimensions and hierarchies, and uses this metadata to enable advanced query rewrites. Oracle8i will recognize, for example, that if an end-user requests a quarterly

Leveraging Oracle8i with Oracle Reports 6i January 2001

report, and a quarterly summary table is not available, a monthly summary table can be used to evaluate the query.

EXTENDED AGGREGATE OPERATIONS AND ANALYTIC FUNCTIONS

Aggregate Operations return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle8i divides the rows of a queried table or view into groups. For example: select dname, sum(sal) from dept, emp where dept.deptno = emp.deptno group by dname

would result in: DNAME SUM(SAL) -------------- --------ACCOUNTING 8750 RESEARCH 10875 SALES 9400

The database applies the aggregate functions to each group of rows and returns a single result row for each group. So the above example shows the total sum of all salaries on a per-department basis.

Introduced in Oracle8i (8.1.5), Extended Aggregate Operations extend this functionality by adding CUBE and ROLLUP extensions, where super-aggregate groupings are produced in addition to the regular groupings (as extra rows). ROLLUP creates subtotals at increasing levels of aggregation, from the most detailed up to a grand total. CUBE is an extension similar to ROLLUP, enabling a single statement to calculate all possible combinations of subtotals. CUBE can generate the information needed in cross-tabulation reports with a single query. We can distinguish between the ‘real’ data rows being returned by the query and the ‘extra’ rows added by the CUBE/ROLLUP

Leveraging Oracle8i with Oracle Reports 6i January 2001

extension by using the GROUPING function in the select list – for ‘real’ rows the GROUPING function returns 0, otherwise it returns a 1. So now we apply CUBE to our previous example: select grouping(dname) grp, dname, sum(sal) from dept, emp where dept.deptno = emp.deptno group by cube(dname);

This results in:

GRP --------0 0 0 1

DNAME SUM(SAL) -------------- --------ACCOUNTING 8750 RESEARCH 10875 SALES 9400 29025

Notice the extra row at the end – it doesn’t have value for the ‘dname’. This is the super-aggregated result of the CUBE for a single group and is the sum of the individual department sums – i.e. the grand total. The results of the GROUPING function are also displayed as the ‘grp’ column.

Oracle 8i Release 2 (8.1.6) introduced a powerful new family of SQL functions for business intelligence and data-warehousing applications. These functions are collectively called ‘analytic functions’ and they provide significantly improved performance and simplified coding for many business analysis queries. These new SQL functions are also being reviewed by ANSI for addition to the new SQL standard. Oracle has created four families of analytic functions, each of which contains several functions:

•

Ranking family - This family supports business questions like “show the top 10 and bottom 10 salesperson per each region” or “show, for each region, salespersons that make up 25% of the sales”. The functions examine the entire output before producing an answer. Oracle provides RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST and NTILE functions.

Leveraging Oracle8i with Oracle Reports 6i January 2001

•

Window Aggregate family - This family addresses questions like “show the 13-week moving average of a stock price” or “show cumulative sum of sales per each region.” The new features provide moving and cumulative processing for all the SQL aggregate functions including AVG, SUM, MIN, MAX, COUNT, VARIANCE and STDDEV.

•

Reporting Aggregate family - One of the most common types of calculations is the comparison of a non-aggregate value to an aggregate value. All percent-of-total and market share calculations require this processing. The reporting aggregate family makes these sorts of calculations simple: it let’s users place values calculated at different aggregation levels on the same row. Without needing a join operation, users can now compare aggregate values to the detail rows. The new family provides reporting aggregate processing for all SQL functions including AVG, SUM, MIN, MAX, COUNT, VARIANCE and STDDEV.

•

LAG/LEAD family - Studying change and variation is at the heart of analysis. Necessarily, this involves comparing the values of different rows in a table. While this has been possible in SQL, usually through self-joins, it has not been efficient or easy to formulate. The LAG/LEAD family enables queries to compare different rows of a table simply by specifying an offset from the current row.

Let’s look at an example of the application of some of these functions and operations. Imagine that the HR manager in our organization has requested the following information about the employees in our company: “I’m trying to make sure our compensation policy and job bands are fair and in line with corporate directives. To determine this, I need to see the following from our HR database:

1. A breakdown of the number of employees and the average salary for each job we have in the company. I’d like to see this first for the entire company, and then on a per-department basis.

2. For each employee on a per-department basis, I would like to see their total compensation and how this compensation ranks within the company as a whole, within their department (and it’s proportion), and how much above/below the company compensation average this is. On the same report I’d like to see their date of employ, their seniority in the company (i.e. the order in which they were employed), who was the next person that employed after them, and how long afterwards.”

Leveraging Oracle8i with Oracle Reports 6i January 2001

With the new 8i analytic functions, we can achieve all this for our demanding HR manager with just two SQL statements and no processing of the data in PL/SQL! Let’s take the first request. This can be written in SQL as follows:

SELECT GROUPING(dname) dept_grouping_code, DECODE(GROUPING(dname), 1, 'All Departments',initcap(dname)) AS dname, GROUPING(job) job_grouping_code, DECODE(GROUPING(job), 1, 'All Jobs', job) AS job, COUNT(*) "Total Empl", AVG(sal+nvl(comm,0)) "Average Comp" FROM emp, dept WHERE dept.deptno = emp.deptno GROUP BY CUBE (dname, job)

Which when executed returns the following:

DEPT_GROUPING_CODE Comp --------------------0 1300 0 2450 0 5000 0 2916.6667 0 3000 0 950 0 2975 0 2175 0 950 0 2850 0 1950

DNAME

JOB_GROUPING_CODE JOB

Total Empl Average

--------------- ----------------- --------- ---------- -------Accounting

0 CLERK

1

Accounting

0 MANAGER

1

Accounting

0 PRESIDENT

1

Accounting

1 All Jobs

3

Research

0 ANALYST

2

Research

0 CLERK

2

Research

0 MANAGER

1

Research

1 All Jobs

5

Sales

0 CLERK

1

Sales

0 MANAGER

1

Sales

0 SALESMAN

4

Leveraging Oracle8i with Oracle Reports 6i January 2001

0 Sales

1 All Jobs

6

1 All Departments

0 ANALYST

2

1 All Departments

0 CLERK

4

1 All Departments

0 MANAGER

3

1 All Departments

0 PRESIDENT

1

1 All Departments

0 SALESMAN

4

1 All Departments

1 All Jobs

14

1933.3333 3000 1037.5 2758.3333 5000 1950 2230.3571

Now let’s take the second example. This can be expressed in SQL as follows: SELECT emp.deptno, dept.dname, avg(sal+nvl(comm,0)) over (partition by dept.deptno) avg_dept_sal, ename, job, sal, nvl(comm,0), (sal+nvl(comm,0)) Compensation, hiredate, RANK() OVER (PARTITION BY emp.deptno ORDER BY (sal+nvl(comm,0)) DESC) as rk, RANK() OVER (order by (sal+nvl(comm,0)) desc) "Rank in Company", RANK() OVER (order by hiredate) "rank in employ", ((LEAD(hiredate, 1) OVER (ORDER BY hiredate))-hiredate) "Days over emp", (LEAD(ename,1) OVER (ORDER BY hiredate)) next_emp FROM emp, dept WHERE dept.deptno = emp.deptno ORDER BY rk

which is returned as follows:

DEPTNO DNAME AVG_DEPT_SAL ENAME JOB SAL NVL(COMM,0) COMPENSATION --------- -------------- ------------ ---------- --------- --------- ----------- ----------HIREDATE RK Rank in Company rank in employ Days over emp NEXT_EMP

Leveraging Oracle8i with Oracle Reports 6i January 2001

--------- --------- --------------- -------------- ------------- ---------30 SALES 1933.3333 BLAKE MANAGER 2850 2850 01-MAY-81 1 5 5 39 CLARK 10 ACCOUNTING 5000 17-NOV-81 1

2916.6667 KING 1

20 RESEARCH 3000 09-DEC-82 1

2175 SCOTT

20 RESEARCH 3000 03-DEC-81 1

2175 FORD

2

2

10 ACCOUNTING 2450 09-JUN-81 2

2916.6667 CLARK

30 SALES 2650 28-SEP-81

1933.3333 MARTIN

30 SALES 1900 20-FEB-81

2

6 1933.3333 ALLEN

3

10 ACCOUNTING 1300 23-JAN-82 3 20 RESEARCH 2975 02-APR-81 3 30 SALES 1750 22-FEB-81

7

8 2916.6667 MILLER 11 2175 JONES 4 1933.3333 WARD

4

9

PRESIDENT 9 ANALYST 13 ANALYST 10 MANAGER 6 SALESMAN 8 SALESMAN 2 CLERK 12 MANAGER 4 SALESMAN 3

20 RESEARCH 1100 12-JAN-83 4

2175 ADAMS

20 RESEARCH 800 17-DEC-80 5

2175 SMITH

CLERK

14

1

30 SALES 1500 08-SEP-81

12

1933.3333 TURNER 5

30 SALES

Leveraging Oracle8i with Oracle Reports 6i January 2001

10 1933.3333 JAMES

CLERK

5000

0

0

16 FORD 3000

0

34 ADAMS 3000

0

0 JAMES 2450

0

91 TURNER 1250

1400

50 KING 1600

300

2 WARD 1300

0

320 SCOTT 2975

0

29 BLAKE 1250

500

39 JONES 1100

0

800

0

14

SALESMAN 7 CLERK

65 ALLEN 1500

0

20 MARTIN 950

0

950 03-DEC-81

6

13

10

51 MILLER

Naturally we want to produce ‘board-room’ quality output for our HR manager, so instead of SQL*Plus, we will use Oracle Reports to produce these reports. So now the question is, how do we put these queries with this special syntax in our enterprise report? The answer is simple - just as you would do with any other regular SQL statement! Oracle Reports passes the SQL straight through to the database so developers may utilize all these functions and the extended aggregate operations within individual (standalone, not linked) queries in all versions of Oracle Reports. In addition, Oracle Reports 6i Release 2 has been further enhanced to ‘understand’ these extensions to the SQL syntax, so that queries using these functions may be linked to any other query or the break order of fields changed in the data model (6i Release 2 is required since these features in Reports actually re-write the SQL statement behind the scenes). Once we create our report (either with the wizard or manually creating the queries) our data model looks like this (with break groups defined for a group-above report):

Leveraging Oracle8i with Oracle Reports 6i January 2001

Figure 1: Reports Data Model We have not linked the queries since the HR manager wanted 2 summaries/analyses. We can use the multi-sectioning and bursting feature of Oracle Reports to display the results of the first query on the first section, and then the results of the second query in another. Of course with a single run of the report, we may run each section to a number of different formats (e.g. PDF, Postscript, HTML, RTF, etc. etc.) and to a number of different destinations (e.g. printer, email, a portal, etc.)

After a defaulting the layout with the wizard and a template, the first section looks as follows:

Leveraging Oracle8i with Oracle Reports 6i January 2001

Figure 2: First section of our HR analysis

We now run the report wizard again and create our layout for the second analysis. We can also invoke the chart wizard to view some of the data pictorially, and thus end up with a layout that looks like this:

Leveraging Oracle8i with Oracle Reports 6i January 2001

Figure 3: The second section of our HR analysis

As we can see, the new extended aggregate operations and analytic functions are very powerful and can easily be used to enhance the analysis of our data from within Oracle Reports.

JAVA STORED PROCEDURES Java has quickly become today’s most popular programming language because it appeals to everyone – from developers who find the language robust and object-oriented, to CIOs and

Leveraging Oracle8i with Oracle Reports 6i January 2001

managers who see Java as reducing costs and simplifying application development and support. Oracle8i extends Oracle’s Java strategy by integrating a scalable Java Virtual Machine (JVM) with the database. This allows developers to write and execute business logic written in Java in their applications as Java Stored Procedures. Oracle Reports has long supported PL/SQL as a language for writing business logic. However in today’s organizations and applications, customers are increasingly taking a ‘mix-and-match’ approach – using one or the other within the same application where it is advantageous to do so. The advantages depend on the type of operation that the application is doing. PL/SQL has been optimized around SQL and it naturally, and safely extends SQL by supporting the same datatypes, data encapsulation, overloading, and exception handling much faster for SQL intensive operations. Java, however, has a richer type set, is optimized for computational operations involving complex object-oriented or cpu-intensive ‘number-crunching’ operations, and facilitates open distributed systems development (i.e. ability to call out to CORBA services and Enterprise Java Beans - EJB’s). The database allows customers the best of both worlds - to write Java Stored Procedures, but also to be able to transparently call them from within PL/SQL. This can be used within Reports.

To continue our HR salary analysis, suppose now we have the requirement to have a central business function (written in Java) to determine the color-coding of the compensation cell of our first report layout. It has to be written in Java since it calls a common set of complex Java methods that we already have which perform some number crunching.

First, we write our business function: public class mybusiness_function { public static String hilite_color(int avg_sal, int avg_company_comp) { String color_scheme; if (avg_sal < avg_company_comp) { /* This is where we call our set of complex Java methods to Determine the color scheme that should be used and populate the Variable ‘color_scheme’. For testing purposes, we are just

Leveraging Oracle8i with Oracle Reports 6i January 2001

going to give it a hard-coded value of ‘cyan’. */ color_scheme = “Cyan”; return color_scheme; } else { return "Red"; } } }

Then we compile this method (saved as mybusiness_function.java) into a class: c:\>javac mybusiness_function.java

Now we deploy the class to Oracle8i using the utility ‘loadjava’: c:\>loadjava –user scott/tiger@orcl mybusiness_function.class

We now have our business logic written in Java, compiled, and loaded into the database. In order to make this appear to the PL/SQL environment (i.e. publish the Java method to SQL), we need to create a simple wrapper PL/SQL function to tell the compiler how the PL/SQL call spec maps onto the Java call spec:

create or replace function hilite_color(avg_sal in number, avg_company_comp in number) return varchar2 as language java name 'mybusiness_function.hilite_color(int, int) return java.lang.String';

Note that this action only needs to be done for (static) Java methods that are going to be called directly from PL/SQL (i.e. the top-level Java entry point). If one Java method is only ever going to be called from another Java method, this step does not have to be done.

Finally we need to call this from Reports. The PL/SQL wrapper makes this Java method appear as a regular PL/SQL stored function, and thus it can be executed in exactly the same way as a regular

Leveraging Oracle8i with Oracle Reports 6i January 2001

stored PL/SQL function from a format trigger on the ‘Average Comp’ field:

function F_Average_Comp1FormatTrigger return boolean is begin SRW.SET_FILL_PATTERN('transparent'); srw.set_background_fill_color(hilite_color(:average_comp,:avg_company_co mp)); return(true);

Upon running the report, it alters our report output as follows:

Figure 4: Color-coded average compensation field via a Java method

Leveraging Oracle8i with Oracle Reports 6i January 2001

INTERMEDIA TEXT SUPPORT

Oracle8i interMedia is a product that enables Oracle8i to manage text, documents, images audio and video in an integrated fashion with other enterprise information. It extends Oracle8i reliability, availability and data management to text and multimedia content in Internet, electronic commerce and media-rich applications.

Oracle’s award-winning ConText® option is now included with interMedia to deliver powerful textretrieval capabilities fundamental to web applications. Oracle8i interMedia text management capabilities let users query documents stored in common formats, including Word, Excel, PowerPoint, WordPerfect, HTML and Acrobat/PDF and to seamlessly combine the text information with regular database information. Oracle8i interMedia indexes the text content to deliver fast, accurate, flexible analysis and retrieval of information in document archives, online newsfeeds, customer call reports and other online text information sources. Let’s take an example. We have indexed the HTML version of the Reports 6i “Publishing Reports” manual that is shipped and installed with Reports 6i by using the script in Appendix A. To run a text query, we can issue the following SQL statement in SQL*Plus:

select docid, title, docname,score(0) from reports_doc where contains (docname, ’PDF’,0) >0

DOCID SCORE(0) -----------3 9 6 4 8 13 12 4 14

TITLE

DOCNAME

---------------------------------------- --------------- ----Glossary

glossary.htm

Publishing Architecture and Concepts

pbr_arch.htm

RWCLI60 Command Line Arguments

pbr_clia.htm

Introduction

pbr_intr.htm

Running Report Requests

pbr_run.htm

Leveraging Oracle8i with Oracle Reports 6i January 2001

4 15 Controlling User Access to Reports

pbr_secu.htm

18 Customizing Reports at Runtime

pbr_xml.htm

13 89

This returns a list of all the primary keys (docid), titles, and the names of the documents containing the word ‘PDF’ (this list is also known as the hitlist). It also returns a pseudo-column (i.e. a column that does not exist in the table) called the score, which is the frequency with which the term was encountered in each of the documents – greater the number of encounters in a particular document, the higher the score for that document. The ‘> 0’ part at the end of the query specifies the hit score or tolerance –greater than zero means that the query will return any document with 1 or more occurrences of the search term(s). However, on a large dataset, typically you would want to set the score a little higher so that the ‘hitlist’ returned is shorter and is more likely to contain hits that are relevant/meaningful (and not just passing, one-off references to the search word).

So how can we leverage this powerful Oracle8i feature in Reports? Just like the Extended Aggregate Operations and the Analytic Functions, the CONTAINS clause extension to SQL is passed straight through to the database and any pseudo-columns that it returns are leverageable in Reports.

Let’s look at an example of this. First, we create a report with the wizard that has the following query:

select docid, title, docname, score(0) from reports_doc where contains (docname, :p_searchterm, 0) >0

order by score(0) desc

A parameter gets instantiated for us called p_searchterm. The order-by clause ensures that we see the most relevant hits (those with the highest score) first. That’s it! If the report is now run, and we enter ‘PDF’ in the parameter form, we get the following:

Leveraging Oracle8i with Oracle Reports 6i January 2001

Figure 5: interMedia Text search results in Reports

We can then go on to use the standard interMedia Text procedures and functions to do such operations as mark-up the search terms in the document (in HTML or ASCII), generate a gist of the document, etc.

Leveraging Oracle8i with Oracle Reports 6i January 2001

ORACLE8I OBJECT SUPPORT

Building on the standard capabilities of Oracle8i, the Objects and Extensibility Option provides an additional set of features that allow users to create and manipulate user-defined object types, as well as user-defined components, called Data Cartridges. Designers can model the structures of realworld entities, as well as the operations that applications need to perform on these entities, by defining object types. Object types thus help extend the data-modeling capabilities beyond that are provided by built-in types such as NUMBER, DATE, or CHAR. Instances of object types can be stored in the rows and columns of Oracle8i database tables.

Oracle Reports can leverage the Objects option of Oracle 8i. Specifically, Oracle Reports queries can be based on Object Tables or Object Views, and Reports can resolve Object Columns, REFs and Methods. Reports also supports the new Large Object (LOB) datatypes in Oracle8i such as BLOB and CLOB.

A table itself can be based on an object (a table object), one or more of the table’s columns can be based on an object (a column object), or one or more of the table’s columns can contain a reference to an object stored in another table (REF column). And each of these objects can themselves contain objects. If a table is based on an object that has no nested objects, the situation is simple: Reports treats each of the object’s attributes as if it were a column in the table. Each selected attribute becomes an item in the block. The situation is more complicated when: •

The table contains a column object

•

The table contains a REF to an object

•

An object is nested within another object

Leveraging Oracle8i with Oracle Reports 6i January 2001

In these cases, the columns and attributes form a hierarchy. However, Reports has no hierarchy of items, so it simply collapses the hierarchy so that columns and attributes can be viewed as columns within a table. Both the hierarchy of the columns and attributes, and the “flattened” nature of the resulting items are represented in Reports’ displays and dialog boxes.

To further examine the support, let’s look at an example. The script in Appendix B creates an object version of the Emp and Dept tables. These objects make reference to columns with objects types, REFs, and methods. Now we run the report wizard, and enter the following SQL statement:

SELECT e.EMPNO, e.ENAME, e.JOB, e.MGR, e.HIREDATE, e.SAL, e.COMM, e.DEPTREF, e.total_sal() total_comp FROM O_EMP e

The report wizard understands that this query includes a method and a REF, and allows us to drill down on the REF to add further columns to our query (such as the location information) as illustrated in figure 6:

Leveraging Oracle8i with Oracle Reports 6i January 2001

Figure 6: Using the Report wizard with a query containing Oracle8 Objects and methods

Once we complete stepping through the wizard, we can see the representation of REFS within the data model (used to determine the location of the employee’s office and each employee’s manager information). Also notice that the method total_comp is treated simply as another field:

Leveraging Oracle8i with Oracle Reports 6i January 2001

Figure 7: Objects in the Data Model Now we run the report, we can see the following:

Leveraging Oracle8i with Oracle Reports 6i January 2001

Figure 8: Oracle8i objects Report Thus we are able to leverage the Oracle8i objects option in a declarative manner from within Oracle Reports.

CONCLUSION

This paper has looked at some of the technologies available in Oracle 8i Release 2 and beyond, and shown how the wizard driven, declarative development environment permits IT developers to easily and rapidly build any kind of highly complex enterprise report for high fidelity publishing of Oracle8i data. At the same time, developers are permitted to leverage the very latest advanced functionality of the Oracle8i database to accelerate application development and make applications as powerful and flexible as possible.

Leveraging Oracle8i with Oracle Reports 6i January 2001

REFERENCES Oracle8i SQL Reference Release 2 (8.1.6) Part # A76989-01 Oracle8i Data Warehousing Guide Release 2 (8.1.6) Part # A76994-01 Oracle8i interMedia Text Reference Release 2 (8.1.6) Part #A77063-01 Oracle8i New Features Summary, An Oracle Technical Whitepaper, downloadable from http://technet.oracle.com/products/oracle8i Developing Oracle8i Applications with PL/SQL and Java, An Oracle Technical Whitepaper, downloadable from http://technet.oracle.com/products/oracle8i What’s New in Oracle Reports 6i, An Oracle Technical Whitepaper, downloadable from http://technet.oracle.com/products/reports Oracle8i for Data Warehousing, An Oracle Technical Whitepaper, downloadable from http://technet.oracle.com/products/oracle8i

Leveraging Oracle8i with Oracle Reports 6i January 2001

APPENDIX A - INTERMEDIA TEXT INDEX SCRIPT

POLICY CREATION SCRIPT The following script was used to text index the Oracle Reports 6i documentation (Publishing Reports manual). exec exec drop drop

ctx_ddl.drop_preference('COMMON_FILTER'); ctx_ddl.drop_preference ('COMMON_DIR'); index datastores_text; table reports_doc;

create table reports_doc (docid number primary key, title varchar2 (80), docname varchar2 (80)); insert into reports_doc values insert into reports_doc values insert into reports_doc values insert into reports_doc values insert into reports_doc values insert into reports_doc values Concepts','pbr_arch.htm'); insert into reports_doc values Server','pbr_case.htm'); insert into reports_doc values Arguments','pbr_clia.htm'); insert into reports_doc values Clusters','pbr_clus.htm'); insert into reports_doc values Parameters','pbr_conf.htm'); insert into reports_doc values Strings','pbr_conn.htm'); insert into reports_doc values insert into reports_doc values Variables','pbr_rfap.htm'); insert into reports_doc values Requests','pbr_run.htm'); insert into reports_doc values Reports','pbr_secu.htm'); insert into reports_doc values insert into reports_doc values Cartridge','pbr_wcap.htm');

Leveraging Oracle8i with Oracle Reports 6i January 2001

(1,'Index','bookix.htm'); (2,'Table of Contents','booktoc.htm'); (3,'Glossary','glossary.htm'); (4,'Appendices','partpaga.htm'); (5,'Coverpage','partpage.htm'); (6,'Publishing Architecture and (7,'Configuring the Reports (8,'RWCLI60 Command Line (9,'Configuring Reports Server (10,'Reports Server Configuration (11,'Database Connection (12,'Introduction','pbr_intr.htm'); (13,'Environment (14,'Running Report (15,'Controlling User Access to (16,'Troubleshooting','pbr_tsap.htm'); (17,'Reports Web

insert into reports_doc values Runtime','pbr_xml.htm'); insert into reports_doc values insert into reports_doc values insert into reports_doc values

(18,'Customizing Reports at (19,'Preface','preface.htm'); (20,'Send Us Your Comments','rcf.htm'); (21,'Title','title.htm');

commit; exec ctx_ddl.create_preference ('COMMON_DIR','FILE_DATASTORE'); exec ctx_ddl.set_attribute ('COMMON_DIR','PATH','D:\orant\TOOLS\DOC60\us\od_pubrep60'); execute ctx_ddl.create_preference('COMMON_FILTER','USER_FILTER'); execute ctx_ddl.set_attribute('COMMON_FILTER','COMMAND','myfilt.bat'); create index datastores_text on reports_doc (docname) indextype is ctxsys.context parameters ('datastore COMMON_DIR filter COMMON_FILTER');

MYFILT.BAT

The following script was the user-defined interMedia Text filter used to index the HTML files. set ORACLE_HOME=D:\Oracle\Ora81 ctxhx.exe %1 %2 ascii8 iso8859-1

Leveraging Oracle8i with Oracle Reports 6i January 2001

APPENDIX B – OBJECT TABLE SCRIPT

The following script was used to create the tables and objects used to demonstrate the Object create type loc_type as object ( street varchar2(100), city varchar2(100), state varchar2(2), zip varchar2(5) ); / create type o_dept_type as object ( deptno number, dname varchar2(14), loc loc_type, MEMBER FUNCTION total_sal RETURN NUMBER, pragma restrict_references(total_sal, WNDS, WNPS) ); / create table o_dept of o_dept_type; create type (EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTref

o_emp_type as object NUMBER(4), VARCHAR2(10), VARCHAR2(9), ref o_emp_type, DATE, NUMBER(7,2), NUMBER(7,2), ref o_dept_type,

MEMBER FUNCTION total_sal RETURN NUMBER, pragma restrict_references(total_sal, WNDS, WNPS) ); / create or replace type body o_emp_type as MEMBER FUNCTION total_sal RETURN NUMBER is tot_sal number(7,2); begin

Leveraging Oracle8i with Oracle Reports 6i January 2001

tot_sal := nvl(self.sal,0) + nvl(self.comm,0); return tot_sal; end; end; / create table o_emp of o_emp_type; create or replace type body o_dept_type as MEMBER FUNCTION total_sal RETURN NUMBER is tot_sal number(7,2); begin select sum(e.total_sal()) into tot_sal from o_emp e where e.deptref.deptno = self.deptno; return tot_sal; end; end; / insert into o_dept(deptno, dname, loc) values (10, 'Accounting', loc_type('885 Third Avenue', 'New York', 'NY', '10022')); insert into o_dept(deptno, dname, loc) values (20, 'Research', loc_type('222 West Las Colinas Boulevard', 'Dallas', 'TX', '75039')); insert into o_dept (deptno, dname, loc) values (30, 'Sales', loc_type('203 North La Salle Street', 'Chicago', 'IL', '60601')); insert into o_dept(deptno, dname, loc) values (40, 'Operations', loc_type('222 Berkeley Street', 'Boston', 'MA', '02116'));

insert into o_emp select 7839, 'King', 'President', NULL, '18-NOV-96', 5000, NULL, ref(d) from o_dept d where deptno = 10; insert into o_emp select 7369, 'Smith', 'Clerk', NULL, '18-DEC-95', 800, NULL, ref(d) from o_dept d where deptno = 20; insert into o_emp select 7499, 'Allen', 'Salesman', NULL, '21-FEB-96', 1600, 300, ref(d) from o_dept d where deptno = 30;

Leveraging Oracle8i with Oracle Reports 6i January 2001

insert into o_emp select 7521, 'Ward', 'Salesman', NULL, '22-FEB-96', 1250, 500, ref(d) from o_dept d where deptno = 30; insert into o_emp select 7566, 'Jones', 'Manager', NULL, '02-APR-96', 2975, NULL, ref(d) from o_dept d where deptno = 20; insert into o_emp select 7654, 'Martin', 'Salesman', NULL, '27-SEP-96', 1250, 1400, ref(d) from o_dept d where deptno = 30; insert into o_emp select 7698, 'Blake', 'Manager', NULL, '01-MAY-96', 2850, NULL, ref(d) from o_dept d where deptno = 30; insert into o_emp select 7782, 'Clark', 'Manager', NULL, '10-JUN-96', 2450, NULL, ref(d) from o_dept d where deptno = 10; insert into o_emp select 7788, 'Scott', 'Analyst', NULL, '09-DEC-97', 3000, NULL, ref(d) from o_dept d where deptno = 20; insert into o_emp select 7844, 'Turner', 'Salesman', NULL, '09-SEP-96', 1500, 0, ref(d) from o_dept d where deptno = 30; insert into o_emp select 7876, 'Adams', 'Clerk', NULL, '12-JAN-98', 1100, NULL, ref(d) from o_dept d where deptno = 20; insert into o_emp select 7900, 'James', 'Clerk', NULL, '03-DEC-96', 950, NULL, ref(d) from o_dept d where deptno = 30; insert into o_emp select 7902, 'Ford', 'Analyst', NULL, '03-DEC-96', 3000, NULL, ref(d) from o_dept d where deptno = 20; insert into o_emp select 7934, 'Miller', 'Clerk', NULL, '23-JAN-97', 1300, NULL, ref(d) from o_dept d where deptno = 10; update o_emp set mgr = (select ref(m) from o_emp m where ename = 'Ford') where ename = 'Smith'; update o_emp set mgr = (select ref(m) from

Leveraging Oracle8i with Oracle Reports 6i January 2001

o_emp m

where

where ename = 'Blake') ename in ('Allen', 'Ward', 'Martin', 'Turner', 'James');

update o_emp set mgr = (select ref(m) from o_emp m where ename = 'Clark') where ename = 'Miller'; update o_emp set mgr = (select ref(m) from o_emp m where ename = 'Scott') where ename = 'Adams'; update o_emp set mgr = (select ref(m) from o_emp m where ename = 'Jones') where ename in ('Scott', 'Ford'); update o_emp set mgr = (select ref(m) from o_emp m where ename = 'King') where ename in ('Jones', 'Blake', 'Clark'); commit;

Leveraging Oracle8i with Oracle Reports 6i January 2001

Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. Worldwide Inquiries: +1.650.506.7000 Fax +1.650.506.7200 http://www.oracle.com/

Copyright © Oracle Corporation 2000 All Rights Reserved This document is provided for informational purposes only, and the information herein is subject to change without notice. Please Report any errors herein to Oracle Corporation. Oracle Corporation does not provide any warranties covering and specifically disclaims any liability in connection with this document. Oracle is a registered trademark, and Oracle8i, Oracle8, PL/SQL, and Oracle Reports are trademarks of Oracle Corporation. All other company and product names mentioned are used for identification purposes only and may be trademarks of their respective owners.

Leveraging Oracle8i with Oracle Reports 6i January 2001