Oracle stored proc referencing package spec variable throwing error during execution from transaction group

I created below procedure in oracle that references a package spec variable to ignore DML completely if it is set.

CREATE OR REPLACE PACKAGE PKG AS
    G_VAR VARCHAR2(1) := 'N';
END PKG;

CREATE OR REPLACE PROCEDURE TEST_LOC_DATA_PRC(IN_KEY_VAL VARCHAR2, IN_VAL NUMBER)
IS
BEGIN
    IF NVL(PKG.G_VAR, 'N') = 'N' THEN
        INSERT INTO TEST_LOC_DATA (KEY_VAL, VAL) VALUES (IN_KEY_VAL, IN_VAL);
    END IF;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        UPDATE TEST_LOC_DATA SET VAL = IN_VAL WHERE KEY_VAL = IN_KEY_VAL;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE || ' - ' || SQLERRM);
END TEST_LOC_DATA_PRC;

It works without any issues, as expected, when invoked in oracle

EXEC TEST_LOC_DATA_PRC('DEF',20);

Now in stored procedure transaction group I am invoking the same and getting “-6508 - ORA-06508: PL/SQL: could not find program unit being called

Could someone explain the reason for this from ignition perspective?

Very likely the user Ignition is connecting to the database as doesn’t have permission to view/use this stored procedure. Oracle’s permissions are…complicated, to put it politely.

Thanks for your prompt response griffith.

I made sure its going to the stored procedure. The error is happening while its referring PKG.G_VAR (first if condition line of the procedure).

If I keep any debug messages before that package variable reference its logging. Also if I remove that condition completey its working, by inserting/updating the table.

I am sure its not able to reference that package variable when invoked from ignition sp transaction group but don’t know the reason why.

Thanks for your help in advance.

Found a better way to handle this by table driven approach suggested by oracle and it works fine :slight_smile:

1 Like