CODEWITHSUNDEEP
oracle-databaseunixplsqloracle12c
Venkata Ramireddy CH
Venkata Ramireddy CH

Reputation: 753

calling a package from shell script

I have a package create with multiple procedure defined. I am trying to call this package from shell script. My package is written under schema ADM. Package name is: PK_COMPUTE_FIELDS.

CREATE OR REPLACE PACKAGE ADM.PK_COMPUTE_FIELDS AS
PROCEDURE computeBalTotal(p_prd_id in integer,p_seg_id IN integer);
  PROCEDURE computeBilAmt(p_prd_id in integer,p_seg_id IN integer);
  PROCEDURE computePaidAmt(p_prd_id in integer,p_seg_id IN integer);
  END PK_COMPUTE_FIELDS ;
CREATE OR REPLACE PACKAGE BODY ADM.PK_COMPUTE_FIELDS 
AS
PROCEDURE computeBalTotal(p_prd_id in integer,p_seg_id IN integer) IS
BEGIN
//derivation logic
END;
  PROCEDURE computeBilAmt(p_prd_id in integer,p_seg_id IN integer)  IS
BEGIN
//derivation logic
END;

  PROCEDURE computePaidAmt(p_prd_id in integer,p_seg_id IN integer)  IS
BEGIN
//derivation logic
END;

END PK_COMPUTE_FIELDS ;

I am using the below shell script to call this package

sqlplus -s <USER>/<PWD>@<DBNAME> <<EOF >> $LOG_NAME

set serveroutput on
whenever sqlerror exit failure rollback
DECLARE 
  P_PRD_ID NUMBER;
  P_SEG_ID NUMBER;

BEGIN 
  P_PRD_ID := 1907;
  P_SEG_ID := 1;

 execute immediate ADM.PK_COMPUTE_FIELDS.computeBalTotal( P_PRD_ID, P_SEG_ID );
  COMMIT; 
END; 

/
EOF

I am getting an error as:

ERROR at line 9:
ORA-06550: line 9, column 20:
PLS-00201: identifier 'ADM.PK_COMPUTE_FIELDS.computeBalTotal' must be declared
ORA-06550: line 9, column 2:
PL/SQL: Statement ignored

Please guide me to correct this issue.

Upvotes: 0

Views: 3673

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

You don't need execute immediate to call the procedure since you already have a BEGIN..END block.

change it to simple

ADM.PK_COMPUTE_FIELDS.computeBalTotal( P_PRD_ID, P_SEG_ID );

Procedures with known arguments can be called directly inside PL/SQL like the above. EXECUTE IMMEDIATE is used in a procedure to execute a dynamic SQL statement(select,insert,update,delete etc) or anonymous PL/SQL block (those which use BEGIN..END;). It is only required when you have arguments: table names,column names etc that you don't know in advance.

If you want to call a procedure outside a BEGIN..END (SQL* plus scope ), you may use EXECUTE

EXECUTE schema.pkg_name.proc_name(args) or simply EXEC schema.pkg_name.proc_name(args)

Upvotes: 2

Related Questions