A script-enabled browser is required for this page to function properly.

SRW.DO_SQL built-in procedure

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, instead of with a user exit. For more information on DDL or DML statements, see the Oracle Server SQL Language Reference Manual.

Syntax


SRW.DO_SQL (sql_statement CHAR);

Parameters

Parameter

Description

sql_statement

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

Usage notes

Rationale

Just like queries, SRW.DO_SQL statements are parsed, open a cursor to the database, and execute the statement. Unlike queries, however, an SRW.DO_SQL statement will do those things each time its owner (for example, a group) fetches data. (For example, if your SRW.DO_SQL statement is owned by a group that fetches 10 records, the statement will be parsed 10 times, 10 cursors will be opened, and the statement will be executed 10 times.) For this reason, computations (for example, :sal + :com) should be performed within the query or PL/SQL, instead of SRW.DO_SQL owned by a group.

Restrictions

SRW.DO_SQL('SELECT ENAME INTO :my_ename FROM EMP');

Examples

Example 1: 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, Reports 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; 

Example 2: Suppose you want to create a "table of contents" by getting the first character of a column's value, and page number on which its field fires to print. Assume that you want to put the "table of contents" into a table named SHIP. You could write the following construct:


DECLARE
 PAGE_NO NUMBER; 
 PAGE_FOR INDEX NUMBER; 
 SORT_CHAR CHAR(1);
 CMD_LINE CHAR(200); 


BEGIN 
  SORT_CHAR := :SORT_NAME ; 
  IF :CALLED = 'Y' THEN 
   SRW.GET_PAGE_NUM(PAGE_FOR_INDEX);
   SRW.USER_EXIT('RWECOP PAGE_FOR_INDEX 
   P_START_PAGENO'); 
   SRW.MESSAGE(2,TO_CHAR(:P_START_PAGENO)); 
 END IF; 
  SRW.GET_PAGE_NUM(PAGE_NO); 
  CMD_LINE := 'INSERT INTO SHIP VALUES
  ('''||SORT_CHAR||''','||TO_CHAR(PAGE_NO)||')'; 
  SRW.MESSAGE(2,CMD_LINE); 
  SRW.DO_SQL(CMD_LINE); 
 COMMIT;


 EXCEPTION 
  WHEN DUP_VAL_ON_INDEX THEN 
  NULL; 
  WHEN SRW.DO_SQL_FAILURE THEN
   SRW.MESSAGE(1,'FAILED TO INSERT ROW INTO SHIP TABLE'); 
  WHEN OTHERS THEN 
  COMMIT; 
END; 

See also

About the Reports Builder built-in package (SRW)

SRW built-in package

About DML and DDL