dbms_xmlgen.getxml showing null output in Oracle

Question

When I run the dbms_xmlgen.getxml funtion in SQL query separately it gives me xml output but when I run it inside procedure, it gives me the null output.
Could you please let me know where the error lies?

Below is the code that I run separately without using PL/SQL

select dbmX_xmlgen.getxml('select 
wmgs.SRC_NUM,
SRC_ID,
HS.X_START_DATE,
pd_INT_ID,
pd.X_R_RATE_KEY,
COND.DESC_TEXT,
pd.NAME,
Relatedpd.NAME ,
Relatedpd.PART_NUM,
pd.EFF_START_DATE,
pd.TYPE,
PAY.NAME ,
PAY.TYPE
from 
alpha.X_SRC wmgs,
alpha.X_pd_INT_SRC HS,
alpha.X_pd_INT pd,
alpha.X_CONDITION COND,
alpha.X_pd_INT Relatedpd,
alpha.X_pd_INT_XM Pay
where
wmgs.ROW_ID = HS.SRC_ID (+)
AND HS.pd_INT_ID = pd.ROW_ID (+)
AND HS.AMT_CURCY_CD = COND.ROW_ID (+)
AND pd.X_REL_pd_ID = Relatedpd.CFG_MODEL_ID (+)
AND pd.ROW_ID=PAY.PAR_ROW_ID (+)
AND wmgs.SRC_NUM=''CAQZ''
AND (HS.X_START_DATE >= to_date(''01/01/2000'',''dd/mm/yyyy'') OR pd.EFF_START_DATE >= to_date(''01/01/2010'',''dd/mm/yyyy'')) 
AND (HS.AMT_DATE <= to_date(''01/01/2099'',''dd/mm/yyyy'') OR pd.EFF_END_DATE<= to_date(''01/01/2099'',''dd/mm/yyyy''))
')xml from dual;

Below is the code that I run through procedure

create or replace procedure alpha.test1(p_start_date IN DATE, p_end_date IN DATE, p_src_num IN VARCHAR2, p_xml_out OUT CLOB) 

IS
v_xml CLOB;
ctx dbms_xmlgen.ctxhandle;


BEGIN

ctx := dbms_xmlgen.newcontext
('select 
wmgs.SRC_NUM,
SRC_ID,
HS.X_START_DATE,
pd_INT_ID,
pd.X_R_RATE_KEY,
COND.DESC_TEXT,
pd.NAME,
Relatedpd.NAME ,
Relatedpd.PART_NUM,
pd.EFF_START_DATE,
pd.TYPE,
PAY.NAME ,
PAY.TYPE
from 
alpha.X_SRC wmgs,
alpha.X_pd_INT_SRC HS,
alpha.X_pd_INT pd,
alpha.X_CONDITION COND,
alpha.X_pd_INT Relatedpd,
alpha.X_pd_INT_XM Pay
where
wmgs.ROW_ID = HS.SRC_ID (+)
AND HS.pd_INT_ID = pd.ROW_ID (+)
AND HS.AMT_CURCY_CD = COND.ROW_ID (+)
AND pd.X_REL_pd_ID = Relatedpd.CFG_MODEL_ID (+)
AND pd.ROW_ID=PAY.PAR_ROW_ID (+)
AND wmgs.SRC_NUM='''|| p_src_num ||'''
AND (HS.X_START_DATE >= to_date('''||p_start_date||''',''dd/mm/yyyy'') OR PROD.EFF_START_DT >= to_date('''||p_start_date||''',''dd/mm/yyyy'')) 
AND (HS.AMT_DATE <= to_date('''||p_end_date||''',''dd/mm/yyyy'') OR PROD.EFF_END_DT<= to_date('''||p_end_date||''',''dd/mm/yyyy''))
');

dbms_xmlgen.SETNULLHANDLING (ctx, 2);

v_xml := dbms_xmlgen.getxml(ctx);

p_xml_out := v_xml;

END;
/

What is the `NLS_DATE_FORMAT` setting in the session you call the procedure from? (Might also be helpful to show the actual call you make, including the parameter values so we can see the dates you are passing in.) — Alex Poole, Apr 17 '19 at 14:11
If you are passing in p_start_date and p_end_date as DATE type, why are you wrapping them in to_date? Also using q' syntax will simplify your string construction since you will not need to us '' for '. — Stephen Caggiano, Apr 17 '19 at 16:55
@AlexPoole - NLS date format setting is dd/mm/yyyy hh24:mi:ss — Nik, Apr 19 '19 at 08:28
@StephenCaggiano - I tried removing to_date from inside the proc Below is the error ```ORA-19202: Error occurred in XML processing ORA-00933: SQL command not properly ended ORA-06512: at "SYS.DBMS_XMLGEN", line 7 ORA-06512: at "SYS.DBMS_XMLGEN", line 147``` — Nik, Apr 19 '19 at 08:41
also it gives error on line v_xml := dbms_xmlgen.getxml(ctx); — Nik, Apr 19 '19 at 08:42
You tried this? AND (HS.X_START_DATE >= p_start_date OR PROD.EFF_START_DT >= p_start_date) AND (HS.AMT_DATE <= p_end_date OR PROD.EFF_END_DT<= p_end_date) — Stephen Caggiano, Apr 22 '19 at 17:36

score 0 · Answer 1 · answered Apr 17 '19 at 15:30

i am not sure if this is what you are looking for, but you could use the internal xml functions of pl sql

then it would look like this:

    DECLARE
    vxml   CLOB;
BEGIN
SELECT XMLELEMENT ( "ROOT",
                          xmlagg ( xmlelement( "data",
                                                XMLELEMENT(
                                                    "SRC_NUM", wmgs.src_num
                                                ) , XMLELEMENT(
                                                    "SRC_ID", SRC_ID
                                                ), XMLELEMENT(
                                                    "X_START_DATE", HS.X_START_DATE
                                                ), XMLELEMENT(
                                                    "pd_INT_ID", pd.pd_INT_ID
                                                ), XMLELEMENT(
                                                    "X_R_RATE_KEY", pd.X_R_RATE_KEY
                                                )
 )))
into vxml
from alpha.x_src wmgs,
alpha.x_pd_int_src hs,
alpha.x_pd_int pd,
alpha.x_condition cond,
alpha.x_pd_int relatedpd,
alpha.x_pd_int_xm pay; 

END;

When I am using where clause, it gives me some ) error. I have checked and none of the braces are missing. — Nik, Apr 19 '19 at 08:44

dbms_xmlgen.getxml showing null output in Oracle

1 Answers1