Home » SQL & PL/SQL » Client Tools » Passing parameter to sqlplus script
Passing parameter to sqlplus script [message #674400] Fri, 25 January 2019 05:06 Go to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
Hi,
I want to pass parameter to the .sql file via sqlplus script for connecting the Database.

I have created a batch file having below script.
sqlplus /nolog @schema_compile.sql

schema_compile.sql file with below script
conn schema_name
sho user
exec dbms_utility.compile_schema('schema_name',FALSE)

I tried passing the parameter, but it did not work.
sqlplus /nolog @schema_compile.sql schema_name

schema_compile.sql file with below script
conn &1
sho user
exec dbms_utility.compile_schema('&1',FALSE)


Could anyone please help me.
Re: Passing parameter to sqlplus script [message #674401 is a reply to message #674400] Fri, 25 January 2019 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What "it did not work" means?

It seems that password is missing.

Re: Passing parameter to sqlplus script [message #674451 is a reply to message #674401] Mon, 28 January 2019 02:09 Go to previous messageGo to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
I have tried as below

conn &1/password@schema
sho user
exec dbms_utility.compile_schema('&1',FALSE)

but still it is nor working. Getting error as "invalid username/password;login denied"

but if i mention the schemaname then it is working.

[Updated on: Mon, 28 January 2019 02:10]

Report message to a moderator

Re: Passing parameter to sqlplus script [message #674453 is a reply to message #674451] Mon, 28 January 2019 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
but still it is nor working. Getting error as "invalid username/password;login denied"

Give the correct password.

You MUST copy and paste what you do and get.

[Updated on: Mon, 28 January 2019 02:20]

Report message to a moderator

Re: Passing parameter to sqlplus script [message #674454 is a reply to message #674451] Mon, 28 January 2019 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
conn &1/password@schema
In red, you must give the service name (tnsnames.ora entry).

Re: Passing parameter to sqlplus script [message #675420 is a reply to message #674400] Thu, 28 March 2019 04:36 Go to previous messageGo to next message
jessika
Messages: 1
Registered: March 2019
Junior Member
Arguments or variables may be passed to a shell script. Simply list the arguments on the command line when running a shell script. In the shell script, $0 is the name of the command run (usually the name of the shell script file); $1 is the first argument, $2 is the second argument, $3 is the third argument, etc...

Re: Passing parameter to sqlplus script [message #675422 is a reply to message #675420] Thu, 28 March 2019 08:22 Go to previous message
JPBoileau
Messages: 79
Registered: September 2017
Member
Here you go.

C:\jp>more testscript.sql

connect &1/&2@&3
select '&4' from dual;
exit

C:\jp>sqlplus /nolog @testscript.sql jboileau password dev1 "hello world"

SQL*Plus: Release 18.0.0.0.0 Production on Thu Mar 28 09:20:03 2019
Version 18.2.0.0.0

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

Connected.
old   1: select '&4' from dual
new   1: select 'hello world' from dual

'HELLOWORLD
-----------
hello world

JP
Previous Topic: Blank rows in spooled txt output file using SQL developer
Next Topic: FNDLOAD for Specific DFF Context
Goto Forum:
  


Current Time: Sun Dec 08 07:14:04 CST 2019