SRW (Sql Report Writer) Package is a built in package in Oracle
Reports Builder. It is 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. There are nearly 70 functions, procedures, and
exceptions are there in this package. Here I am giving brief information and
uses of few important functions, procedures, and exceptions.
SRW.MESSAGE:
It is a Procedure that displays a message with the message number
and text that you specify. It is mainly used to debug a report in Reports
Builder.
SRW.MESSAGE(msg_number NUMBER, msg_text CHAR);
Example:
|
01
|
function
foo return boolean is
|
|
|
02
|
begin
| |
|
03
|
if :sal < 0 then
|
|
|
04
|
SRW.MESSAGE(100,
'Found a negative salary. Check the EMP table.');
|
|
|
05
|
raise SRW.PROGRAM_ABORT;
|
|
|
06
|
else
|
|
|
07
|
:bonus := :sal * .01;
|
|
|
08
|
end if;
|
|
|
09
|
return(true);
|
|
|
10
|
end;
|
|
SRW.PROGRAM_ABORT:
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.
SRW.DO_SQL:
This procedure executes the specified SQL statement from within
Reports Builder. The SQL statement can be DDL (statements that define data), or
DML (statements that manipulate data). DML statements are usually faster when
they are in PL/SQL, instead of in SRW.DO_SQL.
Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL
packaged procedure is especially useful for performing them within Reports
Builder.
Example:
|
01
|
FUNCTION
CREATETABLE RETURN BOOLEAN IS
|
|
|
02
|
BEGIN
|
|
|
03
|
SRW.DO_SQL('CREATE
TABLE TEST_EMP (EMPNO NUMBER NOT NULL
|
|
|
04
|
PRIMARY
KEY, SAL NUMBER (10,2)) PCTFREE 5 PCTUSED 75');
|
|
|
05
|
RETURN (TRUE);
|
|
|
06
|
EXCEPTION
|
|
|
07
|
WHEN SRW.DO_SQL_FAILURE
THEN
|
|
|
08
|
SRW.MESSAGE(100,
'ERROR WHILE CREATING TEST_EMP TABLE.');
|
|
|
09
|
RAISE
|
|
|
10
|
SRW.PROGRAM_ABORT;
|
|
|
11
|
END;
|
SRW.DO_SQL_FAILURE:
Reports Builder raises this exception when the SRW.DO_SQL packaged
procedure fails. This exception stops the report execution and raises the
following error message:
REP-1425: Error running DO_SQL package – REP-msg ORA-msg.
SRW.GET_REPORT_NAME:
This function returns the file name of the report being executed.
SRW.GET_REPORT_NAME (report_name);
Example:
|
01
|
function
AfterPForm return boolean is
|
|
|
02
|
my_var
varchar2(80);
|
|
03
|
BEGIN
|
|
|
04
|
SRW.GET_REPORT_NAME
(my_var);
|
|
|
05
|
SRW.MESSAGE(0,'Report
Filename = '||my_var);
| |
06
|
RETURN (TRUE);
|
|
|
07
|
END;
|
SRW.RUN_REPORT:
This procedure synchronously executes the specified report within
the context of the currently running report.
SRW.RUN_REPORT (“report=test.rdf … “)
SRW.SET_FIELD:
This procedure sets the value of a character, number, or date
field. This is useful when you want to conditionally change a field’s value.
SRW.SET_FIELD (object_id, text CHAR | number NUM | date DATE);
Example:
Suppose you want to conditionally change the number of a field,
based on each employee’s salary. In the format trigger for the field, you could
type the following:
|
01
|
FUNCTION
CHGFIELD RETURN BOOLEAN IS
|
|
|
02
|
TMP NUMBER;
|
|
|
03
|
BEGIN
|
|
|
04
|
if
:sal >= 5000 then
|
|
|
05
|
tmp
:= :sal * 1.10;
|
|
|
06
|
srw.set_field
(0, tmp);
|
|
|
07
|
else
|
|
|
08
|
srw.set_field
(0, 4000);
|
|
|
09
|
end if;
|
|
|
10
|
RETURN (TRUE);
|
|
|
11
|
END;
|
SRW.SET_FIELD should be used only to change the contents of a
field’s datatype, not change the field to a different datatype.
Others in Brief:
- SRW.SET_FONT_FACE:
This procedure specifies font face for a CHAR, DATE, or NUMBER field.
SRW.SET_FONT_FACE(‘arial’);
- SRW.SET_FONT_SIZE:
This procedure specifies font size for a CHAR, DATE, or NUMBER field.
SRW.SET_FONT_SIZE(10);
- SRW.SET_FONT_STYLE:
This procedure specifies font style for a CHAR, DATE, or NUMBER field.
SRW.SET_FONT_STYLE(SRW.ITALIC_STYLE);
- SRW.SET_FORMAT_MASK:
This procedure specifies the format mask for the DATE or NUMBER field.
SRW.SET_FORMAT_MASK(‘mask’);
- SRW.SET_TEXT_COLOR:
This procedure specifies the global text color of the CHAR, DATE, or
NUMBER field. SRW.SET_TEXT_COLOR(‘color’);
