Hi Guys
I'm trying to create CSV format file by using bat file but I'm getting the below error when I try to open the csv file:
Error
SP2-0042: unknown command ")" - rest of line ignored. |
In my sql file , I have the following stuff:
SQL File
SET LINESIZE 600
SET PAGESIZE 0
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET ECHO OFF
SET TERMOUT OFF
--date is added to file name in .bat
spool spool.sql
SELECT 'SPOOL '|| '&1' ||'.csv' FROM DUAL;
spool off
EXECUTE LOG_PROCESS('Start Process ','Circuit to Billing');
@spool.sql
--headings
SELECT REPORT_OUT
from
(select chr(34)||'Task_Name'||chr(34)||chr(44)||
CHR(34)||'SO'||CHR(34)||CHR(44)||
chr(34)||'Account_Number'||chr(34)||chr(44)||
chr(34)||'Circuit'||chr(34)||chr(44)||
CHR(34)||'User_Name'||CHR(34)||CHR(44)||
chr(34)||'Completed_Date'||chr(34)||chr(44)||
CHR(34)||'Status'||CHR(34)||CHR(44) AS REPORT_OUT
FROM dual
);
pause
SPOOL OFF;
HOST del spool.sql
EXIT
Batch file
ECHO OFF
SET SQL_TO_RUN=E_Test.sql
SET EMAIL_SUBJECT=Discount Report
rem pause Before set_db_connection
rem To set the db connection variables by running a db connection setup file existed in any place you like.
rem In this example, the set_db_connect.bat is local.
call set_db_connection.bat
rem pause After set_db_connection
rem Set the file name of the report
SET FILE_NAME=E_Test
rem Commented out the next line to disable using SSDBCONNECT
rem SET SSDBCONNECT_PATH=..\..\bin
SET MAIL_PROGRAM=HTCMAIL
rem To read the start and end dates from mindate.txt
rem SET /P minDate= <mindate.txt
rem SET /P maxDate= <maxDate.txt
set timestamp=%date:~10,4%%date:~4,2%%date:~7,2%
:RUN SQL
rem The db_user, db_password and db_name are set in set_db_connect.bat
sqlplus %db_user%/%db_password%@%db_name% @E_Discounts %FILE_NAME%
RENAME %FILE_NAME%.csv %FILE_NAME%_%timestamp%.csv
rem If the report is not to be mailed out, comment out the next 2 lines.
:CONS_EMAIL
%MAIL_PROGRAM% MasterReport.txt -tf Mail_Recipients.txt -s "%EMAIL_SUBJECT% for %timestamp%" -attach %FILE_NAME%_%timestamp%.csv
REM COPY FOLDER TO ARCHIVE FOLDER
IF NOT EXIST .\ARCHIVE MD .\ARCHIVE
MOVE "%FILE_NAME%*.csv" .\ARCHIVE\
I'm getting email successfully but getting an error message as I posted above.