We help IT Professionals succeed at work.

connect to the Oracle database using shell script

chanikya
chanikya asked
on
2,412 Views
Last Modified: 2012-02-16
Hi Experts,

I am new to shell script.

In the shell script i have the below statement.

sqlplus  / @connectstring << EOF
.... some statements
EOF

When I am  trying to execute the shell script, it is asking  me  username and password.
But as per my requirement it should not ask the username and password and also i should not hard code username and password in the shell script.

what else i need to do, to make use of the same statement without any changes.

your suggestions will be highly appreciated.

Thanks In Advance .

Thanks,
Chanikya.
Comment
Watch Question

Walter RitzelSenior Software Engineer
CERTIFIED EXPERT

Commented:
Hi,
my understand is that you should be creating 2 files, for more clarity:
1) a file with all your SQL statements. You can save it with extension .sql
2) shell script.

The shell script will have the following command line:

sqlplus <user>/<password>@connectionstring @file_name.sql

And that's it.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I have to assume you have already set up OS authentication and the ORACLE variables set  in the script.

Remove the space before the @:

sqlplus  /@connectstring << EOF
.... some statements
EOF
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>should be creating 2 files, for more clarity

Not necessary in UNIX.  'Here' scripts are common.  In my opinion 2 files makes it more confusing.
Praveen Kumar ChandrashekatrDatabase Analysist Senior

Commented:
I won't think you can able to login without hard coding the user name and password.

Oracle has to know what crediential as to be logged in.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>I won't think you can able to login without hard coding the user name and password

Look up OS Authentication:
http://www.oracle-base.com/articles/misc/OsAuthentication.php
Walter RitzelSenior Software Engineer
CERTIFIED EXPERT

Commented:
Sorry, did not see your requirement for not hardcode the user and password.

In this case, you only need to pass as parameters for your shell script.

Then, it would be like this:

user=$1
passwd=$2

sqlplus $user/$passwd@connectionstring @file_name.sql
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Walter RitzelSenior Software Engineer
CERTIFIED EXPERT

Commented:
Ok, I agree. But he is asking a question around shell script.
The answer was presented here, not only by me, in a few variants.

That said, what you have recommended goes beyond that and is correct, but it is not in the scope of his question.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
The question centers around using OS Authentication not how to not use OS authentication.
Walter RitzelSenior Software Engineer
CERTIFIED EXPERT

Commented:
I'll use my right of not agree, as the title of this question is very precise and in the question itself, OS authentication is not mentioned.
This topic was introduced by your assumptions...

But enough of that. In any way, the user has information enough to make his script to work properly.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>sqlplus  / @connectstring << EOF

This is OS authentication.  I did not 'assume'.

Author

Commented:
I would like to know, whether it is possible with os authentication or not.. I got the answer.

sqlplus $username/$password@connect , I got this in many forums..

Thanks a lot guys for your contribution.

Thanks,
Chanikya.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.