错误：PL SQL包中不存在表或视图

我在模式'A'中有一个名为'S_PKG'的软件包。我想将其迁移到架构B。我所做的是复制代码并在B架构中执行。然后，我想执行以下pl sql脚本，其中在模式B中使用“ S_PKG”。

select to_char(sysdate,'YYYYMMDD - HH:MI:SS AM') date_time
from dual
/
insert into zzz_ccc
select zzz_ccc_seq.nextval,to_char(sysdate,'YYYYMMDD - HH:MI:SS AM'),'Start S Load tab',8,user,(select * from global_name)
from dual
/
commit
/
insert into zzz
select zzz_seq.nextval,(select trim(ruser)||' '||trim(rdbase)
from zzz_ccc where ss = 8
and rr = (select max(rr) from zzz_ccc where ss = 8)),null,(select max(rr) from zzz_ccc where ss = 8)
from dual 
/
commit
/
DeclARE
  PV_PRD_ID VARCHAR2(5);
BEGIN
  PV_PRD_ID := '3';
  B.S_PKG.PR_MAIN (PV_PRD_ID);
  COMMIT; 
END; 
/
commit
/
insert into tab_counts(created_dtm,ora_database,ora_user,ora_table,cc,flg,subj)
select sysdate,'ccenter','aim10_ccenter','s_package_run - rows',no_of_rows,1,'S Load Tab Run'
from s_package_run
where to_char(st_date,'YYYYMM-DD') = to_char(sysdate,'YYYYMM-DD')
and package_name = 'LOAD_TAB'
/
commit
/
insert into tab_counts(created_dtm,'s_package_run - dur_min',dur_min,2,'YYYYMM-DD')
and package_name = 'LOAD_TAB'
/
insert into hrc_chk_mar14_24_1
select sysdate,'B','COUNT 6   ','s_load_tab',null
from dual t1
/
insert into zzz
select zzz_seq.nextval,'End S Load tab',(select max(rr) from zzz_ccc where ss = 8)
from dual 
/
commit
/

然后我遇到以下错误。

ORA-00942：表或视图不存在 ORA-06512：位于“ B.S_PKG”的第109行 ORA-06512：位于“ B.S_PKG”的第35行 ORA-06512：在第5行

然后我搜索了包裹。但是我找不到给出错误的确切表。这是我的包裹。

CREATE OR REPLACE PACKAGE BODY B.S_PKG is


PROCEDURE PR_Main(kseq number) IS
sql_stmnt varchar2(5000);
begin

select s_pkg_seq.nextval into dd from dual;

pkg_st_date := sysdate;

sql_stmnt:='truncate table s_msgs';

execute immediate sql_stmnt;


begin
for c_rec in (select * from s_test_1 where pkg_seq = kseq and
flg = 'as' order by pkg_ord)
loop
   if substr(c_rec.run_sql,1) != '-' then
   pkg_name :=  c_rec.run_sql;
   PR_Msgs('*** '||c_rec.run_sql||' Started...',pkg_name||'.'||'pr_main');
   else
   PR_Msgs(c_rec.run_sql,pkg_name||'.'||'pr_main');
   end if;
   insert into s_test_3
   values(sysdate,c_rec.run_sql,c_rec.pkg_seq,c_rec.pkg_ord,c_rec.prc_seq);
end loop;
end;


PR_LOAD_PRODUCT3(1,kseq);

PR_LOAD_PRODUCT3(2,kseq);

PR_LOAD_PRODUCT3(3,kseq);

PR_LOAD_PRODUCT3(4,kseq);

PR_LOAD_PRODUCT3(5,kseq);

PR_LOAD_PRODUCT3(6,kseq);

PR_LOAD_PRODUCT3(7,kseq);

PR_LOAD_PRODUCT3(8,kseq);

PR_LOAD_PRODUCT3(9,kseq);

PR_LOAD_PRODUCT3(10,kseq);

PR_LOAD_PRODUCT3(11,kseq);


