Forum Stats

  • 3,836,755 Users
  • 2,262,182 Discussions
  • 7,900,095 Comments

Discussions

Categories

How to check if a procedure is currently running with a specific parameter

rjsosi
rjsosi Member Posts: 245 Bronze Badge
edited Feb 2, 2022 4:38PM in SQL & PL/SQL

Hi,

We're using "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0"

We are trying to build a retry process for some input record but I am looking for an ability to check and see if the record is already being processed in Oracle before trying to reprocess the same record.

The process will be calling procedure with a control id. Can I find out in oracle if a specific stored procedure is processing with a specific input parameter at the current time?

So is there a table that stores a list of currently running or recently run procedures with specific parameters.

I know you'd be able to see it in Oracle Dynamic performance views but my knowledge there is not as up to date.

Would anyone with a better knowledge of those tables be able to help out here?

Thanks!

Tagged:
· Share on TwitterShare on Facebook

Best Answer

«1

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    Answer ✓

    Lock the row in target table at beginning of processing: select ... for update nowait/wait.

    rjsosi
    · Share on TwitterShare on Facebook
  • rjsosi
    rjsosi Member Posts: 245 Bronze Badge

    Thanks User_H3J7U, for that!

    So one we've applied that statement I'm assuming we'd be able to tell that record is already being processed by checking if those rows are currently locked or not.

    Would you or anyone have any queries I could use to monitor for this?

    ...Or at lease the V$ views or DBA tables I'd be using?

    Thanks Again!

    · Share on TwitterShare on Facebook
  • dvohra21
    dvohra21 Member Posts: 14,612 Gold Crown
    edited Feb 2, 2022 11:46PM

    If the parameter is persistently stored, Find from the views:

    3.81 ALL_STORED_SETTINGS

    ALL_STORED_SETTINGS provides information about the persistent parameter settings for stored PL/SQL units for which the current user has execute privileges.

    Related Views

    • DBA_STORED_SETTINGS lists information about the persistent parameter settings for stored PL/SQL units for which the current user has execute privileges. It also returns parameter information for all objects in the database and is accessible only to users with the SELECT_CATALOG_ROLE privilege.
    • USER_STORED_SETTINGS lists information about the persistent parameter settings for stored PL/SQL units, but only shows information about PL/SQL units owned by the current user. This view does not display the OWNER column.

    PARAM_NAME

    VARCHAR2(128)

    NOT NULL

    The name of the parameter stored persistently with the PL/SQL unit

    PARAM_VALUE

    VARCHAR2(4000)

     The TO_CHAR() representation of the value of the persistently stored parameter. The width of this column is operating system dependent; however, it is never less than 255.

    https://docs.oracle.com/database/121/REFRN/GUID-65105E97-6D40-43ED-B055-079BA0BDBF69.htm#REFRN20256

    · Share on TwitterShare on Facebook
  • BEDE
    BEDE Oracle Developer Member Posts: 2,453 Gold Trophy
    edited Feb 3, 2022 9:09AM

    For such things I used select for update nowait. So, the first instance of the procedure running locks that row, while the second instance attempting to lock the row will fail and raise an exception. You handle that particular exception by having the procedure pass to processing another row or simply return.

    If you do need to commit without losing the lock you may do it by calling a procedure with pragma autonomous_transaction. But beware how you use autonomous transactions: when called from a procedure that has an opened cursor, the fetches from that opened cursor will not see the changes operated by the autonomous transaction.

    Otherwise, if you are to see the current bind variables for a SQL, look into sys.GV_$SQL_BIND_CAPTURE.

    · Share on TwitterShare on Facebook
  • rjsosi
    rjsosi Member Posts: 245 Bronze Badge

    Hi Bede,

    Does sys.GV_$SQL_BIND_CAPTURE. show variable values from processes that have run?

    In other words if I have an anonymous block like the following:

    set serveroutput on
DECLARE
v_stuff varchar2(100):='/bin/env';
BEGIN
  SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));
END;

    Is there some V$ view I can use to see the values of either 'v_stuff' or the values passed to the SLDPROC.SHELL function or the value passed to the SLDPROC.parse_clob procudure?

    · Share on TwitterShare on Facebook
  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy 
    BEGIN
  dbms_application_info.set_action(substrb('v_stuff='||v_stuff,1,64));
  SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));
  dbms_application_info.set_action(null);
exception when others
  dbms_application_info.set_action(null);
  raise;
END;
    · Share on TwitterShare on Facebook
  • rjsosi
    rjsosi Member Posts: 245 Bronze Badge

    Hi User_H3J7U,


    So when I run your script I just get the end result the parse_clob script returns.


    I'm not getting the value of the variable 'v_stuff'.

    I had to add a declaration for the variable for 'v_stuff' in the script as follows:

    DECLARE
  v_stuff varchar2(100):='/bin/whoami';
BEGIN
 dbms_application_info.set_action(substrb('v_stuff='||v_stuff,1,64));
 SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));
 dbms_application_info.set_action(null);
exception when others
then
 dbms_application_info.set_action(null);
 raise;
END;


    I was hoping to see the value of '/bin/whoami'.

    Instead I'm getting the following output:

    oracle
 PL/SQL procedure successfully completed.

    Where does this procedure store the value of '/bin/whoami' so I can access it?

    · Share on TwitterShare on Facebook
  • dvohra21
    dvohra21 Member Posts: 14,612 Gold Crown

    Does sys.GV_$SQL_BIND_CAPTURE. show variable values

    Answered at https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9535631800346735562

    · Share on TwitterShare on Facebook
  • CarlosDLG
    CarlosDLG Member Posts: 1,378 Gold Trophy
    https://community.oracle.com/tech/developers/discussion/comment/16824869#Comment_16824869

    The information you store by calling DBMS_APPLICATION_INFO.set_action can be read querying the v$session view:

    SELECT sid
    , serial#
    , username
    , osuser
    , module
    , action
    , client_info
FROM v$session
WHERE username = '<your-username>';    --or some other filtering criteria

    But since after executing the parse_clob procedure you are setting the action back to NULL, you would only see it while the procedure is running.

    · Share on TwitterShare on Facebook
  • CarlosDLG
    CarlosDLG Member Posts: 1,378 Gold Trophy

    BTW, if you are going to instrument your code to log/store things like these, you might want to consider using something like Logger.

    · Share on TwitterShare on Facebook