troubleshooting Question

Error Calling SQL from Unix with dbms_xmlgen.getxml()

Avatar of srikanthradix
srikanthradixFlag for United States of America asked on
Unix OSOracle Database
11 Comments1 Solution2587 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros