Compile a PLSQL package code that was passed to an sqlplus session via a shell script?

Question

I have two files: (1) and (2)

(1) A Shell script - wrapper.sh

#!/bin/sh
echo "start of shell script"
FILE_NAME="XX_PKGb.sql"
sqlplus user/password @sql_deploy.sql $FILE_NAME
echo "end of shell script."
exit $RETURN_CODE

(2) A plsql script - sql_deploy.sql

set serveroutput on
set term on
set echo off
set feedback off
set define on
set verify off
define SQL_SCRIPT=&1
declare
 v_sql varchar2(240);
begin
 v_sql := 'ALTER SESSION SET CURRENT_SCHEMA = XXEOM';
 --alter the schema (this works)
 EXECUTE IMMEDIATE v_sql; 
 --compile the package (this throws an error) 
 @@'&SQL_SCRIPT'; 
end;
/
exit;

The file XX_PKGb.sql is also present in the same location as the above two files. When I run the shell script, i see that the sqlplus session executes the sql_deploy.sql script, it was able to alter the schema, however, while compiling the package, it threw the below error. How do I compile the piece of code in this sql file please:

CREATE OR REPLACE PACKAGE BODY XXG_PKG
*
ERROR at line 15:
ORA-06550: line 15, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge

• tomas: It looks fine on the shell side. It will be about the SQL script. It would be better to ask it at StackOverflow. I've flagged it for migration.
• tomas: Anyhow, please include XX_PKGb.sql.
• grochmal: Why do you go through the trouble of using @@'&SQL_SCRIPT'; from inside sqlplus? If you only want to run both files (or just the second one) you could set FILE_NAME="@XX_PKGb.sql" and sqlplus will run it fine.

Answer 1

Running XX_PKGb.sql (a package create statement) from with your PL/SQL block is not allowed. If you must do it there then it needs to be dynamic SQL.

The better way is to simply run it from SQLPlus;

set term on
set echo off
set feedback off
set define on
set verify off
ALTER SESSION SET CURRENT_SCHEMA = XXEOM 
/
@XX_PKGb.sql
/
exit

Answer 2

You can't call an external SQL script from within a procedure block. You're trying to issue SQLPlus commands to the PL/SQL compiler. Do you see why that won't work?

Instead, try using this format to pipe normal commands into SQLPlus within your shell script:

sqlplus / as sysdba << EOF
set serveroutput on 
set term on 
set echo off 
set feedback off 
set define on 
set verify off
ALTER SESSION SET CURRENT_SCHEMA = XXEOM;
@XX_PKGb.sql;
EOF