OraFAQ Forum: Client Tools » Passing parameter to sqlplus script

Home » Other » Client Tools » Passing parameter to sqlplus script

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Passing parameter to sqlplus script [message #674400]

Fri, 25 January 2019 05:06

ramya29p
Messages: 146
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.

Code: [Select all] [Show/ hide]

sqlplus /nolog @schema_compile.sql

schema_compile.sql file with below script

Code: [Select all] [Show/ hide]

conn schema_name
sho user
exec dbms_utility.compile_schema('schema_name',FALSE)

I tried passing the parameter, but it did not work.

Code: [Select all] [Show/ hide]

sqlplus /nolog @schema_compile.sql schema_name

schema_compile.sql file with below script

Code: [Select all] [Show/ hide]

conn &1
sho user
exec dbms_utility.compile_schema('&1',FALSE)

Could anyone please help me.

Report message to a moderator

Re: Passing parameter to sqlplus script [message #674401 is a reply to message #674400]

Fri, 25 January 2019 06:01

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What "it did not work" means?

It seems that password is missing.

Report message to a moderator

Re: Passing parameter to sqlplus script [message #674451 is a reply to message #674401]

Mon, 28 January 2019 02:09

ramya29p
Messages: 146
Registered: November 2007
Location: Chennai

Senior Member

I have tried as below

Code: [Select all] [Show/ hide]

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

conn &1/password@schema

In red, you must give the service name (tnsnames.ora entry).

Report message to a moderator

Re: Passing parameter to sqlplus script [message #675420 is a reply to message #674400]

Thu, 28 March 2019 04:36

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...

Report message to a moderator

Re: Passing parameter to sqlplus script [message #675422 is a reply to message #675420]

Thu, 28 March 2019 08:22

JPBoileau
Messages: 88
Registered: September 2017

Member

Here you go.

Code: [Select all] [Show/ hide]

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 22:23:53 CDT 2025