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

Ignition
1

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?

2

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.

3

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.

4

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

1 Like