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:

declare
locator_var CLOB;
amount_var INTEGER;
offset_var INTEGER;
output_var VARCHAR2(10);
begin
  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);
end;
/
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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top