Skip to Main Content
  • Questions
  • print an XMLTYPE Record Type variable


Question and Answer

Connor McDonald

Thanks for the question, shyam.

Asked: February 13, 2019 - 5:22 pm UTC

Last updated: June 07, 2022 - 3:11 am UTC

Version: oracle 12c

Viewed 10K+ times! This question is

You Asked

we have a following definition
TYPE response_t IS RECORD (

A local variable is defined with that record type
lv_resp response_t;

I want to print the value this local variable has stored.I tried dbms_output but dint work.Can you please suggest me an alternative.

and Connor said...

SQL> set serverout on
SQL> declare
  2    x xmltype;
  3  begin
  4    x := xmltype('<xml><name>Connor</name></xml>');
  6    dbms_output.put_line(x.getstringval());
  7  end;
  8  /

PL/SQL procedure successfully completed.

There's a getclobval as well for bigger ones


  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment



Naresh, February 18, 2021 - 11:50 am UTC

The Answer is correct but I have the huge string so I used getclobval() which is not working. Giving error message "PL/SQL: numeric or value error".
Chris Saxon
February 22, 2021 - 4:44 pm UTC

Please could you show us a complete example of what you're doing?

dbms_output.put_line(x.getstringval()); consuming time

Akshat, June 04, 2022 - 5:52 pm UTC


for above member function of XML element,

is there an alternative of this function or any other way around to get the faster printing in string format using dbms_output.put_line.

I am using x.getstringval() in my project and it is consuming lot of time to print the data on the console.

Please help me to improve the performance of the code.

Connor McDonald
June 07, 2022 - 3:11 am UTC

Can you give us a full example of what you're doing with some timings to show us the issue

More to Explore


New to good database design? Check out Chris Saxon's full fundamentals class.