SRW Package in Oracle Report

SRW Package in Oracle Report

20 May

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;

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:

FUNCTION CHGFIELD RETURN BOOLEAN IS

TMP NUMBER;

 BEGIN

  if :sal >= 5000 then

  tmp := :sal * 1.10;

  srw.set_field (0, tmp);

 else

  srw.set_field (0, 4000);

 end if;

 RETURN (TRUE);

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’);

Use of SRW Package in Report

SRW Package:

The report execution can be controlled

Display messages at run time

Layout field can be initialized

Formatting can be done

Make the user to exit report

DDL statements can be performed

Displaying messages:

The SRW.MESSAGE procedure displays the message. It also displays the code and text that the user has specified.

The message text is displayed in a small dialog box in the following syntax.

Msg-code: Text

Code: digits from 0 – 10

Text: Max 190 characters

Warning or Error Messages:

SRW.MESSAGE will not terminate the user from continuing. So this can be used for displaying warning messages.

SRW.PROGRAM_ABORT will terminate the program after displaying the message

RAISE SRW.PROGRAM_ABORT;

Running nested reports:

For running one report from another report the user has to use SRW.RUN_REPORT

Go to layout editor, create a button and label it as ‘Click this button’

Open the property palette of the button and set the Button Behaviour: Type=pl/sql: Pl/sql Trigger= provide the code

procedure U_ButtonButtonAction is

begin

  srw.run_report('module=C:\Documents and Settings\Administrator\Desktop\POXDETIT.rdf destype=Screen');

end;

Restricting data:

the srw.set_maxrow procedure sets the max number of records that can be retrieved for a specific query.

In the BEFORE REPORT trigger, you could use the srw.set_maxrow procedure to ensure that only the required num of records are fetched.

begin

                                    if :p_dname = 'RESEARCH' then

                                                srw.set_maxrow ('Q_2', 2);

                                                end if;

                                    end;

Using DDLs in Reports:

If the user has to execute any SQL Statement from the report builder then the srw.do_sql procedure is to be used.  With the help of SRW.DO_SQL the user can add any DDL or DML operation to the report.

DML are faster in pl/sql, instead in DO_SQL. Since DDLs can not be performed in pl/sql we make use DO_SQL in report builder.

Srw.do_sql (sqlstatement char);

Setting Format Attributes:

Use the SRW.SET_ procedure to set the format attributes. Mainly used to make some change in the appearance of the layout objects.

Format attributes	Value
Text Color	Red
Font Face	Impact
Font Weight	Bold
Font Style	Italic
Font Pattern	Solid diamond

Function age_mask return Boolean is

Begin

If :age < 25 then

Srw.set_text_color(‘Red’);

Srw.set_font_face(‘Impact’) ;

Srw.set_font_weight(‘’srw.bold_weight) ;

Srw.set_font_style(‘srw.italic_style’);

Srw.set_fill_pattern(‘solid diamond’);

End if;

End;