i have a sql query of length 375019. I used a clob variable to store the query as i cannot use the varchar2 type variable. When i try to execute the statement " open ref_cur for v_clob", it throws an error . Is this something doable? Kindly help!


SQL>create table clob_test( id int, text clob);


DECLARE
l_clob CLOB;
l_text varchar2 (50) := ' UNION SELECT * from dual';
BEGIN
DBMS_LOB.createtemporary (l_clob, TRUE);
DBMS_LOB.OPEN (l_clob, DBMS_LOB.lob_readwrite);
dbms_lob.write (l_clob, length('SELECT * from dual'), 1, 'SELECT * from dual');

FOR i IN 1 .. 15000
LOOP
DBMS_LOB.writeappend (l_clob,length(l_text), l_text);
END LOOP;

INSERT INTO clob_test
(ID, text
)
VALUES (0, l_clob||';'
);
commit;

DBMS_LOB.CLOSE (l_clob);
DBMS_LOB.freetemporary (l_clob);
END;


SQL> declare
2 v_sql clob;
3 o_cur sys_refcursor;
4 Begin
5 select text into v_sql from clob_test;
6 open o_cur for v_sql;
7 End;
8 /
open o_cur for v_sql;
*
ERROR at line 6:
ORA-06550: line 6, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored