Access oracle database via stored procedure

Scott Klement's open source interface to using JDBC database drivers from RPG. http://www.scottklement.com/jdbc/
pada
Posts: 6
Joined: Fri Nov 19, 2021 4:15 pm
Location: Belgium

Access oracle database via stored procedure

Post by pada »

Hello,
I have the following Oracle stored procedure :
PROCEDURE genereCodeExterne (num_log IN NUMBER, P_CLASSE IN NUMBER, ISSITEFRANCHISE IN BOOLEAN, P_CODE_RETOURNE OUT VARCHAR(13), P_RETOUR OUT NUMBER)
8 am unable to work with the boolean ISSITEFRANCHISE value, we try to value this variable with JDBC_SETINT, but it does not work, I get the following errors :
--------------------------------------------------------------------------------------------------
Message . . . . : Java exception received when calling Java method.
Cause . . . . . : RPG procedure JDBC_EXECC in program SQLSERVER/JDBCR4
received Java exception "java.sql.SQLException: ORA-06553: PLS-306: numéro
ou types d'arguments erronés dans appel à 'GENEREC" when calling method
"execute" with signature "()Z" in class "java.sql.CallableStatement".
--------------------------------------------------------------------------------------------------
Message . . . . : Java exception received when calling Java method.
Cause . . . . . : RPG procedure JDBC_GETST in program SQLSERVER/JDBCR4
received Java exception "java.sql.SQLException: Définitions manquante" when
calling method "getString" with signature "(I)Ljava.lang.String;" in class
"java.sql.CallableStatement".
-------------------------------------------------------------------------------------------------
If I use a stored procedure without a boolean parameter, it works fine.

You can find the RPG code below :
sql='Call PKCTRLCODEEXT.genereCodeExterne(?,?,?,?,?)';
stmt = JDBC_PrepCall(conn:%trim(Sql));
jdbc_setInt(stmt:1:1);
jdbc_setInt(stmt:2:0);
jdbc_setInt(stmt:3:0);
jdbc_RegisterOutParameter(stmt:4:12);
jdbc_RegisterOutParameter(stmt:5:4);
jdbc_execCall(stmt);
aaaRac = JDBC_GetString(stmt:4);
Write aaar;
JDBC_freeCallStmt(stmt);

Can you help me to find the solution ?
Tank you.
Pascal.
Scott Klement
Site Admin
Posts: 635
Joined: Sun Jul 04, 2021 5:12 am

Re: Access oracle database via stored procedure

Post by Scott Klement »

How is the PKCTRLCODEEXT.genereCodeExterne defined?
pada
Posts: 6
Joined: Fri Nov 19, 2021 4:15 pm
Location: Belgium

Re: Access oracle database via stored procedure

Post by pada »

Hello,

At the start of my original post, I give the following information :
"I have the following Oracle stored procedure :
PROCEDURE genereCodeExterne (num_log IN NUMBER, P_CLASSE IN NUMBER, ISSITEFRANCHISE IN BOOLEAN, P_CODE_RETOURNE OUT VARCHAR(13), P_RETOUR OUT NUMBER)"


What additional information do you wish to receive ?

Thank you.
Pascal.
Scott Klement
Site Admin
Posts: 635
Joined: Sun Jul 04, 2021 5:12 am

Re: Access oracle database via stored procedure

Post by Scott Klement »

Notice that you are getting this error:

Code: Select all

PLS-306: numéro ou types d'arguments erronés dans appel à 'GENEREC
This is a language that I do not speak, but I think it says that you are not passing the correct parameters to the function.

I do not use Oracle databases, so I do not know their rules for how to call a function and what is needed. However, on Db2, there are rules for how data types get "promoted". For example, a CHAR can be promoted to a VARCHAR. But, when a data type cannot be promoted, Db2 won't be able to find the function because Db2 does not only look up a function signature by it's name, it also looks it up by the number and type of the parameters.
  • Your third parameter is set with JDBC_setInt(), but according to your definition, it is boolean.
  • It does not appear that you are setting anything for the 4th or 5th parameters. How can it match the data type if you don't provide any parameters?
Again, I don't know if Oracle works the same way -- I can only take guesses. If nobody else here is able to help you, you may have to do some research on how Oracle works.
pada
Posts: 6
Joined: Fri Nov 19, 2021 4:15 pm
Location: Belgium

