CODEWITHSUNDEEP
bashvariablesoracle11gcommand-line-argumentssqlplus
krupal
krupal

Reputation: 403

Passing variable user,pass,sid in sqlplus comman

I want to pass variables in sqlplus command in my bash script, answer might be given but doesn't work for me.

I tried some thing like this

#!/bin/bash
ssh [email protected] <<XX
echo Please enter an Oracle Username:
read USERNAME

echo "Please enter the Oracle Username's Password:"
read -s PASS 

SID=XE

export conn_str=$USERNAME/$PASS@$SID

sqlplus $conn_str  << EOF
select * FROM tabs;

exit
EOF
XX

also tried 

sqlplus $USERNAME/$PASS@SID            #with option -s  and -l

I also find solution like this but not worked for me.

oracle : 11g ,os : fedora 18

Is there any solution available for this ? thanks.

Upvotes: 0

Views: 5578

Answers (2)

Alex Poole
Alex Poole

Reputation: 191455

You haven't said what actually happens, but I'm guessing you aren't prompted for the credentials, and that maybe it can't find sqlplus. On a Red Hat box this works:

#!/bin/bash
echo Please enter an Oracle Username:
read USERNAME
echo "Please enter the Oracle Username's Password:"
read -s PASS
SID=XE
conn_str=$USERNAME/$PASS@$SID

ssh [email protected] << EOF
# set these for your specific environment
ORACLE_HOME=<path to ORACLE_HOME>
PATH=$PATH:$ORACLE_HOME/bin # or without .../bin depending on client
TNS_ADMIN=<path to tnsnames.ora directory, if not default>

sqlplus -s /nolog
connect $conn_str
select * FROM user_tables;
exit
EOF

This is collecting the values from the user on the local machine to avoid the 'Pseudo-terminal will not be allocated because stdin is not a terminal' issue.

It is then setting up the Oracle environment once on the remote server - what you need to set depends on which client you're using (particularly whether you're using the instant client, though if you're connecting as oracle that seems unlikely, and you can probably run oraenv).

I've also tweaked it to run SQL*Plus with /nolog and then connect once running, so the credentials aren't exposed in the ps output. And I've switched from the old tabs to the more common user_tables.

The nested heredocs work but aren't necessary; the SQL commands are already being entered in the right place and will be seen by SQL*Plus rather than the remote shell.

Of course, since I don't know what error you were actually seeing this is largely speculation. It would probably be simpler to have the client installed locally and use a SQL*Net connection rather than SSH, but there could be firewall restrictions we don't know about.

Upvotes: 2

konsolebox
konsolebox

Reputation: 75568

Try quoting your here doc delimiter to prevent stale variable expansions on the local machine.

ssh [email protected] <<'XX'

Upvotes: 0

Related Questions