I've got the follwing piece of code which takes almost 3 seconds to run when I execute it on the server itself using sqlplus.
DECLARE
p_xml_in LONG(32760);
p_xml_out LONG(32760);
BEGIN
p_xml_in := '<ITEM><USER_ID>D3846</USER_ID><GUTO_ID></GUTO_ID><ACTION>GI
VEUP</ACTION><ACTION_DATE>28/10/2003</ACTION_DATE><MEDIA_TYPE>13</MEDIA_TYPE><AG
ENT_ID>A001</AGENT_ID><VERIFIER_ID></VERIFIER_ID><CAMPAIGN_IMPACT></CAMPAIGN_IMP
ACT><CAMPAIGN_CODE></CAMPAIGN_CODE><SALES_CHANNEL>1</SALES_CHANNEL><NEW_OCCUPANT
><ICE_CUSTOMER_ID></ICE_CUSTOMER_ID><BUSINESS_FLAG>N</BUSINESS_FLAG><TAKEON_DATE
>29/10/2003</TAKEON_DATE><SALUTATION></SALUTATION><FIRST_NAME></FIRST_NAME><INIT
IALS></INITIALS><LAST_NAME></LAST_NAME><FULL_NAME>New Customer</FULL_NAME></NEW_
OCCUPANT><CUSTOMER><ICE_CUSTOMER_ID>383700</ICE_CUSTOMER_ID><PREMISE_ID>4115853<
/PREMISE_ID><BUSINESS_FLAG>N</BUSINESS_FLAG><NO_NEW_CONTACT_REASON>99</NO_NEW_CO
NTACT_REASON><RESP_NEW_ADDR>N</RESP_NEW_ADDR><CALLBACK_DATE></CALLBACK_DATE><CAL
LBACK_COMMENTS></CALLBACK_COMMENTS><ACCOUNT><PREMISE_ID>4115853</PREMISE_ID><FIN
AL_BILL_TO_OFFICE>Y</FINAL_BILL_TO_OFFICE><CORRECT_ADDRESS>Y</CORRECT_ADDRESS><N
AME>W Smith</NAME><CUST_NOT_FLAG>Y</CUST_NOT_FLAG><BUSINESS_FLAG>N</BUSINESS_FLA
G><REFERENCE>952117400012</REFERENCE><PRODUCT><PRODUCT_CODE>E</PRODUCT_CODE><BUS
INESS_FLAG>N</BUSINESS_FLAG><REFERENCE>952117400012</REFERENCE><KEY_TYPE>1</KEY_
TYPE><SERVICE_REFERENCE>576931001022</SERVICE_REFERENCE><REASON></REASON><GET_RE
ADING>N</GET_READING><CONTINUE_SUPPLY>N</CONTINUE_SUPPLY><ESTIMATE_BILL>N</ESTIM
ATE_BILL><RAISE_FINAL_INVOICE>Y</RAISE_FINAL_INVOICE><PAYMENT_CARD_REQD>N</PAYME
NT_CARD_REQD><PAYMENT_VALUE></PAYMENT_VALUE><SYSTEM_CODE>J</SYSTEM_CODE></PRODUC
T></ACCOUNT></CUSTOMER></ITEM>';
p_xml_in LONG(32760);
p_xml_out LONG(32760);
BEGIN
p_xml_in := '<ITEM><USER_ID>D3846</USER_ID><GUTO_ID></GUTO_ID><ACTION>GI
VEUP</ACTION><ACTION_DATE>28/10/2003</ACTION_DATE><MEDIA_TYPE>13</MEDIA_TYPE><AG
ENT_ID>A001</AGENT_ID><VERIFIER_ID></VERIFIER_ID><CAMPAIGN_IMPACT></CAMPAIGN_IMP
ACT><CAMPAIGN_CODE></CAMPAIGN_CODE><SALES_CHANNEL>1</SALES_CHANNEL><NEW_OCCUPANT
><ICE_CUSTOMER_ID></ICE_CUSTOMER_ID><BUSINESS_FLAG>N</BUSINESS_FLAG><TAKEON_DATE
>29/10/2003</TAKEON_DATE><SALUTATION></SALUTATION><FIRST_NAME></FIRST_NAME><INIT
IALS></INITIALS><LAST_NAME></LAST_NAME><FULL_NAME>New Customer</FULL_NAME></NEW_
OCCUPANT><CUSTOMER><ICE_CUSTOMER_ID>383700</ICE_CUSTOMER_ID><PREMISE_ID>4115853<
/PREMISE_ID><BUSINESS_FLAG>N</BUSINESS_FLAG><NO_NEW_CONTACT_REASON>99</NO_NEW_CO
NTACT_REASON><RESP_NEW_ADDR>N</RESP_NEW_ADDR><CALLBACK_DATE></CALLBACK_DATE><CAL
LBACK_COMMENTS></CALLBACK_COMMENTS><ACCOUNT><PREMISE_ID>4115853</PREMISE_ID><FIN
AL_BILL_TO_OFFICE>Y</FINAL_BILL_TO_OFFICE><CORRECT_ADDRESS>Y</CORRECT_ADDRESS><N
AME>W Smith</NAME><CUST_NOT_FLAG>Y</CUST_NOT_FLAG><BUSINESS_FLAG>N</BUSINESS_FLA
G><REFERENCE>952117400012</REFERENCE><PRODUCT><PRODUCT_CODE>E</PRODUCT_CODE><BUS
INESS_FLAG>N</BUSINESS_FLAG><REFERENCE>952117400012</REFERENCE><KEY_TYPE>1</KEY_
TYPE><SERVICE_REFERENCE>576931001022</SERVICE_REFERENCE><REASON></REASON><GET_RE
ADING>N</GET_READING><CONTINUE_SUPPLY>N</CONTINUE_SUPPLY><ESTIMATE_BILL>N</ESTIM
ATE_BILL><RAISE_FINAL_INVOICE>Y</RAISE_FINAL_INVOICE><PAYMENT_CARD_REQD>N</PAYME
NT_CARD_REQD><PAYMENT_VALUE></PAYMENT_VALUE><SYSTEM_CODE>J</SYSTEM_CODE></PRODUC
T></ACCOUNT></CUSTOMER></ITEM>';
pkg_ice_guto.sp_perform_guto(p_xml_in, p_xml_out);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1
, 255));
RAISE;
END;
/
WHEN OTHERS THEN
dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1
, 255));
RAISE;
END;
/
The same query when I run it via a client/server connection takes fore ever to run, it's been almost 2hrs and it's still running....
I wonder what I could do to get to the bottom of this query taking a very long time to execute using a client/server connection.
No errors in the alert.log so far.
Oracle 9.2.0.4
HP-UX11
Connection is TCP/IP.
TIA
Zabair
Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger
