Avatar of srikanthradix
srikanthradix
Flag for United States of America asked on

Error Calling SQL from Unix with dbms_xmlgen.getxml()

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

Open in new window

Unix OSOracle Database

Avatar of undefined
Last Comment
srikanthradix

8/22/2022 - Mon
omarfarid

how the script file was copied / created on unix system? it could be the file format
srikanthradix

ASKER
I don't exactly understand your question. It is created using vi editor. The script is attached in Code snippet.



#!/bin/ksh
 
cat Script1.properties | head -n 1 | tail -n 1| read dirs
echo $dirs | awk -F "__" '{print $1}' | read logdir
 
 
cat Script1.properties | head -n 3 | tail -n 1| read args
echo $args | awk -F "__" '{print $1}' | read orainstance
echo $args | awk -F "__" '{print $2}' | read dbuser
echo $args | awk -F "__" '{print $3}' | read dbpass
echo $args | awk -F "__" '{print $4}' | read fileName
 
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/temp.xml
 
select dbms_xmlgen.getxml( '$sql_query' )xmlrow from dual;
 
spool off;
/
END_OF_SQL
 
fi
 
}
 
echo "Script Execution Started : $(date) on Host: $HOST" >> $logdir/output.log
 
export ORACLE_SID=$orainstance
 
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
 
echo "Script Execution Ended : $(date) on Host: $HOST" >> $logdir/output.log

Open in new window

ASKER CERTIFIED SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
srikanthradix

ASKER
You are right, i am not getting any value there.
When i echoed, i am getting sql as result.
echo $sql_query
Result: sql
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
srikanthradix

ASKER
Oh Shoot! I need to put $sql, but nonetheless, it is giving me only first part

echo $sql_query
Result: select
arnold

enclose the query in single quotes
script_name 'select user from table etc.'
srikanthradix

ASKER
Now, that i have changed my script to include a global variable instead of sending as arguments like the one in code snippet. I am getting the following error.

As you can see i am not able to get those quotes in place like 'DD-MON-YY' instead i am getting without quotes. How do i do that?


Query: select * from tablename where to_date(moddate,DD-MON-YY) in to_date(12-FEB-08,DD-MON-YY)
909470 : map : Permission Denied
909470 : map : Permission Denied
909470 : map : Permission Denied
ERROR:
ORA-19202: Error occurred in XML processing
ORA-00904: "YY": invalid identifier
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1



no rows selected

ERROR:
ORA-19202: Error occurred in XML processing
ORA-00904: "YY": invalid identifier
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1



#!/bin/ksh
 
cat Script1.properties | head -n 1 | tail -n 1| read dirs
echo $dirs | awk -F "__" '{print $1}' | read logdir
 
 
cat Script1.properties | head -n 3 | tail -n 1| read args
echo $args | awk -F "__" '{print $1}' | read orainstance
echo $args | awk -F "__" '{print $2}' | read dbuser
echo $args | awk -F "__" '{print $3}' | read dbpass
echo $args | awk -F "__" '{print $4}' | read fileName
 
sql_query=""
 
ExecuteSQL()
{
if [ "$sql_query" != "" ]
then
 
echo "Query: $sql_query "
 
sqlplus -s $dbuser/$dbpass << END_OF_SQL
 
set pages 0;
set linesize 285;
set long 999999999;
set head off;
spool $logdir/temp.xml
 
select dbms_xmlgen.getxml( '$sql_query' )xmlrow from dual;
 
spool off;
/
END_OF_SQL
 
fi
 
}
 
export ORACLE_SID=$orainstance
 
sql_query='select * from tablename where to_date(moddate,''DD-MON-YY'') in to_date(''12-FEB-08'',''DD-MON-YY'')'
ExecuteSQL

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
srikanthradix

ASKER
Even when i use like this:
sql_query="select * from tablename where to_date(moddate,'DD-MON-YY') in to_date('12-FEB-08','DD-MON-YY')", i am getting the following error.



select dbms_xmlgen.getxml( 'select * from tablename where to_date(modificationdate,'DD-MON-YY') in to_date('12-FEB-08','DD-MON-YY')' )xmlrow from dual
                                                                                        *
ERROR at line 1:
ORA-00907: missing right parenthesis


select dbms_xmlgen.getxml( 'select * from stdratesheets where to_date(modificationdate,'DD-MON-YY') in to_date('12-FEB-08','DD-MON-YY')' )xmlrow from dual
                                                                                        *
ERROR at line 1:
ORA-00907: missing right parenthesis


It seems like dbms_xmlgen.getxml() doesn't take any quotes inside a query. Please suggest.
arnold

You are adding a single quote in your script:
select dbms_xmlgen.getxml( '$sql_query' )xmlrow from dual;

When the $sql_query is expanded, the opening single quote is closed by the opening quote in the query surrounding the to_date clause:
try the following instead:
select dbms_xmlgen.getxml( "$sql_query")xmlrow from dual;
srikanthradix

ASKER
When i put
select dbms_xmlgen.getxml( "$sql_query")xmlrow from dual;
it is giving the following error:

select dbms_xmlgen.getxml( "select * from tablename where to_date(modificationdate,'DD-MON-YY') in to_date('12-FEB-08','DD-MON-YY')" )xmlrow from dual
                           *
ERROR at line 1:
ORA-00972: identifier is too long

if I just put sql_query = "select * from tablename",
select dbms_xmlgen.getxml( "$sql_query")xmlrow from dual;
 it is giving the following error:

select dbms_xmlgen.getxml( "select * from tablename" )xmlrow from dual
                           *
ERROR at line 1:
ORA-00904: "select * from stdratesheets": invalid identifier


All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
srikanthradix

ASKER
Putting two quotes instead of one does the trick. Thanks for your suggestions and helping me.