SRW.DO_SQL
built-in procedureThis 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.
SRW.DO_SQL (sql_statement CHAR);
Parameter |
Description |
|
Is any valid SQL statement. Remember to precede any Reports Builder object names with a colon (:). |
SRW.DO_SQL
only when necessary.
In those cases where it is necessary, assign it to a group that fetches only
a few records, or to a report-level object (which will parse, open a cursor,
and execute only once). SRW.DO_SQL
enables you to add any DDL
or DML operation to your report. This functionality is very valuable, but
it can also be very expensive if used unwisely.
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.
Rationale:
Writing DML statements in PL/SQL is faster than an SRW.DO_SQL
call containing the same statement. The reason to use SRW.DO_SQL
for DML statements is that it can concatenate bind parameters to construct
the DML statement. For example, you can have SRW.DO_SQL
create
a table whose name is determined by a parameter entered on the Runtime Parameter
Form:
SRW.DO_SQL
('CREATE TABLE' || :tname || '(ACCOUNT NUMBER
NOT NULL PRIMARY KEY, COMP NUMBER (10,2))');
Note: You can also use the dbms_sql
package that comes
with ORACLE 7.1 or later for DML. Refer to your ORACLE7 documentation for
more information.
In the 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 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;
About the
Reports Builder built-in package (SRW
)
Copyright © 1984, 2005, Oracle. All rights reserved.