This discussion is archived
2 Replies Latest reply: Nov 23, 2012 1:28 AM by BluShadow RSS

Regarding Execution of clob datatype

975734 Newbie
Currently Being Moderated
Hi ,

I have stored the query in clob data type. Now I am executing that query and passing to ref cursor using the below line. here SQL_Text is clob variable.

OPEN O_REFCURSOR FOR SQL_Text;

for few queries it's working fine but one query it's giving error table does not exist then I have run that query manually which is executing fine. I thought that for bigger queries this problem is coming . How to run the clob bigger queries dynamically.
  • 1. Re: Regarding Execution of clob datatype
    BluShadow Guru Moderator
    Currently Being Moderated
    Please read: {message:id=9360002} and post relevant details so we can help you.

    It could be that your dynamic query that you've built up in your CLOB has a syntax error because of the way you've built it up.
  • 2. Re: Regarding Execution of clob datatype
    BluShadow Guru Moderator
    Currently Being Moderated
    The other possibility could be your database version.

    In 10g, dynamic strings for an OPEN FOR statement are limited to 32767 bytes.

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/openfor_statement.htm#LNPLS01333

    In 11g, dynamic strings for an OPEN FOR statement can be CLOBs

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/openfor_statement.htm#i35231

    If you're not on 11g, then to execute a dynamic statement that's in a CLOB exceeding 32767 bytes can be done with the DBMS_SQL package...

    e.g.
    SQL> ed
    Wrote file afiedt.buf
     
      1  declare
      2    v_large_sql  CLOB;
      3    v_num        NUMBER := 0;
      4    v_upperbound NUMBER;
      5    v_sql        DBMS_SQL.VARCHAR2S;
      6    v_cur        INTEGER;
      7    v_ret        NUMBER;
      8  begin
      9    -- Build a very large SQL statement in the CLOB
     10    LOOP
     11      IF v_num = 0 THEN
     12        v_large_sql := 'CREATE VIEW vw_tmp AS SELECT ''The number of this row is : '||to_char(v_num,'fm0999999')||''' as col1 FROM DUAL';
     13      ELSE
     14        v_large_sql := v_large_sql || ' UNION ALL SELECT ''The number of this row is : '||to_char(v_num,'fm0999999')||''' as col1 FROM DUAL';
     15      END IF;
     16      v_num := v_num + 1;
     17      EXIT WHEN DBMS_LOB.GETLENGTH(v_large_sql) > 40000 OR v_num > 800;
     18    END LOOP;
     19    DBMS_OUTPUT.PUT_LINE('Length:'||DBMS_LOB.GETLENGTH(v_large_sql));
     20    DBMS_OUTPUT.PUT_LINE('Num:'||v_num);
     21    --
     22    -- Now split that large SQL statement into chunks of 256 characters and put in VARCHAR2S array
     23    v_upperbound := CEIL(DBMS_LOB.GETLENGTH(v_large_sql)/256);
     24    FOR i IN 1..v_upperbound
     25    LOOP
     26      v_sql(i) := DBMS_LOB.SUBSTR(v_large_sql
     27                                 ,256 -- amount
     28                                 ,((i-1)*256)+1 -- offset
     29                                 );
     30    END LOOP;
     31    --
     32    -- Now parse and execute the SQL statement
     33    v_cur := DBMS_SQL.OPEN_CURSOR;
     34    DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);
     35    v_ret := DBMS_SQL.EXECUTE(v_cur);
     36    DBMS_OUTPUT.PUT_LINE('View Created');
     37* end;
    SQL> / 
    Length:40015
    Num:548
    View Created
     
    PL/SQL procedure successfully completed.
     
    SQL> select count(*) from vw_tmp;
     
      COUNT(*)
    ----------
           548
     
    SQL> select * from vw_tmp where rownum <= 10;
     
    COL1
    -----------------------------------
    The number of this row is : 0000000
    The number of this row is : 0000001
    The number of this row is : 0000002
    The number of this row is : 0000003
    The number of this row is : 0000004
    The number of this row is : 0000005
    The number of this row is : 0000006
    The number of this row is : 0000007
    The number of this row is : 0000008
    The number of this row is : 0000009
     
    10 rows selected.
     
    SQL>
    Of course you cannot turn a DBMS_SQL cursor into a REF CURSOR in 10g, so if it's a REF CURSOR you definitely need and it needs to deal with queries larger than 32767 bytes, you'll need to ensure you're using 11g.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points