create or replace procedure J_RPT_YRB
(
jlist out sys_refcursor,
CompanyID in varchar2,
StorageID in varchar2,
FPeriodYear int,
FPeriodmonth int,
Warehouse in varchar2,
materialnumber in varchar2,
storeType in varchar2
) is
p_sqlstr varchar2(4000);
p_insertSql varchar2(4000);
p_UpdateSql varchar2(4000);
begin
p_sqlstr:='create global temporary table tJ_RPT_YRB(
FEntryDC int,
ZCOMPANYNAME VARCHAR2(50),
ZSTORAGE VARCHAR2(50),
ZWAREHOUSE VARCHAR2(50),
ZMATERAL VARCHAR2(50),
ZMATERALNAME VARCHAR2(100),
ZBRANDNAME VARCHAR2(20),
ZYEAR NUMBER(12,2),
ZMONTH NUMBER(12,2),
ZQTY INT,
ZACTUALCOST NUMBER(12,2)) ON COMMIT preserve ROWS';
execute immediate p_sqlstr;
--P_RTRP_DROPTEMPIFEXISTS('tJ_RPT_YRB',p_sqlstr);
p_insertSql:='insert into tJ_RPT_YRB select 1,a.FCompanyOrgUnitID,a.FStorageOrgUnitID,a.FWarehouseID,
a.FMaterialID,0,0,a.FYear,a.FPeriod,a.FPeriodEndQty,a.FPeriodEndActualCost from T_IM_InventoryBalance a
left join t_org_company b on a.FCompanyOrgUnitID=b.fid where b.fnumber='''||CompanyID||''' AND a.FYear='''||FPeriodYear||''' AND
a.FPeriod='''||FPeriodmonth||'''';
execute immediate p_insertSql;
open jlist for
select * from tJ_RPT_YRB;end J_RPT_YRB;我换不同名字了 结果PL/SQL:ORA-00942:表或视图不存在
(
jlist out sys_refcursor,
CompanyID in varchar2,
StorageID in varchar2,
FPeriodYear int,
FPeriodmonth int,
Warehouse in varchar2,
materialnumber in varchar2,
storeType in varchar2
) is
p_sqlstr varchar2(4000);
p_insertSql varchar2(4000);
p_UpdateSql varchar2(4000);
begin
p_sqlstr:='create global temporary table tJ_RPT_YRB(
FEntryDC int,
ZCOMPANYNAME VARCHAR2(50),
ZSTORAGE VARCHAR2(50),
ZWAREHOUSE VARCHAR2(50),
ZMATERAL VARCHAR2(50),
ZMATERALNAME VARCHAR2(100),
ZBRANDNAME VARCHAR2(20),
ZYEAR NUMBER(12,2),
ZMONTH NUMBER(12,2),
ZQTY INT,
ZACTUALCOST NUMBER(12,2)) ON COMMIT preserve ROWS';
execute immediate p_sqlstr;
--P_RTRP_DROPTEMPIFEXISTS('tJ_RPT_YRB',p_sqlstr);
p_insertSql:='insert into tJ_RPT_YRB select 1,a.FCompanyOrgUnitID,a.FStorageOrgUnitID,a.FWarehouseID,
a.FMaterialID,0,0,a.FYear,a.FPeriod,a.FPeriodEndQty,a.FPeriodEndActualCost from T_IM_InventoryBalance a
left join t_org_company b on a.FCompanyOrgUnitID=b.fid where b.fnumber='''||CompanyID||''' AND a.FYear='''||FPeriodYear||''' AND
a.FPeriod='''||FPeriodmonth||'''';
execute immediate p_insertSql;
open jlist for
select * from tJ_RPT_YRB;end J_RPT_YRB;我换不同名字了 结果PL/SQL:ORA-00942:表或视图不存在
检查表名拼写是否正确
可以将p_insertSql的语句打印出来,检查,执行 测试一下
1、先将建临时表的代码执行一遍,过程中这段代码就不需要了。
2、将【select * from tJ_RPT_YRB;】改成动态SQL文,这样编译就能正常通过。但就楼上代码而言需要注意一点,【create global temporary table tJ_RPT_YRB】要改为【'create or replace global temporary table tJ_RPT_YRB】,否则过程只能被调用一次。建议使用方案1,方案2没有多大意义。临时表与普通表的区别在于临时表里的数据是临时的。临时表一旦建立,会一直存在,除非对该表执行drop操作,这一点和普通表是一样。所以没有必要每次重复执行建表操作。
这句不是我有执行吗?
open jlist for
select * from tJ_RPT_YRB;楼主这个语句,也得用动态sql处理。
open jlist for 'select * from tJ_RPT_YRB';