Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Output in SQL Worksheet or SQLPlus
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
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
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
![]() |
![]() |