Parameterizing table name in sqlplus input file

Question

I am trying to export some data using sqlplus and the Oracle spool functionality. The problem is that the SQL input file where I am defining my export is not letting me parameterize the table name from which I am exporting data -- it wants a literal :(

Calling sqlplus in a shell script:

sqlplus $USER/$PASSWD@$ORADB<<!

@export.sql $OUT_FILE $SOME_VAR $ENV

exit
!

export.sql:

set heading off
set head off
set term off
set tab off
set embedded on
set feedback off
set pagesize 0
set linesize 800
set trimspool on
set verify off

spool &1

SELECT '&2|' || some_col
FROM &3_TABLE
/
spool off

When $ENV is set to 'dev', I get

Enter value for 3_TABLE

whereas I want it to use dev_TABLE. When I unparameterize the table names in the sql file, the output runs fine. Also note that there is param &2, which is $SOME_VAR from the shell and it gets displayed evaluated fine. The only problem is in the FROM statement.

Is there any way to tell the sql input file to replace the parameterized table names before running SQL?

Thanks

Alex Poole · Accepted Answer · 2012-10-24 07:18:13Z

6

The problem is that SQL*Plus is treating the whole string after the &, up to the next whitespace or simlar, as the substitution variable name. Clearly that isn't what you want here.

Fortunately they've thought of this, and you can denote the end of the variable name with a .:

FROM &3._TABLE

(At least, that works for named variables, and I'm almost sure it will for positional ones... if not then you'd need to define a new variable set to &3 as a workaround).

It is in the documentation, but blink and you'll miss it:

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

There's a related effect that you may want to bear in mind for the future. If the next character after the substitution variable is a . anyway - between the schema and table, or between table and column, for example - then that will be interpreted as the substitution terminator. Say you were passing the schema separately as &4, with value 'scott'; this:

FROM &4.&3._TABLE

looks reasonable but would be substituted as scottdev_TABLE, which won't be recognised. So in that instance you need to have an extra one:

FROM &4..&3._TABLE

which would be substituted as scott.dev_TABLE.

edited Oct 24, 2012 at 7:18

answered Oct 23, 2012 at 23:16

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

1 Comment

amphibient Over a year ago

dude, you are awesome !! i am leaving work now, will mark as answer when i get home (need 10 min or so)

Collectives™ on Stack Overflow

Parameterizing table name in sqlplus input file

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