Page 1 of 2

Access oracle database via stored procedure

Posted: Fri Nov 19, 2021 4:38 pm
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.

Re: Access oracle database via stored procedure

Posted: Fri Nov 19, 2021 5:57 pm
by Scott Klement
How is the PKCTRLCODEEXT.genereCodeExterne defined?

Re: Access oracle database via stored procedure

Posted: Mon Nov 22, 2021 9:07 am
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.

Re: Access oracle database via stored procedure

Posted: Tue Nov 23, 2021 7:26 pm
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.

Re: Access oracle database via stored procedure

Posted: Mon Nov 29, 2021 1:21 pm
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 ?

Re: Access oracle database via stored procedure

Posted: Mon Nov 29, 2021 4:47 pm
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?

Re: Access oracle database via stored procedure

Posted: Wed Dec 01, 2021 9:18 am
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.

Re: Access oracle database via stored procedure

Posted: Wed Dec 01, 2021 6:48 pm
by Scott Klement
replace 'const' with 'value' on the boolean parameter.

Re: Access oracle database via stored procedure

Posted: Fri Dec 03, 2021 11:19 am
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.

Re: Access oracle database via stored procedure

Posted: Fri Dec 03, 2021 11:29 pm
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.