Home » SQL & PL/SQL » SQL & PL/SQL » use of logminer in a procedure (Oracle 10g xe, Windows xp)
use of logminer in a procedure [message #310825] Wed, 02 April 2008 09:15 Go to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
Hi all,
I try to use LogMiner tool to intercept transaction in my database, i try it in a script, it's work perfectly, but when i try to use it in a procedure, an error appear, my gool is to enter the option 'STARTTIME' as a parameter IN, i ask if it's possible, i try to do this:
create or replace function debut (date_start in varchar2 ) return varchar2 is
begin
return date_start;
end;
--fonction cree
variable date_s varchar2(2000);
execute :date_s := debut('29-Mars-2008 10:01:34');
--procedure se termine avec succe

EXECUTE DBMS_LOGMNR.START_LOGMNR(-
       STARTTIME => date_s, -
         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);

when execute dbms_logmnr.start_logmnr, this erros appear:
ERREUR Ó la ligne 1 :
ORA-06550: Ligne 1, colonne 53 :
PLS-00201: l'identificateur 'DATE_S' doit Ûtre dÚclarÚ
ORA-06550: Ligne 1, colonne 7 :
PL/SQL: Statement ignored

thanks in advance for all help.
Re: use of logminer in a procedure [message #310826 is a reply to message #310825] Wed, 02 April 2008 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
PL/SQL not equal to SQL*Plus
They are different languages.
What is valid in 1 is not necessarily valid in the other.
Re: use of logminer in a procedure [message #310830 is a reply to message #310825] Wed, 02 April 2008 09:24 Go to previous messageGo to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
Quote:
PL/SQL not equal to SQL*Plus
They are different languages.
What is valid in 1 is not necessarily valid in the other.

I work with SQL*Plus
Re: use of logminer in a procedure [message #310831 is a reply to message #310825] Wed, 02 April 2008 09:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

variable date_s varchar2(2000);
execute :date_s := debut('29-Mars-2008 10:01:34');


I hope this will help you in understanding why you are getting that error. Clue is you are trying to use a variable define in sqlplus inside pl/sql block. I hope you know that when you use execute statement oracle internally creates a anonymous pl/sql block to execute the statement.

Regards

Raj
Re: use of logminer in a procedure [message #310832 is a reply to message #310825] Wed, 02 April 2008 09:29 Go to previous messageGo to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
No, i don't know that, so i ask if there is a solution to use a variable in the dbms_logmnr procedure.
Re: use of logminer in a procedure [message #310833 is a reply to message #310832] Wed, 02 April 2008 09:49 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Yes you can use variable defined in sql inside pl/sql. Infact, you have used it in one of the lines of your code by prefixing your variable with ":" symbol.
Let me give you a very simple example. I hope you can figure it out yourself.
SQL> var var1 varchar2(10); -- Defined a variable in host (sql area)
SQL> set serveroutput on
Example 1 :
SQL> begin
  2  dbms_output.put_line(var1); 
  3  end;
  4  /
dbms_output.put_line(var1);
                     *
ERROR at line 2:
ORA-06550: line 2, column 22:
PLS-00201: identifier 'VAR1' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Example 2 :
SQL> begin
  2  dbms_output.put_line('Var1 value is ' || :var1);
  3  end;
  4  /
Var1 value is

PL/SQL procedure successfully completed.

Example 3:
SQL> exec :var1 := 'Hello World';
BEGIN :var1 := 'Hello World'; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

Example 4:
SQL> exec :var1 := 'Hello';

PL/SQL procedure successfully completed.

  1  begin
  2  dbms_output.put_line('Var1 value is : ' || :var1);
  3* end;
SQL> /
Var1 value is : Hello

PL/SQL procedure successfully completed.


Example 3 is just to prove what I said before. I hope it helps.

Regards

Raj

[Updated on: Wed, 02 April 2008 09:49]

Report message to a moderator

Previous Topic: ORA-00905, ORA-06512 during Create External Table in PL/SQL
Next Topic: Cursor Within a Cursor
Goto Forum:
  


Current Time: Tue Dec 06 12:28:24 CST 2016

Total time taken to generate the page: 0.14133 seconds