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

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