Shell Programming and Scripting

Hi All,

I ve a to extract data from oracle database . I already wrote code for the same

----------------------------------------------------------------------------------------
/u01/oracle/10.2/db_tst/bin/sqlplus -s $LOGIN/$PASS@$DBCONN <<-END > ${DSTDIR}/${FLNAME}
set serveroutput on;
set head off;
set feed off;
set timing off;
set pages 0;
set line 200
$QUERY
EXIT;
END
----------------------------------------------------------------------------------------

Now , the issue is to execute this from a login named tester other than oracle

iam not able to execute "/u01/oracle/10.2/db_tst/bin/sqlplus" from tester . The error coming up is "Permission Denied"

The permissions for /u01/oracle/10.2/db_tst/bin/sqlplus are as rwxr-xr-x

How to execute the command using normal login?

Thanks in Advance

I think you can try with super user command 'su'...
You can try something like below:

su - user_to_execute -c "sqlplus username/password < inp_sql_file > result_file"
ex:

su - user1 -c "sqlplus orauser1/password < inp.sql >out.sql

i tried with that. su command is interactively asking for password.

then i executed the command as below

su - oracle -c "sqlplus scott/tiger@db_tst" < a

where file 'a' contains password for oracle user.

i got the error "Standard in must be a tty"

Hi malle,

You can't provide password like that...And I don't think you can set up an automatic login and execution of a command without user interaction while using "su" command .

the best bet will be going for tools such as expect
http://expect.nist.gov

It would be great and useful if there anyone having any other idea?

Thank you.

what is the permission for /u01/oracle/10.2/db_tst/bin/ ?

premissions for /$ORACL_HOME/bin/sqlplus : rwxr-x--x

for another user to execute the sqlplus command, the 'others' permission group should have 'r' and 'x' enabled. give a read permission to sqlplus and try again.