Forum Stats

  • 3,836,754 Users
  • 2,262,182 Discussions
  • 7,900,094 Comments

Discussions

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

2»

Answers

  • rjsosi
    rjsosi Member Posts: 245 Bronze Badge

    Ok so I wrote this as an attempt to display called procedure info. You don’t have to understand what my functions and procedures are doing. Just understand that the “SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));” procedure is fed the value ‘/bin/whoami’ and returns the value ‘oracle’. The procedure is as follows:

    set serveroutput on
    DECLARE
      v_stuff   varchar2(100):='/bin/whoami';
      vLine0    VARCHAR2 (3000);
      vLine1    VARCHAR2 (3000);
      vLine2    VARCHAR2 (3000);
      vLine3    VARCHAR2 (3000);
      vLine4    VARCHAR2 (3000);  
      
    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(v_stuff);
      
    Select c.sql_id, c.schemaname, c.osuser, c.action, S.SQL_TEXT into vLine0, vLine1, vLine2, vLine3, VLine4 from gv$session c
    left join v$SQL s 
    on c.SQL_ID = s.SQL_ID
    WHERE c.service_name NOT LIKE '%BACK%' and c.action is not null and rownum < 2;
    
    dbms_output.put_LINE(vLine0||' '||vLine1||' '||vLine2||' '||vLine3||' '||vLine4 );
      
    exception when others
    then
     dbms_application_info.set_action(null);
     raise;
    END;
    


    I was hoping to get the name of the procedure being called: SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));

    as well as the input value of '/bin/whoami/' however here is the output I got:

    oracle
    47796vc447yat SLDBATCH RS818 /bin/whoami SELECT C.SQL_ID, C.SCHEMANAME, C.OSUSER, C.ACTION, S.SQL_TEXT FROM GV$SESSION C LEFT JOIN V$SQL S ON C.SQL_ID = S.SQL_ID WHERE C.SERVICE_NAME NOT LIKE '%BACK%' AND C.ACTION IS NOT NULL AND ROWNUM < 2
     PL/SQL procedure successfully completed.
    

    It shows the return value of oracle. Then it shows the SQL_ID of the 'Select from gv$session view' as well as the schema and my username. Then it shows the value of the parameter.

    Finally it shows the SQL_TEXT of the the same 'Select from gv$session view'. I want it to show the name of the procedure being called: “SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));” as well as the text of the procedure and its SQL_ID along with the parameter value.

    So first of all how can I show the SQL_ID and the name of the procedure I'm calling from that block? Or is it jsut the way I'm querying the gv$session and v$sql views?

    Is there a V$ view or a DBA table that just shows information of procedures and functions. I seem to remember they're stored in a different place than in V$SQL. Is this true?

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    I was hoping to get the name of the procedure being called: SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));

    as well as the input value of '/bin/whoami/' however here is the output I got:

    set serveroutput on
    DECLARE
      v_stuff   varchar2(100):='/bin/whoami';
      vLine0    VARCHAR2 (3000);
      vLine1    VARCHAR2 (3000);
      vLine2    VARCHAR2 (3000);
      vLine3    VARCHAR2 (3000);
      vLine4    VARCHAR2 (3000);  
      
    BEGIN
     SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));
      dbms_output.put_line('SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));');
      dbms_output.put_line('v_stuff='||v_stuff);
    END;
    /
    
  • rjsosi
    rjsosi Member Posts: 245 Bronze Badge

    Thanks USER_H3J7U

    Although, that's more along the lines of code instrumentation. This is more of a brute force method. I was more or less looking for any type of V$ view DBA table that would show me any information on stored procedures sitting in the Library Cache or anywhere in the SGA as I'm not really finding it in the V$SGA view.

  • rjsosi
    rjsosi Member Posts: 245 Bronze Badge

    Hi dvohra21,

    I looked at that table and it looks like the persistently stored parameters are not related to any parameter that is used by the procedure to pass values to and from the procedure itself. When I ran my test code:

    set serveroutput on
    VARIABLE rjs_val NUMBER
    DECLARE
      v_stuff varchar2(100):='/bin/env';
    BEGIN
      SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));
      :ret_val:=3;
      v_stuff := :rjs_val;
      dbms_output.put_line(v_stuff);
    END;
    

    The only parameter values that listed out in your table were parameters for what I'm going to call internal values. In other words parameters not used by the code in the called procedure. It would not show me a parameter called 'param_name' for example. This is true for both the ALL_STORED_SETTINGS as well as the newer ALL_PLSQL_OBJECT_SETTINGS DBA table. If you know of any way I can get the parameter values I'm talking about from these tables please let me know.

    Thanks!