begin
for c_rec in (select * from s_test_1 where pkg_seq = kseq and
flg = 'ae' order by pkg_ord)
loop
   if substr(c_rec.run_sql,1) != '-' then
   PR_Msgs('*** '||c_rec.run_sql||' End OK...',c_rec.prc_seq);
end loop;
end;

sql_stmnt:='insert into s_msgs_his select * from s_msgs where msg_ord > 0';

execute immediate sql_stmnt;

pkg_en_date := sysdate;

insert into s_package_run values (pkg_st_date,pkg_en_date,floor((pkg_en_date-pkg_st_date)*24*60),(pkg_en_date-pkg_st_date)*24*60*60 - floor((pkg_en_date-pkg_st_date)*24*60)*60,gv_count,pkg_name,dd);


end;

-- ==============================================================================================
-- ==============================================================================================

PROCEDURE PR_LOAD_PRODUCT3(pseq number,tseq number) is
sql_stmnt VARCHAR2(5000);
begin

prc_st_date := sysdate;

begin
for c_rec in (select * from s_test_1 where pkg_seq = tseq and
prc_seq = pseq and flg = 'bs' order by pkg_ord)
loop
   prc_name :=  c_rec.run_sql;
   PR_Msgs(c_rec.run_sql||' Started...',pkg_name||'.'||'pr_main');
   insert into s_test_3
   values(sysdate,c_rec.prc_seq);
end loop;
end;

begin
for c_rec in (select * from s_test_1 where pkg_seq = tseq and
prc_seq = pseq and flg = 'aa' order by pkg_ord)

loop
     sql_stmnt := c_rec.run_sql;
     execute immediate sql_stmnt;
     insert into s_test_3
     values(sysdate,c_rec.prc_seq);
end loop;
end;

begin
for c_rec in (select * from s_test_1 where pkg_seq = tseq and
prc_seq = pseq and flg = 'be' order by pkg_ord)
loop
   if substr(c_rec.run_sql,1) != '-' then
   PR_Msgs(c_rec.run_sql||' End OK...',c_rec.prc_seq);
end loop;
end;


select cc into gv_count
from cr2_test_2;

prc_en_date := sysdate;

insert into s_procedure_run values (prc_st_date,prc_en_date,floor((prc_en_date-prc_st_date)*24*60),(prc_en_date-prc_st_date)*24*60*60 - floor((prc_en_date-prc_st_date)*24*60)*60,prc_name,dd);

end;

-- ==============================================================================================
-- ==============================================================================================

PROCEDURE PR_Msgs(pv_msg varchar2,pv_point varchar2) as
cc number(5);
Begin


select s_pkg2_seq.nextval into cc from dual;

insert into s_msgs values (sysdate,pv_msg,pv_point,'',cc);
commit;

End;

-- ==============================================================================================
-- ==============================================================================================


END S_PKG;
/

当我不熟悉PL SQL软件包时，有人可以帮助我找到错误吗？

据我所知，如果您以用户B的身份连接时编译了程序包并且成功，那么当时唯一的“未知”信息是：

sql_stmnt:='truncate table s_msgs';

即使用动态SQL截断的表。 sql_stmnt的值是一个字符串，编译器不会检查其中的内容。但是，当您实际上执行时，它会发现用户B的架构中没有表s_msgs。

同样的道理

sql_stmnt:='insert into s_msgs_his select * from s_msgs where msg_ord > 0';

和表s_msgs_his。

当您将所有内容从A移到B时，我想您应该对这些表执行相同的操作。这样做，然后尝试再次运行您的代码。

[编辑]

这是第109行：

for c_rec in (select * from s_test_1 where pkg_seq = tseq and
prc_seq = pseq and flg = 'aa' order by pkg_ord)

loop
     sql_stmnt := c_rec.run_sql;
     execute immediate sql_stmnt;         --> line 109

检查S_TEST_1中写的内容。看起来好像应该动态执行的一条语句，但是-模式B中不存在其中的表（或者该用户没有使用它的特权）。

错误：PL SQL包中不存在表或视图

layjung0 回答：错误：PL SQL包中不存在表或视图

大家都在问