2 Replies Latest reply on Apr 15, 2010 11:38 AM by Marwim

    Output CLOB

    690792
      Hi.
      I need help.
      I have a Clob variable in pl/sql with length over 40000.
      I want to print it on screen with DBMS_OUTPUT.put_line
      e.g.
      DECLARE
        qryCtx DBMS_XMLGEN.ctxHandle;
        result CLOB;
      BEGIN
        qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM user_tab_columns');
        result := DBMS_XMLGEN.getXML(qryCtx);
        DBMS_OUTPUT.put_line(result);
        --Close context
        DBMS_XMLGEN.closeContext(qryCtx);
      END;
      I am getting
      DECLARE
      *
      ERROR at line 1:
      ORA-06502: PL/SQL: numeric or value error
      ORA-06512: at line 7
      because result is too long.

      I there any way to print it on the screen?
        • 1. Re: Output CLOB
          BluShadow
          You'll need to use the DBMS_LOB package to extract the CLOB in chunks in a loop and then display each chunk seperately. The DBMS_OUTPUT.PUT_LINE is limited in the size of the output that can be displayed to the size of a VARCHAR2 which is 32767 characters.
          1 person found this helpful
          • 2. Re: Output CLOB
            Marwim
            Hello,

            something like
            /**
            * @Name pl
            * @Desc Show CLOB with dbms_output.
            *       Begins a new line when the string p_delimiter is found
            *       If the leght of the resulting string is greater then p_max_length then break at the last space
            *       before p_max_length.
            *       If there is no space then break at p_max_length.
            * @Param p_text – content to show
            * @Param p_max_length – max line length
            * @Param p_delimiter – new line when this string is found
            * @Param p_replace_space – replace the first character of a new line if it is a space with this character
            *           Useful in SQL*PLUS, because it suppresses leading spaces
            */
            PROCEDURE pl
                (
                 p_text                 IN CLOB
                ,p_max_length           IN PLS_INTEGER := 255
                ,p_delimiter            IN VARCHAR2 := CHR(10)
                ,p_replace_space        IN VARCHAR2 := '.'
                )
            IS
                v_pos_Bis               PLS_INTEGER;
                v_text                  CLOB;
                v_delimiter_found       BOOLEAN;
                v_tmp_text              VARCHAR2(2000);
            BEGIN
                IF LENGTH(p_replace_space) <> 1
                  OR p_replace_space IS NULL
                THEN
                    RAISE_APPLICATION_ERROR(-20001,'Parameter p_replace_space must have exactly one character');
                END IF;
            
                dbms_lob.createTemporary(v_text, TRUE);
                dbms_lob.copy(v_text,p_text,dbms_lob.getLength(p_text));
            
                IF dbms_lob.getLength(v_text) <= p_max_length THEN
                    dbms_output.put_line(v_text);
                ELSE
                    WHILE TRUE LOOP
            
                        IF SUBSTR(v_text,1,1) = ' '
                          AND p_replace_space <> ' '
                        THEN
                            v_text := p_replace_space||SUBSTR(v_text,2);
                        END IF;
            
                        v_delimiter_found := FALSE;
                        v_pos_Bis := INSTR(v_text,p_delimiter);
            
                        IF v_pos_Bis = 0 THEN
            
                            v_pos_Bis := INSTR(SUBSTR(v_text,1,p_max_length),' ',-1,1);
            
                            IF v_pos_Bis = 0 THEN
                                v_pos_Bis := p_max_length;
                            END IF;
            
                        ELSE
                            v_delimiter_found := TRUE;
                        END IF;
            
                        IF v_pos_Bis < dbms_lob.getLength(v_text) THEN
            
                            IF v_delimiter_found THEN
                                dbms_output.put_line(SUBSTR(v_text,1,v_pos_Bis - LENGTH(p_delimiter) + 1)||'#');
                                v_text := SUBSTR( v_text, v_pos_Bis + LENGTH(p_delimiter));
                            ELSE
                                dbms_output.put_line(SUBSTR(v_text,1,v_pos_Bis)||'#');
                                v_text := SUBSTR( v_text, v_pos_Bis + 1);
                            END IF;
            
                        ELSE
                            dbms_output.put_line(v_text);
                            EXIT;
                        END IF;
            
                    END LOOP;
                END IF;
                dbms_lob.freetemporary(v_text);
            END pl;
            Regards
            Marcus