SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Unable to log the Timeout Exception

Arun ThomasAug 19 2015 — edited Aug 19 2015

Hi Friends,

please help me in finding the issue with this code, am setting the timeout for 2 secs. but the code is running for a minute and it's going to "When others" instead of "When UTL_HTTP.transer_timeout" PFB the code.

DECLARE

request UTL_HTTP.REQ;

response UTL_HTTP.RESP;

n NUMBER;

buff VARCHAR2 (4000);

clob_buff CLOB;

BEGIN

UTL_HTTP.SET_RESPONSE_ERROR_CHECK (FALSE);

UTL_HTTP.set_transfer_timeout (2);

request := UTL_HTTP.BEGIN_REQUEST ('www.google.com:81', 'GET');

UTL_HTTP.SET_HEADER (request, 'User-Agent', 'Mozilla/4.0');

response := UTL_HTTP.GET_RESPONSE (request);

DBMS_OUTPUT.PUT_LINE (

'HTTP response status code: ' || response.status_code);

IF response.status_code = 200

THEN

BEGIN

clob_buff := EMPTY_CLOB;

LOOP

UTL_HTTP.READ_TEXT (response, buff, LENGTH (buff));

clob_buff := clob_buff || buff;

END LOOP;

UTL_HTTP.END_RESPONSE (response);

EXCEPTION

WHEN UTL_HTTP.END_OF_BODY

THEN

UTL_HTTP.END_RESPONSE (response);

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE (SQLERRM);

DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

UTL_HTTP.END_RESPONSE (response);

END;

SELECT COUNT (*) + 1 INTO n FROM WWW_DATA;

INSERT INTO WWW_DATA

VALUES (n, clob_buff);

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE ('ERROR');

UTL_HTTP.END_RESPONSE (response);

END IF;

EXCEPTION

WHEN UTL_HTTP.transfer_timeout

THEN

DBMS_OUTPUT.put_line ('Timeout');

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Exception in others :' || SQLERRM);

END;

Hoek

Please remove this part from your code, since it is a bug:

EXCEPTION
WHEN OTHERS THEN
pErrMsg := SQLERRM;
ROLLBACK;
RETURN 0;

"do not be a sheep in wolf's clothing." ;)
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5080528400346352393#tom5211388400346093295

And do not commit in a loop.
Commit only once, when your complete transaction is done.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4951966319022

Edited by: Hoek on Aug 13, 2012 11:47 AM

jurgenk

And where do you assign a value to mTEMP?

Keith Jamieson

Can you not find all the tasks which are not done in one sql statement?
Do you have a common column between task_done and task_list, eg task_id.
If not, you should have.

Assuming you have a column task_id in task_list and task_done:

for cur in
(
SELECT tl.* FROM TASK_LIST  tl
   WHERE ISACTIVE = 1 
   and not exists(select 1 from task_done td where td.task_id = tl.task_id)
)
loop
  -- only deal with tasks  from the select here
  null; 
end loop;

710208

Thanks for reply, but my question is regarding the possibilities of getting 1 task executed twice.
the listed code is not the entire code, but the structure of function only.

710208

yes there is a common column.
but my problem is ,some of the tasks in the loop get executed more than once.
note :this happens mostly when the execution time is too long

thanks for the reply.

710208

Entire code is not posted.

Just the flow of code is given. please assume that all those values are assigned and working fine.
Suppose 5 rows are to be processed in the loop. some task say TASK3 get executed twice even though its occurrence
in the list for loop is only 1.
This happens when execution takes a long duration.

thanks .

Purvesh K

user10442658 wrote:
Dear concerned,

I have one function in a package (databse 11g) with following code.
As listed, looping a table TASK_LIST to execute each task after confirming that it is not
already executed (by querying TASK_DONE table).

Problem I have encountered is , at times same task get executed more than once even if I am counter checking
with variable "mSUCCEED". Please guide me in this regard.

I think this happens because of Duplicate Data in TASK_LIST table. Did you check that? If there are Duplicates, either remove them or use a Distinct keyword. However, you would not get Distinct Data with ' SELECT *', you will have to fetch particular columns to get a Distinct. Moreover, you are just using the TASKNAME in the Loop, so why do a "Select *"? Its a Bad practice.
Using this, you will not have to query the Task_Done table, to ensure whether the Job is processed.

Extract from your Other Reply
but my problem is ,some of the tasks in the loop get executed more than once.
note :this happens mostly when the execution time is too long

Plus, the execution time of the code would not be related to Duplicate Tasks being processed. AFAIK, When the "SELECT * FROM TASK_LIST WHERE ISACTIVE = 1" cursor is processed, any change in the data would not be reflected in the current execution. (I stand corrected, if I am wrong.)

Edited by: Purvesh K on Aug 30, 2012 6:07 PM

unknown-7404

>
And do not commit in a loop.
Commit only once, when your complete transaction is done.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4951966319022
>
Well - make up your mind. Should OP commit only once? Or when complete transaction is done? ;)

This code appears to implement a batch process where each step is independent of the other steps. As such each loop iteration does, in fact, represent a different transaction.

Each iteration of the loop is execting a (potentially different) function. Each function could very well be a 'complete transaction' that uses entirely different schemas, objects, tables, queries, etc.

If so then a commit after every function call IS a commit when the transaction is done. If this procedure is nothing more that a batch job that executes a series of independent steps then it is appropriate to issue a COMMIT after each step.

The AskTom link you cited applies to the traditional 'commit within a transaction' case. That, and similar cases, represent users doing frequent commits because they think they are saving resources or protecting the work already done even though there is still more work to do on those same objects.

Sven W.

user10442658 wrote:
Dear concerned,

I have one function in a package (databse 11g) with following code.

omg. this code is so full of bugs, I don't even know how to comment this garbage.
I assume it is not your code and you just have to maintain it. Poor guy. Is the other one already fired? I do hope so.

Well lets start with the most obvious problems.

1) Remove all COMMIT and ROLLBACK commands from it.
2) Remove the when others exception handler from it.

Tell us the result when running it again.

unknown-7404

>
This happens when execution takes a long duration.
>
As Purvesh already state a single execution of this one procedure cannot execute the same task twice unless that task is present twice in the initial SELECT that defines the cursor.

So another possibility is that the procedure is being called a second time while it is still executing the first time.

This can happen when a DBMS_JOB is scheduled to execute and calls the function or if the function is called a second time by another process or user.

There is nothing in your code that locks the task records being processed so a second function call might very well try to execute the same tasks.
Your cursor is

FOR L IN (SELECT * FROM TASK_LIST WHERE ISACTIVE = 1 )

But it doesn't have a FOR UPDATE clause to lock those records. So another query for tasks "WHERE ISACTIVE = 1" will get the same records.

In fact, I don't see anything in the code you posted that sets ISACTIVE to a value other than '1' but you likely have that somewhere in the procedure.

1 - 10

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 16 2015

Added on Aug 19 2015

#oracle

11 comments

1,685 views

SQL & PL/SQL

Unable to log the Timeout Exception

Comments

Post Details