Automic Workload Automation

 View Only
  • 1.  How can I reset Oracle session connection?

    Posted Jul 10, 2020 01:40 PM
    Good day.   I have some workflows containing jobs that call Oracle stored procedures, As a simple example, Workflow_A contains JOBS.1 and JOBS.2 - both executing Oracle stored procedures.   Is it possible in Automic Workflow Automation to force the Oracle session to end after JOBS.1 end so that when JOBS.2 executes Oracle will create a new Oracle session?   If yes, how?

    ------------------------------
    Newbie
    ------------------------------


  • 2.  RE: How can I reset Oracle session connection?

    Posted Jul 10, 2020 01:55 PM
    I've never experienced any collisions between Oracle sessions(?)  How do you know this is the case?

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 3.  RE: How can I reset Oracle session connection?

    Posted Jul 10, 2020 02:30 PM
    Oracle documentation states "... the initialization part of a package is run only once, the first time you reference the package."  and that "Packaged public variables and cursors persist for the duration of a session."    If I have a package (A) which has some global variable defined and initialized in its specification and I have two other procedures (B and C), executed in sequence, that use this global variable from (A) and procedure (B) happens to override the global variable defined in (A) then when procedure (C) executes it will see the value set by (B) not (A).    Yes, I know the use of "global" variables should be avoided and I have no control over that.   Yes, I tested this and verified it to be true.

    How do I kill the session after Job1 ends so that when Job2 runs it will get a new session and thus package (A) specification will go through initialization again?


    ------------------------------
    Newbie
    ------------------------------



  • 4.  RE: How can I reset Oracle session connection?

    Posted Jul 10, 2020 04:02 PM
    I sense this is a question about Oracle PLSQL, and not UC4.  I found a similar question here, but I don't think it offered any concrete solutions;

    http://www.orafaq.com/forum/t/186683/

    Your initial post led me to believe that JOB1 and JOB2 were two different UC4 tasks, and if this is the case, then they ARE different sessions.

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 5.  RE: How can I reset Oracle session connection?

    Posted Jul 10, 2020 11:09 PM
    JOBS.1 and JOBS.2 are two (2) separate task (JOBS objects) within a single workflow. 

    Here's a very simple example using the following Oracle package that demostrates the problem:

    CREATE OR REPLACE PACKAGE A AS
      DAY_OF_WEEK   VARCHAR2(50) := 'Monday';
      PROCEDURE PRINT_DAY_1;
      PROCEDURE PRINT_DAY_2;
    END A;
    /
    CREATE OR REPLACE PACKAGE BODY A AS
      PROCEDURE PRINT_DAY_1 AS BEGIN DAY_OF_WEEK := 'Tuesday'; DBMS_OUTPUT.put_line(DAY_OF_WEEK); END;
      PROCEDURE PRINT_DAY_2 AS BEGIN DBMS_OUTPUT.put_line(DAY_OF_WEEK); END;
    END A;

    Inside Atomic, I created a workflow and inside the workflow created 2 tasks:
    In task JOBS.1 it calls the stored package procedure:  A.PRINT_DAY_1
    In task JOB2.2 it calls the stored package procedure:  A.PRINT_DAY_2

    The actual output:
    Tuesday
    Tuesday

    If task JOBS.1 and JOBS.2 are indeed separate sessions then the output should have been:   
    Tuesday
    Monday

    If I open an instance of Toad (or SQL Developer) and execute BEGIN A.PRINT_DAY_1; END;  then the output is: 
    Tuesday

    If I open a new session in Toad (or SQL Developer) and execute BEGIN A.PRINT_DAY_2; END; then the output is:
    Monday

    If I open another new session in Toad (or SQL Developer) and execute BEGIN A.PRINT_DAY_1; A.PRINT_DAY_2; END;  then the output is:
    Tuesday
    Tuesday

    So, Oracle is behaving exactly as expected.  From what I see from this testing is that  Atomic is not treating each JOBS as separate sessions from the point that calling Oracle stored procedures are handled as separate sessions.

    Try it.

    ------------------------------
    Newbie
    ------------------------------



  • 6.  RE: How can I reset Oracle session connection?

    Posted Jul 13, 2020 02:16 AM
    Hi Gerald,
    in the Ini of the SQL Agent you have to reduce the following value

    retention_Time =

    Number of seconds after which an unused database connection should be terminated.

    Default: 180



    ------------------------------
    Thx & rgds
    Christian
    ------------------------------



  • 7.  RE: How can I reset Oracle session connection?

    Posted Jul 13, 2020 04:38 AM
    Edited by Carsten Schmitz Jul 13, 2020 04:38 AM
    Hi.

    I'm not a big fan of timing-dependent solutions. Yes, it works most of the time, but for one thing this is probably a global setting potentially affecting other jobs, and second, one needs to figure out the exact timing, and if job #1 runs faster than usual, this might fail?

    I see two possible alternative options:

    1. Make the jobs a JOBS that calls SQLplus for executing the stored procedure, thus ensuring each has it's own session
    2. call RESET_PACKAGE in between, which apparently resets all package state:

    https://docs.oracle.com/cd/A91202_01/901_doc/appdev.901/a89852/dbms_s11.htm

    Disclaimer: not sure if the second option does what it advertises, I'm no Oracle expert, just stumbled over this.

    Hth,​

    ------------------------------
    # signature.sh --verbose=[true|false]
    # no configurables beyond this point, only signature

    Calendar Quote of the Month:
    "The point of a DR test with a remote site is usually to be operational when your main site gets flooded / set on fire / invaded by Ninjas at an inconvenient moment."

    Did you know?
    Using the slide show widget for posting individual images is wrong! Please use the "insert image" button in the editor.

    Pro Tip: I will NOT respond to PM asking for help unless there's an actual reason to keep the discussion off of the public forums!

    'Efficient Solutions Monthly Magazine' says:
    "Asking questions the right way never hurts!"

    Here, have some tips:
    http://www.catb.org/~esr/faqs/smart-questions.html
    https://www.chiark.greenend.org.uk/~sgtatham/bugs.html
    ------------------------------



  • 8.  RE: How can I reset Oracle session connection?

    Posted Jul 13, 2020 12:21 PM
    This is a very interesting exercise.   Could you work around it by not using packages?

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 9.  RE: How can I reset Oracle session connection?
    Best Answer

    Posted Jul 22, 2020 04:00 PM
    Thanks everyone for your suggestions.  The approach I took and works was to call the stored procedures via Oracle jobs.

    ------------------------------
    Newbie
    ------------------------------