Reports Builder is
shipped with a built-in package (SRW), a collection of PL/SQL constructs that
include many functions, procedures, and exceptions you can reference in any of
your libraries or reports. The PL/SQL provided by the SRW package enables you to
perform such actions as change the formatting of fields, run reports from
within other reports, create customized messages to display in the event of
report error, and execute SQL statements. You can reference the contents of the
SRW package from any of your libraries or reports without having to attach it.
However, you cannot reference its contents from within another product, for
example, from SQL*Plus. Constructs found in a package are commonly referred to
as "packaged" (that is, packaged functions, packaged procedures, and
packaged exceptions).
SRW.MESSAGE
<> Restrictions <> Examples <> Related
Topics <> All
Built-ins
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.
Syntax
SRW.MESSAGE (msg_number NUMBER,
msg_text CHAR);
Parameters
msg_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.
SRW.MESSAGE examples
<> Related Topics <> All
Examples
/* Suppose you have a user exit named MYEXIT to
which you want to
** pass the values of the SAL
column. Suppose,
also, that you want
** to raise your own error if the user exit
is not found (e.g., because
** it is not linked, compiled,
etc.). To
do these things, you could
** write the following PL/SQL in the
Format Trigger of the F_SAL field:
*/
/* This trigger will raise your message as
follows:
** MSG-1000: User exit MYEXIT failed.
Call Karen Smith x3455.
*/
FUNCTION FOO RETURN BOOLEAN IS
BEGIN
srw.reference(:SAL);
srw.user_exit('myexit
sal');
EXCEPTION
when srw.unknown_user_exit then
srw.message(1000, 'User exit MYEXIT failed.
Call Karen Smith x3455.');
raise srw.program_abort;
RETURN (TRUE);
END;
SRW.MESSAGE restrictions
n You
cannot trap nor change Report Builder error messages.
n SRW.MESSAGE
does not terminate the report execution; if you want to terminate a report
after raising a message, use SRW.PROGRAM_ABORT.
n Any
extra spaces in the message string will be displayed in the message; extra
spaces are not removed by Report Builder.
--------------------------------------------------------------
SRW.PROGRAM_ABORT
<> Examples <> Related
Topics <> All
Built-ins
Description This exception stops the report execution and raises the following error
message:
REP-1419: PL/SQL program aborted.
SRW.PROGRAM_ABORT stops report
execution when you raise it.
Syntax
SRW.PROGRAM_ABORT;
Usage Notes You must raise the exception from within
your PL/SQL.
--------
SRW.PROGRAM_ABORT examples
<> Related Topics <> All
Examples
/* Suppose you want to put a border around the
salary if it is greater than 0.
** Suppose, also, that if the report
fetches a salary less than 0, you want to
** raise a customized error message (i.e.,
"FOUND A NEGATIVE SALARY. . ."),
** then terminate the report execution. To
do so, you could write the
** following format trigger for F_SAL.
*/
FUNCTION foo return boolean is
BEGIN
if :sal >= 0 then
srw.attr.mask :=
SRW.BORDERWIDTH_ATTR;
srw.attr.borderwidth := 1;
srw.set_attr
(0, srw.attr);
else
srw.message(100, 'FOUND A NEGATIVE SALARY.
CHECK
THE EMP TABLE.');
raise srw.program_abort;
end if;
RETURN (TRUE);
END;
-------------------------------------------------------------------------------------------------
SRW.RUN_REPORT
<> Restrictions <> Examples <> Related
Topics <> All
Built-ins
Description This procedure invokes RWRUN60 with the string that you specify. This
procedure is useful for:
n running
drill-down reports (i.e., calling a report from a button's action trigger)
n sending
parts of a report to different recipients (e.g., to send a report via e-mail to
each manager with just his or her group's data)
n sending
parts of a report to different printers (e.g., to send each manager's report to
his or her printer)
n running
multiple reports from a single "driver" report
SRW.RUN_REPORT executes the specified
RWRUN60 command.
Syntax
SRW.RUN_REPORT (command_line CHAR);
Parameters
command_line Is
a valid RWRUN60 command.
---------------------
SRW.RUN_REPORT example
<> Related Topics <> All
Examples
/* Suppose you have the following two reports:
** MGR_RUN, which queries manager
names, and invokes a second report named MAIL_IT
**
MAIL_IT, which queries employee names for the manager that MGR_RUN passes it,
** and sends the report output to the manager via e-mail.
** The description of MGR_RUN could be
as follows:
** Query:
SELECT
ENAME, EMPNO FROM EMP WHERE JOB='MANAGER'
** Group Filter:
*/
FUNCTION
FOO RETURN BOOLEAN IS
BEGIN
srw.run_report('report=MAIL_IT
desname='||:ename ||' desformat=dflt batch=yes
mgr_no='||
TO_CHAR(:empno)
);
RETURN
(TRUE);
EXCEPTION
when srw.run_report_failure then
srw.message(30, 'Error mailing reports.');
raise srw.program_abort;
END;
/* This PL/SQL invokes MAIL_IT,
specifies that MAIL_IT's output
** should be sent to the manager via
Oracle Mail, and passes the
** manager number, so that the MAIL_IT report can
query only the
** manager's employees.
** Note: EMPNO's values must be converted
to characters
** (TO_CHAR in the PL/SQL above),
because SRW.RUN_REPORT
** requires a character string.
** Layout: None is needed, because this
report only fetches data,
** then passes it to a second report.
** The description of MAIL_IT could be
as follows:
** Query:
SELECT
DEPTNO, ENAME, SAL FROM EMP WHERE MGR=:MGR_NO
** Layout: Master/Detail
*/
/* Suppose that you have three reports that you
almost always run together.
** The reports are named SALARY,
COMMISS, and TAXES. To run these reports
** with one RWRUN60 command, you create a
driver report named PAYROLL.
** The description of PAYROLL could be
as follows:
** Query:
SELECT
DEPTNO FROM DEPT
** Before Report Trigger:
*/
FUNCTION
FOO RETURN BOOLEAN IS
BEGIN
srw.run_report('batch=yes
report=SALARY
destype=file desformat=dflt desname=salary.lis');
srw.run_report('batch=yes
report=COMMISS
destype=file desformat=dflt desname=comiss.lis');
srw.run_report('batch=yes
report=TAXES
destype=file desformat=dflt desname=comiss.lis');
RETURN
(TRUE);
END;
/* Layout: Tabular
** When you run PAYROLL from the
designer or RWRUN60, the other three
** reports will all be run. (Note
that, in this case, the query and
** the layout for Payroll could be
anything. They
are only used here
**
in order to make it possible to run PAYROLL.)
*/
-------------------------------
SRW.RUN_REPORT restrictions
n If
you want parameter values that are entered on the Runtime Parameter Form to be
passed in the RWRUN60 string, you must call SRW.RUN_REPORT after the before
form trigger.
n The
string that is specified for or passed to this procedure must follow the syntax
and case-sensitivity rules for your operating system.
n No
userid should be specified for the SRW.RUN_REPORT procedure. The
userid is inherited by the "calling" report.
n If
the parent report that invokes SRW.RUN_REPORT is run in batch, then DESTYPE can
only be File, Printer, Sysout, or Mail. Otherwise, DESTYPE can be File,
Printer, or Mail.
n If
SRW.RUN_REPORT is used in the PL/SQL for a button, the Runtime Parameter Form
will not appear by default when the button is selected. If
you want the Runtime Parameter Form to appear, you must specify PARAMFORM=YES
in the call to SRW.RUN_REPORT.
n If
you do not specify a path, Report Builder will use its file path search order
to find the report.
---------------------------------------------------------------------------------------------
SRW.USER_EXIT
<> Restrictions <> Examples <> Related
Topics <> All
Built-ins
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.
Syntax
SRW.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.USER_EXIT example
<> Related Topics <> All
Examples
/* Suppose you have a user exit named STORE to
which you want
** to pass salary values from Report
Builder. To
do so, you
** could write the following
formula. For
more information on
** how to call user exits, see Calling a user
exit.
*/
FUNCTION FOO RETURN BOOLEAN IS
BEGIN
IF
:SAL >= 0 THEN
SRW.REFERENCE(:SAL);
SRW.USER_EXIT('STORE
SAL');
ELSE
SRW.MESSAGE(100, 'FOUND A NEGATIVE SALARY. CHECK THE
EMP TABLE.');
END IF;
EXCEPTION
WHEN
SRW.UNKNOWN_USER_EXIT THEN
SRW.MESSAGE(200, 'STORE USER EXIT WAS UNKNOWN.
CHECK
IF IT''S LINKED.');
WHEN
SRW.USER_EXIT_FAILURE THEN
SRW.MESSAGE(200,
'STORE USER EXIT FAILED.
CHECK
ITS CODE.');
RETURN(TRUE);
END;
---------------------
SRW.USER_EXIT restrictions
n User
exits are not portable. If your report must be portable, and you need to add conditional logic
to it, use PL/SQL.
n If
the user exit string passes a column or parameter to the user exit program,
SRW.REFERENCE must be called before this procedure.
Nice post very helpful
ReplyDeletedbakings