Re: DBMS_SQL will not work with SQL statement > 32K

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 04 Dec 1999 08:46:40 -0500
Message-ID: <ko6i4s03fhde0pfduqjp22riqvdgglcdmf@4ax.com>

A copy of this was sent to kjhealey_at_my-deja.com (if that email address didn't require changing) On Fri, 03 Dec 1999 20:04:49 GMT, you wrote:

>Is there any way to execute dynamic DDL from PL/SQL when the DDL
>statement is larger than 32K?
>
>We are writing a PL/SQL function that will create Audit Trail like
>Triggers for all of the tables in our database.
>
>The PL/SQL function creates "CREATE TRIGGER..." statements based on
>information in the system_tables and then executes the statements using
>the DBMS_SQL package.
>
>The problem is that in some cases, the "CREATE TRIGGER..." statement
>becomes larger than 32K and cannot fit in a VARCHAR2 or be passed to the
>DBMS_SQL.PARSE function.
>

dbms_sql.parse is overloaded. one takes a single string, the other an array of strings (to overcome 32k). here is an example of the other method:

declare

    l_stmt          dbms_sql.varchar2s;
    l_cursor        integer default dbms_sql.open_cursor;
    l_rows          number  default 0;

begin

    l_stmt(1) := 'create';
    l_stmt(2) := 'table';
    l_stmt(3) := 'foo ( x int primary key )';

    dbms_sql.parse( c             =>   l_cursor,
                    statement     => l_stmt,
                    lb            => l_stmt.first,
                    ub            => l_stmt.last,
                    lfflg         => TRUE,
                    language_flag => dbms_sql.native );

    l_rows := dbms_sql.execute(l_cursor);

    dbms_sql.close_cursor( l_cursor );
exception

    when others then

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

end;
/

>Any ideas?
>
>Thanks,
>Kevin
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Dec 04 1999 - 07:46:40 CST