Selecting a XMLType Column in a Package view gives Access Violation

[Roeland]

Summary:
When I write a (package?) procedure that queries all columns using "select * from ..." from a table with more then 24 records AND a XMLType column, it gives me a
"Access violation at address 060A01E9 in module 'orageneric11.dll'. Write of address 00000000"

TEST SETUP:

create table Test_XMLTYPE
(
  id   number not null,
  data xmltype
)
;

Package creation

create or replace package TEST_Xmltype_PCK is

  type t_Test_Xmltype_Cursor is ref cursor return Test_Xmltype%rowtype;

  procedure View_Test_Xmltype (p_Cursor out sys_refcursor);

  procedure View_Test_Xmltype (p_Test_Xmltype_Cursor out t_Test_Xmltype_Cursor);

end TEST_Xmltype_PCK;

create or replace package body TEST_Xmltype_PCK is

  procedure View_Test_Xmltype (p_Cursor out sys_refcursor) is
    mSQL varchar2(1000 char);
  begin
    mSQL := 'select * from Test_Xmltype t';

    open p_Cursor for
      mSQL;
  end View_Test_Xmltype;

  procedure View_Test_Xmltype (p_Test_Xmltype_Cursor out t_Test_Xmltype_Cursor) is
  begin
    open p_Test_Xmltype_Cursor for
      select *
      from Test_Xmltype t;
  end View_Test_Xmltype;

end TEST_Xmltype_PCK;

FINDINGS:
1) If I put less then 25 records in this table, everything is fine.
If the table contains 25 or more records then I get errors.

2) If I transform the query to "select id, Data.getclobval() from..." then everything works. Alas, this gives me a lot more work and it's less future proof.

3) Executing these same queries in a SQL-window inside PL/SQL Developer works flawless, more then 24 records or not.

4) Of course these procedures are being used inside an application (using DOA), where everything works fine as long as the number of records stays under 25. It has cost me a lot of effort to debug this issue, because the table in my debug environment had only 21 records...
So, if you fix these issue in PL/SQL Developer please fix it in DOA also.

I'm sorry that I have to repeat this, but could you please put more effort in XML and XML handling?

Anyway, have some nice holidays, you deserve it,

Roeland

PL/SQL Developer 9.0.4.1644

 

[Marco Kalter]

We'll check it out.