Query Template Fail Occasionally

[former_member708670]

Hello all,

There is a transaction that gets called whenever xml is sent via http post. The transaction calls a query template. That transaction gets called multiple times throughout the day.

The query template takes in xml data and calls an oracle procedure. The oracle procedure inserts data into a table.

This whole procedure is successful 99% of the time. But occasionally like once or twice a day, it’ll fail. I know the failing isn’t due to the data, cause I can reprocess the same xml data and it’ll work.

I get the error message “Error processing command for template ....". I also get the message like "SQL error has occurred on query No more data to read from socket". Does anyone know what may cause a query to work 99% of the time and fail occasionally 1% of the time? I can confirm this is happening while our database is up.

Thanks,

Zack

[agentry_src]

Perhaps there is some table/record locking occurring? Can you provide a copy of the stored procedure?

[former_member708670]

Hey Mike,

Thanks for the response. Unfortunately, I can't share my code. I can explain what the stored procedure does. I don't think this is a table/record locking issue by the way. Also, this query template is returning the error sometimes at like 4:00am (It can happen anytime of the day).

Here's what the stored procedure does.

procedure stored_procedure(xmlData_clob)

declares/instantiate an XML parser

pull XML fields from xmlData_clob using XML parser

insert statement to insert the data pulled from the XML into a table

frees objects used to parse the XML

commit

EXCEPTION:

rollback

frees objects used to parse the XML

[agentry_src]

Only thing I can suggest is to parse the xml separately in MII to see if that generates an error. Without seeing the SP code, not much I can help you with.