CODEWITHSUNDEEP
sqloracle-databaseoracle11gcmd
boxed__l
boxed__l

Reputation: 1336

SQL script error for passed parameter

I am using Command Prompt to run SQL Scripts. One of the script requires a file name which is read from a settings file (file_name=ABC) by CMD and feed to the script like so

sqlplus @"Script_Run" %file_name%

Inside the script the input is read in the line:

UTL_FILE.FOPEN('DIR','&1.ext','R');

Now when I run the bat file,

the substitution happening is: 

old: "&1.ext"
new: "ABCext"

the script returns "Invalid File Operation" error.

But when I change the script line to:

UTL_FILE.FOPEN('DIR','&1..ext','R');

it works. Why does the sql script here require two dots ? improved formatting

Upvotes: 0

Views: 58

Answers (1)

Marco Baldelli
Marco Baldelli

Reputation: 3728

As documented in the SQL*Plus® User's Guide and Reference, the period is used to concatenate the value of a substitution variable with other characters:

If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character. For example:

SELECT SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID='&X.5';
Enter value for X:  20

is interpreted as

SELECT SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID='205';

Upvotes: 2

Related Questions