PROCEDURE SQL Server WITH PARAMETERS TO XML ERROR PLS-00049

0

I have a package with a store procedure that receives three parameters and makes a query of a user, that extraction I need to pass it to an xml file, but when I pass the parameters it marks me an error of "bad bind variable", if I put a fixed value, if I throw the xml, someone knows where I have the error?

CREATE OR REPLACE PROCEDURE XX7E_EXT_FAC(P_TRX_NUM  VARCHAR2, P_CUST_NAME VARCHAR2, P_CUST_ID VARCHAR2) IS
  ctx DBMS_XMLGEN.ctxHandle;
  xml CLOB;
BEGIN
  ctx := dbms_xmlgen.newcontext('SELECT CT.CUSTOMER_TRX_ID, CT.TRX_NUMBER, CT.INVOICE_CURRENCY_CODE, HP.PARTY_NAME

FROM RA_CUSTOMER_TRX_ALL CT
INNER JOIN RA_CUSTOMER_TRX_LINES_all CTL_PREV ON CT.CUSTOMER_TRX_ID = CTL_PREV.CUSTOMER_TRX_ID
INNER JOIN HZ_CUST_ACCOUNTS HCA ON HCA.CUST_ACCOUNT_ID=CT.BILL_TO_CUSTOMER_ID

INNER JOIN HZ_PARTIES HP ON HP.PARTY_ID = HCA.PARTY_ID

WHERE CT.TRX_NUMBER =  NVL('''||:P_TRX_NUM||''', CT.TRX_NUMBER) 

AND CT.CUSTOMER_TRX_ID = NVL('||:P_CUST_ID||',CT.CUSTOMER_TRX_ID)');

dbms_xmlgen.setrowtag(ctx, '');
xml := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(substr(xml,1,255));
END;

/

    
asked by Hery Enriquez 15.06.2017 в 00:37
source

0 answers