Oracle package error

Question

I have package which will delete data from some tables. I created package which has function to audit in another table and procedure with initialization logic. I stopped on the strange Ora-errors. Maybe you have idea what can be wrong here.

This is the specification:

CREATE OR REPLACE PACKAGE CLIENT_DELETE_PACKAGE
IS
  deletion_results VARCHAR2(200);
  deletedEntries     NUMBER;
  min_date           DATE;
  max_date           DATE;
  -- Enable/disable logging
  Enable_Audit_Trail BOOLEAN := True;
  -- Enable Safe Mode
  Enable_Safe_Mode BOOLEAN := True;
  PROCEDURE AUDIT_TRAIL(
      MyTYPE       IN VARCHAR2,
      MyDATA       IN VARCHAR2,
      MyDatasource IN VARCHAR2,
      MyResultData IN VARCHAR2);
  -- ECHO Check that the package is available.
  FUNCTION ECHO(
      MyDATA IN VARCHAR2)
    RETURN VARCHAR2;
  -- JOB_REQUEST
  FUNCTION DEL_WLM_ALERT(
      MyAlertKey IN NUMBER)
    RETURN VARCHAR2;
  PROCEDURE INIT;
END CLIENT_DELETE_PACKAGE;
/

And the body:

CREATE OR REPLACE PACKAGE BODY CLIENT_DELETE_PACKAGE
AS
  -- Auditing
  PROCEDURE AUDIT_TRAIL(
      MyTYPE       IN VARCHAR2,
      MyDatasource IN VARCHAR2,
      MyResultData IN VARCHAR2)
  AS
    --allow to keep the entry even if a roll-back occurs in the other procedure.
    pragma autonomous_transaction;
  BEGIN
    /*ID: provided by sequence
    EVENT_TIME: default value SysDate
    TYPE: DELETED/LOCKED ITEM
    DATASOURCE_NAME: datasource name
    RESULT_DATA: data link to type
    */
    IF (Enable_Audit_Trail) THEN
      INSERT INTO CLIENT_DELETION_AUDIT
        ( id, TYPE, DATASOURCE_NAME, RESULT_DATA
        )
      SELECT SEQ_AUDIT_TRAIL.NEXTVAL, MyTYPE, MyDatasource, MyResultData FROM dual;
      COMMIT;
    END IF;
  END AUDIT_TRAIL;
-- -------------------------------------------------------------------------
-- Echoing function
-- -------------------------------------------------------------------------
  FUNCTION ECHO(
      MyDatasource IN VARCHAR2)
    RETURN VARCHAR2
  AS
  BEGIN
    /*check if the package is available */
    RETURN 'i am here';
  END ECHO;
-- -------------------------------------------------------------------------
-- Delete WLM Alert Data
-- -------------------------------------------------------------------------
  FUNCTION DEL_WLM_ALERT(
      MyAlertKey IN NUMBER)
    RETURN VARCHAR2
  AS
    countLookUp       NUMBER;
    countTotalEntries NUMBER;
    deletedEntries    NUMBER;
    dataSourceToCheck VARCHAR2(200);
    numberOfAlertLeft NUMBER;
    alert_trx_id      NUMBER;
    trx_group_id      NUMBER;
    large_str_id      NUMBER;
  BEGIN
    countTotalEntries:=0;
    deletedEntries   :=0;
    ---Get transaction ID for usage later---
    SELECT TRANSACTION_ID
    INTO alert_trx_id
    FROM WLM_RULE_ALERT WRA
    JOIN WLM_ALERT_HEADER WAH
    ON WAH.ALERT_IDENTIFIER = WRA.ALERT_IDENTIFIER
    WHERE wah.alert_key     = MyAlertKey
    AND rownum              =1;
    ---Get transaction GRoup ID for usage later---
    SELECT TRANSACTION_GROUP_ID
    INTO trx_group_id
    FROM ALERTING_TRANSACTION AT
    JOIN WLM_RULE_ALERT WRA
    ON WRA.TRANSACTION_ID = AT.ID
    JOIN WLM_ALERT_HEADER WAH
    ON WAH.ALERT_IDENTIFIER = WRA.ALERT_IDENTIFIER
    WHERE wah.alert_key     = MyAlertKey
    AND rownum              =1;
    ---Get LargeString ID for usage later---
    SELECT MIN(tg.largestring_id)
    INTO large_str_id
    FROM transaction_group tg
    WHERE tg.ID = trx_group_id
    AND rownum  =1;
    -- deleted from ALERT_MATCH_DATA --
    DELETE
    FROM ALERT_MATCH_DATA AMD
    WHERE ALERT_MATCH_ID IN
      (SELECT AM.ID
      FROM ALERT_MATCH AM
      JOIN WLM_RULE_ALERT WRA
      ON WRA.id = AM.WLM_RULE_ALERT_ID
      JOIN WLM_ALERT_HEADER WAH
      ON WAH.ALERT_IDENTIFIER = WRA.ALERT_IDENTIFIER
      WHERE wah.alert_key     = MyAlertKey
      );
    -- deleted from ALERT_MATCH --
    DELETE
    FROM ALERT_MATCH AM
    WHERE AM.WLM_RULE_ALERT_ID IN
      (SELECT WRA.id
      FROM WLM_RULE_ALERT WRA
      JOIN WLM_ALERT_HEADER WAH
      ON WAH.ALERT_IDENTIFIER = WRA.ALERT_IDENTIFIER
      WHERE wah.alert_key     = MyAlertKey
      );
    deletedEntries      :=deletedEntries+SQL%ROWCOUNT;
    IF(numberOfAlertLeft =0) THEN
      -- deleted from DATA_SOURCE_CUSTOMER --
      DELETE
      FROM DATA_SOURCE_CUSTOMER DSC
      WHERE ID       = alert_trx_id;
      deletedEntries:=deletedEntries+SQL%ROWCOUNT;
      -- deleted from DATA_SOURCE_SWIFT --
      DELETE
      FROM DATA_SOURCE_SWIFT DSC
      WHERE ID       = alert_trx_id;
      deletedEntries:=deletedEntries+SQL%ROWCOUNT;
      -- deleted from ALERTING_TRANSACTION --
      DELETE
      FROM ALERTING_TRANSACTION AT
      WHERE AT.ID                = alert_trx_id
      OR AT.TRANSACTION_GROUP_ID = trx_group_id;
      deletedEntries            :=deletedEntries+SQL%ROWCOUNT;
      IF( countLookUp            = 1 ) THEN
        -- deleted from TRANSACTION_GROUP --
        DELETE
        FROM TRANSACTION_GROUP AT
        WHERE AT.ID    = trx_group_id;
        deletedEntries:=deletedEntries+SQL%ROWCOUNT;
        -- deleted from LARGESTRING_PART --
        DELETE
        FROM LARGESTRING_PART
        WHERE LARGESTRING_ID                IN large_str_id;
        deletedEntries       :=deletedEntries+SQL%ROWCOUNT;
        -- deleted from LARGESTRING --
        DELETE
        FROM LARGESTRING
        WHERE id       = large_str_id;
        deletedEntries:=deletedEntries+SQL%ROWCOUNT;
      END IF;
    END IF;
    countTotalEntries:=countTotalEntries+countLookUp;
    -- deleted from WLM_RULE_ALERT --
    DELETE
    FROM WLM_RULE_ALERT WRA
    WHERE WRA.ALERT_IDENTIFIER IN
      (SELECT WAH.ALERT_IDENTIFIER
      FROM WLM_ALERT_HEADER WAH
      WHERE wah.alert_key= MyAlertKey
      );
    deletedEntries:=deletedEntries+SQL%ROWCOUNT;
    -- deleted from WLM_CUSTOMER_MATCH_DETAILS --
    DELETE
    FROM WLM_CUSTOMER_MATCH_DETAILS
    WHERE ALERT_KEY= MyAlertKey;
    deletedEntries:=deletedEntries+SQL%ROWCOUNT;
    -- deleted from WORKFLOW_ACTION_LOG --
    DELETE
    FROM WORKFLOW_ACTION_LOG
    WHERE WORKFLOW_WORKITEM_ID IN
      (SELECT id
      FROM WORKFLOW_WORKITEM
      WHERE ENTITY_KEY=TO_CHAR(MyAlertKey)
      AND ENTITY_NAME ='WLM Alert'
      );
    deletedEntries:=deletedEntries+SQL%ROWCOUNT;
    -- deleted from WORKFLOW_WORKITEM_LINK --
    DELETE
    FROM WORKFLOW_WORKITEM_LINK
    WHERE ENTITY_KEY=MyAlertKey
    AND ENTITY_NAME ='WLM Alert';
    deletedEntries :=deletedEntries+SQL%ROWCOUNT;
    -- deleted from WORKFLOW_WORKITEM --
    DELETE
    FROM WORKFLOW_WORKITEM
    WHERE ENTITY_KEY= TO_CHAR(MyAlertKey)
    AND entity_name ='WLM Alert';
    deletedEntries :=deletedEntries+SQL%ROWCOUNT;
    -- deleted from WLM_ALERT_HEADER --
    DELETE
    FROM WLM_ALERT_HEADER
    WHERE alert_key = MyAlertKey;
    deletedEntries :=deletedEntries+SQL%ROWCOUNT;
  END DEL_WLM_ALERT;
-- initialization procedure
  PROCEDURE INIT
  AS
    CURSOR alert_id
    IS
    WITH alerts AS
      (SELECT wah.ALERT_KEY                                                     AS alert_key,
        ds.NAME                                                                 AS datasource_name,
        NVL(el.ENTITY_ID,0)                                                     AS alert_key_locked,
        MIN(EVENT_DATE) OVER (PARTITION BY DATASOURCE_NAME order by EVENT_DATE) AS min_date,
        MAX(EVENT_DATE) OVER (PARTITION BY DATASOURCE_NAME order by EVENT_DATE) AS max_date
      FROM WLM_ALERT_HEADER wah
      LEFT JOIN WORKFLOW_WORKITEM ww
      ON wah.ALERT_KEY=ww.ENTITY_KEY
      LEFT JOIN DATA_SOURCE ds
      ON wah.AT_DATASOURCE_ID=ds.ID
      LEFT JOIN CLIENT_DELETION_SETTINGS rds
      ON ds.NAME =rds.DATASOURCE_NAME
      LEFT JOIN ENTITY_LOCKS el
      ON el.ENTITY_ID  =wah.ALERT_KEY
      WHERE EVENT_DATE < (sysdate - rds.PERIOD)
    UNION ALL
    SELECT wah.ALERT_KEY                                                      AS alert_key,
      ds.NAME                                                                 AS datasource_name,
      NVL(el.ENTITY_ID,0)                                                     AS alert_key_locked,
      MIN(EVENT_DATE) OVER (PARTITION BY DATASOURCE_NAME order by EVENT_DATE) AS min_date,
      MAX(EVENT_DATE) OVER (PARTITION BY DATASOURCE_NAME order by EVENT_DATE) AS max_date
    FROM WLM_ALERT_HEADER wah
    LEFT JOIN WORKFLOW_WORKITEM ww
    ON wah.ALERT_KEY=ww.ENTITY_KEY
    LEFT JOIN
      (SELECT ID, NAME
      FROM DATA_SOURCE
      WHERE NAME NOT IN
        (SELECT DATASOURCE_NAME FROM CLIENT_DELETION_SETTINGS
        )
      ) ds
    ON wah.AT_DATASOURCE_ID=ds.ID
    LEFT JOIN CLIENT_DELETION_SETTINGS rds
    ON ds.NAME =rds.DATASOURCE_NAME
    LEFT JOIN ENTITY_LOCKS el
    ON el.ENTITY_ID  = wah.ALERT_KEY
    WHERE EVENT_DATE < (sysdate -
      (SELECT period FROM CLIENT_deletion_settings WHERE datasource_name='DEFAULT'
      ))
      )
    SELECT *
    FROM CLIENT_DELETION_SETTINGS rbs
    LEFT JOIN alerts al
    ON al.datasource_name=rbs.datasource_name;

  BEGIN
    FOR x IN alert_id
    LOOP
      IF (x.alert_key_locked > 0) THEN
        AUDIT_TRAIL('LOCKED', x.datasource_name, x.alert_key);
        COMMIT;
      ELSE
        -- execute deletion for each alert_id
        deletion_results:=DEL_WLM_ALERT(x.alert_key);
        COMMIT;
      END IF;
      AUDIT_TRAIL('DELETED', x.datasource_name, deletedEntries || ' | ' || MIN(min_date) || ' -- ' || MAX(max_date));
    END LOOP;
