How do I view a CLOB output parameter in TOAD from an Oracle Stored Procedure?

Question

I have a stored procedure in a package in an Oracle database that has 2 input parameters + 1 output CLOB parameter. How do I view the output in Toad? (Preferably with the user only having execute/select permissions)

Solution:

DECLARE
   my_output_parameter CLOB;
BEGIN 
   my_package.my_stored_proc(1, 2, my_output_parameter);
   DBMS_OUTPUT.PUT_LINE(my_output_parameter);
END;

Don't forget to execute as script, rather than just execute statement, and results appear in the DBMS Output window, not the datagrid.

Answer 1

I guess DBMS_OUTPUT.PUT_LINE has an internal line limit of 255 chars. However it has been removed from 10g Release 2 onwards. You can try inserting the column data in a table and view it later on by querying that table.

Please refer -

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:146412348066

Answer 2

Would you consider printing the CLOB as a result set? You could then use a PIPELINED function (more about them here: PIPELINED functions by Tim Hall) which would return the CLOB line by line, take a look at the example below:

CREATE TABLE my_clob_tab (
  id NUMBER,
  clob_col CLOB
)
/

INSERT INTO my_clob_tab
  VALUES (1,
          to_clob('first line' || chr(10) ||
          'second line, a longer one' || chr(10) ||
          'third'))
/

CREATE OR REPLACE TYPE t_my_line_str AS TABLE OF VARCHAR2(2000)
/

CREATE OR REPLACE FUNCTION print_clob_func(p_id IN NUMBER)
  RETURN t_my_line_str PIPELINED
AS
  v_buffer VARCHAR2(32767);
  v_clob CLOB;
  v_len NUMBER;
  v_offset NUMBER := 1;
  v_line_break_pos NUMBER;
  v_amount NUMBER;
BEGIN
  SELECT clob_col
    INTO v_clob
  FROM my_clob_tab
  WHERE id = p_id;

  IF v_clob IS NOT NULL THEN
    v_len := dbms_lob.getlength(v_clob);

    WHILE v_offset < v_len
    LOOP
      v_line_break_pos := instr(v_clob, chr(10), v_offset);

      IF v_line_break_pos = 0 THEN
        v_amount := v_len - v_offset + 1;
      ELSE
        v_amount := v_line_break_pos - v_offset;
      END IF;

      dbms_lob.read(v_clob, v_amount, v_offset, v_buffer);
      v_offset := v_offset + v_amount + 1;

      PIPE ROW (v_buffer);      
    END LOOP;
  END IF;
END;
/

(the function can be changed so that it takes as a parameter the CLOB you get from your procedure)

The code reads the content of the CLOB line by line (I assumed that the line separator is CHR(10) - if you are on Windows, you can change it to CHR(10) || CHR(13)) and PIPEs each line to the SELECT statement.

The function that reads the clob could also print the output to the standard output via dbms_output.put_line, but it would be trickier, because you'd have to take into account that standard output's maximal line length is limitied to, correct me if I'm wrong, 2000 characters, but it is doable (can't try that solution right now, unfortunately). In the meanwhile, please check above proposal and give me some feedback if that would work for you.

Back to the solution, now we can issue this SELECT statement:

SELECT COLUMN_VALUE AS clob_line_by_line FROM TABLE(print_clob_func(1));

Which will give us the following output:

CLOB_LINE_BY_LINE
-------------------------
first line
second line, a longer one
third

Check it at SQLFiddle: SQLFiddle example

Answer 3

Approach with inserting PL/SQL block and dbms_output:

DECLARE
   my_output_parameter CLOB;
BEGIN 
   my_package.my_stored_proc(1, 2, my_output_parameter);

  declare 
    vClob CLOB := my_output_parameter;
    vPos  number;
    vLen  number;
  begin
    vLen := DBMS_LOB.GetLength(vClob);
    vPos := 1;
    while vPos < vLen loop
      DBMS_OUTPUT.Put(DBMS_LOB.Substr(vCLOB, 200, vPos));
      vPos := vPos + 200;  
    end loop;
    DBMS_OUTPUT.new_line;
  end;

END;