Search code examples
oracle-databasebatch-filesqlplus

sqlplus: get parameters of file.sql execution

From a bat file I am using a process which is calling SQL/Plus in the same way:

@echo off
start sqlplus user/password@instance_name file.sql <instance_name>

echo End of process
pause

Inside file.sql I have something like :

SET FEEDBACK OFF
SET UNDERLINE OFF
SET PAGESIZE 0
SET VERIFY OFF
SET SCAN ON
SET HEADING OFF
SET RECSEP OFF
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET TERMOUT OFF
SET LINESIZE 1000
SET BLOCKTERMINATOR OFF

set instance_name=<FROM PARAMETER>

SET VERIFY ON
SET HEADING ON

EXIT

How can I get the name which I am passing as parameter in SQL/Plus?

Solution

  • Use &1

    From the documentation the SQL/Plus command has the syntax:

    SQLPLUS [ [Options] [Logon|/NOLOG] [Start] ]

    Then the START parameter:

    Start

    @{url|file_name[.ext]} [arg ...]

    Specifies the name of a script and arguments to run. The script can be called from the local file system or from a web server.

    SQLPlus passes the arguments to the script as if executing the file using the SQLPlus START command. If no file suffix (file extension) is specified, the suffix defined by the SET SUFFIX command is used. The default suffix is .sql.

    See the START command for more information.

    Then for the START command:

    arg ...

    Data items you wish to pass to parameters in the script. If you enter one or more arguments, SQL*Plus substitutes the values into the parameters (&1, &2, and so forth) in the script. The first argument replaces each occurrence of &1, the second replaces each occurrence of &2, and so on.