1. *** New version of B4J is available ***
    B4J v7.8
    Dismiss Notice

B4J Question Calling Oracle Procedure

Discussion in 'B4J Questions' started by XbNnX_507, Apr 26, 2019.

  1. XbNnX_507

    XbNnX_507 Active Member Licensed User

    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.
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    Try this:
    Code:
    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 and OliverA like this.
  3. XbNnX_507

    XbNnX_507 Active Member Licensed User

    This is my Oracle Procedure
    Code:
    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.
    Code:
    sql.Initialize("oracle.jdbc.OracleDriver""jdbc:oracle:thin:USER/PASS@127.0.0.1:2222:orcl" )
        
    Dim CallableStatement As JavaObject = sql.CreateCallStatement ("{call PROCEDURE1(?,?)}"Array(6Null) )
        
    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:
    Code:
    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:
    Code:
    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 : 6Sql = 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
     
  4. OliverA

    OliverA Expert Licensed User

    Try this:
    Code:
    Dim CallableStatement As JavaObject = sql.CreateCallStatement ("{call PROCEDURE1(?,?)}"Null )
        CallableStatement.RunMethod(
    "setInt"Array(16)) ' 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
     
    Erel and XbNnX_507 like this.
  5. XbNnX_507

    XbNnX_507 Active Member Licensed User

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

    XbNnX_507 Active Member Licensed User

    for those using JServer Pool with Oracle You need to call
    Code:
    Dim rs As ResultSet = CallableStatement.RunMethod("getObject"Array(2))
    Otherwise you'll get an exception.
    Hope this helps.
     
    OliverA likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice