1

I am calling a stored procedure that is returning the rowid for a list of rows. I am having trouble with storing the actual rowid value in Java so I can then later use that rowid for a follow up update statement.

For example :

Select name, age, ROWID, from myTable where rownum<20;

I can store the name and age, but when I try to store ROWID in a string like:

    setRowID(workTableResponseJson.getString("ROWID"));

public void setRowID(String rowid) {
    this.rowid = rowid;
}

The value does not exactly match. I instead get a value like :oracle.sql.ROWID@123a123b

Does anyone know how to properly store the rowid value in Java so you can then later use it in a follow up statement?

Thank you.

EDIT: APC answered the question below and I have marked it. Thank you.

The issue was with the query itself. I needed to change:

select rowid

to instead be:

select ROWIDTOCHAR(ROWID)

java was then able to store that value in a string. Although, I did not need to do the second modification as requested as it currently works when I still use:

update name='bob' where rowid=:rowid;
Stoutg
  • 13
  • 4
  • 3
    You might want to consider whether storing the rowid is a good idea as is well explained here: https://asktom.oracle.com/pls/asktom/asktom.search?tag=is-it-safe-to-use-rowid-to-locate-a-row – karen Aug 09 '18 at 16:25

1 Answers1

1

A ROWID might look like a string but its datatype is not a string. The Oracle oracle.sql.ROWID class has a toString() method, so you should use that in Java.

But it looks like you're working with JSON. So this might not be the right solution for you. An alternative approach would be to cast the ROWID to a string in the SQL statement: 

Select t.name, t.age, ROWIDTOCHAR(t.rowid) as Row_id
from myTable t
where rownum<20;

This would give you a string you can use as a Java argument:

setRowID(workTableResponseJson.getString("Row_id"));

If you subsequently need to use the ROWID to identify a row you'll need to cast it back:

update myTable t
set t.age = 42
where t.rowid = CHARTOROWID(:row_id)

Warning: I haven't got the set-up to test all of this, so there may be syntax errors. And, obviously, I'm not sure why you're storing ROWIDs in the first place, so maybe this whole approach won't solve your problem.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Using ojdb6.jar `toString()` returns the default `Object.toString()` output. Using `ROWID` `.stringValue()` gives a result which matches the Oracle function `ROWIDTOCHAR` – lafual Oct 20 '20 at 05:25