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

From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 19 Nov 2004 14:28:52 -0800

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@xxxxxxxxxxxx
<mailto:sfaroult@xxxxxxxxxxxx> ]

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). 


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

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

Other related posts: