Home » SQL & PL/SQL » SQL & PL/SQL » how to print xmlgen result
how to print xmlgen result [message #229917] Tue, 10 April 2007 05:40 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Gurus,

I am just doing R&D.

declare 
out_xml clob;
l_buffer RAW(20000); 
l_text_buffer VARCHAR2(32767); 

begin 
		
select xmlgen.getxml('select * from tab') into out_xml from dual;
		
DBMS_LOB.READ(out_xml,10000, 1 ,l_buffer); 
l_text_buffer := UTL_RAW.CAST_TO_VARCHAR2(l_buffer); 
dbms_output.put_line(l_text_buffer);

end;
I am getting following error

ORA-06550: line 7, column 8:
PL/SQL: ORA-00904: "XMLGEN"."GETXML": invalid identifier
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 25:
PLS-00363: expression '10000' cannot be used as an assignment target
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored

Any one can suggest how can print the xml result ..


--Yash


Re: how to print xmlgen result [message #229924 is a reply to message #229917] Tue, 10 April 2007 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just:
select xmlgen.getxml('select * from tab') from dual;

Without any PL/SQL?
Moreover I think the function you need is "dbms_xmlquery.getxml" not "xmlgen.getxml".
SQL> select dbms_xmlquery.getxml('select * from dual') from dual;
DBMS_XMLQUERY.GETXML('SELECT*FROMDUAL')
-------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <DUMMY>X</DUMMY>
   </ROW>
</ROWSET>

1 row selected.


Regards
Michel
Re: how to print xmlgen result [message #229937 is a reply to message #229924] Tue, 10 April 2007 06:16 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Michel,

i have run all the sql statment which u have suggested ..i am getting fallowing error..


select xmlgen.getxml('select * from tab') from dual;

ORA-03120: two-task conversion routine: integer overflow


select dbms_xmlquery.getxml('select * from dual') from dual


ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1


DBMS_XMLQUERY.GETXML('SELECT*FROMDUAL')

ORA-00900: invalid SQL statement

i am using oracle 8.1.7 version..


--Yash
Re: how to print xmlgen result [message #230832 is a reply to message #229937] Fri, 13 April 2007 01:05 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Michel,


can u suggest some thing why i am getting these errors.


--Yash
Re: how to print xmlgen result [message #230835 is a reply to message #230832] Fri, 13 April 2007 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I need your full Oracle version and your NLS parameters.

Regards
Michel
Re: how to print xmlgen result [message #230838 is a reply to message #230835] Fri, 13 April 2007 01:18 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
my oracle version is -: 8.1.7.0

NLS parameter is as:

NLS_LANGUAGE,AMERICAN
NLS_TERRITORY,AMERICA
NLS_CURRENCY,$
NLS_ISO_CURRENCY,AMERICA
NLS_NUMERIC_CHARACTERS,.,
NLS_CALENDAR,GREGORIAN
NLS_DATE_FORMAT,DD-MON-RR
NLS_DATE_LANGUAGE,AMERICAN
NLS_SORT,BINARY
NLS_TIME_FORMAT,HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT,DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT,HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT,DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY,$
NLS_COMP,BINARY


--Yash
Re: how to print xmlgen result [message #230849 is a reply to message #230838] Fri, 13 April 2007 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry too old version to make test.
Maybe you encountered a bug, maybe not.
It should work.

Only one thing to say: upgrade to a newer version.

Regards
Michel
Re: how to print xmlgen result [message #230862 is a reply to message #230849] Fri, 13 April 2007 02:17 Go to previous message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thanks Michel

i have tested in oracle 9i version it is working fine ...

--Yash
Previous Topic: Environment variables
Next Topic: download a pdf file from long raw
Goto Forum:
  


Current Time: Thu Dec 08 22:19:25 CST 2016

Total time taken to generate the page: 0.25779 seconds