All about Reports 6i

About User Exits
A user exit is a program that you write and then link into the Report Builder executable or user exit DLL files. You build user exits when you want to pass control from Report Builder to a program you have written, which performs some function, and then returns control to Report Builder.You can write the following types of user exits:

ORACLE Pre-compiler user exits

OCI (ORACLE Call Interface) user exits

Non-ORACLE user exits

You can also write a user exit that combines both the ORACLE Pre-compiler interface and the OCI. User exits can perform the following tasks:

Perform complex data manipulation

Pass data to Report Builder from operating system text files

Manipulate LONG RAW data

Support PL/SQL blocks

Control real time devices, such as a printer or a robot
You can use user exits for other tasks, such as mathematical processing. However, Oracle Corporation recommends that you perform such tasks with PL/SQL within Report Builder. Usage Notes

Not all types of user exits can perform all of the described tasks. You can accomplish most of these tasks only with ORACLE Pre-compiler user exits.

Some details of implementing user exits are specific to each operating system.
Writing a user exit call

You can call the user exit from any place in which you can enter PL/SQL within Report Builder. Use the following syntax:
Syntax

[SRW.REFERENCE(:object_name_1);]

[SRW.REFERENCE(:object2_name_2); ...];

SRW.USER_EXIT('user_exit_name [argument_list]'); where:
object_name_n Is the name of an Report Builder parameter or column whose value will be passed to the user exit in the user exit string. This causes Report Builder to build a dependency list: it will ensure that each referenced object will contain the most recently computed or fetched value before it is passed to the user exit. Note: You must reference each parameter or column separately. For details, see "SRW.REFERENCE".
user_exit_name Is the name of the user exit to which you are passing control. The user exit name may be any length. (On some operating systems, the name may be at most 6 characters. Check with your system administrator.)
argument_list Can contain the names of parameters and columns, constants, character strings, or any combination thereof, that you wish to pass to the user exit. The argument list may be any length. Note: The above syntax will need to be embedded in a PL/SQL program unit.
SRW.USER_EXIT

Description : This procedure calls the user exit named in user_exit_string. It is useful when you want to pass control to a 3GL program during a report's execution.
SyntaxSRW.USER_EXIT (user_exit_string CHAR);Parameters :user_exit_string :Is the name of the user exit you want to call and any columns or parameters that you want to pass to the user exit program.
SRW.REFERENCE

Description : This procedure causes Report Builder to add the referenced object to the PL/SQL construct's dependency list. This causes Report Builder to determine the object's value just before firing the PL/SQL construct. This is useful when you want to ensure that a column value passed to a user exit is the most recently computed or fetched value. SyntaxSRW.REFERENCE (:object CHARDATENUMBER); Parametersobject Is the Report Builder parameter or column whose value needs to be ascertained before the construct fires.
SRW.MESSAGE

Description :This procedure displays a message with the message number and text that you specify. The message is displayed in the format below. After the message is raised and you accept it, the report execution will continue. MSG-msg_number: msg_text. SyntaxSRW.MESSAGE (msg_number NUMBER, msg_text CHAR); Parametersmsg_number Is a number from one to ten digits, to be displayed on the message line. Numbers less than five digits will be padded with zeros out to five digits. For example, if you specify 123, it will be displayed as SRW-00123.msg_text Is at most 190 minus the msg_number alphanumeric characters to be displayed on the message line.

Lexical References :
Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL.

SELECT Clause
SELECT &P_ENAME NAME, &P_EMPNO ENO, &P_JOB ROLE FROM EMP
FROM Clause
SELECT ORDID, TOTAL FROM &ATABLE
WHERE Clause
SELECT ORDID, TOTAL FROM ORD WHERE &CUST
GROUP BY Clause
SELECT NVL(COMMPLAN, DFLTCOMM) CPLAN, SUM(TOTAL) TOTAL FROM ORD GROUP BY &NEWCOMM
HAVING Clause
SELECT CUSTID, SUM(TOTAL) TOTAL FROM ORD GROUP BY CUSTID HAVING &MINTOTAL
ORDER BY Clause
SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL FROM ORD ORDER BY &SORT
CONNECT BY and START WITH Clauses
Multiple Clauses
SELECT &COLSTABLE
COLSTABLE could be used to change both the SELECT and FROM clauses at runtime. For example, you could enter DNAME ENAME, LOC SAL FROM DEPT for COLSTABLE at runtime.

