RE: Get Package, Re-write, Re-compile...

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 19 Nov 2004 14:28:52 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F60262119FC6E@irvmbxw02>

When using dbms_sql.parse with the SQL statement being stored in a table of varchar2, the total length of the statement is not limited to 32K. Here is a proof of concept:
create table my_table (column_w_name_of_30_characters date) ; declare

   sqlst dbms_sql.varchar2s ;
   c_dynsql pls_integer ;
   ignore pls_integer ;
begin

   sqlst (nvl (sqlst.last, 0) + 1) := 'create trigger my_table_b4i
before insert on my_table for each row' ;
   sqlst (nvl (sqlst.last, 0) + 1) := 'begin' ;

   for i in 1..1000
   loop

      sqlst (nvl (sqlst.last, 0) + 1) := ' if
:new.column_w_name_of_30_characters > ' || to_char (i * 3) ;

      sqlst (nvl (sqlst.last, 0) + 1) := '   then' ;
      sqlst (nvl (sqlst.last, 0) + 1) := '

:new.column_w_name_of_30_characters :=
:new.column_w_name_of_30_characters ' ;

      sqlst (nvl (sqlst.last, 0) + 1) := ' + ' || to_char (i) || ' ;' ;

      sqlst (nvl (sqlst.last, 0) + 1) := ' end if ;' ;    end loop ;
   sqlst (nvl (sqlst.last, 0) + 1) := 'end my_table_b4i ;' ;    c_dynsql := dbms_sql.open_cursor ;
   dbms_sql.parse (c => c_dynsql,

                   statement => sqlst,
                   lb => sqlst.first,
                   ub => sqlst.last,
                   lfflg => true,
                   language_flag => dbms_sql.native
                  ) ;

   ignore := dbms_sql.execute (c => c_dynsql) ;    dbms_sql.close_cursor (c => c_dynsql) ; exception

   when others
   then

      if dbms_sql.is_open (c => c_dynsql)
      then
         dbms_sql.close_cursor (c => c_dynsql) ;
      end if ;
      raise ;

end ;
/
set long 1000000
select trigger_body
 from user_triggers
 where trigger_name = 'MY_TABLE_B4I' ;

-----Original Message-----
Post, Ethan

Think the 9i docs say the same thing but just after the section on that it implies the limit for both is 32K and it didn't sound like per line, of course the Oracle docs could be wrong..guess I need to try it though.

-----Original Message-----
Stephane Faroult [mailto:sfaroult_at_roughsea.com <mailto:sfaroult_at_roughsea.com> ]

Perhaps something to try. Notwithstanding the fact that you must be careful when executing DDL with the dbms_sql package, there is (at least

in the 10G version) an overloaded 'parse' function which takes an array of varchar2 in lieu of the customary single varchar2 as the text for the statement (basically, you have an array with one row per line of statement).

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 19 2004 - 16:35:10 CST