select * from v$session

Find the session that is running your stored procedure. If the STATUS column = 'ACTIVE', then it is running a query. If it
is INACTIVE, then the session is not currently running a query.

-John