SQL script error for passed parameter

Question

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

Marco Baldelli · Accepted Answer · 2014-12-26 14:07:20Z

2

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';

answered Dec 26, 2014 at 14:07

Marco Baldelli

3,7381 gold badge24 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

SQL script error for passed parameter

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related