SQL file executes with no error but also no results through shell script

I have a sqlfile that I want to run via shell script against an Oracle pluggable database. The sql file basically just loops through table names to apply grants to a role. When I run the script, it looks like it executes the file, but it really doesnt, no grants are applied. When I log into sqlplus and execute the sql file manually, it does actually execute and I see the output I expect.

Can someone help me understand the reason for this behavior and how to resolve it?

==SCRIPT==

#!/bin/ksh

ORACLE_SID=${1}
TWO_TASK=${2}

sqlplus -s sys/${sys_password}@${TWO_TASK} as sysdba << !EOSQL! >> ${output}
        @/tmp/foobar.sql MY_SCHEMA;
exit
!EOSQL!

==SCRIPT RESULT==

SQL> SQL> old   7: select substr('&1',6,6) into rolesuffix from dual;
new   7: select substr('MY_SCHEMA',6,6) into rolesuffix from dual;
old   8: FOR lCursorRec in (select * from dba_objects where object_type in ('TABLE', 'VIEW') and owner in ('&1'))
new   8: FOR lCursorRec in (select * from dba_objects where object_type in ('TABLE', 'VIEW') and owner in ('MY_SCHEMA'))

PL/SQL procedure successfully completed.

==RUN ON COMMAND LINE==

SQL> @/tmp/foobar.sql MY_SCHEMA;
old   7: select substr('&1',6,6) into rolesuffix from dual;
new   7: select substr('MY_SCHEMA',6,6) into rolesuffix from dual;
old   8: FOR lCursorRec in (select * from dba_objects where object_type in ('TABLE', 'VIEW') and owner in ('&1'))
new   8: FOR lCursorRec in (select * from dba_objects where object_type in ('TABLE', 'VIEW') and owner in ('MY_SCHEMA'))

GRANT SELECT ON MY_SCHEMA."TABLE_A" TO READ_ROLE
GRANT SELECT ON MY_SCHEMA."TABLE_B" TO READ_ROLE
.
.
.

Answer

At advice of commenter, I double checked the script was logging into the PDB correctly, but it wasn’t, it was going to the CDB so the sqlfile that runs without error couldn’t find expected schemas. Updated script to do away with TWO_TASK since I was using it incorrectly and need to read more of the documentation on it, and logged in directly to PDB.

orasid=${TWO_TASK}
.
.
.
sqlplus -s sys/${sys_password}@${orasid} as sysdba << !EOSQL! >> ${output}
        @/tmp/foobar.sql MY_SCHEMA;
exit
!EOSQL!

Advertisement

Answer