Using SRW Packaged Procedures in Reports

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.