Solved

Execute Immediate on a very long string causes exception

Posted on 2008-10-30
5
1,583 Views
Last Modified: 2013-12-19
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
Comment
Question by:gswitz
  • 3
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 22842812
execute immediate can only run a varchar2 string which as a 32K limit
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 50 total points
ID: 22842822
using dbms_sql you can build longer commands
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 450 total points
ID: 22842948
/* 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
 

Author Closing Comment

by:gswitz
ID: 31511741
It was necessary to concat the string into the clob in multiple statements.
lclob:='my ';
lclob:=lclob || ' sql';

Thanks,

G
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22843278
glad we could help
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
passing parameters to sql script oracle 4 60
constraint check 2 48
pl/sql - query very slow 26 73
oracle sqlplus query delimiter 8 20
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question