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

**URL:** <https://forum.inductiveautomation.com/t/oracle-stored-proc-referencing-package-spec-variable-throwing-error-during-execution-from-transaction-group/35401>\
**Category:** Ignition\
**Created:** [June 8, 2020, 12:37pm UTC](https://forum.inductiveautomation.com/t/oracle-stored-proc-referencing-package-spec-variable-throwing-error-during-execution-from-transaction-group/35401 "2020-06-08T12:37:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jdsince1986](https://avatars.discourse-cdn.com/v4/letter/j/e19adc/32.png) [@jdsince1986](https://forum.inductiveautomation.com/u/jdsince1986)\
**Post date:** [June 8, 2020, 12:38pm UTC](https://forum.inductiveautomation.com/t/oracle-stored-proc-referencing-package-spec-variable-throwing-error-during-execution-from-transaction-group/35401/1 "2020-06-08T12:38:00Z")

</div>

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

```auto
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

```auto
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?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 8, 2020, 3:54pm UTC](https://forum.inductiveautomation.com/t/oracle-stored-proc-referencing-package-spec-variable-throwing-error-during-execution-from-transaction-group/35401/2 "2020-06-08T15:54:09Z")

</div>

> [@jdsince1986](#):
>
> **could not find program unit being called** ”

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.

---

<div class="post-metadata">

**Author:** ![jdsince1986](https://avatars.discourse-cdn.com/v4/letter/j/e19adc/32.png) [@jdsince1986](https://forum.inductiveautomation.com/u/jdsince1986)\
**Post date:** [June 8, 2020, 4:16pm UTC](https://forum.inductiveautomation.com/t/oracle-stored-proc-referencing-package-spec-variable-throwing-error-during-execution-from-transaction-group/35401/3 "2020-06-08T16:16:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jdsince1986](https://avatars.discourse-cdn.com/v4/letter/j/e19adc/32.png) [@jdsince1986](https://forum.inductiveautomation.com/u/jdsince1986)\
**Post date:** [June 11, 2020, 1:44pm UTC](https://forum.inductiveautomation.com/t/oracle-stored-proc-referencing-package-spec-variable-throwing-error-during-execution-from-transaction-group/35401/4 "2020-06-11T13:44:47Z")

</div>

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