2 Replies Latest reply: Nov 23, 2012 3:28 AM by BluShadow RSS

    Regarding Execution of clob datatype

    975734
      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
          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
            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.