create or replace procedure target_1
is
cursor cursor_1 is select intervention_fact_ky from test_source.INTERVENTION_F order by intervention_fact_ky;
v_ky test_source.intervention_f.intervention_fact_ky%type;
begin
open cursor_1;
loop
fetch cursor_1 into v_ky;
exit when cursor_1%notfound;
insert into test_target_a.BILLED_PL_CODE_R(INTERVENTION_KY,SRVC_DELIVERY_DIM_KY,PRD_ID,PRD_HIER_ITEM_DN,PRD_LINE_CD,PRD_LINE_NM,PL_ACTUAL,PL_USED)
select
intervention_fact_ky,substr(SRVC_DELIVERY_METHOD_DIM_KY,-3),PRODUCT_ID,PRODUCT_HIER_ITEM_DN,PRODUCT_LINE_CD,PRODUCT_LINE_NM,nvl(PRODUCT_LINE_CD,'OT'),nvl(PRODUCT_LINE_CD,'UNKNOWN')
from test_source.intervention_f,test_source.esg_product_line,test_source.product_hier_d
where
test_source.INTERVENTION_F.REPORTING_PRODUCT_NUMBER_ID = test_source.PRODUCT_HIER_D.PRODUCT_ID and
test_source.ESG_PRODUCT_LINE.RAS_PRODUCT_LINE_CD = test_source.PRODUCT_HIER_D.PRODUCT_HIER_LEVEL_02_CD
and
test_source.INTERVENTION_F.LOGICAL_DELETE_FG ='N';
end loop;
close cursor_1;
end;
报错信息是:
Line # = 3 Column # = 65 Error Text = PL/SQL: ORA-00942: 表或视图不存在
Line # = 3 Column # = 20 Error Text = PL/SQL: SQL Statement ignored
Line # = 4 Column # = 6 Error Text = PLS-00201: 必须声明标识符 'TEST_SOURCE.INTERVENTION_F'
Line # = 4 Column # = 6 Error Text = PL/SQL: Item ignored
Line # = 8 Column # = 21 Error Text = PLS-00320: 此表达式的类型声明不完整或格式不正确
Line # = 8 Column # = 1 Error Text = PL/SQL: SQL Statement ignored
Line # = 10 Column # = 1 Error Text = PL/SQL: SQL Statement ignored
Line # = 13 Column # = 74 Error Text = PL/SQL: ORA-00942: 表或视图不存在
中间的这些select语句都是正确的,表也都是存在的,为什么放在过程里会有这样的编译错误?这些select语句都可以单独执行的!
SQL Statement ignored是为什么啊?谢谢各位大侠了,小弟初学~
is
cursor cursor_1 is select intervention_fact_ky from test_source.INTERVENTION_F order by intervention_fact_ky;
v_ky test_source.intervention_f.intervention_fact_ky%type;
begin
open cursor_1;
loop
fetch cursor_1 into v_ky;
exit when cursor_1%notfound;
insert into test_target_a.BILLED_PL_CODE_R(INTERVENTION_KY,SRVC_DELIVERY_DIM_KY,PRD_ID,PRD_HIER_ITEM_DN,PRD_LINE_CD,PRD_LINE_NM,PL_ACTUAL,PL_USED)
select
intervention_fact_ky,substr(SRVC_DELIVERY_METHOD_DIM_KY,-3),PRODUCT_ID,PRODUCT_HIER_ITEM_DN,PRODUCT_LINE_CD,PRODUCT_LINE_NM,nvl(PRODUCT_LINE_CD,'OT'),nvl(PRODUCT_LINE_CD,'UNKNOWN')
from test_source.intervention_f,test_source.esg_product_line,test_source.product_hier_d
where
test_source.INTERVENTION_F.REPORTING_PRODUCT_NUMBER_ID = test_source.PRODUCT_HIER_D.PRODUCT_ID and
test_source.ESG_PRODUCT_LINE.RAS_PRODUCT_LINE_CD = test_source.PRODUCT_HIER_D.PRODUCT_HIER_LEVEL_02_CD
and
test_source.INTERVENTION_F.LOGICAL_DELETE_FG ='N';
end loop;
close cursor_1;
end;
报错信息是:
Line # = 3 Column # = 65 Error Text = PL/SQL: ORA-00942: 表或视图不存在
Line # = 3 Column # = 20 Error Text = PL/SQL: SQL Statement ignored
Line # = 4 Column # = 6 Error Text = PLS-00201: 必须声明标识符 'TEST_SOURCE.INTERVENTION_F'
Line # = 4 Column # = 6 Error Text = PL/SQL: Item ignored
Line # = 8 Column # = 21 Error Text = PLS-00320: 此表达式的类型声明不完整或格式不正确
Line # = 8 Column # = 1 Error Text = PL/SQL: SQL Statement ignored
Line # = 10 Column # = 1 Error Text = PL/SQL: SQL Statement ignored
Line # = 13 Column # = 74 Error Text = PL/SQL: ORA-00942: 表或视图不存在
中间的这些select语句都是正确的,表也都是存在的,为什么放在过程里会有这样的编译错误?这些select语句都可以单独执行的!
SQL Statement ignored是为什么啊?谢谢各位大侠了,小弟初学~
test_source.product_hier_d
这两个表不存在。
注意它们在test_source用户下。LZ所谓表都存在,是不是不在这个用户下。