Home » SQL & PL/SQL » SQL & PL/SQL » ROWID differences (Oracle XE)
ROWID differences [message #401625] Tue, 05 May 2009 18:54 Go to next message
wmbutler
Messages: 3
Registered: May 2009
Location: Nashville, TN
Junior Member

All,

I have a few queries where I like to use the oracle unique identifier, rowid, for identifying a particular record. In the past, I used coldfusion with a native oracle driver to perform the query. It would display the rowid as a long text string.

select rowid from customer where customer_id = 1;

ROWID
------------------
AAADinAAEAAAAYGAAA

In the past, I could then perform a select on that rowid to pull up the associated data.

Well, I migrated the oracle database to XE from 8.1.7 and also switched to railo (a coldfusion open source version). I also ended up using the oracle java thin client instead of the native oracle driver (which I suspect is the culprit).

Now, when I perform the query from within railo (via the OJDBC driver), the returned value of rowid is:

select rowid from customer where customer_id = 1;

oracle.sql.ROWID@31bcc8 instead of AAADinAAEAAAAYGAAA

If I use sqlplus from the unix prompt, I still get the nice familiar rowid from before: AAADinAAEAAAAYGAAA

My question is this: Is there a way to get my old familiar rowid representation. I'm happy to either convert the oracle.sql.ROWID@31bcc8 representation or figure out a different keyword to use in my query.
Re: ROWID differences [message #401626 is a reply to message #401625] Tue, 05 May 2009 19:14 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>If I use sqlplus from the unix prompt, I still get the nice familiar rowid from before: AAADinAAEAAAAYGAAA
If you stipulate the statement above is true, then the problem is not Oracle RDBMS.
From my perspective, the "problem" is within railo (the OJDBC driver).

I am not sure anyone here can solve this for you.
Rhetorically speaking, might it just be a data presentation issue?

I am convinced that the desired character string is being extracted from the database.
Exactly how or why you are seeing what you report, I can not explain.

Is the part to the right of "@" change & is unique depending upon the actual row returned?

Good Luck!
Re: ROWID differences [message #401628 is a reply to message #401626] Tue, 05 May 2009 19:31 Go to previous messageGo to next message
wmbutler
Messages: 3
Registered: May 2009
Location: Nashville, TN
Junior Member

Yes, the part to the right of the '@' is unique, but when I try to reference via a select statement either "oracle.sql.ROWID@127d15e" or just "127d15e", I can't get it to locate a row.

I sort of doubt it's railo causing the problem, but rather the jdbc thin client. When I search for "oracle.sql.ROWID" in google, I get all sorts of interesting references for how this 'value' can be manipulated in java but nobody really talks about how to get the original rowid.

If nobody here knows the answer, I'll try to install the oracle native driver on Linux and reference it instead, but I'd rather not go that route if I can possibly avoid it.

Re: ROWID differences [message #401629 is a reply to message #401625] Tue, 05 May 2009 19:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_rowid.htm#ARPLS053

Can't hurt & just might help.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i46148

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref202
Re: ROWID differences [message #401658 is a reply to message #401628] Wed, 06 May 2009 00:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
wmbutler wrote on Wed, 06 May 2009 02:31
Yes, the part to the right of the '@' is unique, but when I try to reference via a select statement either "oracle.sql.ROWID@127d15e" or just "127d15e", I can't get it to locate a row.


I don't know railo, but in java this is the way an object is denoted. I think railo, or whatever thing is displaying this, has a rowid datatype that has no toString() which would display it contents neatly
Re: ROWID differences [message #401686 is a reply to message #401658] Wed, 06 May 2009 03:07 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Like Frank even I have no knowlede on Railo. Can you check if you convert rowid to char in Railo to see if it then displays the rowid as varchar field?

select rowidtochar(rowid) customer where customer_id = 1 


Re: ROWID differences [message #401693 is a reply to message #401686] Wed, 06 May 2009 03:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Taking a step back, I think the problem lies in this quote:
Quote:
I have a few queries where I like to use the oracle unique identifier, rowid, for identifying a particular record.


WHY?

You've got a unique id on your table - customer_id
Just use that?
Why double the amount of SQL that you perform?
Re: ROWID differences [message #401755 is a reply to message #401693] Wed, 06 May 2009 06:22 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Also, what happens if the row migrates between the first and second queries?
(Not exactly a high statistical chance I grant you, but...)

[Updated on: Wed, 06 May 2009 06:22]

Report message to a moderator

Re: ROWID differences [message #401816 is a reply to message #401693] Wed, 06 May 2009 09:02 Go to previous message
wmbutler
Messages: 3
Registered: May 2009
Location: Nashville, TN
Junior Member

JRowbottom wrote on Wed, 06 May 2009 03:24
Taking a step back, I think the problem lies in this quote:
Quote:
I have a few queries where I like to use the oracle unique identifier, rowid, for identifying a particular record.


WHY?

You've got a unique id on your table - customer_id
Just use that?
Why double the amount of SQL that you perform?



Because I don't have a unique id on the table for the specific row. Thought I'd spare you guys the long winded explanation, but if you want it, I'll surely give it to you. Suffice it to say that customer_id is not a unique key, as a matter of fact, customer_id is not even the real world column name. I was just using it as an example to explain the problem.

Thanks to others for your suggestions. I actually took the time to switch to the native oracle driver (oci) today. Railo still grabs the rowid value as the oracle.sql.rowid@98448, so I think I need to contact the railo developers at this point.
Previous Topic: Employee database programme
Next Topic: how to check whenever package got modified
Goto Forum:
  


Current Time: Fri Dec 09 05:53:06 CST 2016

Total time taken to generate the page: 0.09424 seconds