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;
/