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.htmlhttps://www.chiark.greenend.org.uk/~sgtatham/bugs.html------------------------------
Original Message:
Sent: 07-13-2020 02:15 AM
From: Christian Böck
Subject: How can I reset Oracle session connection?
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
Original Message:
Sent: 07-10-2020 11:09 PM
From: Gerald Lewis
Subject: How can I reset Oracle session connection?
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
Original Message:
Sent: 07-10-2020 04:01 PM
From: Pete Wirfs
Subject: How can I reset Oracle session connection?
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
Original Message:
Sent: 07-10-2020 02:29 PM
From: Gerald Lewis
Subject: How can I reset Oracle session connection?
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
Original Message:
Sent: 07-10-2020 01:54 PM
From: Pete Wirfs
Subject: How can I reset Oracle session connection?
I've never experienced any collisions between Oracle sessions(?) How do you know this is the case?
------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
Original Message:
Sent: 07-10-2020 01:40 PM
From: Gerald Lewis
Subject: How can I reset Oracle session connection?
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
------------------------------