spool..truncate..procedure ????

Hi,
I've created a procedure. after the procedure inserts row
in the table I need to spool the data to a file. I did the following.

create or replace procedure test

v_file_name varhcar2(1000);

begin

truncate table test;
insert
into test
values (a,b,c);

commit;

file_name := 'name'||to_char(sysdate,'dd-mm-rr hh:mm')||'.xls'

spool v_file_name
select * from test;
spool off;

end;

would some one be kind and answer these question.

-- whay can't I use TRUNCATE in the procedure
--I am not sure why the spool doesn't like v_file_name !!!!
how can I pass the file name to the spool.
--I only can create the procedure in sql plus by cut &paste.
-- do I need to create a sql script that does the spooling for
me rather than put in the procedure, and if so how can I call
the script from my procedure ???

truncate is a DDL command and i think you can only use DDL if you use DBMS_SQL package

I dont think you can use spool inside PL/SQL

how can you pass file_name to be spooled ?????
as I mentioned before.

With DBMS_SQL you can do the truncate.
With UTL_FILE you can spool to a file in PL/SQL
http://oradoc.photo.net/ora81/DOC/se...ile.htm#998101
is the link.

thanks, but I only need to do this now.
is there an easy way of doing it without using
utl_file

as you can see, I need to append the date to the file name.

set pagesize 0 ;
set linesize 79 ;
set verify off ;
set echo off ;
set heading off ;
file_name varchar2(1000) := 'd:\fds\reimb\new\cgbill'||to_char(sysdate,'mm-dd-rr hh:mm')||'.xls';

SPOOL file_name
SELECT job_no,
fpn,
voucher_amt,
travel_amt,
payroll_amt
FROM bill_05jan00;
SPOOL OFF;

I hope variables are so easy to use in sql*plus ;)
ocpdude

X:=Y is used in pl/sql and your script seems like sql, you have to find other ways to append the date I guess

Yes, maybe this PL could be a SQL script
And a variable is defined with
DEFINE file_name
spool &file_name

I've tried this but I only got the file cgbill.lst with no date. as if
date doesn't exist.

set pagesize 0 ;
set linesize 79 ;
set verify off ;
set echo off ;
set heading off ;

define v_file_name = 'd:\fds\reimb\new\cgbill'||to_char(sysdate,'mm-dd-rr hh:mm')||'.xls';

SPOOL &v_file_name;
SELECT job_no,
fpn,
voucher_amt,
travel_amt,
payroll_amt
FROM bill_05jan00;
SPOOL OFF;

any idea,
thanks all for your help

I haven't tried, but maybe if you put first
select to_char(sysdate,'mm-dd-rr hh:mm') into v_date
from dual;
and then
define v_file_name = 'd:\fds\reimb\new\cgbill'||&v_date||'.xls';

I figure this because sysdate can only be retrieved with a select.
Hope it helps.

I've tried this but, still giving me cgbill.lst ....!!!!


set pagesize 0 ;
set linesize 79 ;
set verify off ;
set echo off ;
set heading off ;


define v_date = select to_char(sysdate,'mm-dd-rr hh:mm') from dual;

define v_file_name = 'd:\fds\reimb\new\cgbill'||&v_date||'.xls';

SPOOL &v_file_name;
SELECT job_no,
fpn,
voucher_amt,
travel_amt,
payroll_amt
FROM bill_05jan00;
SPOOL OFF;

You cannot concatenate into the variable you DEFINE. Thats why it doesn't append your '.xls' to the filename. Try selecting into it:

Select 'D:\...\cgbill'||&v_date||'.xls' into v_file_name from dual;

Take a look at COLUMN command in SQL*Plus documentation.

Any how, here is how you should set the filename:

COLUMN fname_with_date NOPRINT NEW_VALUE file_str
SELECT 'c:\temp\x'||to_char(sysdate,'mm-dd-yyy_hh24:mi')||'.txt' AS fname_with_date FROM dual;
Then reference &file_str in SPOOL command.

Let's see if it works in SQL*Plus:

SQL> COLUMN fname_with_date NOPRINT NEW_VALUE file_str
SQL> SELECT 'c:\temp\x'||to_char(sysdate,'mm-dd-yyyy_hh24:mi')||'.txt' AS fname_with_date FROM dual;

SQL> SPOOL &file_str
SQL> SELECT * FROM DUAL;

D
-
X

SQL> REM Let's see wher is it spooling to:
SQL> SPOOL
currently spooling to c:\temp\x01-08-2001_21:43.txt
SQL> SPOOL OFF
SQL>

HTH,

Just for the record.
In Windows a file cannot contain ':'

You are correct, I was to lazy to check if the file was actually created in the c:\temp directory - I simply belive what SQL*Plus told me where it is spooling currently ;-(.

Now I removed the ":" from the file name and actually checked the file - it was created properly.

Cooooooool,

WHILE count_of_thanks <= OO LOOP
dbms_output.put_Line('thanks a million dude');
END LOOP