我'm writing some DDL/PLSQL for some dimensions so I can have it handy as a backup and I'使用程序/功能来自动化一切 . 对于我的问题，我专注于日期维度 . 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]已丢弃现有的包状态，未执行，更改或删除存储过程
-
[ORA-04065]未执行，更改或删除存储过程"ADMIN.NEW_DATE"
-
[ORA-06508] PL / SQLL找不到被调用的程序单元："ADMIN.NEW_DATE"
-
[ORA-06512]位于“ADMIN.DATE_DIM_CREATE，第85行
第2行 -
[ORA-06512]
我还应该指出，其中一个存储过程调用了一堆函数来处理将插入到日期维度表中的日期维度值 .
这是第一个存储过程DATE_DIM_CREATE的代码，它调用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;
这是NEW_DATE过程，它将值插入表中并调用所有函数：
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;
我不确定是否需要在 NEW_DATE 存储过程中放置 INSERT INTO ，但这样做对我当前的问题没有任何影响 .
另外，我不是DBA，所以我确信我的代码有一百万个错误 . 但是，如果有一种方法可以解决错误并使代码工作，同时仍然使用底层技术（其中一个存储过程调用另一个调用一堆函数的存储过程），那么这就是我最终要实现的一些功能帮助 .
Update #1 似乎在动态SQL中的 NEW_DATE 过程中封装 INSERT INTO 语句有所帮助 . 但是，它引入了以下新错误：
-
ORA-00984：此处不允许使用列
-
ORA-06512：在"ADMIN.NEW_DATE"，第5行
-
ORA-06512：第6行
这些新错误是否可能是由于我在 NEW_DATE 程序的 VALUES 部分中使用 P_DATE 参数引起的？如果是这样，我将如何更改此选项以允许使用该参数？我尝试了以下，但收到了相同的错误消息（ 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
)';
谢谢
2 回答
ORA-04068消息指示从属程序单元已失效（未编译） . 除其他事项外，这种情况发生在该程序单元的依赖性已经受到DDL的影响时 .
您有一个删除并重新创建表的过程，以及另一个填充该表的过程 . 当您运行第一个过程时，其DDL使第二个过程无效 .
但是您的第一个过程调用第二个过程，并且该依赖项是问题的根源 .
虽然可以说问题的真正根源是有一个程序可以删除并重新创建一个表 . 很少有真正需要这种情况的真实案例 . 大多数情况下，这种方法只是想象力的失败 .
但是，如果您真的坚持这个想法，那么您还需要在动态SQL中包装NEW_DATE插入语句 . 这将破坏依赖关系树并防止表被删除时失效 .
是 . NEW_DATE（）在DATE_DIM上具有编译时依赖性，因为INSERT是静态SQL . 删除表时，该过程无效 . 如果将INSERT语句设置为动态SQL调用，则依赖项将进入运行时 . 这意味着NEW_DATE（）赢了't be invalidated when you drop the table. In fact you will still be able to execute if the table doesn' t;它只会向ORA-00904或类似错误投掷 .
那么为什么我们不让所有的程序都使用动态SQL，从而消除了ORA-04068消息的诅咒？因为动态SQL是一个痛苦的问题，尤其是调试 . 此外，我们从依赖项中丢失了一些 Value 信息，这使得对数据库更改进行影响分析变得更加困难 . 此外，做你正在做的事情，并将DDL置于可重复的程序化过程中通常是不必要的 .
绝对 . 这是一个字符串文字，EXECUTE IMMEDIATE在SQL引擎中按字面运行 .
P_DATE是PL / SQL参数，超出SQL范围 .如果要传递参数，则需要使用正确的语法：
请注意，占位符就是这样 . 如果你想在27个位置使用相同的值，你需要在USING子句中有27个占位符和27个匹配的匹配项 .
我想如果你对动态SQL有任何疑问，你的第一个调用端口应该是文档 . Find it here . 如果这对您没有帮助，请开始新的thead .
我目前没有plsql来检查它，但你的问题是你没有以正确的方式调用程序 . 方式是新的DE_DATe \ e . 我应该提一下，我已经在create table中更正了一些内容，你可以在create table中包含注释