Search code examples
sqloracle-databasestored-procedurestoadclob

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

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.

Solution

  • 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