Home » SQL & PL/SQL » SQL & PL/SQL » ORA_ROWSCN NUll for first row (10 g )
ORA_ROWSCN NUll for first row [message #423829] Mon, 28 September 2009 09:25 Go to next message
madhuottapalam
Messages: 10
Registered: September 2009
Junior Member
We got an issue when we have SCN with CONNECT BY clause. The issue is, the first row in the recordset will not return ORA_ROWSCN. Ora_rowscn will be null for first row. We
have a workaround but not sure if this is optimized way

SELECT id, ORA_ROW_SCN
FROM (SELECT j.*,ORA_ROWSCN as ORA_ROW_SCN from table1 j) n
START WITH id = 5557
CONNECT BY PRIOR parent_id = id;

ID ORA_ROW_SCN
5557
6 185772539
2 185772539
1 185772539

Same query with workaround

SELECT oscn. id, oscn.ORA_ROW_SCN
FROM
( SELECT id,level lvl
FROM table1
START WITH d = 5557
CONNECT BY PRIOR parent_id = id

)temp
INNER JOIN
(SELECT N.*,N.ora_rowscn as ORA_ROW_SCN FROM table1 N) oscn
ON temp. ID=oscn.ID;

ID ORA_ROW_SCN
5557 185772539
6 185772539
2 185772539
1 185772539

Does any have noticed it. We have opened a TAR but i thought to check if anyone out here have a solution for this

thanks

Madhu
Re: ORA_ROWSCN NUll for first row [message #423836 is a reply to message #423829] Mon, 28 September 2009 09:53 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hi,

It will always be beneficial if you could post the following details like

a) OS Flavour and Version
b) Database version

For how to do it please check the sticky note (Very first post) in this forum.

You could be hitting this Bug 6075522

for more information about this bug logon to metalink.

Hope this helps.

Regards

Raj
Re: ORA_ROWSCN NUll for first row [message #423837 is a reply to message #423836] Mon, 28 September 2009 09:58 Go to previous message
madhuottapalam
Messages: 10
Registered: September 2009
Junior Member
Sorry for that.. Missed the very basic thing...

OS : Windows 2003 Enterprise Edition SP2
Oracle : Enterprise Edition . 10.2.0.4.0

We have opened a TAR but i thought to ask here also.

thanks

Madhu

[Updated on: Mon, 28 September 2009 10:03]

Report message to a moderator

Previous Topic: P-Code in Oracle
Next Topic: Convert 1/1/1900 to NULL
Goto Forum:
  


Current Time: Tue Dec 06 00:21:50 CST 2016

Total time taken to generate the page: 0.09789 seconds