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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in June 2002