Home » SQL & PL/SQL » SQL & PL/SQL » Problem Generating XML File
Problem Generating XML File [message #195214] Wed, 27 September 2006 07:37 Go to next message
yraghavendra
Messages: 27
Registered: August 2005
Location: India
Junior Member
Hi,

I wrote the following proc to generate the xml.

PROCEDURE proc_get_qll_xml
(
p_request_id NUMBER,
p_master_id NUMBER,
p_xml OUT CLOB
)

IS

err_mesg VARCHAR2 (1000);
l_ctx dbms_xmlquery.ctxtype;
l_rep_type_code report_type.report_type_code%TYPE;
l_duedate VARCHAR2(50);
l_submitdate VARCHAR2(50);
l_comment report_master.comments%TYPE;
l_des report_request.data_entry_site_code%TYPE;
cur_get_details sys_refcursor;
l_query Varchar2(4000);

BEGIN
OPEN cur_get_details FOR
SELECT report_type.report_type_code,
TO_CHAR (report_master.due_date, 'YYYYMMDD'),
TO_CHAR (report_master.submitted_date, 'YYYYMMDD'),
report_master.comments, report_request.data_entry_site_code
FROM report_type, report_request, report_master
WHERE report_master.report_request_id = p_request_id
AND report_master.report_master_id = p_master_id
AND report_type.report_type_code = report_master.report_type_code
AND report_request.report_request_id =
report_master.report_request_id;
FETCH cur_get_details INTO l_rep_type_code,
l_duedate,
l_submitdate,
l_comment,
l_des;
CLOSE cur_get_details;

l_query := 'select '||l_duedate||','||''''||l_rep_type_code||''''||' from dual';

l_ctx := dbms_xmlquery.newcontext (l_query);
dbms_xmlquery.usenullattributeindicator (l_ctx, TRUE);
p_xml := dbms_xmlquery.getxml (l_ctx);

--Handling Exception
EXCEPTION
WHEN OTHERS
THEN
err_mesg := SUBSTR (SQLERRM, 1, 200);
err_mesg := 'PROC_GET_QLL_XML: '
|| err_mesg
|| '>>'
|| DBMS_UTILITY.format_error_backtrace;
raise_application_error (-20999, err_mesg, TRUE);
END proc_get_qll_xml;


Its compiling but its not generating the xml file.
It is throwing the following exception:

QUERY:
l_query := 'select '||l_duedate||','||''''||l_rep_type_code||''''||' from dual';


EXECPTION:

<?xml version = '1.0'?>
<ERROR>oracle.xml.sql.OracleXMLSQLException: Character ''' is not allowed in an XML tag name.</ERROR>

I tried removing the colons even though its throwing the error saying:

QUERY:
l_query := 'select '||l_duedate||','||l_rep_type_code||' from dual';

ERROR:

<?xml version = '1.0'?>
<ERROR>oracle.xml.sql.OracleXMLSQLException: ORA-00904: "IBPR": invalid identifier
</ERROR>

IBPR is the value fetched for l_rep_type_code.

But Its working fine if i remove the "l_rep_type_code" variable.

Can u tell me what is the problem.

Thanks.


Re: Problem Generating XML File [message #195216 is a reply to message #195214] Wed, 27 September 2006 07:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try replacing your query line with:
l_query := 'select '''||l_duedate||''' col_1,'''||l_rep_type_code||''' col_2 from dual';


It'd help if you'd let us know what the XML format you were looking for was.
Re: Problem Generating XML File [message #195221 is a reply to message #195216] Wed, 27 September 2006 08:03 Go to previous message
yraghavendra
Messages: 27
Registered: August 2005
Location: India
Junior Member
Hi JRowbottom,

Thanks very much..
Its working fine....


regards,
Raghavendra
Previous Topic: case issue
Next Topic: Help to call Oracle Stored Funciton from ASP3.0
Goto Forum:
  


Current Time: Sun Dec 04 14:59:00 CST 2016

Total time taken to generate the page: 0.07878 seconds