Re: Procedure to show and spool all the results

Thanks, but suppose the customer_id and product_id is not a number (eg. A1, A2, etc), how should I change it because I got this error ORA-00904: invalid column name

Thanks

Rauf Sarwar wrote:
> 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 Fri Mar 25 2005 - 13:21:27 CST