-
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
[url]http://oradoc.photo.net/ora81/DOC/server.815/a68001/utl_file.htm#998101[/url]
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|