Batch with parameters in Sqlplus

Question

Hello,

I need to create a batch file that drops the database user, recreates and imports the database with an existing backup.

I have already created a routine that drops in the user and recreates with the defined permissions, this routine I saved in a file and to use this routine, I need to inform two parameters, the user name and if it is to drop or keep if this user exists, follows an example:

SQL> @CriaUsuario
Indique o nome do usußrio.: <Nome_do_usuario>
Dropar usußrio? (s/n).: <sim para dropar e não para manter>

I already created a batch, which connects and calls the routine described above, but I cannot pass the parameters to this one. It follows batch:

@ECHO OFF
CLS

@ECHO OFF
CLS
SET usuario=<banco>
SET senha=<senha_do_banco>
SET sid=<sid>

SQLPLUS %usuario%/%senha%@%sid% @ImportaUsuario.sql "baseteste" "s"

However nothing happens, the system logs with the informed user, executes the file @Importausuario, but does not inform the two parameters. Follow the result in cmd:

SQL*Plus: Release 11.2.0.1.0 Production on Ter Mar 13 17:47:55 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Conectado a:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

Indique o nome do usußrio.:

Does anyone know the correct way to inform the parameters in this case?

Answer 1

You can use position substitution. For example, your "basetest" argument would be referenced by &1 within the script. Consequently your "s" argument would be &2 and so on.