Re: Output in SQL Worksheet or SQLPlus

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Sat, 21 Aug 1999 01:52:13 GMT
Message-ID: <37c204b2.43424951@netnews.worldnet.att.net>

If you are just executing a SQL query, then don't use PL/SQL at all. Execute the SELECT by itself, and SQL*Plus (or worksheet) will display the results.

From PL/SQL, there are a couple methods you could use. One is to use the DBMS_OUTPUT package. For example:

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2 DBMS_OUTPUT.PUT_LINE('HI THERE');
  3 END;
  4 /
HI THERE DBMS_OUTPUT outputs one line at a time, so you would have to write a loop to output the results of a query. Another aproach is to use a REFCURSOR variable. There's an example in my book, but I'll retype it here:

SQL> --This is a SQL*Plus (or worksheet) command
SQL> --to declare a refcursor bind variable.
SQL> VARIABLE query_output REFCURSOR
SQL> 
SQL> --This pl/sql block sets the refcursor variable
SQL> --to the results of a query.
SQL> BEGIN

  2 open :query_output for
  3 select * from dual;
  4 end;
  5 /

PL/SQL procedure successfully completed.

SQL> 
SQL> --Now tell SQL*Plus to print the results of the
SQL> --query using the bind variable
SQL> print query_output

D
-
X

When printing a refcursor variable, you can use the COLUMN commands to format the output to your liking.

Hope this helps. (especially after all that typing :-)).

Jonathan

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are

On Fri, 20 Aug 1999 20:51:48 GMT, "Tom Williamson" <tomw_at_action.cnchost.com> wrote:

>Please pardon this newbie question, but what's the best way to display
>output in SQL Worksheet or SQL Plus, when using PL/SQL?
>
>In MS-SQL Server it would be simple - either simply SELECT * FROM TABLE or
>else PRINT the values you want.
>
>In Oracle, SELECT X FROM TABLENAME results in an error stating "an INTO
>clause is expected", and there doesn't seem to be a PRINT or ECHO statement
>anywhere (at least I can't find it.) What's everyone's favorite way of
>doing this (getting output onto the screen)?
>
>Thanks - Tom
>
>
>
>
>
Received on Fri Aug 20 1999 - 20:52:13 CDT