Re: HELP....CURSORS

From: Gary Wong <gwong_at_idirect.com>
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
gwong_at_idirect.com
Toronto Canada. Received on Fri May 19 1995 - 00:00:00 CEST

Original text of this message