Executing Dynamic Query stored in CLOB variable

[engineer2100]

I Want to execute Dynamic select query stored in the CLOB variable from Stored procedure.

SQL Query length is more then 1GB. How can I do that?

Please let me know. Thanx in advance.

Engi

 

[SantaMufasa]

Engi,

Here is a super-simplified scenario that is a proof of concept for your specifications except for the 1GB-SQL-statement length:

desc engi

 Name                    Null?    Type
 ----------------------- -------- -------------
 ID                      NOT NULL NUMBER(7)
 NAME                    NOT NULL VARCHAR2(50)

declare
    stm varchar2(32767);
begin
    select x into stm from clobber;
    dbms_output.put_line(stm);
    execute immediate stm;
end;
/
alter table engi add x number

PL/SQL procedure successfully completed.

SQL> desc engi
 Name                    Null?    Type
 ----------------------- -------- ------------
 ID                      NOT NULL NUMBER(7)
 NAME                    NOT NULL VARCHAR2(50)
 X                                NUMBER

So, the above code executed successfully from a CLOB source. I cannot imagine that Oracle will execute a SQL statement that is 1GB. In the code, above, longest theoretically executable SQL statement is 32K.

A 1GB SQL statement is pretty outrageous. Could you please post an explanation of what such a statement is doing and why it must be so long? Perhaps we can devise an executable alternative.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[engineer2100]

Greetings my Guru,

We are involved in a task which creates extracts based off a Materialized view and a Master data Table. Phase I of this task has around 213 columns. Now to get the updated rows between the MV's and the MDT, we are building dynamic SQL query. Since we need to select these 213 columns and then also have the WHERE clause built to pull out the changed rows, we anticipate the SQL query length to grwo beyond 32k length.

Yeah I understand that 1 GB is somewhat outrageous, but this number of columns would grow to 1000 - 1500 columns.

-Engi