XMLElement sometimes empty in SQL Window

[Roeland]

Hi,

I have a query of the form

select XMLELEMENT("Path", XMLAttributes(t.Id as "Id",
                                          t.Keyword_Id as "KeywordId")) xmldoc
  from Table1 t

The query runs fine in a SQL Window, but when I look at the values , I sometimes notice that the value from the 7th row is empty in the large data editor. That surprised me, so I ran the same query again in the command window. Now I can see that all the values are indeed correctly filled.

It's even more strange: When I add an extra column, sometimes everything seems to work fine. By removing this column, I get this issue again.

And what is also strange, it's always the 7th row that have this issue, even when I limit the results?!?

This seems to me as a bug.

Could you explain this behavior? And plz fix it?

Version 8.0.4.1514

Roeland

 

[Marco Kalter]

Can you go to Help > Support Info, press the 'Copy all pages' button on this info screen, and paste this text into an e-mail reply?

 

[Roeland]

Email send

 

[Roeland]

Any news on this issue?

 

[Marco Kalter]

Not yet.

 

[Roeland]

I have investigated this issue a bit more and here are my weird conclusions:

1) This works

select XMLELEMENT("Path", just_a_column) xmldoc
  from
   (SELECT LEVEL just_a_column
    FROM   dual
    CONNECT BY LEVEL <= 1500) t

This don't (notice that i only added 1 column):

select XMLELEMENT("Path", just_a_column) xmldoc,
         just_a_column
  from
   (SELECT LEVEL just_a_column
    FROM   dual
    CONNECT BY LEVEL <= 1500) t

But when I add some more columns, it sudden starts to give the correct result again

select XMLELEMENT("Path", just_a_column) xmldoc,
         just_a_column,
         just_a_column,
         just_a_column
  from
   (SELECT LEVEL just_a_column
    FROM   dual
    CONNECT BY LEVEL <= 1500) t

2) Problem:

The following clob's are empty (and what a strange pattern!):

7
37,
107,
137,
207,
237,
307,
337,
407,
437,
...,
N07,
N37,
...

How could someone code such a strange bug?

Conclusion:
1) This happens only in the SQL Grid
2) This is a very annoying bug. It's not obvious, depends on the number of columns, and the rownumber in the grid (and this being N07 and N37).
3) This should be easily repeatable.

Could it be solved?

PL/SQL Developer 8.0.4.1514
Oracle 10.2.0.4
OraClient 11.2.0 (but same issue on the 11.1.0)

Thanks,

Roeland

 

[Marco Kalter]

Thanks. As soon as we have a solution I will let you know here.

 

[Roeland]

This bug still isn't fixed in V9 beta1

Please fix it ASAP!

This is giving wrong results. Something we simply cannot accept!

Roeland

Version 9.0.0.1591 Beta
Oracle 10.2.0.4
OraClient 11.2.0
Windows 7 32 bit

 

[Roeland]

Could you fix this bug please?
The examples given above show this strange error consistently.

Adding or removing columns could sometimes help.
Adding an extra "Select * from (" wrapper helps also.

But these are just workarounds.

Roeland

PL/SQL Dev v9.0.0.1596 (Beta 6)

 

[Roeland]

Still not fixed in PL/SQL Dev 9.0.0.1598 (Beta 8)

 

[Roeland]

Still not fixed in
PL/SQL Dev 9.0.0.1599 (Release Candidate).

Marco, are you going to fix this issue for v9?

This gives the issue

select XMLELEMENT("Path", just_a_column) xmldoc
from
 (SELECT LEVEL just_a_column
  FROM   dual
  CONNECT BY LEVEL <= 1500) t

Note that if you add a ".getClobval()" or ".getstringVal()" this works correctly.

select XMLELEMENT("Path", just_a_column).getstringVal() xmldoc
from
 (SELECT LEVEL just_a_column
  FROM   dual
  CONNECT BY LEVEL <= 1500) t

Regards,

Roeland

 

[Marco Kalter]

We have not yet been able to reproduce this.

 

[Roeland]

Marco,

This seems to be fixed in PL/SQL Dev 9.0.0.1601

Thanks,

Roeland

 

[Roeland]

Sorry Marco,

This is still not fixed.

In a SQL Window:

select just_a_column, XMLELEMENT("Path", just_a_column) xmldoc
from
 (SELECT LEVEL just_a_column
  FROM   dual
  CONNECT BY LEVEL <= 150) t

When I open records 7, 37, 107, 137, ... they are empty.

When I do this query in a Command Window or sqlplus, all info is shown

Roeland

PS: Support Info send by email

 

[Marco Kalter]

Thanks, we'll check it out.

 

[Roeland]

This issue is still not fixed in Version 10.0.0.1685 Beta 5...

I'm still baffled by the strange behavior of this error.

 

[Roeland]

I want to celebrate the 3th anniversary of this bug report without a fix!

Cheers!

Version 10.0.5.1710

 

[T-Gergely]

This bug is caused by incorrect use of the OCI NULL indicators based on OCIDefineByPos instead of OCIDefineObject.

indp (IN)

pointer to an indicator variable or array. For scalar data types, pointer to sb2 or an array of sb2s. Ignored for SQLT_NTY defines. For SQLT_NTY defines, a pointer to a named data type indicator structure or an array of named data type indicator structures is associated by a subsequent OCIDefineObject() call.

 

[Roeland]

@T-Gergely: Thanks for investigating this!

 

[Roeland]

Still not fixed in 11.0.0.1750 Beta