DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: spool..truncate..procedure ????

  1. #1
    Join Date
    Nov 2000
    Posts
    198
    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 ???

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  3. #3
    Join Date
    Nov 2000
    Posts
    198
    how can you pass file_name to be spooled ?????
    as I mentioned before.

  4. #4
    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.
    Ramon Caballero, DBA, rcaballe@yahoo.com

  5. #5
    Join Date
    Nov 2000
    Posts
    198
    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;


  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  7. #7
    Yes, maybe this PL could be a SQL script
    And a variable is defined with
    DEFINE file_name
    spool &file_name
    Ramon Caballero, DBA, rcaballe@yahoo.com

  8. #8
    Join Date
    Nov 2000
    Posts
    198
    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

  9. #9
    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.
    Ramon Caballero, DBA, rcaballe@yahoo.com

  10. #10
    Join Date
    Nov 2000
    Posts
    198

    Angry

    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
  •  


Click Here to Expand Forum to Full Width