Pluggable DB : Shell script fails in sqlplus

Vikas Yadav 25-Oracle

Hi,

I have an external table which refers a pre-processor ,which further refers a shell script.

It works fine with a non pluggable db. The script when modified with  'alter session', works fine in a pluggable db node too.

However, when the script is accessed from sqlplus, it does not work inside a pluggable db node. It gives me ora-01017 error for tables referenced inside the script.

But, if I hard code the pwd details inside the script it works .

May I know, how the script can made to work without having to provide pwd details in it, in both pluggable and non pluggable db node.

Please note: made use of sysdba user to run the scripts in unix environment.

Let me know if more detail is required.

Script :

var1=`/bin/basename $1`

dirpath=`$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF

set pages 0 feed off

alter session set container=PBRO1I;

select directory_path from all_directories

where directory_name = '$var1';

EOF`

/bin/ls ${dirpath}

Thanks

Gaz in Oz

Calling a pre-processor external table script with the login credentials "as sysdba" is a BAD idea...

Create a database user user specifically for this task and implement Oracle wallet to avoid having to hard code the password in a script.