Re: PL/SQL cursor trouble
Date: 23 Mar 93 07:39:40 EST
Message-ID: <1993Mar23.073940.1_at_mcvax2.d48.lilly.com>
>I'm working on my first PL/SQL script which should merely produce a
>listing of data joined from several tables. I've been using all the
>handbooks, but can't find the solution to a probably simple problem:
>
>I'm not getting any output on my screen.
>
>I assume this is because I'm not telling Oracle to give me any,
>but how DO I tell it to do so???
>
>Because I want a listing of all data, I've created a cursor,
>"name_list_cursor", and use the FETCH... INTO statement in a loop,
>as described in the PL/SQL handbook. I think I need something else
>in the loop to display the list, but what??
>
>(TERMOUT is turned off at the beginning, but turned on again before the
>column formatting.)
>
>I tried SPOOL filename; but it didn't like that (gave me the message
>that it was expecting ":=" or some other symbol before "filename").
>
>Following is the script (it's not terribly long).
>
>Oh! Another question, could I be getting trouble
>from my "Group by" and/or "Order by" in my cursor? I wasn't sure if that
>was allowed, but I found nothing to the contrary. (The "Group By" is
>the reason for the Max() around all the columns in the cursor's Select
>statement, by the way.)
>
>Does anyone have any advice or ideas??
>Thanks in advance,
>Karen Schillinger
>Schillinger_at_rz.uni-karlsruhe.de
Karen:
PL/SQL v1.0... does not support terminal output. What you have to do is to insert the rows into another table (I call it a print table) and then use a normal sql statement to retrieve the data. For example:
create table print_table
(x char(80));
declare
cursor c1 as select name from emp;
begin
for rec1 in c1 loop
insert into print_table values (name);
end loop;
end;
/
spool out_file
select * from emp;
spool off;
hopefully in the next release of PL/SQL there will be the ability to output to the screen.
In response to your question about group and the order by, you can use any normal sql statement in a cursor. This includes Unions, Sub-Queries, and anything else you can write at the SQL*Plus prompt.
While that may not be the answer you were looking for, I hope that it helps.
Brian Stanek
Profound Consulting
P.S. Does anyone know if the PL/SQL v2 supports output??? Received on Tue Mar 23 1993 - 13:39:40 CET