Oracle
Reports is shipped with a collection of PL/SQL constructs that contain
many
functions, procedures, and exceptions that you can reference in your
libraries
or reports. The name of Oracle Reports'
package is SRW. Therefore,
anytime
you reference a construct in the SRW package, you must prefix it with
SRW
(such as, SRW.DO_SQL).
SRW
Packages
Oracle
Reports contains the following packages:
- SRW.BREAK - SRW.RUN_REPORT_BATCHNO
- SRW.CONTEXT_FAILURE -
SRW.SET_FIELD_CHAR
- SRW.DO_SQL - SRW.SET_FIELD_DATE
- SRW.DO_SQL_FAILURE - SRW.SET_FIELD_NUM
- SRW.GETERR_RUN - SRW.SET_MAXROW
- SRW.GET_PAGE_NUM - SRW.TRACE_ADD_OPTION
- SRW.INTEGER_ERROR - SRW.TRACE_END
- SRW.MAXROW_INERR - SRW.TRACE_REM_OPTION
- SRW.MAXROW_UNSET - SRW.TRACE_START
- SRW.MESSAGE - SRW.TRUNCATED_VALUE
- SRW.NULL_ARGUMENTS - SRW.UNKNOWN_QUERY
- SRW.PROGRAM_ABORT - SRW.UNKNOWN_USER_EXIT
- SRW.REFERENCE - SRW.USER_EXIT
- SRW.RUN_REPORT - SRW.USER_EXIT20
- SRW.SET_ATTR - SRW.USER_EXIT_FAILURE
- SRW.RUN_REPORT_FAILURE
Commonly
Used SRW Packages And Examples
SRW.DO_SQL
executes a specified SQL statement. This
procedure executes
any
DDL or DML statements. However, DML
statements are usually faster
when
they are in PL/SQL than when executed via SRW.DO_SQL. Since you
cannot
perform a DDL statement in PL/SQL, this packaged procedure is useful
for
performing them within Reports rather than using a user exit.
Avoid
DDL statements that modify the tables on which the report is based. A
snapshot
of the tables is taken prior to report execution and must remain
valid
throughout the execution of the report.
Example
1 - SRW.DO_SQL
Syntax/Explanation:
SRW.DO_SQL(sql_statement CHAR); -- Executes
specified SQL statement.
SRW.DO_SQL_FAILURE; -- Stops report execution
upon SRW.DO_SQL failure.
SRW.MESSAGE(msg_number NUMBER, msg_text
CHAR);
-- Displays a specified
message and message number.
SRW.PROGRAM_ABORT; -- Stops execution of report when raised.
Example:
Create
a temporary table named TEMP prior to the Runtime Parameter Form:
FUNCTION
createtab RETURN BOOLEAN IS
BEGIN
SRW.DO_SQL(CREATE TABLE temp (empno NUMBER
NOT NULL PRIMARY KEY,
sal NUMBER (10,2))
PCTFREE 5 PCTUSED
75');
RETURN TRUE;
EXCEPTION
WHEN SRW.DO_SQL_FAILURE THEN
SRW.MESSAGE(100,'ERROR WHILE CREATING
CHECK TABLE.');
SRW.MESSAGE(50, 'REPORT WAS STOPPED
BEFORE THE PARM FORM.');
RAISE SRW.PROGRAM_ABORT;
END;
Example
2 - SRW.RUN_REPORT
Syntax/Explanation:
SRW.RUN_REPORT(command_line CHAR); -- Executes specified R25RUN command
SRW.RUN_REPORT_FAILURE; -- Stops report execution when failure of
-- SRW.RUN_REPORT
occurs.
Example:
Drill
Down Report called from a report button:
FUNCTION
foo RETURN BOOLEAN IS
BEGIN
SRW.RUN_REPORT('report=REP_A
p_parm1=20'); -- calls report REP_A
and
EXCEPTION --
display to screen;
WHEN SRW.RUN_REPORT_FAILURE THEN -- passes value 20 to the
SRW.MESSAGE(100, 'ERROR CALLING
REPORT.') -- p_parm1 parameter
RAISE SRW.PROGRAM_ABORT;
RETURN TRUE;
END;
Note
that you can only call another report to the screen using SRW.RUN_REPORT
from
a button. If you issue SRW.RUN_REPORT
from a report trigger, you
must
pass BATCH=YES. Therefore, DESTYPE can
only be FILE, PRINTER, or MAIL.
Example
3 - SRW.SET_ATTR
Syntax/Explanation
SRW.SET_ATTR
applies attribute settings, such as font size or color, to layout
objects. This procedure applies formatting attributes
to the current frame,
repeating
frame, field, or boilerplate object. You
specify which formatting
attributes
should be applied to an object by defining a mask. A mask is a
collection
of attributes; each attribute has one or more values. You can
change
a number of attributes, such as the borderwidth, background border
color,
border pattern, foreground border color, fill pattern, global text
color,
weight and several other attributes. For
a complete list, refer to
the
"The Oracle Reports PL/SQL Package: SRW" section in the
"PL/SQL
Interface" chapter of the Oracle Reports Reference Manual.
SRW.SET_ATTR(object_id
NUMBER, attr SRW.GENERIC_ATTR);
object_id Is always zero. (The object must always set its own
attributes.)
attr Is srw.attr (that is, the attributes to
change and their values).
Example:
If
salary equals 2000, this code segment sets the following attributes:
Border width = 1
Foreground border color = red
Background border color = blue
Border pattern = checker
Foreground fill color = yellow
Background fill color = green
Fill pattern = crisscross
IF
:sal = 2000 THEN
SRW.ATTR.MASK :=
SRW.BORDERWIDTH_ATTR +
SRW.FBCOLOR_ATTR +
SRW.BBCOLOR_ATTR +
SRW.BORDPATT_ATTR +
SRW.FFCOLOR_ATTR +
SRW.BFCOLOR_ATTR +
SRW.FILLPATT_ATTR;
SRW.ATTR.BORDERWIDTH := 1;
SRW.ATTR.FBCOLOR := 'red';
SRW.ATTR.BBCOLOR :=
'blue';
SRW.ATTR.BORDPATT := 'checker';
SRW.ATTR.FFCOLOR := 'yellow';
SRW.ATTR.BFCOLOR := 'green';
SRW.ATTR.FILLPATT := 'crisscross';
SRW.SET_ATTR(0,
SRW.ATTR);
END
IF;
RETURN
TRUE;
Example
4 - SRW. SET_FIELD
Syntax/Explanation:
SRW.SET_FIELD_CHAR(object_id,
text CHAR);
SRW.SET_FIELD_DATE(object_id,
date DATE);
SRW.SET_FIELD_NUM(object_id,
number NUMBER);
These
SRW SET_FIELD procedures are very useful in format triggers. They are
often
used to change the data that will be displayed in a particular item
based
on a specific condition. These are
invaluable, since you cannot use
assignment
statements to set a field's value in Reports.
SRW.SET_FIELD_XXX
must
be used to accomplish this.
Example:
To
display the text "LOW" in the salary_range field for every employee
whose
salary
is less than 20,000, create the following format trigger on the
salary_range
field.
FUNCTION
chgfield RETURN BOOLEAN IS
BEGIN
IF :sal < 20000 THEN
SRW.SET_FIELD_CHAR(0, 'LOW');
ELSE
END IF;
RETURN
TRUE;
END;
For
additional examples using the SRW.SET_FIELD_XXXX procedures in format
triggers,
see:
Note31364.1 Using Format Triggers in
Reports.
