srw-do_sql

Description  This procedure executes the specified SQL statement from within Report 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 Report Builder, instead of via a user exit.  For more information on DDL or DML statements, see the ORACLE8 Server SQL Language Reference Manual.

Syntax           SRW.DO_SQL (sql_statement CHAR);

Parameters

sql_statement Is any valid SQL statement.  Remember to precede any Report Builder object names with a colon (:).

Restrictions

·         In Report trigger order of execution, notice where the SET TRANSACTION READONLY occurs.

·         A bind variable’s value can be at most 64,000 bytes.  (When the value exceeds that limit, it will be truncated to the left-most 64,000 bytes.)

·         If you use a parameter as the destination of a character column for an INTO clause, you should ensure that the parameter is wide enough to contain the selected values.  For example, suppose that you have the SRW.DO_SQL statement below: The destination parameter (my_ename) needs a width that is equal to the maximum width of the ENAME column.  The reason for this is that the selected value contains trailing spaces up to the assumed size of the value.  If the parameter is not large enough, you will get a truncation exception.  If you are not sure about the maximum width of the SELECT list item, then you should use 2000 as the width for the parameter.

srw.do_sql(‘SELECT ENAME INTO :my_ename FROM EMP’);

Example

/* Suppose you want your report to create a table named CHECK ** just before the Runtime Parameter Form is displayed.  ** Because CREATE TABLE is a SQL DDL statement (and PL/SQL ** cannot perform DDL statements), you need to use SRW.DO_SQL.  ** Therefore, your PL/SQL could look like this in the Before Form trigger:*/

/* Additional Information: If you use a table created in this way for your** report output, the table must exist before you create your query in the ** data model. Otherwise, Report Builder would not be able to parse your query.*/

FUNCTION CREATETAB RETURN BOOLEAN IS

BEGIN

SRW.DO_SQL(‘CREATE TABLE CHECK (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 RUNTIME

 PARAMETER FORM.’);

 RAISE SRW.PROGRAM_ABORT;

END;