Bind References :
Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.

You create a bind reference by entering a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.

SELECT Clause
SELECT CUSTID, NVL(COMMPLAN, :DFLTCOMM) COMMPLAN FROM ORD
WHERE Clause
SELECT ORDID, TOTAL FROM ORD WHERE CUSTID = :CUST
GROUP BY Clause
SELECT NVL(COMMPLAN, :DFLTCOMM) COMMPLAN, SUM(TOTAL) TOTAL FROM ORD GROUP BY NVL(COMMPLAN, :DFLTCOMM)
HAVING Clause
SELECT CUSTID, SUM(TOTAL) TOTAL FROM ORD GROUP BY CUSTID HAVING SUM(TOTAL) > :MINTOTAL
ORDER BY Clause
SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL FROM ORD ORDER BY DECODE(:SORT, 1, SHIPDATE, 2, ORDERDATE)
Placeholder Columns :
A placeholder is a column for which you set the data type and value in PL/SQL that you define. You can set the value of a placeholder column in the following places:
Ø The Before Report Trigger, if the placeholder is a report-level column
Ø A report-level formula column, if the placeholder is a report-level column
Ø A formula in the placeholder's group or a group below it (the value is set once for each record of the group)

PROCEDURE RUN_PRODUCT (product NUMBER, module VARCHAR2, commmode NUMBER, execmode NUMBER, location NUMBER, paramlist_id VARCHAR2, display VARCHAR2);
Product :Specifies a numeric constant for the Oracle product you want to invoke: FORMS specifies a Runform session. GRAPHICS specifies Graphics Builder. REPORTS specifies Report Builder. BOOK specifies Oracle Book.
module Specifies the VARCHAR2 name of the module or module to be executed by the called product. Valid values are the name of a form module, report, Graphics Builder display, or Oracle Book module. The application looks for the module or module in the default paths defined for the called product.
commmode Specifies the communication mode to be used when running the called product. Valid numeric constants for this parameter are SYNCHRONOUS and ASYNCHRONOUS.SYNCHRONOUS specifies that control returns to Form Builder only after the called product has been exited. The end user cannot work in the form while the called product is running. ASYNCHRONOUS specifies that control returns to the calling application immediately, even if the called application has not completed its display.
Execmode : Specifies the execution mode to be used when running the called product. Valid numeric constants for this parameter are BATCH and RUNTIME. When you run Report Builder and Graphics Builder, execmode can be either BATCH or RUNTIME. When you run Form Builder, always set execmode to RUNTIME.
Location : Specifies the location of the module or module you want the called product to execute, either the file system or the database. Valid constants for this property are FILESYSTEM and DB.
Paramlist_name or paramlist_ID : Specifies the parameter list to be passed to the called product. Valid values for this parameter are the VARCHAR2 name of the parameter list, the ID of the parameter list, or a null string (''). To specify a parameter list ID, use a variable of type PARAMLIST.
Display : Specifies the VARCHAR2 name of the Form Builder chart item that will contain the display (such as a pie chart, bar chart, or graph) generated by Graphics Builder. The name of the chart item must be specified in the format block_name.item_name

PROCEDURE Run_Emp_Report IS
pl_id ParamList;
BEGIN
pl_id := Get_Parameter_List('tmpdata');
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List( pl_id );
END IF;
pl_id := Create_Parameter_List('tmpdata'); Add_Parameter(pl_id,'EMP_QUERY',DATA_PARAMETER,'EMP_RECS');
Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'NO');
Run_Product(REPORTS, 'empreport', SYNCHRONOUS, RUNTIME,FILESYSTEM, pl_id, NULL);
END;