numeric or value error on converting clob to SMLType

[rjv_rnjn]

Hi,
I'm trying to run the following code block

declare
testxml clob;
begin
textxml := '[TABLE]<root><no>1</no></root><root><no>2</no></root>[/TABLE]'

open :csr for
     select tt.no
     from (
          select xmltype.extract (value(a), '/root/no/text()').getstringval() as no
          from table (xmlsequence (sys.xmltype(:testxml).Extract('/table1/root'))) a
           ) tt
           group by tt.no ;
end;

The query as written above throws me an error numeric or value error at sys.xmltype (ORA 06502 & ORA 06512). But if I declare the testxml as varchar2; it works fine. Searched quite a bit but to no avail.

The interesting thing is the same query works fine in Oracle SQL developer. Is there any option that I need to set to get this thing working?

Any ideas please!!
THanks.

 

[Marco Kalter]

I don't think this PL/SQL Block is correct. There is a local testxml variable, a reference to a textxml variable that does not exist, and a :testxml bind variable. Can you review your code and submit an updated example?

 

[rjv_rnjn]

Sorry for this confusion. I was trying the code in both PL/SQL developer and Oracle SQL developer to find out if there is a problem with my code block or some setting required in PL/SQL Developer.
The code I run in PSD is

declare

begin

open :csr for
  select tt.jno
  from (
  select xmltype.extract (value(a), '/root/jno/text()').getstringval() as jno
  from table (xmlsequence (sys.xmltype(:testxml).Extract('/table1/root'))) a) tt
  group by tt.jno
  ;

end;

The variable csr is defined as Cursor and :testxml as CLOB.
The CLOB variable is being assigned the value:

3400060834000126

Its interesting to note that if I change the :testxml type to String, the query returns me the results as expected.

I also noticed that we have got an older version of PSD licensed (6.0.4.906).

 

[Bo Pedersen]

In a test window, you shall use a bind variable with the type Temporary CLOB for CLOB input values.

 

[rjv_rnjn]

Thank you Bo, that was where I was wrong.
I used Temporary Clob and saved the input in a file and it worked (putting the text where "(CLOB)" text comes up is also not the solution, it has to be saved -- just for the benefit of beginners),
Thanks again.