Re: Procedure to show and spool all the results

jackfung188_at_gmail.com wrote:
> Thanks for everyone's reply, sorry for confusing, maybe I simply
> explain what I am trying to do
>
> I want to run the procedure, get the customer_id/product id and
> generate the results (it is just an example):
> select * from customer where customer_id=xxx;
> select * from order where customer_id=xxx;
> select * from product where product_id=yyy;
>
> Besides, I want to spool to the file automatically depends on the
> customer_id, eg xxx.txt every time.
>
> The reason I want to do it is to verify the data on every customer.
>
> Regarding DA's suggestion, I do not get access to init.ora. I guess I
> couldn't use UTL_FILE in this case.

An old Chinese proverb goes... "Don't use a cannon to kill a mosquito". Looks like you are trying to force the issue thru PLSQL. You can do this easily from sqlplus with spool, set serverout on and define OR download Toad from www.toadsoft.com it is a freeware. Create a script like this and run it from sqlplus. You can spool the result to a csv file.

set pagesize 0
set echo off
set verify off
set feedback off
set heading off
set termout off
set trimspool on
set define &

define cust_id = &customer_id
spool &cust_id..csv
select col1||','||col2||','||col3||','||.... from ...
where customer_id = &cust_id
/
spool off
undefine cust_id

define prod_id = &product_id
spool &prod_id..csv
....
....
spool off
undefine prod_id

You will be prompted for customer_id and product_id.

Regards
/Rauf Received on Thu Mar 24 2005 - 18:02:29 CST