Pass parameter to SQL file from within another SQL file

Question

I thought for sure there would be an SO question on this, but I haven't been able to find one.

I have 2 SQL files, myFile1.sql and myFile2.sql. myFile1.sql calls myFile2.sql like so:

-- In myFile1.sql:
@scripts/myFile2

This works with no problem, but now I'd like to pass an argument to the file. I've tried doing the following, with no success (results in a File Not Found exception):

@scripts/myFile2 'ImAnArgument'

Does anyone know what the syntax would be to do this?

Have you tried it without the parameter? If so does it run as expected? — Allan
– Allan, Commented Jun 3, 2014 at 20:24
It runs without issue. I have been varying setting a parameter to &1 or not to see what happens. — user1205577
– user1205577, Commented Jun 3, 2014 at 20:54

Allan · Accepted Answer · 2014-06-03 20:29:22Z

0

I'm guessing your problem is that scripts/myFile2.sql is a relative path from the script it is located in. If that is so, then it is following that path from the directory where SQL*Plus was started (the current working directory). If this is the problem, then it's not the parameter that is the issue, but rather that SQL*Plus can't find the file. In this case, you should use @@, which invokes the path relative to the file it's located in.

The parameter should work just as you proposed (documentation). Parameters provided when invoking a file are placed into substitution variables (rather than bind variables) and can be referenced by using an ampersand followed by the argument number. In your example, 'ImAnArgument' would be &1.

edited Jun 3, 2014 at 20:29

answered Jun 3, 2014 at 20:22

Allan

17.5k5 gold badges54 silver badges73 bronze badges

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

Comments

user1205577 · Accepted Answer · 2014-06-03 21:08:57Z

0

After many attempts, I wasn't able to pass a parameter in (and I still don't understand why not). But here is what I did to get the same affect:

-- In myFile1.sql:
DEFINE my_arg = 'ImAnArgument';
@scripts/myFile2

Then

-- In myFile2.sql 
-- Do stuff using the variable my_arg, such as
SELECT my_arg FROM my_table;

answered Jun 3, 2014 at 21:08

user1205577

2,4809 gold badges38 silver badges48 bronze badges

Collectives™ on Stack Overflow

Pass parameter to SQL file from within another SQL file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related