Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Question Unanswered: Please Help!! Pl/sql

    Hi,

    I have a working SQL script which consists of some report formatting and a select statment. My problem is that this script is going to be set as a daily automated task on a solaris system which will generate a report. However, I am having extreme difficulty with making the report file name as the previous date in the format YYYYMMDD. (For example, today's date is August 6, 2003 and the automated task runs and it should generate a report with file name 20030805.lst) How do I do this? I have minimal knowledge with PL/SQL. I really don't have a clue how to write a procedure that can store sysdate-1 (which is essentially the previous day's date) in a variable for me to use in the SPOOL statement. I need to get this done asap and I really need help. I have looked at online documents but I don't understand how the block structure works.

    How can I generate this file with the specific file name with my existing SQL statement?

    Thanks in advance!!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Did you try:

    col mydate new_value _today
    break on report
    select to_char(sysdate,'YYYYMMDD') mydate from dual;
    spool &_today
    ...etc...


    PS: spool command cannot be used in PL/SQL, only in SQL Plus.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Posts
    2
    Thank you! It works perfectly....I've never thought of doing it this way!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •