spool..truncate..procedure ????

[arshiah]

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;

[jmodic]

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,

[rcaballe]

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

[jmodic]

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.

[ocpdude]

Cooooooool,

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