Help to execute a dynamic SQL in Oracle 11g

Posted on 2011-05-10
Last Modified: 2012-05-11
    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..


Question by:paeddy
    LVL 20

    Accepted Solution

    What is the variable definition of a1, b1 ?
    If you don't need a length greater then 32767  bytes define var1 as varchar2(32767).
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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.
    LVL 8

    Assisted Solution

    var1 varchar2(8000);
    var1:='select .............';
    execute immediate var1;

    Author Comment

    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..
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Feel free to accept your last post as the answer.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now