Forum Stats

  • 3,734,328 Users
  • 2,246,950 Discussions


Pluggable DB : Shell script fails in sqlplus

Vikas Yadav 25-Oracle
Vikas Yadav 25-Oracle Member Posts: 50 Employee


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';


/bin/ls ${dirpath}



  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited Sep 15, 2020 1:16AM

    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.

Sign In or Register to comment.