Re: How do I know whether procedure is running or not ?

From: Bob Bunch (OCP) <bunchb_at_hotmail.com>
Date: Sun, 03 Apr 2005 01:08:49 -0500
Message-Id: <pan.2005.04.03.06.08.44.453000@hotmail.com>

On Sun, 20 Feb 2005 14:57:55 -0800, DA Morgan wrote:

> fmarchioni_at_libero.it wrote:
>

>> Hi all!
>> Can anybody point out how to check if a stored procedure
>> is executing ? (Is there a table which contains references
>> to objects which are in execution ?)
>> Thanks a lot
>> Francesco

>
> To do this you need to write your procedure properly ... which
> means putting a call to DBMS_APPLICATION_INFO.SET_MODULE as the
> first line of code after BEGIN.

This definitely works, though you have to "baby sit" to see if it worked or not. I like to use this for debugging:

• ---------------------------------------------------------------
• Procedure: writeLog
• ---------------------------------------------------------------

CONN system/&&system_pw@&&alias

create or replace and compile java source named "writeLog" as import java.io.*;
public class writeLog {

	static String[]	classArgs;
	public static void wl(String logFile, String logText) throws IOException {
		File outputFile = new File(logFile);
		FileWriter out = new FileWriter(outputFile.toString(), true);
		out.write(logText + "\n");
		out.close();
	}

}
/

CREATE OR REPLACE PROCEDURE j_writelog (

   p_logfile IN VARCHAR2,
   p_logtext IN VARCHAR2
)
AS

   LANGUAGE JAVA
   NAME 'writeLog.wl( java.lang.String, java.lang.String)'; /

SHOW errors

CREATE OR REPLACE PROCEDURE writelog (

   p_logfile IN VARCHAR2,
   p_logtext IN VARCHAR2
)
AS
BEGIN

• must grant self the ability to write to p_logFile! dbms_java.grant_permission ('SYSTEM', 'java.io.FilePermission', p_logfile, 'write' ); SYSTEM.j_writelog (p_logfile, p_logtext); END; /

SHOW errors

GRANT EXECUTE ON writelog TO PUBLIC;
CREATE PUBLIC SYNONYM writelog FOR SYSTEM.writelog;

...then to use it:

     writeLog('c:\tmp\OracleDebug\log.txt', v_logEntry);

I don't know how thread-safe it is, but it at least works just fine for local testing! ;)

HTH
Bob Bunch
8i OCP Uber-Nerd Received on Sun Apr 03 2005 - 00:08:49 CST