Stored procedure not being recognized from another stored procedure

Question

I'm writing some DDL/PLSQL for some dimensions so I can have it handy as a backup and I'm using procedures/functions to automate everything. For my question, I'm focusing on a date dimension. The problem I'm running into is that when I try to run one stored procedure from another stored procedure, I get a collection of error messages that say,

[ORA-04068] the existing state of packages has been discarded not executed, altered or dropped stored procedure
[ORA-04065] not executed, altered or dropped stored procedure "ADMIN.NEW_DATE"
[ORA-06508] PL/SQLL could not find program unit being called: "ADMIN.NEW_DATE"
[ORA-06512] at "ADMIN.DATE_DIM_CREATE, line 85
[ORA-06512] at line 2

I should also point out that one of the stored procedures calls a bunch of functions that handle the date dimension values that will be inserted into the date dimension table.

Here's the code for the first stored procedure DATE_DIM_CREATE, which calls NEW_DATE:

create or replace 
PROCEDURE DATE_DIM_CREATE
    AUTHID CURRENT_USER
IS
    V_START_DATE DATE := TO_DATE('01/01/1900','MM/DD/YYYY');
    V_CURRENT_DATE  DATE := V_START_DATE;
    V_END_DATE DATE := TO_DATE('12/31/2099','MM/DD/YYYY');
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE "DATE_DIM" PURGE';

    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''AMERICAN'' ';
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA'' ';
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_CALENDAR = ''GREGORIAN'' ';
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''MM/DD/YYYY'' ';

    EXECUTE IMMEDIATE
    'CREATE TABLE DATE_DIM
    (
        DATE_KEY NUMBER(8, 0) NOT NULL,  
        DATE_TYPE VARCHAR2(14),
        FULL_DATE DATE,
        FULL_DATE_REVERSE VARCHAR2(10),
        FULL_DATE_DESCRIPTION VARCHAR2(31),
        DAY_NUMBER NUMBER(2, 0),
        DAY_NAME VARCHAR2(9),
        DAY_SHORT VARCHAR2(3),
        FIRST_DAY_IN_MONTH DATE,
        LAST_DAY_IN_MONTH DATE,
        FIRST_DAY_IN_PREVIOUS_MONTH DATE,
        LAST_DAY_IN_PREVIOUS_MONTH DATE,
        FIRST_DAY_IN_NEXT_MONTH DATE,
        LAST_DAY_IN_NEXT_MONTH DATE,
        FIRST_DAY_IN_CALENDAR_QTR DATE,
        LAST_DAY_IN_CALENDAR_QTR DATE,
        FIRST_DAY_IN_PREV_CALENDAR_QTR DATE,
        LAST_DAY_IN_PREV_CALENDAR_QTR DATE,
        FIRST_DAY_IN_NEXT_CALENDAR_QTR DATE,
        LAST_DAY_IN_NEXT_CALENDAR_QTR DATE,
        FIRST_DAY_IN_CALENDAR_YEAR DATE,
        LAST_DAY_IN_CALENDAR_YEAR DATE,
        DAY_NUMBER_IN_CALENDAR_YEAR NUMBER(3, 0),
        FIRST_DAY_IN_FISCAL_QTR DATE,
        LAST_DAY_IN_FISCAL_QTR DATE,
        FIRST_DAY_IN_PREV_FISCAL_QTR DATE,
        LAST_DAY_IN_PREV_FISCAL_QTR DATE,
        FIRST_DAY_IN_NEXT_FISCAL_QTR DATE,
        LAST_DAY_IN_NEXT_FISCAL_QTR DATE,
        FIRST_DAY_IN_FISCAL_YEAR DATE,  
        LAST_DAY_IN_FISCAL_YEAR DATE,
        DAY_NUMBER_IN_FISCAL_YEAR NUMBER(3, 0),
        IS_WEEKDAY VARCHAR2(1),
        IS_WEEKEND VARCHAR2(1), 
        WEEK_IN_MONTH NUMBER(1, 0),
        WEEK_IN_CALENDAR_YEAR_ISO NUMBER(2, 0),
        WEEK_IN_FISCAL_YEAR_ISO NUMBER(2, 0),
        BEGIN_FULL_WEEK DATE,
        END_FULL_WEEK DATE,
        BEGIN_WORK_WEEK DATE,
        END_WORK_WEEK DATE,
        MONTH_NUMBER NUMBER(2, 0),
        MONTH_NAME VARCHAR(9),
        MONTH_SHORT VARCHAR(3),
        CALENDAR_QUARTER NUMBER(1, 0),
        CALENDAR_QUARTER_NAME VARCHAR2(11),
        CALENDAR_QUARTER_YEAR VARCHAR2(9),
        FISCAL_QUARTER NUMBER(1, 0),
        FISCAL_QUARTER_NAME VARCHAR2(11),
        FISCAL_QUARTER_YEAR VARCHAR2(9),
        CALENDAR_YEAR_NUMBER NUMBER(4, 0),
        CALENDAR_YEAR VARCHAR2(6),
        FISCAL_YEAR_NUMBER NUMBER(4, 0),
        FISCAL_YEAR VARCHAR2(6),
        IS_HOLIDAY VARCHAR2(1),
        IS_BUSINESS_DAY VARCHAR2(1),
        CONSTRAINT DATE_KEY_PK PRIMARY KEY (DATE_KEY)
    )';

