sqlplus string to connect with out showing in ps command

[Ratzie]

I am trying to reconfigure our scripts to prevent the showing of user name and password in the ps command.
But when I try I get an error:
Original:
sqlplus -s user1/userpw < SELECT tn from TN_TABLE WHERE TN = '$i';
yada yada...

What I am trying to do:
sqlplus -s < connect user1/userpw
yada, yada

ERROR:
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] | [INTERNAL]
where ::= [/][@] | /

[TwoProc · Accepted solution]

sqplus /nolog <connect user1/userpw
SELECT tn from TN_TABLE WHERE TN = '$i';
yada yada...
exit
EOF

[Ratzie]

Great works!
Is there a way to get rid of the extra fluff on feed back. I am piping to a file:

Extra fluff:
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Jun 30 13:00:25 2005

(c) Copyright 2000 Oracle Corporation. All rights reserved.

SQL> Connected.
SQL>
no rows selected

SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

[TwoProc]

You already had that part...

sqplus -s /nolog <connect user1/userpw
SELECT tn from TN_TABLE WHERE TN = '$i';
yada yada...
exit
EOF

[Ratzie]

yes but it still returns information on every disconnect, if I have 7000 records to check...

SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

[TwoProc]

Wow, I'm on 9i, and when I use the "-s" flag, it doesn't show what you're showing. But, while we're at it, why would disconnect and rec onnect 7000 times? Wouldn't just one connection with 7000 subsequent commands and then a single exit do it for you?

[Ratzie]

But I am running a while loop to a huge file.

cat file |while read i
do
echo $i
sqlplus /nolog < CONNECT $LOGONID
SELECT tn from tn_table WHERE TN = '$i';
EOF
done

[Ratzie]

This is on 8.1.4 if there is a better way to check this OI am all open to suggestions.

[Yogeeraj_1]

hi,

one work around would be to spool to a file in the sqlplus then use another file which you would stuff all the outputs from sqlplus, i.e. something like

cat /dev/null > myresults.lis
cat file |while read i
do
echo $i
sqlplus /nolog <CONNECT $LOGONID
spool tmpfile.lis
SELECT tn from tn_table WHERE TN = '$i';
spool off
EOF
cat tmpfile.lis >> myresults.lis
done

hope this helps!
regards
yogeeraj

[Yogeeraj_1]

hi again,

at the sqlplus level, you can also use the SQLPLUS commands like "set heading off" etc.
to remove any other unimportant info which of course you can also do at script level.

good luck

regards
yogeeraj