The same stored procedure in schema doesn't work package-CodePudding

I have following stored procedure inside the package:

CREATE OR REPLACE PROCEDURE P_TRUNC_I_SUB_PARTITION (
      P_TABLE_NAME   IN VARCHAR2,
      P_STICHTAG     IN NUMBER,
      P_SUB_ID       IN VARCHAR2 DEFAULT '*')
   IS
      SQL_TRUNCATE                   VARCHAR2 (4000);
      VVPRUEF                        VARCHAR2 (4000);
      SQL_ADD_SUBPARTITION           VARCHAR2 (4000);
      PROOF_IF_DATA                  VARCHAR2 (4000);
      PARTITION_DOES_EXIST           EXCEPTION;
      PRAGMA EXCEPTION_INIT (PARTITION_DOES_EXIST, -14622);
 
      PARTITION_DOES_NOT_EXIST_SUB   EXCEPTION;
      PRAGMA EXCEPTION_INIT (PARTITION_DOES_NOT_EXIST_SUB, -14702);
      PARTITION_DOES_NOT_EXIST2      EXCEPTION;
      PRAGMA EXCEPTION_INIT (PARTITION_DOES_NOT_EXIST2, -02149);
   BEGIN
      -- Wenn keine bestimmte Subpartition angegeben ist, dann truncate auf Partition Ebene
      IF P_SUB_ID = '' OR P_SUB_ID = '*'
      THEN
         SQL_TRUNCATE :=
               'ALTER TABLE '
            || P_TABLE_NAME
            || ' TRUNCATE PARTITION FOR ('
            || P_STICHTAG
            || ') UPDATE INDEXES';
      ELSE
         SQL_TRUNCATE :=
               'ALTER TABLE '
            || P_TABLE_NAME
            || ' TRUNCATE SUBPARTITION FOR ('
            || P_STICHTAG
            || ','
            || ''''
            || P_SUB_ID
            || ''''
            || ') UPDATE INDEXES';
      END IF;
 
      EXECUTE IMMEDIATE (SQL_TRUNCATE);
   EXCEPTION
      -- Wenn diese bestimmte Subpartition bereits vorhanden ist, dann leere sie
      WHEN PARTITION_DOES_NOT_EXIST2
      THEN
         DBMS_OUTPUT.PUT_LINE (SQLERRM);
         NULL;
      WHEN PARTITION_DOES_NOT_EXIST_SUB
      THEN
         DBMS_OUTPUT.PUT_LINE (SQLERRM);
         NULL;
      WHEN OTHERS
      THEN
         RAISE_APPLICATION_ERROR (
            -20002,
            'P_TRUNC_I_SUB_PARTITION(): ' || SUBSTR (SQLERRM, 1, 500));
   END P_TRUNC_I_SUB_PARTITION;

Inside a schema it works:

CALL BAISMART.P_TRUNC_I_SUB_PARTITION('TABLE_NAME', 20220126, 'PURTITION_NAME')

but when I call it from the package:

CALL BAISMART.PCK_BAIS_UTIL.P_TRUNC_I_SUB_PARTITION('TABLE_NAME', 20220126, 'PURTITION_NAME')

I get an error:

SQL Error [6553] [65000]: ORA-06553: PLS-306: wrong number or types of arguments in call to 'P_TRUNC_I_SUB_PARTITION'.

Code of the package itself:


-- DROP PACKAGE BAISMART.PCK_BAIS_UTIL;
 
CREATE OR REPLACE PACKAGE BAISMART.PCK_BAIS_UTIL
AS
   PROCEDURE P_TRUNC_I_SUB_PARTITION;
END PCK_BAIS_UTIL;
 
 
 
CREATE OR REPLACE PACKAGE BODY BAISMART.PCK_BAIS_UTIL
AS
     ------------------------
  PROCEDURE P_TRUNC_I_SUB_PARTITION (
      P_TABLE_NAME   IN VARCHAR2,
      P_STICHTAG     IN NUMBER,
      P_SUB_ID       IN VARCHAR2 DEFAULT '*')
   IS
      SQL_TRUNCATE                   VARCHAR2 (4000);
      VVPRUEF                        VARCHAR2 (4000);
      SQL_ADD_SUBPARTITION           VARCHAR2 (4000);
      PROOF_IF_DATA                  VARCHAR2 (4000);
      PARTITION_DOES_EXIST           EXCEPTION;
      PRAGMA EXCEPTION_INIT (PARTITION_DOES_EXIST, -14622);
 
      PARTITION_DOES_NOT_EXIST_SUB   EXCEPTION;
      PRAGMA EXCEPTION_INIT (PARTITION_DOES_NOT_EXIST_SUB, -14702);
      PARTITION_DOES_NOT_EXIST2      EXCEPTION;
      PRAGMA EXCEPTION_INIT (PARTITION_DOES_NOT_EXIST2, -02149);
   BEGIN
      -- Wenn keine bestimmte Subpartition angegeben ist, dann truncate auf Partition Ebene
      IF P_SUB_ID = '' OR P_SUB_ID = '*'
      THEN
         SQL_TRUNCATE :=
               'ALTER TABLE '
            || P_TABLE_NAME
            || ' TRUNCATE PARTITION FOR ('
            || P_STICHTAG
            || ') UPDATE INDEXES';
      ELSE
         SQL_TRUNCATE :=
               'ALTER TABLE '
            || P_TABLE_NAME
            || ' TRUNCATE SUBPARTITION FOR ('
            || P_STICHTAG
           || ','
            || ''''
            || P_SUB_ID
            || ''''
            || ') UPDATE INDEXES';
      END IF;
 
      EXECUTE IMMEDIATE (SQL_TRUNCATE);
   EXCEPTION
      -- Wenn diese bestimmte Subpartition bereits vorhanden ist, dann leere sie
      WHEN PARTITION_DOES_NOT_EXIST2
      THEN
         DBMS_OUTPUT.PUT_LINE (SQLERRM);
         NULL;
      WHEN PARTITION_DOES_NOT_EXIST_SUB
      THEN
         DBMS_OUTPUT.PUT_LINE (SQLERRM);
         NULL;
      WHEN OTHERS
      THEN
         RAISE_APPLICATION_ERROR (
            -20002,
            'P_TRUNC_I_SUB_PARTITION(): ' || SUBSTR (SQLERRM, 1, 500));
   END P_TRUNC_I_SUB_PARTITION;
 
 
END PCK_BAIS_UTIL;

Has anybody any idea why it can happen?

CodePudding user response：

Your package specification and body don't match; so you will get a compilation error from the body, and your call will get PLS-00306.

Your specification says:

   PROCEDURE P_TRUNC_I_SUB_PARTITION;

so the procedure takes no arguments. That should match the body:

   PROCEDURE P_TRUNC_I_SUB_PARTITION (
      P_TABLE_NAME   IN VARCHAR2,
      P_STICHTAG     IN NUMBER,
      P_SUB_ID       IN VARCHAR2 DEFAULT '*'
   );

db<>fiddle with the modified specification.