SRW Package in Oracle Reports

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:

FUNCTION foo
   RETURN BOOLEAN
IS
BEGIN
   IF :sal < 0
   THEN
      srw.MESSAGE (100, 'Found a negative salary. Check the EMP table.');
      RAISE srw.program_abort;
   ELSE
      :bonus := :sal * .01;
   END IF;

   RETURN (TRUE);
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:

FUNCTION createtable
   RETURN BOOLEAN
IS
BEGIN
   srw.do_sql
      ('CREATE TABLE TEST_EMP (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 TEST_EMP TABLE.');
      RAISE srw.program_abort;
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:

FUNCTION afterpform
   RETURN BOOLEAN
IS
   my_var   VARCHAR2 (80);
BEGIN
   srw.get_report_name (my_var);
   srw.MESSAGE (0, 'Report Filename = ' || my_var);
   RETURN (TRUE);
END;

Continue>>>