EXECUTE IMMEDIATE
    'COMMENT ON TABLE DATE_DIM
    IS ''Date dimension table used for storing date attributes.'' ';
EXECUTE IMMEDIATE
    'COMMENT ON COLUMN DATE_DIM.DATE_KEY
    IS ''Date key is the primary/surrogate key for the date dimension table.'' ';

WHILE V_CURRENT_DATE <= V_END_DATE LOOP
    NEW_DATE(V_CURRENT_DATE);
    V_CURRENT_DATE := V_CURRENT_DATE + INTERVAL '1' DAY;
END LOOP;

NEW_DATE(TO_DATE('12/29/9999', 'MM/DD/YYYY'));
NEW_DATE(TO_DATE('12/30/9999', 'MM/DD/YYYY'));
NEW_DATE(TO_DATE('12/31/9999', 'MM/DD/YYYY'));

END DATE_DIM_CREATE;

And here's the NEW_DATE procedure, which inserts the values into the table and calls all the functions:

create or replace 
PROCEDURE NEW_DATE(P_DATE IN DATE)
    AUTHID CURRENT_USER
IS
BEGIN
    INSERT INTO DATE_DIM 
    (
        DATE_KEY,
        DATE_TYPE,
        FULL_DATE,
        FULL_DATE_REVERSE,
        FULL_DATE_DESCRIPTION,
        DAY_NUMBER,
        DAY_NAME,
        DAY_SHORT,
        FIRST_DAY_IN_MONTH,
        LAST_DAY_IN_MONTH,
        FIRST_DAY_IN_PREVIOUS_MONTH,
        LAST_DAY_IN_PREVIOUS_MONTH,
        FIRST_DAY_IN_NEXT_MONTH,
        LAST_DAY_IN_NEXT_MONTH,
        FIRST_DAY_IN_CALENDAR_QTR,
        LAST_DAY_IN_CALENDAR_QTR,
        FIRST_DAY_IN_PREV_CALENDAR_QTR,
        LAST_DAY_IN_PREV_CALENDAR_QTR,
        FIRST_DAY_IN_NEXT_CALENDAR_QTR,
        LAST_DAY_IN_NEXT_CALENDAR_QTR,
        FIRST_DAY_IN_CALENDAR_YEAR,
        LAST_DAY_IN_CALENDAR_YEAR,
        DAY_NUMBER_IN_CALENDAR_YEAR,
        FIRST_DAY_IN_FISCAL_QTR,
        LAST_DAY_IN_FISCAL_QTR,
        FIRST_DAY_IN_PREV_FISCAL_QTR,
        LAST_DAY_IN_PREV_FISCAL_QTR,
        FIRST_DAY_IN_NEXT_FISCAL_QTR,
        LAST_DAY_IN_NEXT_FISCAL_QTR,
        FIRST_DAY_IN_FISCAL_YEAR,
        LAST_DAY_IN_FISCAL_YEAR,
        DAY_NUMBER_IN_FISCAL_YEAR,
        IS_WEEKDAY,
        IS_WEEKEND,
        WEEK_IN_MONTH,
        WEEK_IN_CALENDAR_YEAR_ISO,
        WEEK_IN_FISCAL_YEAR_ISO,
        BEGIN_FULL_WEEK,
        END_FULL_WEEK,
        BEGIN_WORK_WEEK,
        END_WORK_WEEK,
        MONTH_NUMBER,
        MONTH_NAME,
        MONTH_SHORT,
        CALENDAR_QUARTER,
        CALENDAR_QUARTER_NAME,
        CALENDAR_QUARTER_YEAR,
        FISCAL_QUARTER,
        FISCAL_QUARTER_NAME,
        FISCAL_QUARTER_YEAR,
        CALENDAR_YEAR_NUMBER,
        CALENDAR_YEAR,
        FISCAL_YEAR_NUMBER,
        FISCAL_YEAR,
        IS_HOLIDAY,
        IS_BUSINESS_DAY
    ) 
    VALUES 
    (
        DATE_KEY_FX(P_DATE),                  --DATE_KEY
        DATE_TYPE_FX(P_DATE),                 --DATE_TYPE
        P_DATE,                               --FULL_DATE
        FULL_DATE_REVERSE_FX(P_DATE),         --FULL_DATE_REVERSE
        FULL_DATE_DESCRIPTION_FX(P_DATE),     --FULL_DATE_DESCRIPTION
        DAY_NUMBER_FX(P_DATE),                --DAY_NUMBER
        DAY_NAME_FX(P_DATE),                  --DAY_NAME
        DAY_SHORT_FX(P_DATE),                 --DAY_SHORT
        DAY_IN_MONTH_FX(P_DATE, 0, 0),        --FIRST_DAY_IN_MONTH
        DAY_IN_MONTH_FX(P_DATE, 1, 0),        --LAST_DAY_IN_MONTH
        DAY_IN_MONTH_FX(P_DATE, 0, -1),       --FIRST_DAY_IN_PREVIOUS_MONTH
        DAY_IN_MONTH_FX(P_DATE, 1, -1),       --LAST_DAY_IN_PREVIOUS_MONTH
        DAY_IN_MONTH_FX(P_DATE, 0, 1),        --FIRST_DAY_IN_NEXT_MONTH
        DAY_IN_MONTH_FX(P_DATE, 1, 1),        --LAST_DAY_IN_NEXT_MONTH
        DAY_IN_QTR_FX(P_DATE, 0, 0, 0),       --FIRST_DAY_IN_CALENDAR_QTR
        DAY_IN_QTR_FX(P_DATE, 1, 0, 0),       --LAST_DAY_IN_CALENDAR_QTR
        DAY_IN_QTR_FX(P_DATE, 0, -1, 0),      --FIRST_DAY_IN_PREV_CALENDAR_QTR
        DAY_IN_QTR_FX(P_DATE, 1, -1, 0),      --LAST_DAY_IN_PREV_CALENDAR_QTR
        DAY_IN_QTR_FX(P_DATE, 0, 1, 0),       --FIRST_DAY_IN_NEXT_CALENDAR_QTR
        DAY_IN_QTR_FX(P_DATE, 1, 1, 0),       --LAST_DAY_IN_NEXT_CALENDAR_QTR
        DAY_IN_YEAR_FX(P_DATE, 0, 0),         --FIRST_DAY_IN_CALENDAR_YEAR
        DAY_IN_YEAR_FX(P_DATE, 1, 0),         --LAST_DAY_IN_CALENDAR_YEAR
        DAY_NUMBER_IN_YEAR_FX(P_DATE, 0),     --DAY_NUMBER_IN_CALENDAR_YEAR
        DAY_IN_QTR_FX(P_DATE, 0, 0, 1),       --FIRST_DAY_IN_FISCAL_QTR
        DAY_IN_QTR_FX(P_DATE, 1, 0, 1),       --LAST_DAY_IN_FISCAL_QTR
        DAY_IN_QTR_FX(P_DATE, 0, -1, 1),      --FIRST_DAY_IN_PREV_FISCAL_QTR
        DAY_IN_QTR_FX(P_DATE, 1, -1, 1),      --LAST_DAY_IN_PREV_FISCAL_QTR
        DAY_IN_QTR_FX(P_DATE, 0, 1, 1),       --FIRST_DAY_IN_NEXT_FISCAL_QTR
        DAY_IN_QTR_FX(P_DATE, 1, 1, 1),       --LAST_DAY_IN_NEXT_FISCAL_QTR
        DAY_IN_YEAR_FX(P_DATE, 0, 1),         --FIRST_DAY_IN_FISCAL_YEAR
        DAY_IN_YEAR_FX(P_DATE, 1, 1),         --LAST_DAY_IN_FISCAL_YEAR
        DAY_NUMBER_IN_YEAR_FX(P_DATE, 1),     --DAY_NUMBER_IN_FISCAL_YEAR
        IS_WEEKDAY_FX(P_DATE),                --IS_WEEKDAY
        IS_WEEKEND_FX(P_DATE),                --IS_WEEKEND
        WEEK_IN_MONTH_FX(P_DATE),             --WEEK_IN_MONTH
        WEEK_IN_YEAR_ISO_FX(P_DATE, 0),       --WEEK_IN_CALENDAR_YEAR_ISO
        WEEK_IN_YEAR_ISO_FX(P_DATE, 1),       --WEEK_IN_FISCAL_YEAR_ISO
        WEEK_POINT_FX(P_DATE, 0, 0),          --BEGIN_FULL_WEEK
        WEEK_POINT_FX(P_DATE, 1, 0),          --END_FULL_WEEK
        WEEK_POINT_FX(P_DATE, 0, 1),          --BEGIN_WORK_WEEK
        WEEK_POINT_FX(P_DATE, 1, 1),          --END_WORK_WEEK
        MONTH_NUMBER_FX(P_DATE),              --MONTH_NUMBER
        MONTH_NAME_FX(P_DATE),                --MONTH_NAME
        MONTH_SHORT_FX(P_DATE),               --MONTH_SHORT
        QUARTER_FX(P_DATE, 0),                --CALENDAR_QUARTER
        QUARTER_NAME_FX(P_DATE, 0),           --CALENDAR_QUARTER_NAME
        QUARTER_YEAR_FX(P_DATE, 0),           --CALENDAR_QUARTER_YEAR
        QUARTER_FX(P_DATE, 1),                --FISCAL_QUARTER
        QUARTER_NAME_FX(P_DATE, 1),           --FISCAL_QUARTER_NAME
        QUARTER_YEAR_FX(P_DATE, 1),           --FISCAL_QUARTER_YEAR
        YEAR_NUMBER_FX(P_DATE, 0),            --CALENDAR_YEAR_NUMBER
        YEAR_FORMAT_FX(P_DATE, 0),            --CALENDAR_YEAR
        YEAR_NUMBER_FX(P_DATE, 1),            --FISCAL_YEAR_NUMBER
        YEAR_FORMAT_FX(P_DATE, 1),            --FISCAL_YEAR
        IS_HOLIDAY_FX(P_DATE),                --IS_HOLIDAY
        IS_BUSINESS_DAY_FX(P_DATE)            --IS_BUSINESSDAY
    );

END NEW_DATE;

I'm not sure if I need to put EXECUTE IMMEDIATE around the INSERT INTO in the NEW_DATE stored procedure, but doing so didn't have any effect on my current problem.

Also, I'm no DBA, so I'm sure there are a million things wrong with my code. However, if there's a way to resolve the error and get the code to work while still employing the underlying technique (where one stored procedure calls another stored procedure that calls a bunch of functions), then that's what I'd to ultimately achieve with some assistance.

Update #1 It seems like encapsulating the INSERT INTO statement in the NEW_DATE procedure in dynamic SQL helped somewhat. However, it introduced the following new errors:

ORA-00984: column not allowed here
ORA-06512: at "ADMIN.NEW_DATE", line 5
ORA-06512: at line 6

Is it possible that these new errors are caused by my use of the P_DATE parameter in the VALUES section of the NEW_DATE procedure? If so, how would I change this to allow the parameter to be used? I tried the following, but received the same error messages (focus on the FULL_DATE column):

EXECUTE IMMEDIATE
    'INSERT INTO
     (
         DATE_TYPE,
         FULL_DATE,
         FULL_DATE_REVERSE
     )
     VALUES
     (
         DATE_TYPE_FX(P_DATE),               --DATE_TYPE
         '|| P_DATE ||',                     --FULL_DATE
         FULL_DATE_REVERSE_FX(P_DATE)        --FULL_DATE_REVERSE
     )';

