Home » SQL & PL/SQL » SQL & PL/SQL » use off execute immediate (Oracle 10g xe, Windows xp)
use off execute immediate [message #311969] Mon, 07 April 2008 10:26 Go to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
hello all,
I would like to use EXECUTE IMMEDIATE to run the DBMS_LOGMNR.START_LOGMNR procedure in my procedure "logminer_session", for the alter session, that's work without problem, but problem begin with dbms_logmnr, this is the code:
CREATE OR REPLACE PROCEDURE SYSTEM.LOGMINER_SESSION As
va dae;
v_sql_stmt VARCHAR2(4000);
v_sql_stmt2 VARCHAR2(4000);

begin
SELECT max(date_c) INTO va FROM connexion_test;

v_sql_stmt2 :='ALTER SESSION SET NLS_DATE_FORMAT = "DD-MON-YYYY HH24:MI:SS"';
execute immediate v_sql_stmt2;
execute immediate  'DBMS_LOGMNR.START_LOGMNR(-
         STARTTIME =>:va, -
         ENDTIME => SYSDATE, -
                OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + 
                    DBMS_LOGMNR.COMMITTED_DATA_ONLY + 
                    DBMS_LOGMNR.NO_SQL_DELIMITER + 
                    DBMS_LOGMNR.NO_ROWID_IN_STMT + 
                    DBMS_LOGMNR.CONTINUOUS_MINE)' using va  ;
					
end;

and the errors:
ERREUR Ó la ligne 1 :
ORA-00900: instruction SQL non valide
ORA-06512: Ó "SYSTEM.LOGMINER_SESSION", ligne 18
ORA-06512: Ó ligne 1

help me please
Re: use off execute immediate [message #311973 is a reply to message #311969] Mon, 07 April 2008 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't have to use execute immediate to execute a package just name the package, procedure and its parameters.

Regards
Michel
Re: use off execute immediate [message #311979 is a reply to message #311969] Mon, 07 April 2008 10:50 Go to previous messageGo to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
I try to do that by but this show me errors:
CREATE OR REPLACE PROCEDURE SYSTEM.LOGMINER_SESSION As
va date;
v_sql_stmt VARCHAR2(4000);
v_sql_stmt2 VARCHAR2(4000);

begin
SELECT max(date_c) INTO va FROM connexion_test;
v_sql_stmt2 :='ALTER SESSION SET NLS_DATE_FORMAT = "DD-MON-YYYY HH24:MI:SS"';
execute immediate v_sql_stmt2;
DBMS_LOGMNR.START_LOGMNR(
         STARTTIME =>va, 
         ENDTIME => SYSDATE, 
                OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + 
                    DBMS_LOGMNR.COMMITTED_DATA_ONLY + 
                    DBMS_LOGMNR.NO_SQL_DELIMITER + 
                    DBMS_LOGMNR.NO_ROWID_IN_STMT + 
                    DBMS_LOGMNR.CONTINUOUS_MINE);
					
end;

errors:
Erreurs pour PROCEDURE SYSTEM.LOGMINER_SESSION :

LINE/COL ERROR
-------- ----------------------------------------------------------------
10/1     PL/SQL: Statement ignored
13/28    PLS-00201: l'identificateur 'DBMS_LOGMNR' doit Ûtre dÚclarÚ
Re: use off execute immediate [message #311982 is a reply to message #311979] Mon, 07 April 2008 10:56 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Erreurs pour PROCEDURE SYSTEM.LOGMINER_SESSION :

To be honest i have not used log miner package. But at the same time I am curious to know why you are trying to create a procedure in system schema? It is usually meant for database administration purposes. You will normally get the error mentioned if oracle cannot find the object in other words either the object does not exists or you don't have to access to the object.

HTH

Regards

Raj

[Updated on: Mon, 07 April 2008 10:57]

Report message to a moderator

Re: use off execute immediate [message #311984 is a reply to message #311969] Mon, 07 April 2008 11:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SYS.DBMS_LOGMNR.START_LOGMNR(
is what is needed because user SYS owns the Log Miner procedure(s); among other DBMS_% supplied procedures.
Re: use off execute immediate [message #312031 is a reply to message #311969] Mon, 07 April 2008 19:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
yes, but if dbms_logmnr is meant to be used by normal scheams, then there should be a public synonym with the appropriate grants in place to make it happen.

Kevin
Re: use off execute immediate [message #312206 is a reply to message #312031] Tue, 08 April 2008 03:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
In order to use dbms_logmnr, the database must be in archivelog mode.

As with any procedure, roles do not apply, so all necessary privileges, such as execute on dbms_logmnr must be granted explicitly, not through a role. Also, select privileges must be on the underlying base tables of views. So, if you need to select from the v$logfile view in the procedure, then you need select privileges on the v_$logfile base table that the view selects from.

The starttime parameter of the start_logmnr procedure is date datatype. So, you can pass it any valid date, in any of the usual ways, like passing it in as a parameter or assigned the value in the procedure directly or by selecting. In the documentation, most of the examples alter the nls_date_format so that they can pass a varchar2 datatype and allow an implicit conversion. This is not a good habit.

You have to add your redo logfiles in order to mine them. The first one is new, then the rest are added.

Please see the demonstration below.

SYS@orcl_11g> SELECT log_mode FROM v$database
  2  /

LOG_MODE
------------
ARCHIVELOG

SYS@orcl_11g> CREATE USER test IDENTIFIED BY test
  2  /

User created.

SYS@orcl_11g> GRANT CONNECT, RESOURCE TO test
  2  /

Grant succeeded.

SYS@orcl_11g> GRANT EXECUTE ON DBMS_LOGMNR TO test
  2  /

Grant succeeded.

SYS@orcl_11g> GRANT SELECT ON v_$logfile TO test
  2  /

Grant succeeded.

SYS@orcl_11g> CONNECT test/test
Connected.
TEST@orcl_11g> 
TEST@orcl_11g> DESC DBMS_LOGMNR
PROCEDURE ADD_LOGFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOGFILENAME                    VARCHAR2                IN
 OPTIONS                        BINARY_INTEGER          IN     DEFAULT
FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_REDO_UNDO                  NUMBER                  IN     DEFAULT
 COLUMN_NAME                    VARCHAR2                IN     DEFAULT
PROCEDURE END_LOGMNR
FUNCTION MINE_VALUE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_REDO_UNDO                  NUMBER                  IN     DEFAULT
 COLUMN_NAME                    VARCHAR2                IN     DEFAULT
PROCEDURE REMOVE_LOGFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOGFILENAME                    VARCHAR2                IN
PROCEDURE START_LOGMNR
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STARTSCN                       NUMBER                  IN     DEFAULT
 ENDSCN                         NUMBER                  IN     DEFAULT
 STARTTIME                      DATE                    IN     DEFAULT
 ENDTIME                        DATE                    IN     DEFAULT
 DICTFILENAME                   VARCHAR2                IN     DEFAULT
 OPTIONS                        BINARY_INTEGER          IN     DEFAULT

TEST@orcl_11g> SELECT member FROM v$logfile
  2  /

MEMBER
--------------------------------------------------------------------------------
C:\APP\BARBARA\ORADATA\ORCL\REDO03.LOG
C:\APP\BARBARA\ORADATA\ORCL\REDO02.LOG
C:\APP\BARBARA\ORADATA\ORCL\REDO01.LOG

TEST@orcl_11g> CREATE OR REPLACE PROCEDURE logminer_session
  2  AS
  3    v_count NUMBER := 0;
  4    va DATE := TO_DATE ('29-Mar-2008 10:10:34', 'dd-Mon-yyyy hh24:mi:ss');
  5  BEGIN
  6    FOR r IN (SELECT member FROM v$logfile) LOOP
  7  	 IF v_count = 0 THEN
  8  	   DBMS_LOGMNR.ADD_LOGFILE
  9  	     (LOGFILENAME => r.member,
 10  	      OPTIONS => DBMS_LOGMNR.NEW);
 11  	   v_count := v_count + 1;
 12  	 ELSE
 13  	   DBMS_LOGMNR.ADD_LOGFILE
 14  	     (LOGFILENAME => r.member,
 15  	      OPTIONS => DBMS_LOGMNR.ADDFILE);
 16  	 END IF;
 17    END LOOP;
 18    DBMS_LOGMNR.START_LOGMNR
 19  	 (STARTTIME   => va,
 20  	  ENDTIME     => SYSDATE,
 21  	  OPTIONS     => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +
 22  			 DBMS_LOGMNR.COMMITTED_DATA_ONLY +
 23  			 DBMS_LOGMNR.NO_SQL_DELIMITER +
 24  			 DBMS_LOGMNR.NO_ROWID_IN_STMT +
 25  			 DBMS_LOGMNR.CONTINUOUS_MINE);
 26    DBMS_LOGMNR.END_LOGMNR;
 27  END logminer_session;
 28  /

Procedure created.

TEST@orcl_11g> SHOW ERRORS
No errors.
TEST@orcl_11g> EXECUTE logminer_session

PL/SQL procedure successfully completed.

TEST@orcl_11g> 






Re: use off execute immediate [message #312329 is a reply to message #311969] Tue, 08 April 2008 10:27 Go to previous messageGo to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
thank you very much, this demonstration is very helpful
Re: use off execute immediate [message #312332 is a reply to message #312206] Tue, 08 April 2008 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no synonym for log miner package, so SYS is mandatory when you call it (unless you create the public synonym or use SYS and this latter must NOT be done).

Regards
Michel
Re: use off execute immediate [message #312382 is a reply to message #312332] Tue, 08 April 2008 13:26 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
In 11g, there is a synonym. I did not create it.

SCOTT@orcl_11g> select object_type from all_objects where object_name = 'DBMS_LOGMNR';

OBJECT_TYPE
-------------------
PACKAGE BODY
PACKAGE
SYNONYM
Previous Topic: odd characters in dba_tab_cols
Next Topic: automation script
Goto Forum:
  


Current Time: Thu Dec 08 00:29:58 CST 2016

Total time taken to generate the page: 0.08634 seconds