B4J Question SQL Procedures / Oracle

[XbNnX_507]

Hi, i have this procedure in a OracleDatabase just for testing..

create or replace PROCEDURE PROCEDURE1(cursorParam OUT SYS_REFCURSOR)
 IS
  BEGIN
   OPEN cursorParam FOR
    SELECT * FROM TABLAPRUEBA;
END ;

I'm calling this Procedure from B4J with this:

Dim ObjectCall As Object = sql.CreateCallStatement("{CALL PROCEDURE1}", Array() )
    Dim rs As ResultSet = sql.ExecCall( ObjectCall )

It throws me this error:

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

what am i doing wrong ?

 

[OliverA]

I think a () may be required after the procedure name:

sql.CreateCallStatement("{CALL PROCEDURE1()}", Array() )

Link: https://docs.oracle.com/cd/B28359_01/java.111/b31225/chseven.htm#CACIBAHF

 

[XbNnX_507]

I think a () may be required after the procedure name:

sql.CreateCallStatement("{CALL PROCEDURE1()}", Array() )

Link: https://docs.oracle.com/cd/B28359_01/java.111/b31225/chseven.htm#CACIBAHF

Same Error. Seems like the problem is the Out parameter probably because i'm returning a cursor

 

[XbNnX_507]

CallableStatement cstmt = conn.prepareCall("{? = CALL PROCEDURE(?)}");   
cstmt.registerOutParameter(1, Types.FLOAT); ' <- how to set this ? outParameter ? 
cstmt.executeUpdate();

 

[OliverA]

I think you need to use this: https://www.b4x.com/android/forum/threads/sql-simplyfying-calling-stored-procedures.73181/

 

[XbNnX_507]

I think you need to use this: https://www.b4x.com/android/forum/threads/sql-simplyfying-calling-stored-procedures.73181/

Great Library!.

I would like to return a resulset from the procedure
seems like the SQLRESULSET type is not included...

 MYSQLProcedure.AddOutputParameter(2,MYSQLProcedure.SQLINTEGER) ' <- MYSQLProcedure.SQLRESULSET ?

 

[OliverA]

I don’t think you use AddOutputParameter. There is a FetchResultSet method. Look at the example posted