Thanks

APC · Accepted Answer

The ORA-04068 message indicates that a dependent program unit has been invalidated (uncompiled). This happens, amongst other things, when a dependency of that program unit has been subjected to DDL.

You have one procedure which drops and re-creates a table, and another procedure which populates that table. When you run the first procedure its DDL invalidates the second procedure.

But your first procedure calls the second procedure, and that dependency is the source of the problem.

Although arguably the real source of the problem is having a procedure which drops and re-creates a table. There are very few genuine cases where this is actually necessary. Mostly this approach is just a failure of the imagination.

However, if you are genuine wedded to this idea, you need to wrap the NEW_DATE insert statement in dynamic SQL too. That will break the dependency tree and prevent the invalidation when the table gets dropped.

"So I need to wrap the INSERT INTO statement, inside the NEW_DATE procedure, in dynamic SQL"

Yes. NEW_DATE() has a compile time dependency on DATE_DIM, because the INSERT is static SQL. When you drop the table the procedure is invalidated. If you make the INSERT statement a dynamic SQL call the dependency moves into run time. This means NEW_DATE() won't be invalidated when you drop the table. In fact you will still be able to execute if the table doesn't exist; it will just hurl at ORA-00904 or similar error.

So why don't we make all our procedures use dynamic SQL and so banish the curse of the ORA-04068 message? Because dynamic SQL is a pain in the neck to write and especially to debug. Also we lose some value information from the dependencies, which make it harder to undertake impact analyses of database changes. Besides, doing what you're doing, and putting DDL into a repeatable programmatic process is usually unnecessary.

"Do you think the parameter is the cause of column not allowed here error I'm receiving?"

Absolutely. That's a string literal and EXECUTE IMMEDIATE runs it literally, in the SQL engine . P_DATE is a PL/SQL parameter and is out of scope in SQL.

If you want to pass arguments you need to use the right syntax:

EXECUTE IMMEDIATE
    'INSERT INTO
     (
         DATE_TYPE,
         FULL_DATE,
         FULL_DATE_REVERSE
     )
     VALUES
     (
         DATE_TYPE_FX(:1),               --DATE_TYPE
         :2,                     --FULL_DATE
         FULL_DATE_REVERSE_FX(:3)        --FULL_DATE_REVERSE
     )' using p_date, p_date, p_date;

Note that the place-holders are just that. If you want to use the same value in 27 palces you need 27 place-holders and 27 matching occurrences in the USING clause.

I think if you have any more questions with dynamic SQL your first port of call should be the documentation. Find it here. If that doesn't help you, please start a new thead.

Stored procedure not being recognized from another stored procedure

Answers (2)

Related Questions