Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

CSV file not generated properly

MuzzMay 25 2018 — edited Jun 14 2018

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.

This post has been answered by Frank Kulash on May 25 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 12 2018
Added on May 25 2018
10 comments
1,093 views