Re: Dynamic procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 09 Jun 1998 16:48:30 GMT
Message-ID: <3580669a.16083617@192.86.155.100>

A copy of this was sent to a_at_a.com (a)
(if that email address didn't require changing) On Tue, 09 Jun 1998 13:51:25 GMT, you wrote:

>Hi Robert,
>
>Thanks for your reply.
>
>I was thinking of using dynamic SQL to execute an anonymous PL/SQL
>block but I can't guarantee that the block will fit into a
>VARCHAR2(2000) variable which I assume must be passed to the dynamic
>SQL command (unless I can use a LONG somehow).
>

No, you can pass upto 32k into dbms_sql.parse. For example, I just ran:

create or replace procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

exception

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end;
/

declare

    p_long_string varchar2(32000) default 'declare

    x varchar2(32000) default ''

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

(above line repeated lots of times

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'';

begin

    dbms_output.put_line( ''length of x is '' || length(x) ); end;';
begin

    execute_immediate(p_long_string);
end;
/

And the output of the dynamically executed block was:

Procedure created.

length of x is 29716

PL/SQL procedure successfully completed.

So, the dbms_sql.parse call did at least 29k....

If you need more then 32k and you are using 7.3 or up, you will find that dbms_sql.parse comes in 2 types now:

SQL> desc dbms_sql.parse

PROCEDURE dbms_sql.parse

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 C                              NUMBER(38)              IN
 STATEMENT                      VARCHAR2                IN
 LANGUAGE_FLAG                  NUMBER(38)              IN

PROCEDURE dbms_sql.parse
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 C                              NUMBER(38)              IN
 STATEMENT                      TABLE OF VARCHAR2(256)  IN
 LB                             NUMBER(38)              IN
 UB                             NUMBER(38)              IN
 LFFLG                          BOOLEAN                 IN
 LANGUAGE_FLAG                  NUMBER(38)              IN

the second one takes a table of VARCHAR2(256) strings that will be 'glued' together, giving you more the 32k of text as well...

>So, I was thinking that I could write the anonymous block out to file
>and then, from inside PL/SQL, I could invoke that anonymous block.
>
>Do you have any ideas on how this could be done ?
>
>Your help is much appreciated.
>
>Natalina.
>
>
>On Mon, 08 Jun 1998 21:57:06 GMT, "Robert Gauthier"
><rogaut1_at_videotron.ca> wrote:
>
>>Hi Natalina,
>>
>>From your requirements, you seem to need to
>>use Dynamic SQL (DBMS_SQL package). This
>>package allows you to write dynamic SQL and
>>is available since Oracle 7.0.
>>
>>Hope this help !
>>
>>Robert Gauthier
>>DBA, Application Designer
>>Montréal, Canada
>>
>>
>>
>>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jun 09 1998 - 11:48:30 CDT