Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1610
  • Last Modified:

Execute Immediate on a very long string causes exception

The SQL is 52,028 characters without spaces and 78,960 with spaces.

I need to use execute immediate so I can define the partition for one of the tables dynamically. This is the only reason that I need the sql to execute in an execute immediate block.

Any thoughts?

ERROR at line 1:
ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer
too small
ORA-06512: at line 1721

Thanks,

Geoff
0
gswitz
Asked:
gswitz
  • 3
2 Solutions
 
sdstuberCommented:
execute immediate can only run a varchar2 string which as a 32K limit
0
 
sdstuberCommented:
using dbms_sql you can build longer commands
0
 
paquicubaCommented:
/* Source -->  http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.htm#sthref6141

To parse SQL statements larger than 32 KB,
DBMS_SQL makes use of PL/SQL tables to pass a table of strings to the PARSE procedure.
These strings are concatenated and then passed on to the Oracle server.

You can declare a local variable as the VARCHAR2S table-item type,
and then use the PARSE procedure to parse a large SQL statement as VARCHAR2S.

The definition of the VARCHAR2S datatype is:
TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;

*/

Declare
L_Query_String   CLOB;
L_Buffer         Constant Binary_integer := 256;
L_String_Len          Integer := 0;
L_Start_Pos      Integer := 1;
L_End_Pos        Integer := 256;
L_CLOB_len       Pls_integer;
L_Execution_Val  Pls_integer;
L_Cursor         Pls_integer := Dbms_sql.Open_cursor;
L_Sql_table      Dbms_sql.Varchar2s;

Begin  
 
  L_Query_String := 'Insert Into Test values(1)';

  L_CLOB_len := Dbms_lob.Getlength(L_Query_String);

  Loop

    If L_String_Len + L_Buffer > L_CLOB_len Then
      L_End_Pos := L_CLOB_len - L_String_Len;
    End If;

    L_Sql_table(Nvl(L_Sql_table.Last, 0) + 1) := Dbms_lob.Substr(L_Query_String, L_End_Pos, L_Start_Pos);

    L_Start_Pos := L_Start_Pos + L_Buffer;
    L_String_Len := L_String_Len + L_End_Pos;

    If L_String_Len >= L_CLOB_len Then
      Exit;
    End If;
   
  End Loop;

   Dbms_sql.Parse(L_Cursor, L_Sql_table, L_Sql_table.First, L_Sql_table.Last, False, Dbms_sql.Native);

   L_Execution_Val := Dbms_sql.Execute(L_Cursor);
   
   Commit;

   Dbms_sql.Close_cursor(L_Cursor);
End;
/
select * from test
0
 
gswitzAuthor Commented:
It was necessary to concat the string into the clob in multiple statements.
lclob:='my ';
lclob:=lclob || ' sql';

Thanks,

G
0
 
sdstuberCommented:
glad we could help
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now