TIPTOP觸發使用ORACLE PROCEDURE

#4gl #tiptop #oracle

艾拉 2021-10-26 17:29:12 ‧ 1088 瀏覽

分享至

TIPTOP觸發使用ORACLE PROCEDURE
因為PROCEDURE會OUTPUT REF CURSOR
想請教TIPTOP該如何宣告此種變數
或如何使用?

目前r.c2會出現
error:(-4334) The variable 'P_CURSOR' in its current form is too complex to be used in this statement.
The compilation was not successful.

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

by2048

iT邦高手 1 級 ‧ 2021-10-27 08:37:29

一般的程式段宣告
#0 LET l_sql = "SELECT cpf01,cpf02 from cpf_file"
LET l_sql = l_sql CLIPPED," ORDER BY cpf01 "
#1 PREPARE r610_prepare1 FROM l_sql
IF SQLCA.sqlcode != 0 THEN
CALL cl_err('prepare:',SQLCA.sqlcode,1)
EXIT PROGRAM
END IF
#2 DECLARE r610_curs1 CURSOR FOR r610_prepare1
CALL cl_outnam('apyr610') RETURNING l_name
START REPORT r610_rep TO l_name
LET g_pageno = 0
#3 FOREACH r610_curs1 INTO sr.*
IF SQLCA.sqlcode != 0 THEN
CALL cl_err('foreach:',SQLCA.sqlcode,1) EXIT FOREACH
END IF
OUTPUT TO REPORT r610_rep(sr.*)
END FOREACH

您所說的ORACLE PROCEDURE 是指上述這種還是以下這種
CREATE OR REPLACE PROCEDURE MY_PROCEDURE2 (
i_p_1 VARCHAR2,
i_p_2 VARCHAR2,
i_p_3 NUMBER
)
IS
BEGIN
DBMS_OUTPUT.put_line (i_p_1);
DBMS_OUTPUT.put_line (i_p_2);
DBMS_OUTPUT.put_line (i_p_3);
END MY_PROCEDURE2;

回應 5
分享
檢舉

看更多先前的回應...收起先前的回應...

艾拉 iT邦新手 5 級 ‧ 2021-10-27 11:21:34 檢舉

是指下面這種

CREATE OR REPLACE PACKAGE XXX AS
PROCEDURE OOO (
P_ARGV1 VARCHAR2,
P_OUT_MSG OUT VARCHAR2,
P_OUT_CURSOR OUT C_CURSOR
);
END XXX;

上面PACKAGE已完成也可使用
但想從TIPTOP觸發,
目前遇到困難是P_OUT_CURSOR這是產出CURSOR資料集,
所以出現r.c2 錯誤error:(-4334)

by2048 iT邦高手 1 級 ‧ 2021-10-28 12:36:17 檢舉

#1 LET g_sql="UPDATE ",g_dbs_gl CLIPPED," aba_file SET abaacti = 'N' WHERE aba01 = ? AND aba00 = ? "
#2 PREPARE p591_updaba_p FROM g_sql
#3 EXECUTE p591_updaba_p USING g_existno,g_ooz.ooz02b
IF SQLCA.sqlcode THEN
CALL cl_err('(upd abaacti)',SQLCA.sqlcode,1) LET g_success = 'N' RETURN
END IF
照樓下大師的方法需參數的方式;不需參數應該就沒? 沒USING

艾拉 iT邦新手 5 級 ‧ 2021-10-28 15:39:08 檢舉

#1 PREPARE p100_fnds FROM "call XXX.OOO(?,?,?,?)"
#2 EXECUTE p100_fnds USING
#3 P_ARGV1 IN,
#4 P_OUT_MSG OUT,
#5 P_CURSOR OUT

目前寫法是用?參數傳入給ORACLE PACKAGE使用的
P_CURSOR在package屬於REF CURSOR型態
所以p_cursor會是資料集,每次執行結果有幾筆是不確定的

by2048 iT邦高手 1 級 ‧ 2021-10-28 16:29:46 檢舉

call XXX.OOO(?,?,?,?)
這種方法沒用過,幫不上忙

艾拉 iT邦新手 5 級 ‧ 2021-10-29 10:11:34 檢舉

還是謝謝協助討論~~

登入發表回應

做工仔人!

iT邦大師 1 級 ‧ 2021-10-27 17:30:21

-4334 的錯誤全文為:
to be used in this statement.
By "too complex" 4GL means "has too many component parts." In this statement, only simple variables (those that have a single component) can be used. If variable-name is an array, you must provide a subscript to select just one element. If it is a record, you must choose just one of its components. (However, if this statement permits a list of variables, as in the INITIALIZE statement, you can use asterisk or THRU notation to convert a record name into a list of components.)

參考一下這篇