Using SRW Packaged Procedures In Reports (Doc ID 61643.1)

Checked for relevance on 19-APR-2013

This article covers Reports versions 2.5 up to 11gR2.


                  Using SRW Packaged Procedures in Reports
               

Introduction
------------

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:

   Note 31364.1  Using Format Triggers in Reports.


-------------------------------------------------------------------------------
                                              Oracle WorldWide Customer Support