B4J Question Calling Oracle Procedure

[XbNnX_507]

Hi, i'm having some problem with calling procedures from the oracle database...
In order to call them i need to register the type of the out param - to an Oracle specific type
.registerOutParameter(index, OracleTypes.CURSOR);

This link show what i'm trying to achieve-> https://enterprisedt.com/publications/oracle/result_set.html

is it possible to set the outParameter to a OracleType.cursor using SQL.CreateCallStatement ?
Thanks.

 

[Erel]

Try this:

Dim CallableStatement As JavaObject = SQL.CreateCallStatement (Query, Null)
Dim OracleTypes As JavaObject
OracleTypes.InitializeStatic("oracle.jdbc.driver.OracleTypes")
CallableStatement.RunMethod("registerOutParameter", Array(1, OracleTypes.GetField("CURSOR"))

 

[XbNnX_507]

This is my Oracle Procedure

create or replace PROCEDURE  PROCEDURE1(
    tno     IN   INTEGER DEFAULT 1500, --variable entrada
    crsr     OUT SYS_REFCURSOR -- oracle cursor
)
AS
BEGIN
    OPEN crsr FOR select * from resultados where tno_no= tno;  --- return the cursor back to jdbc resultset
END;

I'm calling it this way.

    sql.Initialize("oracle.jdbc.OracleDriver", "jdbc:oracle:thin:USER/[email protected]:2222:orcl" )
    Dim CallableStatement As JavaObject = sql.CreateCallStatement ("{call PROCEDURE1(?,?)}", Array(6, Null) )
    Dim OracleTypes As JavaObject
    OracleTypes.InitializeStatic("oracle.jdbc.driver.OracleTypes")
    CallableStatement.RunMethod("registerOutParameter", Array(1, OracleTypes.GetField("CURSOR")))
    Dim rs As ResultSet = sql.ExecCall( CallableStatement)
    Do While rs.NextRow
       
    Loop
    rs.Close

i got this error:

Waiting for debugger to connect...
Program started.
Error occurred on line: 28 (Main)
java.sql.SQLException: Parameter Type Conflict
    at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2551)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3772)
    at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1300)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
    at anywheresoftware.b4j.objects.SQL.ExecCall(SQL.java:391)
    at b4j.example.main._appstart(main.java:130)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:78)
    at b4j.example.main.start(main.java:38)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$166(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$179(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$177(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$178(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$152(WinApplication.java:177)
    at java.lang.Thread.run(Thread.java:748)

I'm guessing this error is because sql.CreateCallStatement ("{call PROCEDURE1(?,?)}", ARRAY(5, NULL)) ) '<- the Null Value

But if i do : sql.CreateCallStatement ("{call PROCEDURE1(?,?)}"ARRAY ( 5, OracleTypes.GetField("CURSOR") ) )
i get this error:

Waiting for debugger to connect...
Program started.
Error occurred on line: 32 (Main)
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PROCEDURE1'
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(T4CCallableStatement.java:223)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:56)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:907)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
    at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1300)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
    at anywheresoftware.b4j.objects.SQL.ExecCall(SQL.java:391)
    at b4j.example.main._appstart(main.java:127)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:78)
    at b4j.example.main.start(main.java:38)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$166(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$179(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$177(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$178(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$152(WinApplication.java:177)
    at java.lang.Thread.run(Thread.java:748)
Caused by: Error : 6550, Position : 6, Sql = BEGIN DGRPWEB.rtransaccionb(:1 ,:2 ); END;, OriginalSql = {call DGRPWEB.rtransaccionb(?,?)}, Error Msg = ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'RTRANSACCIONB'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
    ... 39 more

 

[OliverA]

Try this:

    Dim CallableStatement As JavaObject = sql.CreateCallStatement ("{call PROCEDURE1(?,?)}", Null )
    CallableStatement.RunMethod("setInt", Array(1, 6)) ' Note: The first ? is indexed as 1 not 0
    Dim OracleTypes As JavaObject
    OracleTypes.InitializeStatic("oracle.jdbc.driver.OracleTypes")
    CallableStatement.RunMethod("registerOutParameter", Array(2, OracleTypes.GetField("CURSOR"))) ' 2nd ? is indexed as 2
    CallableStatement.RunMethod("execute", Null) ' sql.execCall uses executeQuery() method
    Dim rs As ResultSet = CallableStatement.RunMethod("getCursor", Array(2)) ' This may not work. The stackoverflow example linked below uses a lot of casting

Note: Not tested (for typos or anything)

Sources used:
https://stackoverflow.com/questions/53531178/oracle-jdbc-get-sys-refcursor-from-procedure
https://github.com/AnywhereSoftware/B4J_SQL/blob/master/src/anywheresoftware/b4j/objects/SQL.java

 

[XbNnX_507]

Dim CallableStatement As JavaObject = sql.CreateCallStatement ("{call PROCEDURE1(?,?)}", Null )
CallableStatement.RunMethod("setInt", Array(1, 6)) ' Note: The first ? is indexed as 1 not 0
Dim OracleTypes As JavaObject
OracleTypes.InitializeStatic("oracle.jdbc.driver.OracleTypes")
CallableStatement.RunMethod("registerOutParameter", Array(2, OracleTypes.GetField("CURSOR"))) ' 2nd ? is indexed as 2
CallableStatement.RunMethod("execute", Null) ' sql.execCall uses executeQuery() method
Dim rs As ResultSet = CallableStatement.RunMethod("getCursor", Array(2)) ' This may not work. The stackoverflow example linked below uses a lot of casting

Thanks. it works!.Finally.
I was trying everything.

 

[XbNnX_507]

for those using JServer Pool with Oracle You need to call

Dim rs As ResultSet = CallableStatement.RunMethod("getObject", Array(2))

Otherwise you'll get an exception.
Hope this helps.