Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

ss

Oracle Tips by Burleson 

Displaying PL/SQL Output

Another change with PL/SQL from SQL is that the database does not return the output.  PL/SQL code normally will change data, insert values, and so forth, inside the database.  It will not normally display results back to the user.  To do this we use a procedure called dbms_output.put_line  to place the results in a buffer that SQL*Plus will retrieve and display.  SQL*Plus must be told to retrieve data from this buffer in order to display the results.  The SQL*Plus command “set serveroutput on” causes SQL*Plus to retrieve and display the buffer.

SQL> declare
  2    v_line varchar2(40);
  3  begin
  4    v_line := 'Hello World';
  5    dbms_output.put_line (v_line);
  6  end;
  7  /
 

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> declare
  2    v_line varchar2(40);
  3  begin
  4    v_line := 'Hello World';
  5    dbms_output.put_line (v_line);
  6  end;
  7  /
Hello World

PL/SQL procedure successfully completed.

The first time the script is run, the result was just a notice that the script completed successfully.  Once we set serverouput on and rerun the script, the results are shown. 

As discussed earlier, this is an anonymous block of PL/SQL code.  It is sent to the database, compiled and executed, then SQL*Plus retrieves the results.  The script is stored in the SQL*Plus buffer and can be rerun by executing the forward slash.

SQL> /
Hello World

PL/SQL procedure successfully completed.

The script is not stored in the database as a stored or named procedure  is.   It must be resent to the database and compiled each time it is executed.

As with SQL  statements, SQL*Plus  variables can be used to make the PL/SQL script dynamic.  Just as with a SQL statement, the variables are local to SQL*Plus and are substituted before the code is sent to the database.

SQL> declare
  2    v_line varchar2(40);
  3  begin
  4    v_line := 'Hello &name';
  5    dbms_output.put_line (v_line);
  6  end;
  7  /
Enter value for name: John
old   4:   v_line := 'Hello &name';
new   4:   v_line := 'Hello John';
Hello John 

PL/SQL procedure successfully completed

The SQL *Plus accept command is a more flexible method of embedding dynamic data in the script.

SQL> accept v_string prompt "Enter Your First Name: " 

Enter Your First Name: Thomas 

SQL> declare
  2    v_line varchar2(40):= '&v_string';
  3  begin
  4    v_line := 'Hello '||v_line;
  5    dbms_output.put_line (v_line);
  6  end;
  7  / 

old   2:   v_line varchar2(40):= '&v_string';
new   2:   v_line varchar2(40):= 'Thomas'; 

Hello Thomas 

PL/SQL procedure successfully completed.

Let’s look at this script a little closer.  The first line is the SQL*Plus accept command to get the SQL*Plus variable v_string.  This line must be executed alone, not part of the PL/SQL block.  At the prompt the name Thomas was entered.  Now the script is run but it is slightly modified from previous examples.

SQL> declare
  2    v_line varchar2(40):= '&v_string';
 

The variable v_line is declared as a varchar2(40) and is given a default value that equals v_string.  The PL/SQL assignment operator (:=) is used to assign the value.  So v_line is a bucket that gets assigned the string ‘Thomas’.  A developer would read an assignment statement in English as “v_line gets v_string” to indicate the assignment.  Let’s examine a more complex assignment statement.

4    v_line := 'Hello '||v_line;

Line 4 uses the concatenate operator to append ‘Hello ‘ to the front of v_line and then assigns it back to the variable v_line.  The variable v_line now contains the string ‘Hello Thomas’.  Line 5 places the value of v_line in the buffer to be retrieved by SQL *Plus.

old   2:   v_line varchar2(40):= '&v_string';
new   2:   v_line varchar2(40):= 'Thomas';
 

These two lines demonstrate SQL*Plus’s verify function showing us what is substituted before the code is sent to the database for execution.  This information can be switched on/off with the verify command

SQL> set verify on
SQL> set verify off


The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks