• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

jdbc execute Oracle stored procedure errors

 
Brian Foster
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm new to jdbc and Oracle, so I could use some help.  I am trying to run a stored procedure and getting errors.

If I run an execute() or executeUpdate(), I get: "InternalError: operation not allowed: Ordinal binding and Named binding cannot be combined!"
If I run an executeQuery(), I get: "InternalError: Missing IN or OUT parameter at index:: 2"

The stored procedure has no outputs and five (5) inputs.  Also, I did not write the procedure, so I have no insight other than what was provided in the documentation:




Here is my javascript code I am trying to run:



All five parameters are passed to this routine.

The format of my url is:

I have tried passing clientVersion as number and string and I get the same errors either way.  I have also tried setting the parameters to static values and get the same errors.

If I open slqplus and run an exec of the procedure, then everything works fine.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why even try executeUpdate(), this is a plain execute().

It's strange, as i do not see any named binding going on; you are only doing it by ordinal. And index 2 seems fine. Are you sure there aren't any other setString()s in your code?

Regardless, can you run the SP manually?
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What language is that?


Also, that stored procedure is missing IN/OUT declarations.
I could be wrong (it has been known) but I didn't think there was a default.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:Also, that stored procedure is missing IN/OUT declarations.
I could be wrong (it has been known) but I didn't think there was a default.

If you omit IN, OUT, and IN OUT, then the argument defaults to IN.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Ta.
I don't think I ever relied on defaults...
It just looks wrong.
 
Brian Foster
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:What language is that?


Also, that stored procedure is missing IN/OUT declarations.
I could be wrong (it has been known) but I didn't think there was a default.


I am using vRealize Orchestrator, which uses javascript.  However, it may not be 100% javascript compliant.  For instance, I was not able to declare stmt using "PreparedCall stmt;" because PrepardCall was an unknown type.
 
Brian Foster
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:


Ta.
I don't think I ever relied on defaults...
It just looks wrong.


The actual stored procedure may declare the parameters as IN.  I didn't write the procedure, so I only have what was provided to us by the group that wrote it.

If there is a better way to write the prepareCall section, any advice would be appreciated.  As I said I'm new to jdbc and Oracle, so I have been piecing together information I have found online and from deconstructing other vRealize Orchestrator workflows and actions.
 
Brian Foster
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:Why even try executeUpdate(), this is a plain execute().

It's strange, as i do not see any named binding going on; you are only doing it by ordinal. And index 2 seems fine. Are you sure there aren't any other setString()s in your code?

Regardless, can you run the SP manually?


Originally, I had it as just execute().  I tried executeUpdate() and executeQuery() to see if either of them would work.  I don't have any other setString()s in my code.

If I open sqlplus and run the stored procedure manually, then it works, so I know the SP is correct.  Also, I didn't write the SP, I am just trying to do an integration with it.  I am using vRealize Orchestrator and trying to automate the DB schema creation using the provided stored procedure.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you get it to print the stack trace instead of just the message in the catch block?

Other than that, I have no idea about vRealize Orchestrator...
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:


Ta.
I don't think I ever relied on defaults...
It just looks wrong.

I believe i almost always relying on the defaults. There's a reason it's a default, and unless i specifically deviate from that, i leave in that way. Besides, i prefix my SP parameters (i rarely prefix variables) with I_, O_, and IO_, which is quite indicative on its own.

But, it just a matter of preference.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Foster wrote:
Brian Tkatch wrote:Why even try executeUpdate(), this is a plain execute().

It's strange, as i do not see any named binding going on; you are only doing it by ordinal. And index 2 seems fine. Are you sure there aren't any other setString()s in your code?

Regardless, can you run the SP manually?


Originally, I had it as just execute().  I tried executeUpdate() and executeQuery() to see if either of them would work.  I don't have any other setString()s in my code.

If I open sqlplus and run the stored procedure manually, then it works, so I know the SP is correct.  Also, I didn't write the SP, I am just trying to do an integration with it.  I am using vRealize Orchestrator and trying to automate the DB schema creation using the provided stored procedure.


Okay, so it works. Great. Now let's make sure the correct database is being connected to, as well as the correct schema and procedure being called. Also, try calling it with hardcoded values to test it. Let's make sure the call itself works before looking at those specified parameters.
 
Brian Foster
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
Brian Foster wrote:
Brian Tkatch wrote:Why even try executeUpdate(), this is a plain execute().

It's strange, as i do not see any named binding going on; you are only doing it by ordinal. And index 2 seems fine. Are you sure there aren't any other setString()s in your code?

Regardless, can you run the SP manually?


Originally, I had it as just execute().  I tried executeUpdate() and executeQuery() to see if either of them would work.  I don't have any other setString()s in my code.

If I open sqlplus and run the stored procedure manually, then it works, so I know the SP is correct.  Also, I didn't write the SP, I am just trying to do an integration with it.  I am using vRealize Orchestrator and trying to automate the DB schema creation using the provided stored procedure.


Okay, so it works. Great. Now let's make sure the correct database is being connected to, as well as the correct schema and procedure being called. Also, try calling it with hardcoded values to test it. Let's make sure the call itself works before looking at those specified parameters.


I changed back to just an execute().  If I hard code the values, the call still fails with the same message.  I have not found a way to get vRO to print a full stack trace.  Here is the error message as reported by vRO.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic