Help to execute a dynamic SQL in Oracle 11g

    I have a variable that is defined as CLOB.
var1 clob;
Then i have several queries concatenated based on some conditions, like something below:
var1 := 'insert into temp select a,b,c from x,y,z';
var1:= rtrim ( var1|| ' '|| ' x.a = : a1 ');
var1:= rtrim ( var1|| ' '|| ' y.b1 =  :b1 ');
and so on..

At the end I have execute immediate var1 using a1, b1..

When I use PLSQL block to test it works fine. Then I tried to put the same in a stored  procedure and try to execute the procedure.
But the procedure execution gives ''ORA-01461: can bind a LONG value only for insert into a LONG column'' error..
I checked the length of the CLOB variable after all cancatenations and its coming to around 4000 - 5000 characters..

Can you guys help me? do not know if I am doing something wrong or missing something..


Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

flow01Connect With a Mentor Commented:
What is the variable definition of a1, b1 ?
If you don't need a length greater then 32767  bytes define var1 as varchar2(32767).
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I don't believe this error has to do with the length of the SQL.  Oracle thinks you are trying to insert a LONG datatype into a non-LONG column.

Check the insert and bind values themselves.
POracleConnect With a Mentor Commented:
var1 varchar2(8000);
var1:='select .............';
execute immediate var1;
paeddyAuthor Commented:
Thanks very much guys.
I could resolve the issue just after putting this question in. The problem was that the procedure input parameter was declared as IN OUT CHAR (p_var in out char). The p_var actual datatype definition is CHAR(1). When I am trying to insert this IN OUT variable into a temp table of CHAR(1) size, this error was occurring..

Thanks very much for all your prompt responses..
slightwv (䄆 Netminder) Commented:
Feel free to accept your last post as the answer.
All Courses

From novice to tech pro — start learning today.