Issues when user input data in sql command

Question

I have a batch files which when i run calls a SQL file. This SQL file prompts user to input data which I store in MYPeriod( this accepts date as input ).the output of SQL file is a CSV file. Here is my code for SQL file:

PROMPT Enter a period
ACCEPT MYPeriod PROMPT 'Period: '

SET LINESIZE 500 FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF HEAD OFF PAGESIZE 0 TERM OFF

spool E:\abc\Test\File1_&MYPeriod.csv

select Account || ',' || Membername || ',' || GROUP || ',' || FUTURE1  from ACTUAL_V@UAT1_Test where Key=21 and period_name= '&MYPeriod' ;
spool off
exit

My queries :

When I run this , a file gets generated in location E:\abc\Test with a name File1_12-2012csv.Lst. I want a csv file . If i hard code the file name(replace &MYPeriod by test) File1_Test.csv gets generated perfectly.Why the code is not able to create file with the name user has input..?
The output of this creates a csv file and retrieves the accounts from db but it prints two extra line at top. The new query and old query. How do I redefine my code, so that it gets remove automatically.

Appreciate your help guys.

First I want the file name to File1_(what user enters the period).csv.. now its not doing that.. — user2376670
– user2376670, Commented May 13, 2013 at 7:16
Second issue - if i fix my file name to File1_new.csv , then a csv file gets created with data , but this file has two extra lines :select Account || ',' || Membername || ',' || GROUP || ',' || FUTURE1 from ACTUAL_V@UAT1_Test where Key=21 and period_name= '&MYPeriod' ; and 2nd line select Account || ',' || Membername || ',' || GROUP || ',' || FUTURE1 from ACTUAL_V@UAT1_Test where Key=21 and period_name= '&20-2012' ; . I dont want these two lines . How do i remove it..? — user2376670
– user2376670, Commented May 13, 2013 at 7:18
Is there a way I create a file with user input date at end : File1_12_2013.csv..something like this.? — user2376670
– user2376670, Commented May 13, 2013 at 7:29

Alex Poole · Accepted Answer · 2013-05-13 17:52:00Z

2

Substitution variables are optionally terminated by a period to separate them from any characters that follow. If you want to use one in the string as well, you have to add that termination explicitly:

spool E:\abc\Test\File1_&MYPeriod..csv

For the two extra lines issue, add set verify off; at the moment it is set to on (by default) so it shows you the old and new value of any substitution variables you use.

The only way I know to get the date into the file name is to put it into a substitution variable first:

set termout off
column x_run_dt new_value y_run_dt
select to_char(sysdate, 'YYYYMMDDHH24MISS') as x_run_dt from dual;
set termout on
spool E:\abc\Test\File1_&MYPeriod._&y_run_dt..csv

The new_value clause lets you create a substitution variable, &y_run_dt in this case, with a value from a queried column, x_run_dt. Wrapping the select that generates that value between set termout clauses hides it from the normal output, when run as a script.

edited May 13, 2013 at 17:52

answered May 13, 2013 at 7:33

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2376670 Over a year ago

thanks it worked perfectly.Also I would like to add current date and time the file was run. how do i add that..?sumthing like spool E:\abc\Test\File1_&MYPeriod.(+current date and time in system).csv..how do i do that.?

Collectives™ on Stack Overflow

Issues when user input data in sql command

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related