CODEWITHSUNDEEP
batch-filenewlinesqlplus
NeedBasedLearner
NeedBasedLearner

Reputation: 39

Batch file - newline character issue

I'm having some weird trouble with batch script output. My requirement is to run a sql script via batch file (using sqlplus), the output of this SQL script will be another batch file (ex: For_Loop.bat). The batch file will contain around 20 FOR loops as below. Note that there is a newline character at the end of directory path, without this the FOR loop won't execute and also there is a newline after ')' in the 2nd line after 'found'.

Now the problem I'm facing is - this "For_Loop.bat" file runs fine sometime and sometime it is throwing the error below. This to me looks like a newline character issue. This newline character is introduced by SQL query as below. So can you please spot what's going wrong here? I'm not sure why this doesn't run smoothly all the time. Btw the objective of this FOR loop is to copy only the latest files from one folder to other.

For_Loop.bat

FOR /F "delims=" %%i IN ('dir "tbl_rec_value1" /b /t:c /o-d') DO ( COPY /Y "tbl_col_value1\%%i" "tbl_rec_value1"
goto found )
:found
FOR /F "delims=" %%i IN ('dir "tbl_rec_value2" /b /t:c /o-d') DO ( COPY /Y "tbl_col_value2\%%i" "tbl_rec_value2"
goto found )
:found

SQL Note: chr(13) is to introduce newline character.

select 'FOR /F "delims=" %%i IN ('||''''|| 'dir "'|| tbl_col1 || tbl_col2 || '" /b /t:c /o-d'||''''||') DO ( COPY /Y "'|| tbl_col1 ||'%%i" "' || tbl_col1 || 'Dir_Level2"' ||  chr(13) || 'goto found )' || chr(13) || ':found' cmd
from tbl;

Error message - 

:found was unexpected at this time.

Update

Another weird behavior I have noticed with this is -

  1. The SQL output writes to For_Loop.bat file. When I run this For_Loop.bat file it fails with the above error.
  2. If I cut the content of For_Loop.bat, save it. Paste the content back and save it again. When run now this batch file executes successfully. No modification done to the For_Loop.bat file content whatsoever apart from cut/paste.

This is telling me that there are some attributes set when the file is written by SQL output that are causing the problem.

If you are aware of any such things, please let me know.

Upvotes: 1

Views: 710

Answers (1)

Magoo
Magoo

Reputation: 80113

Put a chr(10) directly after each chr(13). Windows standard is CRLF not CR alone. Loading such a file into an editor and saving it will likely add in the missing LFs

Upvotes: 1

Related Questions