Ask the Expert

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.

    Requires Free Membership to View

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 first published in June 2002

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: