Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> JAVA, RPGLE Oracle errors, Oracle help
Pete Mets
post May 6 2012, 10:02 AM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 6-May 12
Member No.: 47,174



I have an RPGLE program connecting to an Oracle database and processing records on an iseries.
I have a service program and binding directory that handles the JAVA stuff and it works great.
I need to update the Oracle view (Column UPLD to a ‘Y’ so the records fall out)
I need to update each Oracle record but only at the end of the read loop.

I have tried to update two ways: with and without “ around Licplt which is a field being used in the program.
I did it this way to control each row. There should only be one license plate (from_lic) and I made it match my work field.

Below is the Java error and my code.
So close…

Thanks,

Pete

Display Spooled File
File . . . . . : QPJOBLOG Page/Line 3/21
Control . . . . . W+10 Columns 41 - 118
Find . . . . . .
....+....5....+....6....+....7....+....8....+....9....+....0....+....1....+...
tement . . . . . . . . . : 20
module . . . . . . . . . : JDBCR4
procedure . . . . . . . : JDBC_EXECUPD
tement . . . . . . . . . : 3084
sage . . . . : Java exception received when calling Java method.
se . . . . . : RPG procedure JDBC_EXECU in program PMETSOPULO/JDBCR4
eceived Java exception "java.sql.SQLException: ORA-00904: "licplt": invalid
dentifier " when calling method "executeUpdate" with signature
(Ljava.lang.String;)I" in class "java.sql.Statement". Recovery . . . :
ontact the person responsible for program maintenance to determine the
ause of the problem. Technical description . . . . . . . . : If the
xception indicates that the Java class was not found, ensure the class for
he method is in the class path. If the exception indicates that the Java
ethod was not found, check the method name and signature. If the signature
s not correct, change the RPG prototype for the method, or change the Java
ethod, so that the return type and parameter types match. You can determine
More...
F3=Exit F12=Cancel F19=Left F20=Right F24=More keys


Columns . . . : 6 76 Browse PMETSOPULO/QRPGLESRC
SEU==> JFPWMS08
FMT ** ... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+.
0141.00 rs = JDBC_ExecQry(conn: 'Select ord_no, +
0142.00 from_item, +
0143.00 from_lic, +
0144.00 from_each, +
0145.00 from_exp_dt, +
0146.00 upld +
0147.00 from sonica.v_host_ship_info +
0148.00 order by ord_no, from_item, from_lic');
0149.00
0150.00 dow (jdbc_nextRow(rs));
0151.00 ord_no = jdbc_getCol(rs: 1);
0152.00 from_item = jdbc_getCol(rs: 2);
0153.00 from_lic = jdbc_getCol(rs: 3);
0154.00 from_each = jdbc_getCol(rs: 4);
0155.00 from_exp_dt = jdbc_getCol(rs: 5);
0156.00 upld = jdbc_getCol(rs: 6);
0157.00 eval licplt = from_lic;

0264.00 // Update Oracle on each Read
0265.00
0266.00 count = JDBC_ExecUpd(conn:'update sonica.v_host_ship_info +
0267.00 set upld = "Y" +
0268.00 where from_lic = "licplt"');
0269.00 enddo;




F3=Exit F5=Refresh F9=Retrieve F10=Cursor F11=Toggle F12=Cancel
F16=Repeat find F24=More keys

Go to the top of the page
 
+Quote Post
burleson
post May 6 2012, 04:43 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,613
Joined: 26-January 04
Member No.: 13



Hi Pete,

>> I need to update the Oracle view (Column UPLD to a 'Y' so the records fall out)

Do you mean to say that you need to update a table row using a view?

Not all views can be used for updating.


********************************************
>> ORA-00904

Please read:

http://www.dba-oracle.com/t_ora_00904_stri..._identifier.htm
To test, try ecxecuting this SQL directly from sql*Plus:

CODE
update sonica.v_host_ship_info set upld = "Y" where from_lic = "licplt";


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Pete Mets
post May 7 2012, 09:33 AM
Post #3


Newbie
*

Group: Members
Posts: 3
Joined: 6-May 12
Member No.: 47,174



QUOTE (burleson @ May 6 2012, 05:43 PM) *
Hi Pete,

>> I need to update the Oracle view (Column UPLD to a 'Y' so the records fall out)

Do you mean to say that you need to update a table row using a view?

Not all views can be used for updating.


********************************************
>> ORA-00904

Please read:

http://www.dba-oracle.com/t_ora_00904_stri..._identifier.htm
To test, try ecxecuting this SQL directly from sql*Plus:

CODE
update sonica.v_host_ship_info set upld = "Y" where from_lic = "licplt";



Hi Donald,

thanks for the response.

I recently found out that I may need to call a store procedure before I run any sort of update.
Maybe that is what you meant about not all views being updateable.
Questions to you is the 00904 error.
Is there something I am missing the way Oracle is interpreting my update statement?
I want to have this update a row at a time.
Would I be better off with a different type of statement?

Pete
Go to the top of the page
 
+Quote Post
burleson
post May 7 2012, 01:33 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,613
Joined: 26-January 04
Member No.: 13



Hi Pete,


>> Is there something I am missing the way Oracle is interpreting my update statement?

Yes. It's probably the quote marks. Google for how to do quotes in Java.

>> Row at a time

See here how to do a cursor loop:

http://www.dba-oracle.com/t_pl_sql_cursor.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Pete Mets
post May 7 2012, 09:32 PM
Post #5


Newbie
*

Group: Members
Posts: 3
Joined: 6-May 12
Member No.: 47,174



QUOTE (burleson @ May 7 2012, 02:33 PM) *
Hi Pete,


>> Is there something I am missing the way Oracle is interpreting my update statement?

Yes. It's probably the quote marks. Google for how to do quotes in Java.

>> Row at a time

See here how to do a cursor loop:

http://www.dba-oracle.com/t_pl_sql_cursor.htm


thanks Don,

I'll try that.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd October 2014 - 10:22 AM