Concatenation of CLOB datatypes in a LOOP in PL/SQL

Question

Good Day Gurus,

I am trying to concatenate clobs in a PL/SQL loop and it has been returning null whilst when using DBMS_OUTPUT prints out the loop values and when executing each result of the clobs gives an output as well.

The system is meant to execute an already stored SQL in a table based on the report name passed into it. This particular report has many report names; hence the concatenation of each of the reports. The arguments passed are the report name, version of the report you're interested in, the kind of separator you want, and an argument list for the unknowns in the SQL if any. There are also two main types of SQL; 1 that needs the table_name be replaced with a temp table_name and another that needs an ID be appended to a table_name in the SQL.

please find below the code for the REPREF1 function. Many thanks for your anticipated cooperation and response.

CREATE OR REPLACE FUNCTION REPREF1(p_ver in varchar2 DEFAULT 'LATEST', p_separator in varchar2 default ', ', p_arglist in var default null)
  RETURN CLOB
  IS


   l_clob       CLOB;


   FUNCTION GET_CLOB(p_repnam in varchar2, p_ver in varchar2 DEFAULT 'LATEST', p_separator in varchar2 default ', ', p_arglist in var default null)
   RETURN CLOB
   IS

---------------------------------------------------------------------------------
--TITLE - GET_CLOB beta - b.0  DATE 2010Mar12
--
-- DESCRIPTION -  A function that return a report based on the report name put in
--
-- USAGE - select get_clob(p_repnam,p_ver, p_separator, var(varay(val_1,...val_n), varay(val_1,...val_n))) FROM dual
-----------------------------------------------------------------------------------------------------------------------------


   v_sql        VARCHAR2(32767);
   l_result     CLOB;
   v_title      VARCHAR2(4000);
   v_repdate    VARCHAR2 (30);
   v_cnt        NUMBER (2);
   v_numarg     NUMBER (3); 
   v_cdcru      NUMBER (3); 
   v_bcnt       NUMBER (3);
   v_newtabdat VARCHAR2 (30);
   v_newtablin VARCHAR2 (30);
   l_collist    VARAY;
   v_ver        VARCHAR2(6);
   n            PLS_INTEGER;
   v_cnttab     NUMBER (3);


 -- EXEC_SQL_CLOB  
 FUNCTION EXEC_SQL_CLOB (p_sql IN VARCHAR2, p_numarg in NUMBER, p_collist in varay default null, p_arglist in varay default null, p_separator in varchar2 default '')
  RETURN CLOB
  IS

------------------------------------------------------------------------------------------------------
--TITLE - EXEC_SQL_CLOB beta - b.0  DATE 2010Mar22
--
-- DESCRIPTION -  A function that returns a clob value after executing the sql query that is passed into it 
--
-- USAGE - select exec_sql_clob(p_sql, p_numarg, var(varay(val_1, val_2,...val_n), varay(val_1, val_2,...val_n))) FROM dual
---------------------------------------------------------------------------------------------------------------


   l_cur        INTEGER DEFAULT dbms_sql.open_cursor;
   l_status     INTEGER;
   v_col        VARCHAR2 (4000);
   l_result     CLOB;
   l_colCnt     NUMBER DEFAULT 0;
   l_separator  VARCHAR2(10) DEFAULT '';
   v_numarg     NUMBER (3);


   BEGIN                

        -- parse the query for the report
       dbms_sql.parse(  l_cur,  p_sql, dbms_sql.native );

       -- whilst it is not more than 255 per line
        FOR i IN 1 .. 255 
        LOOP

            BEGIN

                -- define each column in the select list
                dbms_sql.define_column( l_cur, i, v_col, 2000 );


                l_colCnt := i;

            EXCEPTION
            WHEN OTHERS 
            THEN

                IF ( SQLCODE = -1007 ) 
                THEN 

                    EXIT;

                ELSE

                    RAISE;

                END IF;

            END;

        END LOOP;


       -- If query has no bind variables
       IF (p_arglist IS NULL)
       THEN

           IF (p_numarg = 0)
           THEN
                -- Execute the query in the cursor
                l_status := dbms_sql.execute(l_cur);         

                LOOP

                    -- Exit loop when fetch is complete
                    EXIT WHEN ( dbms_sql.fetch_rows(l_cur) <= 0 );
                    l_separator := '';

                    FOR i in 1 .. l_colCnt 
                    LOOP

                        dbms_sql.column_value( l_cur, i, v_col );
                        l_result := l_result || l_separator || v_col;
                        l_result := replace(replace(l_result, chr (13) || chr(10), ' '), chr(10), ' ');
                        l_separator := p_separator;

                    END LOOP;

                    l_result := l_result || CHR(13);

                END LOOP;

           ELSE

                RAISE_APPLICATION_ERROR(-20011,' INCORRECT NUMBER OF ARGUMENTS PASSED IN LIST ');

           END IF;

       -- Query has bind variables
       ELSE

           -- Check if the numarg passed is the same has stored in the table
           SELECT NUMARG
           INTO v_numarg
           FROM REPVER
           WHERE REPCODE = p_sql;

         -- If number of arguments is greater than 0
         IF (v_numarg > 0)
         THEN

           -- Check if the number of arguments are the same
           IF (p_numarg = v_numarg)
           THEN

                -- Replace the bind variables in the query
                FOR j in 1 .. p_arglist.count
                LOOP

                    DBMS_SQL.BIND_VARIABLE(l_cur, p_collist(j), p_arglist(j));

                END LOOP;

                -- Execute the query in the cursor
                l_status := dbms_sql.execute(l_cur);         

                LOOP

                    -- Exit loop when fetch is complete
                    EXIT WHEN ( dbms_sql.fetch_rows(l_cur) <= 0 );
                    l_separator := '';

                    FOR i in 1 .. l_colCnt 
                    LOOP

                        dbms_sql.column_value( l_cur, i, v_col );
                        l_result := l_result || l_separator || v_col;
                        l_result := replace(replace(l_result, chr (13) || chr(10), ' '), chr(10), ' ');
                        l_separator := p_separator;

                    END LOOP;

                    l_result := l_result || CHR(13);

                END LOOP;

           ELSE

                RAISE_APPLICATION_ERROR(-20011,' INCORRECT NUMBER OF ARGUMENTS PASSED IN LIST ');

           END IF;

         ELSE

           -- If the number of argument is equal to 0
           IF (p_numarg = 0)
           THEN
                -- Execute the query in the cursor
                l_status := dbms_sql.execute(l_cur);         

                LOOP

                    -- Exit loop when fetch is complete
                    EXIT WHEN ( dbms_sql.fetch_rows(l_cur) <= 0 );
                    l_separator := '';

                    FOR i in 1 .. l_colCnt 
                    LOOP

                        dbms_sql.column_value( l_cur, i, v_col );
                        l_result := l_result || l_separator || v_col;
                        l_result := replace(replace(l_result, chr (13) || chr(10), ' '), chr(10), ' ');
                        l_separator := p_separator;

                    END LOOP;

                    l_result := l_result || CHR(13);

                END LOOP;

           ELSE

                RAISE_APPLICATION_ERROR(-20011,' INCORRECT NUMBER OF ARGUMENTS PASSED IN LIST ');

           END IF;

         END IF;

       END IF;

      -- Close cursor
      dbms_sql.close_cursor(l_cur);


       RETURN l_result;

   END EXEC_SQL_CLOB;

 BEGIN


     -- Check if the version entered is null or latest
     IF (p_ver IS NULL) OR (UPPER(p_ver) = UPPER('LATEST'))
     THEN

         SELECT MAX(VER)
         INTO v_ver
         FROM REPORT B, REPVER R
         WHERE UPPER(REPNAM) = UPPER(p_repnam)
         AND B.REPREF = R.REPREF;

     ELSE      

         v_ver := p_ver;

     END IF;


     -- Check if the repname and version entered exists
     SELECT COUNT(*)
     INTO v_cnt
     FROM REPORT B, REPVER R
     WHERE UPPER(REPNAM) = UPPER(p_repnam)
     AND VER = v_ver
     AND B.REPREF = R.REPREF;


   IF (v_cnt > 0)
   THEN

        -- Store the SQL statement, title and number of arguments of the report name passed.
       SELECT REPCODE, REPTITLE, NUMARG, COLLIST
       INTO v_sql, v_title, v_numarg, l_collist
       FROM REPVER R, REPORT B
       WHERE UPPER(REPNAM) = UPPER(p_repnam)
       AND B.REPREF = R.REPREF
       AND VER = v_ver;

       v_repdate := to_char(sysdate, 'YYYY-MM-DD HH24:MI');

       l_result := v_title || ' (' || p_repnam || ' version ' || v_ver || ') generated ' || v_repdate || CHR(13) || CHR(13);

       -- Check for some specific type of queries
        SELECT COUNT(*)
        INTO v_cdcru
        FROM REPVER R, REPORT B
        WHERE CTDDATA = 'Y'
        AND UPPER(REPNAM) = UPPER(p_repnam)
        AND B.REPREF = R.REPREF
        AND VER = v_ver;

        SELECT COUNT(*)
        INTO v_bcnt
        FROM REPVER R, BODCREPS B
        WHERE BENLIST = 'Y'
        AND UPPER(REPNAM) = UPPER(p_repnam)
        AND B.REPREF = R.REPREF
        AND VER = v_ver;

        IF (v_cdcru > 0)
        THEN

            v_newtabdata := 'CT_' || 'DAT_' || p_arglist(1)(p_arglist(1).FIRST); 
            v_newtablink := 'CT_' || 'LIN_' || p_arglist(1)(p_arglist(1).FIRST);

            -- Check if the tables exist
            SELECT count(*)
            INTO v_cnttab
            FROM all_tables
            WHERE TABLE_NAME = v_newtabdat
            OR TABLE_NAME = v_newtablin
            AND OWNER = 'SCOTT';

            IF(v_cnttab > 0)
            THEN

                v_sql := UPPER(v_sql); 
                v_sql := REPLACE(v_sql,'CT_DAT_CRU', v_newtabdat);
                v_sql := REPLACE(v_sql,'CT_LIN_CRU', v_newtablin);

            ELSE

                v_sql := 'SELECT ''THE TABLE NOT CREATED YET''
                          FROM DUAL';

            END IF;

        END IF;

        IF (v_bcnt > 0)
        THEN

            v_sql := UPPER(v_sql); 
            v_sql := REPLACE(v_sql,'LIST', p_arglist(1)(p_arglist(1).LAST));

        END IF;

       IF (p_arglist IS NULL)
       THEN

            -- execute the query
            l_result := l_result || EXEC_SQL_CLOB(v_sql,v_numarg,l_collist,null,p_separator);

       ELSE

           n := p_arglist.count;
           -- execute the query
           l_result := l_result || EXEC_SQL_CLOB(v_sql,v_numarg,l_collist,p_arglist(n),p_separator);

       END IF;

       RETURN l_result;

   ELSE

       RAISE_APPLICATION_ERROR(-20012,p_repnam || ' or ' || p_ver || ' DOES NOT EXIST ');

   END IF;


 END GET_CLOB;

BEGIN

       FOR i IN (SELECT REPNAM 
                 FROM report 
                 WHERE REPREF NOT IN ('R01','R02','R03','R04'))

       LOOP

           SELECT CONCAT_CLOB(GET_CLOB(i.REPNAM,P_VER,P_SEPARATOR,P_ARGLIST))
           INTO l_clob
           FROM DUAL; 

           DBMS_OUTPUT.PUT_LINE (i.REPNAM);
          -- DBMS_OUTPUT.PUT_LINE (COUNT(i.REPNAM));

       END LOOP;


     return l_clob;

END REPREF1;
/

Cheers, Tunde

Many thanks APC for making the code look better. @Robert, the last loop in the code returns null even with the CONCAT_CLOB aggregate function that concatenates clobs.

FOR i IN (SELECT REPNAM 
                     FROM report 
                     WHERE REPREF NOT IN ('R01','R02','R03','R04'))

           LOOP

               SELECT CONCAT_CLOB(GET_CLOB(i.REPNAM,P_VER,P_SEPARATOR,P_ARGLIST))
               INTO l_clob
               FROM DUAL; 

               DBMS_OUTPUT.PUT_LINE (i.REPNAM);


           END LOOP;

when I try this,

FOR i IN (SELECT REPNAM 
          FROM report 
          WHERE REPREF NOT IN ('R01','R02','R03','R04'))

        LOOP

             l_clob := l_clob || CHR(13)||GET_CLOB(i.REPNAM,P_VER,P_SEPARATOR,P_ARGLIST);
             DBMS_OUTPUT.PUT_LINE (i.REPNAM);


        END LOOP;

It also gives null; but this time the dbms output for the repnam are not complete.

Cheers once again!

Answer 1

Could you narrow it down to the point where you think the problem occurs? This is seriously heavy stuff, mostly for its archaic DBA-dude kind of naming and formatting.

I am pretty proficient in PL/SQL, but I really have a problem looking at all-caps kind of PL/SQL code for too long.

Edit: Thx APC for fixing the code highlighting.

Answer 2

Don't know about your code. Here is how it works for me: Whenever I create a function returning a clob value I do this:

function foo return clob is
l_clob clob;
begin
      dbms_lob.createtemporary(lob_loc => l_lob, cache => true, dur => dbms_lob.call);
      ...
      return l_clob;
end;

When concatenating values into a clob I use a function:

   procedure add_string_to_clob(p_lob    in out nocopy clob
                             ,p_string varchar2) is
   begin
      dbms_lob.writeappend(lob_loc => p_lob, amount => length(p_string), buffer => p_string);
   end;