存盤程序里執行execute immediate l_sql，報錯ORA-02070: 資料庫 DL_OA 不支持此背景關系中的 some function，如果下代碼直接執行p_updateValue這個程序就沒有問題，執行p_bi2oaIncome_days，再呼叫到p_updateValue，執行到 execute immediate l_sql;就報錯誤ORA-02070: 資料庫 DL_OA 不支持此背景關系中的 some function，求大神指點
procedure p_bi2oaIncome_days(psdate in varchar2,pedate in varchar2) is
CURSOR C_date IS --宣告顯式游標
select dt from rpt_date_v where dt>=psdate and dt<=pedate;
C_ROW C_date%ROWTYPE; --定義游標變數
begin
--Fetch 回圈
OPEN C_date;--必須要明確的打開和關閉游標
LOOP
FETCH C_date INTO C_ROW;
EXIT WHEN C_date%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(C_ROW.DT || '++' );
----do something
p_bi2oaIncome_hospitals(C_ROW.DT);
----
END LOOP;
CLOSE C_date;
end p_bi2oaIncome_days;
procedure p_bi2oaIncome_hospitals(pdate in varchar2) is
l_sql varchar2(2000);
l_yestoday varchar2(10);
l_startday varchar2(10);
l_month varchar2(10);
for i IN 1..l_orgid.count LOOP
p_updateValue(l_orgid(i),l_yestoday,l_startday,l_orgidOA(i),l_month);
end loop;
end p_bi2oaIncome_hospitals;
procedure p_updateValue(orgid in varchar2,pdate2 in varchar2,pdate1 in varchar2,orgidOA in varchar2,l_month in varchar2) is
l_sql varchar2(2000);
l_kpivalue number(12,2);
l_kpivalueSum number(12,2);
l_fieldValue varchar2(50);
l_year number(12,2);
begin
l_kpivalue:=0;
l_year:=2021;
if l_month='01' then
l_fieldValue :='field0009';
elsif l_month='02' then
l_fieldValue :='field0013';
elsif l_month='03' then
l_fieldValue :='field0017';
elsif l_month='04' then
l_fieldValue :='field0021';
elsif l_month='05' then
l_fieldValue :='field0025';
elsif l_month='06' then
l_fieldValue :='field0029';
elsif l_month='07' then
l_fieldValue :='field0033';
elsif l_month='08' then
l_fieldValue :='field0037';
elsif l_month='09' then
l_fieldValue :='field0041';
elsif l_month='10' then
l_fieldValue :='field0045';
elsif l_month='11' then
l_fieldValue :='field0049';
elsif l_month='12' then
l_fieldValue :='field0053';
else
l_fieldValue :='';
end if;
--此處得量值復制省略
l_sql:='update formmain_8977@dl_oa set "'||l_fieldValue||'" = '||l_kpivalue||' where "field0002" = '''||orgidOA||''' and "field0001" = '||l_year||' ';
execute immediate l_sql;
commit;
end p_updateValue;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/256470.html
標籤：開發
上一篇：資料庫----常見函式