END INIT;
END CLIENT_DELETE_PACKAGE;
/

Those are errors:

Error(11,13): PLS-00323: subprogram or cursor 'AUDIT_TRAIL' is declared in a package specification and must be defined in the package body
Error(17,12): PLS-00323: subprogram or cursor 'ECHO' is declared in a package specification and must be defined in the package body
Error(234,9): PL/SQL: Statement ignored
Error(234,33): PLS-00302: component 'DATASOURCE_NAME' must be declared
Error(241,7): PL/SQL: Statement ignoredError(241,32): PLS-00302: component 'DATASOURCE_NAME' must be declared
Error(241,32): PLS-00302: component 'DATASOURCE_NAME' must be declared

Answer 1

You have declared the procedure and function in both places, but the arguments are different, so the PL/SQL compiler sees them as different things. You have a procedure and function declared in the spec but never defined; and a procedure and function that are private to the body - they are completely independent.

You need to change the spec to match:

CREATE OR REPLACE PACKAGE CLIENT_DELETE_PACKAGE
IS
  ...
  PROCEDURE AUDIT_TRAIL(
      MyTYPE       IN VARCHAR2,
      -- MyDATA       IN VARCHAR2, -- this isn't in the body version
      MyDatasource IN VARCHAR2,
      MyResultData IN VARCHAR2);
  -- ECHO Check that the package is available.
  FUNCTION ECHO(
      -- MyDATA IN VARCHAR2) -- wrong name
      MyDatasource IN VARCHAR2)
    RETURN VARCHAR2;
  ...

The PLS-00302 is because you have duplicate column names in your cursor query. You can see what with a simplified example. If you run a query like:

column dummy format a6

select *
from dual
cross join dual;

DUMMY  DUMMY 
------ ------
X      X     

the client can display both columns in the result set with the same header. If you tried to create a view based on that query, for instance, you'd get an "ORA-00957: duplicate column name" error as the column names in the view DDL have to be the same.

Something similar is happening here, but the cursor is implicitly being given unique column aliases for both columns. As the join condition ON al.datasource_name=rbs.datasource_name shows, the same column name appears in both the table and CTE you are joining together, so select * would get the same column name twice. As both are implicitly being aliased to something unique, there is no column actually called datasource_name in the cursor record type.

You should change your cursor query to not use a blanket select *; list the columns you actually need from the table and CTE. That's generally advisable anyway.

Answer 2

Your AUDIT_TRAIL spec does not follow its body declaration. Both "headers" must be the same - same arguments. Including arg names.

The same for ECHO.

About DATASOURCE_NAME - is it really a column in the table CLIENT_DELETION_AUDIT? Isn't it missing?