Ora-19202 Error Occurred In Xml Processing Ora-00904
Contents |
- 4:05 pm UTC Category: Database � Version: 9.0.1 Whilst you are here, check out some content from the extract value from clob xml oracle AskTom team: Edition-Based Redefinition, Part 2 Latest Followup You Asked how to query clob data in oracle Hi Tom I have a table t_request (request_number number, request_detail clob) For example the clob could
How To Read Xml From Clob Column In Oracle
have a value of
How To Retrieve Clob Data In Oracle
but am getting an error sql > select e.request_detail.extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal() from t_request e where request_number = 9348605; select e.request_detail.extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal() * ERROR at line 1: ORA-22806: not an object or REF However the same code works in a PL/SQL block where i convert the request_detail (clob) to sys.xmltype before using the extract function on it. oracle sql extractvalue Please show me how to extract the value out of an xml tag on a clob using a sql query . I have seen various examples of doing the same on xmltype column but they do not work on a clob . Thanks Sangita and we said... Well, you know -- this is alot like storing a DATE in a number, a number in a string. You know -- a bad idea. You'll spend the rest of your life converting -- and if the data in the clob isn't actually XML (well formed enough), you won't find out until you need it (just like when that string that should be a number contains 'hello world') Here is a temporary solution -- long term, use the right type for the data: ops$tkyte@ORA920LAP> create table t ( x clob ); Table created. ops$tkyte@ORA920LAP> ops$tkyte@ORA920LAP> insert into t values ( 2 ' Messages: 22Registered: October 2008 Junior Member I have a reqmt that is little complex. Example: (Real Data) Table1 (Column Name) Height Width Color Table2 ID,Height, Width, Color, Remarks 1,2,3,NULL,NULL 2,NULL,NULL,2,NULL 3,1,2,3,4 What I ora-31011: xml parsing failed require is pick up the data from table1 and for each row in table1, check the corresponding column extract data from xml column in oracle to see if null. If null create a counter and add 1 to it. I would only check for 1 record at a time in table2 (ID is input https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9255019878716 parameter in pl/sql) In other words, if my ID is 1, my count should be 1 (since COLOr is null in table2 for id=1 and color is a row in table1) if my id is 2, count is 2 (ht and width are NULL and are both separate rows in table1) if id is 3, count=0 since all http://www.orafaq.com/forum/t/159606/0/ fields have data even though some of these columsn exists as rows in table1. Report message to a moderator Re: complex sql? [message #464838 is a reply to message #464837] Fri, 09 July 2010 17:30 BlackSwan Messages: 24958Registered: January 2009 Location: SoCal Senior Member It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/ It would be helpful if you provided Test Case - http://www.orafaq.com/wiki/Test_case It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized. Report message to a moderator Re: complex sql? [message #464861 is a reply to message #464837] Sat, 10 July 2010 00:41 Michel Cadot Messages: 63911Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator [quote title=Quote:]Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Please read OraFAQ Forum Guide, especially "How to format your post?" section. Make s here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about http://stackoverflow.com/questions/33168011/unable-to-use-xmltype-in-oracle-pl-sql-web-service Stack Overflow the company Business Learn more about hiring developers or posting ads with https://www.experts-exchange.com/questions/23973939/Error-Calling-SQL-from-Unix-with-dbms-xmlgen-getxml.html us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Unable to use XMLTYPE in Oracle PL/SQL web service up vote 0 down vote in oracle favorite I have been trying to provide my Oracle PL/SQL package as a web service in Oracle 11g (11.2.0.2.0). While everything else is going well, I seem to have a problem in using any procedure/function that uses XMLTYPE over the web service. While I would be very surprised, if XMLTYPE was not supported in Oracle web service, and also because I haven't found any resources that indicated such limitation, I believe clob data in I'm doing something wrong. However, I cannot figure out where the problem would be and therefore the question. I have configured a native web service in my Oracle database according to Native Oracle XML DB Web Services in Oracle 11g Release 1 and I have been successful in testing procedures with basic datatypes using SoapUI. To rule out any other sources for the problem I created the following minimized test package that reproduces the problem: CREATE OR REPLACE PACKAGE web_test_package AUTHID CURRENT_USER AS PROCEDURE xmltype_test (dummy IN varchar2); END; / CREATE OR REPLACE PACKAGE BODY web_test_package AS PROCEDURE xmltype_test (dummy IN varchar2) IS xt XmlType; BEGIN select XMLTYPE(' for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > Error Calling SQL from Unix with dbms_xmlgen.getxml() Want to Advertise Here? Solved Error Calling SQL from Unix with dbms_xmlgen.getxml() Posted on 2008-12-10 Unix OS Oracle Database 1 Verified Solution 11 Comments 1,872 Views Last Modified: 2012-05-05 I have written a function in Unix which executes SQL. Please find the function in code snippet. I copied the sql statement as is from there into TOAD. It is working perfectly fine by returning 7 rows. When i execute this script in Unix, i am getting the following error. Please suggest. ERROR: ORA-19202: Error occurred in XML processing ORA-24333: zero iteration count ORA-06512: at "SYS.DBMS_XMLGEN", line 176 ORA-06512: at line 1 no rows selected ExecuteSQL() { sql_query="" sql_query=$1 if [ "$sql_query" != "" ] then sqlplus -s $dbuser/$dbpass << END_OF_SQL set pages 0; set linesize 285; set long 999999999; set head off; spool $logdir/$fileName; select dbms_xmlgen.getxml( '$sql_query' )xmlrow from dual; spool off; / END_OF_SQL fi } sql="select * from table where to_date(moddate,'DD-MON-YY') in to_date('12-FEB-08','DD-MON-YY')" ExecuteSQL sql 1 > $logdir/temp.xml 2 > $logdir/error.log Select all Open in new window 0 Question by:srikanthradix Facebook Twitter LinkedIn Google LVL 76 Active today Best Solution byarnold On the line after you perform the assignment of sql_query=$1, add echo "$sql_query". When calling the script do you use quotes to enclose the query you are passing to the script? myscript "sql query" Go to Solution 11 Comments LVL 40 Overall: Level 40 Unix OS 27 Oracle Database 6 Message Active today Expert Comment by:omarfarid2008-12-10 how the script file was copied / created on unix system? it could be the file format 0 Message Author Comment by:srikanthradix2008-12-10 I don't exactly understand your question. It is created using vi editor. The script is attached in Code snippet. #!/bin/ksh cat Script1.propertieHow To Extract Data From Clob In Oracle