?
Solved

execute immediate with clob

Posted on 2007-07-25
11
Medium Priority
?
4,077 Views
Last Modified: 2013-12-07
I am using oracle 10G database. I am trying to execute dynamically created sql using execute immediate. The sql text formed were having length approximately 90000 characters. The query generated were stored using datatype CLOB. When program try to execute the query using execute immediate its throwing error. Is there any way to execute query using execute immediate?
0
Comment
Question by:CRISIL
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19568169
How are you passing the query to the DB server?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19568440
I'm assuming you are getting an error similar to:  'string literal too long' or something similar?

I believe that you are bound to the 32K limit for string literals in PL/SQL.  Depending on what the generated SQL looks like, you may be able to use temp tables and/or PL/SQL tables.

If you can provide a little more information we may be able to help you.  Sample data and expected results would help a lot.
0
 
LVL 8

Expert Comment

by:YANN0S
ID: 19569151
Native dynamic SQL does not support SQL statements larger than 32K. You can use DBMS_SQL package:
(http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg09dyn.htm#26741)
"DBMS_SQL Supports SQL Statements Larger than 32KB
The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not."
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 22

Expert Comment

by:JimBrandley
ID: 19569301
I send clobs without problems, and can do so with dynamic SQL. Just:
Begin
Insert into mytable (myKey, myClob) values ( :p1, :p2);
End;

Just pass the clob as a parameter. That's why I asked about how you were passing the query.
0
 

Author Comment

by:CRISIL
ID: 19574721
Sorry for not mentioning the problem clearly.
i am storing the queries in a table bi_sql_scipt having columns id NUMBER and sql_qry CLOB.
User enter the filter condition for the query using input screen. the query is then generated dynamically through PL-SQL based on these condition and stored in bi_sql_script table.
All the queries are generated as DML statements (i.e. insert into tbl_1 (select a, b, c from tb_a, tb_b where a.a= b.a) ).
Now i am using following code to execute those queries.

declare
cursor cur_qry is
select id, sql_qry
from bi_sql_scipt ;
begin
for v_qry in cur_qry loop
    execute immediate  to_char( v_qry.sql_qry );
    commit;
end loop;
end;

but i am getting error ORA-06502: PL/SQL: numeric or value error

Also i have tried using DBMS_SQL package but getting the same error.
0
 
LVL 8

Expert Comment

by:YANN0S
ID: 19575507
Can you post the DBMS_SQL code?
0
 

Author Comment

by:CRISIL
ID: 19575752
               DECLARE
                    v_cur_hdl         INTEGER;
                    v_rows_processed  BINARY_INTEGER;
                BEGIN
                   --
                   -- v_doc_12
                   --
                   v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
                   DBMS_SQL.PARSE(v_cur_hdl, TO_CHAR(v_ins_qry), DBMS_SQL.NATIVE);
                   v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
                   DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
                   COMMIT;
                    --
                EXCEPTION WHEN OTHERS THEN
                    --
                    DBMS_OUTPUT.PUT_LINE('ERROR IS:' || SQLERRM);
                    --
                END;
0
 
LVL 8

Expert Comment

by:YANN0S
ID: 19575890
In which line do you get the error message?
0
 
LVL 8

Expert Comment

by:YANN0S
ID: 19575895
Sorry to get the line remove the EXCEPTION handler
0
 
LVL 8

Accepted Solution

by:
YANN0S earned 2000 total points
ID: 19576087
You are not doing anything different with DBMS_SQL. You are converting the CLOB TO_CHAR. This actually truncates the statement:

"In the SQL environment, only the first 4K bytes of the CLOB are converted and used in the operation; in the PL/SQL environment, only the first 32K bytes of the CLOB are converted and used in the operation"
(from http://download.oracle.com/docs/cd/B12037_01/appdev.101/b10796/adlob_sq.htm#1011243)

There is an overloaded DBMS_SQL.PARSE procedure that gets an array of strings.
(http://download-uk.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_sql.htm#997677)

In the following link you can find an example of that
http://www.psoug.org/reference/dbms_sql.html
(search for "Executing CLOBS Demo Tables" and check until "Executing CLOBS Demo Data"
0
 

Author Comment

by:CRISIL
ID: 19578033
Thanks YANN0S,

Those links were very useful.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses
Course of the Month17 days, 8 hours left to enroll

830 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