下面是我的存储过程,我是从sql server 2000 移植过来的,不知道他为什么不把临时表放在存储过程中,因此我手动用execute immediate 语句执行,但是,当我编译的时候还是提示我表或视图没有找到,为什么阿
CREATE OR REPLACE PROCEDURE
"CLINIC"."ZXM_MZ_SEARCHPREMEDICLINICT_MZ" (
stime IN DATE DEFAULT NULL,
etime IN DATE DEFAULT NULL,
MediCode IN VARCHAR2 DEFAULT NULL,
sorttemp IN VARCHAR2 DEFAULT NULL,
RC1 IN OUT Omwb_emulation.globalPkg.RCT1)
AS
stime_ DATE := stime;
etime_ DATE := etime;
MediCode_ VARCHAR2(1024) := MediCode;
sorttemp_ VARCHAR2(1024) := sorttemp;
mysql varchar2(1024);
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
BEGIN
NULL;
/*[SPCONV-ERR(2)]:(set nocount) Manual conversion required*/mysql:='CREATE GLOBAL TEMPORARY TABLE TT_TEMP_BASEINFO_5 ('||
'MediCode VARCHAR2(12) NOT NULL,'||
'MediName VARCHAR2(30) NOT NULL,'||
'etalon VARCHAR2(30) NOT NULL,'||
'unit VARCHAR2(30) NOT NULL, '||
'Number_ NUMBER(10,0) NOT NULL, '||
'price_pf NUMBER(10,4) NOT NULL, '||
'price_ls NUMBER(10,4) NOT NULL, '||
'SortID NUMBER(3,0) NOT NULL,'||
'CodeOrder1 VARCHAR2(12) NOT NULL,'||
'CodeOrder2 VARCHAR2(12) NOT NULL '||
') ON COMMIT PRESERVE ROWS ';
execute immediate mysql; /*[SPCONV-ERR(12)]:CREATE TABLE TT_TEMP_BASEINFO_5 statement passed to ddl file*/ DELETE FROM TT_TEMP_BASEINFO_5;
/*[SPCONV-ERR(27)]:(LIKE) if using '[' Manual conversion required*/ BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
INSERT INTO TT_TEMP_BASEINFO_5 (MediCode, MediName, etalon, unit, Number_, price_pf, price_ls, SortID, CodeOrder1, CodeOrder2)
SELECT MediCode, MediName, etalon, packUnit, NVL(SUM(Number_),
0), price_pf, price_ls, MediSortID, SUBSTR(MediCode, 1, 2),
SUBSTR(MediCode, 5, 10)
FROM Medi_yk_new a, tsmedicine_table b
WHERE
(a.MediID = b.MediID)
and
(MediCode LIKE ZXM_MZ_SEARCHPREMEDICLINICT_MZ.MediCode_) and b.DelFlag = 0
GROUP BY MediCode, MediName, etalon, packUnit, price_pf, price_ls,
MediSortID;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQLCODE, SQLERRM,true);
END;
--order by MediCode
NULL;
/*[SPCONV-ERR(30)]:(set nocount) Manual conversion required*/ OPEN RC1 FOR SELECT '"' || MediCode || '","' || mediName || '","' || etalon || '","' || Unit || '",' || RPAD(number_, 10, ' ') || ',' || RPAD(TO_CHAR(price_pf,'yy.mm.dd'), 15, ' ') || ',' || RPAD(TO_CHAR(price_ls,'yy.mm.dd'), 15, ' ') || ',"' || ( sortName ) || '",'
FROM TT_TEMP_BASEINFO_5 a, Medi_Sort
b
WHERE a.SortID = b.MediSOrtID
ORDER BY CodeOrder1 , CodeOrder2 ;
END ;
CREATE OR REPLACE PROCEDURE
"CLINIC"."ZXM_MZ_SEARCHPREMEDICLINICT_MZ" (
stime IN DATE DEFAULT NULL,
etime IN DATE DEFAULT NULL,
MediCode IN VARCHAR2 DEFAULT NULL,
sorttemp IN VARCHAR2 DEFAULT NULL,
RC1 IN OUT Omwb_emulation.globalPkg.RCT1)
AS
stime_ DATE := stime;
etime_ DATE := etime;
MediCode_ VARCHAR2(1024) := MediCode;
sorttemp_ VARCHAR2(1024) := sorttemp;
mysql varchar2(1024);
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
BEGIN
NULL;
/*[SPCONV-ERR(2)]:(set nocount) Manual conversion required*/mysql:='CREATE GLOBAL TEMPORARY TABLE TT_TEMP_BASEINFO_5 ('||
'MediCode VARCHAR2(12) NOT NULL,'||
'MediName VARCHAR2(30) NOT NULL,'||
'etalon VARCHAR2(30) NOT NULL,'||
'unit VARCHAR2(30) NOT NULL, '||
'Number_ NUMBER(10,0) NOT NULL, '||
'price_pf NUMBER(10,4) NOT NULL, '||
'price_ls NUMBER(10,4) NOT NULL, '||
'SortID NUMBER(3,0) NOT NULL,'||
'CodeOrder1 VARCHAR2(12) NOT NULL,'||
'CodeOrder2 VARCHAR2(12) NOT NULL '||
') ON COMMIT PRESERVE ROWS ';
execute immediate mysql; /*[SPCONV-ERR(12)]:CREATE TABLE TT_TEMP_BASEINFO_5 statement passed to ddl file*/ DELETE FROM TT_TEMP_BASEINFO_5;
/*[SPCONV-ERR(27)]:(LIKE) if using '[' Manual conversion required*/ BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
INSERT INTO TT_TEMP_BASEINFO_5 (MediCode, MediName, etalon, unit, Number_, price_pf, price_ls, SortID, CodeOrder1, CodeOrder2)
SELECT MediCode, MediName, etalon, packUnit, NVL(SUM(Number_),
0), price_pf, price_ls, MediSortID, SUBSTR(MediCode, 1, 2),
SUBSTR(MediCode, 5, 10)
FROM Medi_yk_new a, tsmedicine_table b
WHERE
(a.MediID = b.MediID)
and
(MediCode LIKE ZXM_MZ_SEARCHPREMEDICLINICT_MZ.MediCode_) and b.DelFlag = 0
GROUP BY MediCode, MediName, etalon, packUnit, price_pf, price_ls,
MediSortID;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQLCODE, SQLERRM,true);
END;
--order by MediCode
NULL;
/*[SPCONV-ERR(30)]:(set nocount) Manual conversion required*/ OPEN RC1 FOR SELECT '"' || MediCode || '","' || mediName || '","' || etalon || '","' || Unit || '",' || RPAD(number_, 10, ' ') || ',' || RPAD(TO_CHAR(price_pf,'yy.mm.dd'), 15, ' ') || ',' || RPAD(TO_CHAR(price_ls,'yy.mm.dd'), 15, ' ') || ',"' || ( sortName ) || '",'
FROM TT_TEMP_BASEINFO_5 a, Medi_Sort
b
WHERE a.SortID = b.MediSOrtID
ORDER BY CodeOrder1 , CodeOrder2 ;
END ;
LOCAL+TEMPORARY
这个配置设置好.