Re: Procedure to show and spool all the results

On 2005-03-22, jackfung188_at_gmail.com <jackfung188_at_gmail.com> wrote:
> Hi All,
>
> I want to write a procedure which is able to spool all the select
> statement into a text file.
>
> eg. the result I want in the spool file:
> select * from customer where customer_id=xx;
> select * from order where customer_id=xx;
> select * from product where product_id=yy;
>
> The procedure should allows me to put the xx and yy value when i
> execute the procedure. I heard it's impossible to do unless using ref
> cursor? If so, how can I do it?

The following example should help you get started:

create table rc_tbl (a number, b varchar2(10));

insert into rc_tbl values (1, 'one');
insert into rc_tbl values (2, 'two');
insert into rc_tbl values (3, 'three');

create or replace procedure rc_proc(rc in out sys_refcursor) as begin
  open rc for select * from rc_tbl;
end;
/

show errors

variable rc_var refcursor

begin rc_proc(:rc_var); end;
/

print rc_var

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/