| use off execute immediate [message #311969] |
Mon, 07 April 2008 10:26  |
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 #311979 is a reply to message #311969] |
Mon, 07 April 2008 10:50  |
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 |
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 #312206 is a reply to message #312031] |
Tue, 08 April 2008 03:42 |
 |
Barbara Boehmer
Messages: 9106
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 #312382 is a reply to message #312332] |
Tue, 08 April 2008 13:26 |
|
Barbara Boehmer
Messages: 9106
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
