How to see the actual query executed by an Oracle report containing an error

Question

Like most Oracle Reports in O*Financials, the query is made up of dynamic parts depending on the parameters entered.

When I run the concurrent request the log file contains an obscure error:

ORA-00933: SQL command not properly ended d.acctd_amount_dr, d.amount_dr) ) C_AMOUNT , trx . trx_number C_TRX_NUMBER FROM ar_cash_receipt_history crh , ar_distributions d , gl_code_combinations gc , ar_batches b , ar_cash_receipts cr , hz_cust_accounts cust_acct , h

(I don't know why it puts spaces in between the trx.trx_number which is one of my changes.)

Answer 1

I have no experience with Oracle Reports itself. But what always works to see which sql statements are being sent from a client to an Oracle db, is a sql*net trace. For instructions about how to configure sql*net to create a trace file, please consult the Oracle sql*net documentation or take a look at the OraFAQ.

And please, don't forget to deactivate the tracing feature again after you are done with that sql statement.

Answer 2

Thanks guys.

I copied the query text and concatenated in all the parameters like &LP_ORDER_BY and then displayed it with an SRW_MESSAGE in the BEFORE_REPORT trigger. Bit tedious, they should have some available field that holds the query.