Q

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.


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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

1 comment

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close