Home » SQL & PL/SQL » SQL & PL/SQL » getting informations about processes (Oracle 11 - Unix)
getting informations about processes [message #677326] Thu, 12 September 2019 05:52 Go to next message
limner1977
Messages: 6
Registered: September 2019
Junior Member
Hi
i'm writing a stored procedure that write logs into an oracle table about the procedure that calls it.

this monititoring procedure, using OWA_UTIL.who_called_me retrieve the name and the owner of the procedure to log info.

Now i need to store the starting time of the procedure that called the monitoring procedure.
I would not like to use a variable to store sysdate at the beginning, instead i would like to know it there is a way inside oracle to find then a stored procedure, package, function or anonymous block has been launched.

this is the example

ad anonymous block call, as last instruction before commit, my monitoring procedure.
the monitoring procedure write info in a logging table, with also start and end time
end time is gotten using sysdate inside monitoring procedure...but how could i retrieve "starting time" ?

thanks
Limner
Re: getting informations about processes [message #677332 is a reply to message #677326] Thu, 12 September 2019 07:33 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Can you debug code or suggest changes to code that you cannot see?
Neither can anyone else.

Also, what defines "starting time"? Is that the time the calling procedure begins? If so, then the first instruction in that procedure would be to store sysdate into a local variable of type DATE, then pass that as an additional input parameter to your 'logging' procedure.
Re: getting informations about processes [message #677341 is a reply to message #677326] Thu, 12 September 2019 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i would like to know it there is a way inside oracle to find then a stored procedure, package, function or anonymous block has been launched.

No.

Re: getting informations about processes [message #677348 is a reply to message #677332] Fri, 13 September 2019 01:09 Go to previous messageGo to next message
limner1977
Messages: 6
Registered: September 2019
Junior Member
hi

There is no need of code example because there is no debug to do: i was trying to understand if something can be achieved or not. Surprised

Of course, if i inizialize a variable to sysdate as first instruction of a procedure and, at the end i give it to another procedure that make insert in a table, i have the starting time and also ending time of a procedure.

What i was trying to do was to avoid to initialize a variable and do everything from the last istruction that is a calling to the "monitoring procedure"

i write an example here



declare

bla bla bla

begin

first istruction
second istruction
.
.
.

monitoring_procedure(variable)
commit;

exception
when others then
bla bla bla
end;




the monitoring_procedure would write in a log table info as starting and ending time of procedure that called the it.
i was simply trying to avoid to put another instruction, as first, that was the initialiting variable to sysdate to get the starting time and pass it to the monitoring procedure.
that was the question: using OWA_UTIL.who_called_me i was able to get some usefull informations, like the name and type of procedure that called the monitoring procedure and i was trying to understand if Oracle, togheter with the type of procedure, also store the starting time of that procedure.

Sorry for my english, i hope that this is more understandable.

Danilo

[Updated on: Fri, 13 September 2019 03:57]

Report message to a moderator

Re: getting informations about processes [message #677361 is a reply to message #677348] Fri, 13 September 2019 07:47 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
I'd recommend that you do two calls to the write_log proc, one at the start and one at the end. This will allow you to debug better in the case the proc fails - You will have a start time and no end time, which means the proc failed.

JP
Re: getting informations about processes [message #677362 is a reply to message #677348] Fri, 13 September 2019 08:18 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
So you don't want to write a single line of code to assign the sysdate to a local variable, and are hoping that oracle has some mechanism already that assigns sysdate to some internal structure? Sorry, you need to write that single line of code.

But as JPBoileau said, you'd be better of writing a log record at the beginning, and another at the end. That's exactly what all of my procs do, and it allows for other informative messages to be written to this 'program log'.
Re: getting informations about processes [message #677385 is a reply to message #677361] Mon, 16 September 2019 01:42 Go to previous messageGo to next message
limner1977
Messages: 6
Registered: September 2019
Junior Member
Dear Mr JPBoileau

thank you for your usefull informations.
I'm trying to create a code that can be used by the society where i work for, and i can't do all the modifications that i would: for example there are two reasons why i tried to find another way to find the starting date of a process inside oracle, but those reason are not important for the forum, anyway thank you for sharing your knowledge.

I'm corious about one info you gave:
"..two calls to the write_log proc " may you give more knowledge about those two calls? Maybe the reason of those two calls are for "capturing" informations about the process if the process itself have an error during the running?

Is it possible to have more info about this?
thanks you again Mr JPBoileau!

Danilo
Re: getting informations about processes [message #677387 is a reply to message #677385] Mon, 16 September 2019 07:56 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
CREATE TABLE SOMELOG (MESSAGE VARCHAR2(80), LOG_DATE DATE);

CREATE OR REPLACE PROCEDURE WRITE_LOG(pMessage VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO SOMELOG VALUES (pMessage, SYSDATE);
   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE FAIL_PROC IS
BEGIN
   WRITE_LOG('START FAIL_PROC');
   
   RAISE NO_DATA_FOUND;

   WRITE_LOG('END FAIL_PROC');
END;
/


DEV1> EXEC FAIL_PROC;
BEGIN FAIL_PROC; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "DMVPHX\BOILEAU_JEANP.FAIL_PROC", line 5
ORA-06512: at line 1


DEV1> SELECT * FROM SOMELOG;

MESSAGE
--------------------------------------------------------------------------------
LOG_DATE
---------
START FAIL_PROC
16-SEP-19

This should make it fairly obvious.

JP
Re: getting informations about processes [message #677388 is a reply to message #677385] Mon, 16 September 2019 08:55 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
limner1977 wrote on Mon, 16 September 2019 01:42
Dear Mr JPBoileau

thank you for your usefull informations.
I'm trying to create a code that can be used by the society where i work for, and i can't do all the modifications that i would: for example there are two reasons why i tried to find another way to find the starting date of a process inside oracle, but those reason are not important for the forum, anyway thank you for sharing your knowledge.


Actually, those reasons are very important if you want help to find the optimal solution to the real problem, and not a techincal fix to your pre-conceived solution. This has all the hallmarks of the classic 'x-y problem' - http://xyproblem.info/
Re: getting informations about processes [message #677392 is a reply to message #677388] Mon, 16 September 2019 10:24 Go to previous messageGo to next message
limner1977
Messages: 6
Registered: September 2019
Junior Member
Mr JPBoileau

it is perfect clear to me the way you explained about the logging way you suggest, and thank you for the info shown on the post.

About the "error logging" do you use DBMS_ERRLOG ? as far as i know not all types of errors are logged by this method...or ma i wrong?

Thanks in advance Mr JPBoileau.
Re: getting informations about processes [message #677393 is a reply to message #677392] Mon, 16 September 2019 10:31 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
DBMS_ERRLOG is specifically for logging data errors when doing DML, it has nothing to do with your original question.

JP
Re: getting informations about processes [message #677394 is a reply to message #677393] Mon, 16 September 2019 10:47 Go to previous messageGo to next message
limner1977
Messages: 6
Registered: September 2019
Junior Member
yes correct, because the original question has been answered once i understand that oracle store informations about the process but not about the starting date of the process.

The second questions raised once i saw your answer about the write_proc.
Initially i immaginated that you were speaking about a oracle feature now i understood that you were speaking about a "homemade" process, and speaking about the failure of a process (the case we have only the "starting date") i was thinking about storing the errors.

Maybe this is for another topic, anyway thanks JPBoileau
Re: getting informations about processes [message #677401 is a reply to message #677394] Tue, 17 September 2019 06:33 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
don't use the owa_utility package the dbms_utility package does a much better job. who_called_me shows the latest line. The format_call_stack and format_error_stack from the DBMS_UTILITY shows the entire path of execution.

The following query will give the time that the session connected

SELECT Logon_time
FROM V$session
WHERE Audsid = SYS_CONTEXT ('USERENV', 'sessionid');

Re: getting informations about processes [message #677407 is a reply to message #677401] Tue, 17 September 2019 10:35 Go to previous message
limner1977
Messages: 6
Registered: September 2019
Junior Member
ah! Surprised

thank you very much, i'll start study dbms_utility immediately, i don't know this package.

If i'll have other questions, i'll write.

thank you for this suggestion, mr BillB

thanks
Limner
Previous Topic: help around LIST_AGG function
Next Topic: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists
Goto Forum:
  


Current Time: Thu Mar 28 09:24:06 CDT 2024