Re: HELP....CURSORS
Date: 1995/05/19
Message-ID: <Edda1995May19.171303.27581_at_news.compulink.com>#1/1
In article <3p1g5d$hdk_at_wraith.cs.uow.edu.au>, ahsan_at_wraith.cs.uow.edu.au
says...
>
>
>Iam having trouble in using cursors i.e how to define two cursors one
after
>the other in a PL/SQL procedure. What iam trying to do is
>
>create procedure temp(....) as
>
>cursor a is
> select...
> from....
> where...
>begin
>for var in a
>loop
> (statements)
>end loop
>
>
>cursor b is
> select...
> from....
> where...
>begin
>for var in b
>loop
> (statements)
>end loop
>end temp;
>
>
>THE PROBLEM IS THAT IT'S NOT WORKING THIS WAY i.e I am not been able to
> define more than one cursor in a procedure.
>CAN WE DO IT IN THIS WAY OR IS THERE ANY OTHER WAY ?.
>
>The second problem is how to change the headings while u r in procedure.
>i.e how to change
>
>EMP_NAME EMP-NO
>---------------------
>JACK 123
>MARK 456
>
>to
>
>Employer Name Employer No
>------------------------------
>JACK 123
>MARK 456
>
>REMEMBER WE R WORKING IN A PROCEDURE
>Therefore the commands like SET HEADINGS OFF DOES NOT WORK..
>
>Please reply
>Thank you in advance
>ahsan_at_wraith.cs.uow.edu.au
The only way to display a report type output in a procedure is by using the procedure DBMS_OUTPUT.PUT_LINE(variables).
This will display the output to the screen.
To display you example do the follwing.
set serveroutput on
declare
/* declare you cursor here */
cursor emp_cur is select emp_name, emp_no from employees;
t_emp_name varchar2(20) t_emp_no number(15) begin dbms_output.put_line('Employee name Employee no'); dbms_output.put_line('------------------------------------');open emp_cur
loop
fetch emp_cur into t_emp_name, t_emp_no; exit when emp_cur%notfound;
dbms_output.put_line(t_emp_name||to_char(t_emp_no)); end loop;
end;
The syntax might not be correct.
You might also have to set the buffer size.
I hope this helps.
Gary Wong
Toronto Canada.
Received on Fri May 19 1995 - 00:00:00 CEST