Error Calling SQL from Unix with dbms_xmlgen.getxml()

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

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

Select allOpen in new window

You are right, i am not getting any value there.
When i echoed, i am getting sql as result.
echo $sql_query
Result: sql

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

echo $sql_query
Result: select

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

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

Select allOpen in new window

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.

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;

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

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