Page 1 of 1

Oracle Insert Issue

Posted: Sat May 14, 2022 1:28 am
by TxPenguin
For a number of years I have been using the JDBC programs to access Oracle; always as a read-only with a return of a result set.

I am now trying to perform an insert (and an update) to an Oracle table and I am receiving a strange error in the JDBCR4 program.

First, here is the error I am finding in my job log:

Message ID . . . . . . : RNX0301 Severity . . . . . . . : 50
Message type . . . . . : Escape
Date sent . . . . . . : 05/13/22 Time sent . . . . . . : 17:50:07

Message . . . . : Java exception received when calling Java method.
Cause . . . . . : RPG procedure JDBC_EXECU in program SLJDBC/JDBCR4 received Java exception "java.sql.SQLException: ORA-00933: SQL command not properly ended█" when calling method "executeUpdate" with signature "(Ljava.lang.String;)I" in class "java.sql.Statement".

Prior to executing the insert statement, I create the connection (successfully) as such:
If OracleConnection = *NULL;
OracleConnection = $LoadJDBCConn(OracleDatabase : OracleConnection);
EndIf;

My SQL statement, in part, looks like this: (I will say, when I run the statement inside of DBeaver (my SQL tool of choice), it executes with no issue)

ExecuteThis = 'INSERT INTO schema.tablename +
(GATEWAY, AIRLINE, AIRBILLNUMBER, +
……..
CREATEUID, CREATETMSTP) +
VALUES +
(Db2_NsiGWAY, Db2_NsiALIN, Db2_NsiABNU, +
……..
HardcodeDBA, SYSDATE);';

My execution of the statement looks as such (it is within here where the error occurs):
OracleRowCountReturn = JDBC_ExecUpd( OracleConnection : ExecuteThis );

Based on what I read of the JDBC_ExecUpd code in the JDBCR4 program, I believe I have it coded correctly.

As I said, the connection is created successfully and the user on Oracle has Insert, Update, Delete, and Select writes against the table in question.

Any thoughts anyone has on this would be very welcomed.

Thanks,
Steve

Re: Oracle Insert Issue

Posted: Sat May 14, 2022 3:07 am
by Scott Klement
I know very little about Oracle, but...
  1. a quick Google search found this article that discusses this error message: https://www.databasestar.com/ora-00933/
  2. Another possibility that comes to mind is that your "ExecuteThis" variable may not be long enough to hold the entire statement.
  3. Or, perhaps, the statement is larger than 32767 characters long (which is the size of the parameter that JDBC_ExecUpd uses.)
Do any of these make sense?

Re: Oracle Insert Issue

Posted: Sat May 14, 2022 1:44 pm
by TxPenguin
Scott,

Thank you for the thoughts.

1. I had found that article, as well, beforehand, but my Insert does not have an Order By clause.
2. The variable is 1500 bytes, and when I do a debug immediately before the execution I can see it is only using about 1200 of the bytes.
3. Same answer as #2, it is 1500 bytes in length.

It all does make perfect sense and I'm glad to see I was following the same steps as you did before I posted for the assistance.

I do have a question on the Oracle driver that is used, though. The Oracle folks at the place contend that is an Oracle v8 driver and they had to allow older version driver access to occur. I believe the driver was part of your JDBC package, correct? If so, do you know if there is a new v12 or v19 driver that is out there that works with your JDBC process?

I am going to continue to monitor here and tinker with the process and see if something works. If it does, I'll report back on the thread.

Thanks,
Steve

Re: Oracle Insert Issue

Posted: Mon May 16, 2022 6:33 pm
by TxPenguin
Scott,

Thank goodness for perseverance and Google (for each time I received yet a different ORA error code along the way).

I now have a successful Insert using the JDBC_ExecUpd process.

Have a great day.

Steve