[Question] How to solve the problem of calling Oracle stored procedure task instance failure

[wistwill]

Describe the question I have defined an instance of Oracle storage procedure task, but there is an error during execution： [INFO] 2021-02-10 00:08:58.816 - [taskAppId=TASK-4-5-26]:[115] - create dir success /tmp/dolphinscheduler/exec/process/2/4/5/26 [INFO] 2021-02-10 00:08:58.915 - [taskAppId=TASK-4-5-26]:[75] - procedure task params {"method":"PKG_SPIS_STAT_TMPCMAINEXTRAS.GET_TMPCMAINEXTRAS_PKG(IN_STARTDATE,IN_ENDDATE,IN_BUSINESSNO)","datasource":4,"type":"ORACLE","localParams":[{"prop":"IN_STARTDATE","direct":"IN","type":"VARCHAR","value":"2021-02-08"},{"prop":"IN_ENDDATE","direct":"IN","type":"VARCHAR","value":"2021-02-08"}]} [INFO] 2021-02-10 00:08:58.922 - [taskAppId=TASK-4-5-26]:[92] - procedure type : ORACLE, datasource : 4, method : PKG_SPIS_STAT_TMPCMAINEXTRAS.GET_TMPCMAINEXTRAS_PKG(IN_STARTDATE,IN_ENDDATE,IN_BUSINESSNO) , localParams : [Property{prop='IN_STARTDATE', direct=IN, type=VARCHAR, value='2021-02-08'}, Property{prop='IN_ENDDATE', direct=IN, type=VARCHAR, value='2021-02-08'}] [INFO] 2021-02-10 00:08:59.045 - [taskAppId=TASK-4-5-26]:[132] - call method : {call PKG_SPIS_STAT_TMPCMAINEXTRAS.GET_TMPCMAINEXTRAS_PKG(IN_STARTDATE,IN_ENDDATE,IN_BUSINESSNO)(?,?)} [INFO] 2021-02-10 00:08:59.045 - [taskAppId=TASK-4-5-26]:[221] - localParams : prop : IN_STARTDATE , dirct : IN , type : VARCHAR , value : 2021-02-08 [INFO] 2021-02-10 00:08:59.045 - [taskAppId=TASK-4-5-26]:[221] - localParams : prop : IN_ENDDATE , dirct : IN , type : VARCHAR , value : 2021-02-08 [ERROR] 2021-02-10 00:08:59.047 - [taskAppId=TASK-4-5-26]:[154] - procedure task error java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00801: internal error [22503] ORA-06550: line 1, column 7: PL/SQL: Statement ignored

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494) at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallabl

Which version of DolphinScheduler: -[apache-dolphinscheduler-incubating-1.3.4]

Additional context Testing with PL / SQL is no problem.

Requirement or improvement This seems to be an internal problem. I wonder if there is a problem with the format I defined. If so, please give me a correct example of defining Oracle stored procedures; If it's not the problem I defined, how can I solve it.

[zhuangchong]

The IN_BUSINESSNO variable is undefined

[wistwill]

> The IN_BUSINESSNO variable is undefined I test defined IN_BUSINESSNO but got an error: [INFO] 2021-02-17 23:16:26.966 - [taskAppId=TASK-4-9-53]:[115] - create dir success /tmp/dolphinscheduler/exec/process/2/4/9/53 [INFO] 2021-02-17 23:16:27.048 - [taskAppId=TASK-4-9-53]:[75] - procedure task params {"method":"PKG_SPIS_STAT_TMPCMAINEXTRAS.GET_TMPCMAINEXTRAS_PKG(IN_STARTDATE,IN_ENDDATE,IN_BUSINESSNO)","datasource":4,"type":"ORACLE","localParams":[{"prop":"IN_STARTDATE","direct":"IN","type":"VARCHAR","value":"2021/02/08"},{"prop":"IN_ENDDATE","direct":"IN","type":"VARCHAR","value":"2021/02/08"},{"prop":"IN_BUSINESSNO","direct":"IN","type":"VARCHAR","value":""}]} [INFO] 2021-02-17 23:16:27.631 - [taskAppId=TASK-4-9-53]:[92] - procedure type : ORACLE, datasource : 4, method : PKG_SPIS_STAT_TMPCMAINEXTRAS.GET_TMPCMAINEXTRAS_PKG(IN_STARTDATE,IN_ENDDATE,IN_BUSINESSNO) , localParams : [Property{prop='IN_STARTDATE', direct=IN, type=VARCHAR, value='2021/02/08'}, Property{prop='IN_ENDDATE', direct=IN, type=VARCHAR, value='2021/02/08'}, Property{prop='IN_BUSINESSNO', direct=IN, type=VARCHAR, value=''}] [INFO] 2021-02-17 23:16:27.999 - [taskAppId=TASK-4-9-53]:[132] - call method : {call PKG_SPIS_STAT_TMPCMAINEXTRAS.GET_TMPCMAINEXTRAS_PKG(IN_STARTDATE,IN_ENDDATE,IN_BUSINESSNO)(?,?,?)} [INFO] 2021-02-17 23:16:27.999 - [taskAppId=TASK-4-9-53]:[221] - localParams : prop : IN_STARTDATE , dirct : IN , type : VARCHAR , value : 2021/02/08 [INFO] 2021-02-17 23:16:28.000 - [taskAppId=TASK-4-9-53]:[221] - localParams : prop : IN_ENDDATE , dirct : IN , type : VARCHAR , value : 2021/02/08 [INFO] 2021-02-17 23:16:28.000 - [taskAppId=TASK-4-9-53]:[221] - localParams : prop : IN_BUSINESSNO , dirct : IN , type : VARCHAR , value : [ERROR] 2021-02-17 23:16:28.019 - [taskAppId=TASK-4-9-53]:[154] - procedure task error java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00801: internal error [22503] ORA-06550: line 1, column 7: PL/SQL: Statement ignored

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity for 30 days. It will be closed in next 7 days if no further activity occurs.

[github-actions[bot]]

This issue has been closed because it has not received response for too long time. You could reopen it if you encountered similar problems in the future.