Stored Procedure hangs (sqlplus not responding) when using cursor

[jwalacha]

Hello,

I have defined the following cursor in my stored procedure:

CURSOR CSrc_Dir IS
SELECT dir_location
FROM InvRelief_Dir_Info
WHERE stock_or_ca = 'REPORTS';

I used the following cursor in my procedure body using the OPEN CURSOR - FETCH INTO variable - CLOSE CURSOR commands.

If the given record exists in table INVRELIEF_DIR_INFO, then the procedure successfully completes execution.

In case a record is not found, the stored procedure seems to hang and never displays the message that it successfully executed. Even the windows task manager will tell me that PL/SQL is not responding.

The open-fetch-close command is used with BEGIN-EXCEPTION-END block, and it doesn't seem to throw the WHEN OTHERS exception.

Has anyone seen a similar situation and has an explanation for this?

Thanks,

Jay.

 

[sem]

I suppose that if you're lucky to find that record, the search is stoped immediately. Otherwise it's continued untill the end of the table and, of course, may consume a lot of resources.

Regards, Dima

 

[jwalacha]

This table has a maximum of 6 records at all times.
So this shouldn't be an issue, I would think.

 

[carp]

Do you have an exiting mechanism inside your loop? And if you do, are you sure it's a condition that will eventually be met? I've seen stuff like:

LOOP
.
.
.
EXIT WHEN upper(my_variable) = 'my_constant';
END LOOP;

and of course this will create an infinite loop (which sounds like what you're encountering).

 

[jwalacha]

This is what I have:

BEGIN
OPEN CSrc_Dir;
FETCH CSrc_Dir INTO src_dir;
CLOSE CSrc_Dir;
EXCEPTION
WHEN OTHERS THEN
-- report error into an error table
END;

What happens is that I get no errors in my error table, and the SQLplus just hangs...

 

[carp]

Nope - nothing tricky going on there!
I don't see any reason it should hang - is this all that is going on in the procedure?

 

[jwalacha]

No, there is more than this going on in the stored procedure.

Although I realized that this was the part I was having a problem since I delete the required record from the Invrelief_dir_info table for debugging.

Another Note:
When I removed the cursor, and used just a SELECT INTO statement within the BEGIN-EXCEPTION-END block, the procedure completed successfully in all cases.

There must be something going on that is not obvious to me.

 

[Bimalaggarwal]

use the following exit condition after the fetch statement:


EXIT WHEN CSrc_Dir%NOTFOUND OR CSrc_Dir%NOTFOUND IS NULL;