Problem solve Get help with specific problems with your technologies, process and projects.

Displaying the output of PL/SQL procedures

I am new to CLOB, BLOB. I tried a few examples given in Oracle complete reference book. Whenever I run the examples...

of PL/SQL procedures using DBMS_LOB or DBMS_OUTPUT package, I do not get any output statements at all. It just quits saying PL/SQL procedure successfully completed.

For example I ran the following procedure:

locator_var CLOB;
amount_var INTEGER;
offset_var INTEGER;
output_var VARCHAR2(10);
  amount_var := 10;
  offset_var := 1;
  select Budget into locator_var from Proposal where proposal_id = 1;
  DBMS_LOB.READ(locator_var, amount_var, offset_var, output_var);
  DBMS_OUTPUT.PUT_LINE('Start of Proposal Text : ' || output_var);
It did not give any output. It just said PL/SQL procedure successfully completed. Do I need to turn ON something to see the output? Please let me know.

DBMS_OUTPUT.PUT_LINE relies on the SQL*Plus command SET SERVEROUTPUT ON in order to display the output. The default for SERVEROUTPUT is OFF so that's why you're not seeing anything except the successfully completed message. Turn it ON, and you'll be all set.

By the way, you'll need to set it ON every time you start SQL*Plus as the settings always revert default.

For More Information

This was last published in June 2002

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.