Re: Access oracle database via stored procedure

Post by pada »

The 4th and 5th parameters are set by respectively :
jdbc_RegisterOutParameter(stmt:4:12); 12 for varchar
jdbc_RegisterOutParameter(stmt:5:4); 4 for integer

It seems that there is a problem to pass a parameter of type "boolean" to the function since this type of variable does not exist as such in RPGLE which uses the type of variable "indicator" which takes the value *On or *Off .
Do you have any idea how to define in RPGLE the type of a variable which could be converted to boolean when calling the function ?
Scott Klement
Site Admin
Posts: 635
Joined: Sun Jul 04, 2021 5:12 am

Re: Access oracle database via stored procedure

Post by Scott Klement »

I did a quick Google search and found this:
https://docs.oracle.com/javase/7/docs/a ... 20boolean)

It appears that there is a setBoolean routine that you can call. I have not tried it, myself. This would be my guess as to how this should work:

Code: Select all

  *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++    
  * JDBC_setBoolean(): Set a boolean parameter in a prepared        
  *                    statment                                     
  *                                                                 
  *   idx = (input) parameter index                                 
  *  bool = (input) boolean value. *ON=True, *OFF=false             
  *                                                                 
  *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++    
 D JDBC_setBoolean...                                               
 D                 PR                  ExtProc(*JAVA                
 D                                     :'java.sql.PreparedStatement'
 D                                     :'setBoolean')               
 D   idx                               like(jInt) value             
 D   bool                         1n   const                        
Can you try it and tell me if it works?
pada
Posts: 6
Joined: Fri Nov 19, 2021 4:15 pm
Location: Belgium

Re: Access oracle database via stored procedure

Post by pada »

Sorry but I have a compilation error :

2432 D PR ExtProc(*JAVA
2433 D :'java.sql.PreparedStatement'
2434 D :'setBoolean')
2435 D idx like(jInt) value
2436 D boolean 1n const
2437
*RNF3964 30 2436 033100 The VALUE keyword is required for this parameter type when
used in a method call.
Scott Klement
Site Admin
Posts: 635
Joined: Sun Jul 04, 2021 5:12 am

Re: Access oracle database via stored procedure

Post by Scott Klement »

replace 'const' with 'value' on the boolean parameter.
pada
Posts: 6
Joined: Fri Nov 19, 2021 4:15 pm
Location: Belgium

Re: Access oracle database via stored procedure

Post by pada »

It also doesn't work, I get the following 2 errors when calling the package :
-------------------------------------------------------------------------------------------------------
Message . . . . : Java exception received when calling Java method.
Cause . . . . . : RPG procedure JDBC_EXECC in program JDBCR4/JDBCR4 received
Java exception "java.sql.SQLException: ORA-06553: PLS-306: numéro ou types
d'arguments erronés dans appel à 'GENEREC" when calling method "execute"
with signature "()Z" in class "java.sql.CallableStatement".
-------------------------------------------------------------------------------------------------------
Message . . . . : Java exception received when calling Java method.
Cause . . . . . : RPG procedure JDBC_GETST in program JDBCR4/JDBCR4 received
Java exception "java.sql.SQLException: Définitions manquante" when calling
method "getString" with signature "(I)Ljava.lang.String;" in class
"java.sql.CallableStatement".
-------------------------------------------------------------------------------------------------------

By the way, I found the following information on : https://stackoverflow.com/questions/145 ... ith-a-bool
"The JDBC drivers do not support the passing of BOOLEAN parameters to PL/SQL stored procedures. If a PL/SQL procedure contains BOOLEAN values, you can work around the restriction by wrapping the PL/SQL procedure with a second PL/SQL procedure that accepts the argument as an INT and passes it to the first stored procedure. When the second procedure is called, the server performs the conversion from INT to BOOLEAN."

We will therefore opt for the proposed solution and we will no longer use a boolean variable as an oracle package parameter.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

If you find a solution, it would be nice of you to let me know, you can send me an email at the address listed in my profile.

Many thanks for your help.
Scott Klement
Site Admin
Posts: 635
Joined: Sun Jul 04, 2021 5:12 am

Re: Access oracle database via stored procedure

Post by Scott Klement »

There basically no chance that I'm going to find a solution. I never use Oracle databases, and I haven't used JDBCR4 in many years.
Post Reply