How to remove extra lines in the output file?

namtrinhthanh · Post by **namtrinhthanh** » Tue Oct 03, 2006 1:42 am

I want to extract table from Oracle using DSExecute and sqlplus as below:

cmd = "sqlplus -s ":userID:"/":passWD:"@":dbName:" @":sFolder:"\eAccountBilling.sql ":extractDate:" > AccountBilling.txt"

Call DSExecute("NT",cmd,output,rc)

I use one parameter extractDate to pass to sql script file eAccountBilling.sql

In script file, I specify that parameter as &1. When this job runs, the value of the parameter I passed is: 2006-09-30

This job works fine, however, the output file contains the query result and 2 extra lines as:
old 1: select * from ACW.Package a where a.MIS_UPDATE_DATE between trunc(cast('&1' as date) -1) and trunc(cast('&1' as date) -1) + 0.99999
new 1: select * from ACW.Package a where a.MIS_UPDATE_DATE between trunc(cast('2006-09-30' as date) -1) and trunc(cast('2006-09-30' as date) -1) + 0.99999
... query result goes here

Could somebody tell me how to remove those 2 extra lines from the output file? Thank you!

ray.wurlod · Post by **ray.wurlod** » Tue Oct 03, 2006 3:03 am

Welcome aboard. :D

It would appear that you have a switch enabled in your shell that reports argument substitutions. If you can find out what this switch is, disabling it ought to remove the message.
Type CMD /? to find out more about shell switches.

You might also like to try using %1 rather than &1 as the argument placeholder.

ray.wurlod · Post by **ray.wurlod** » Tue Oct 03, 2006 3:04 am

Welcome aboard. :D

It would appear that you have a switch enabled in your shell that reports argument substitutions. If you can find out what this switch is, disabling it ought to remove the message.
Type CMD /? to find out more about shell switches.

You might also like to try using %1 rather than &1 as the argument placeholder.

namtrinhthanh · Post by **namtrinhthanh** » Tue Oct 03, 2006 7:37 pm

Thank you, Ray! Could you tell me some examples about the switch in the shell? I do not know how to find those switches.

ray.wurlod · Post by **ray.wurlod** » Tue Oct 03, 2006 8:41 pm

Open a CMD shell (Start > Run > cmd)
Type in CMD /?
Read what's displayed.
Type EXIT to exit from shell.

namtrinhthanh · Post by **namtrinhthanh** » Wed Oct 04, 2006 1:01 am

I have found the solution, we need to include this command in the script file: SET VERIFY OFF.

chulett · Post by **chulett** » Wed Oct 04, 2006 6:28 am

So, in other words an Oracle sqplus setting, nothing to do with the shell script at all but rather the sqlplus script.