Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Cascading LOVS - default values [APEX 5.0]

bakus123May 22 2017 — edited Jul 5 2017

Hi friends, I have two cascading LOVs. After changing value of my first LOV in second LOV appropriate values are populated.

lovs.PNG

First LOV:

name - P2_DEPTNO

select dname,

   deptno

from dept

order by 1;

Second LOV:

name - P2_EMPNO

select ename,

   empno

from emp

where deptno = :P2_DEPTNO;

cascading LOV parent - P2_DEPTNO

What should I add to set default value of second LOV (first row from query) after changing first LOV value?

Thanks a lot in advance

This post has been answered by ApExEngma on Jul 4 2017
Jump to Answer

Comments

1024640
Hi,
you ought to create synonym for this package or call the package as SYS.dbms_logmnr

...and you don't have to use execute immediate.

Edited by: Ivan Blanarik on May 6, 2011 9:07 AM
s197oo302
With your help, I could run the procedure like this.
CREATE OR REPLACE PROCEDURE LOGMINER IS

BEGIN

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/home/oracle/oradata/SFS/arch/1_32317_734026431.dbf', OPTIONS => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
DBMS_LOGMNR.END_LOGMNR;
END LOGMINER;
/


and I like to run this one every 30 minutes with another archive file that means +1 No of archive file.
I might think this can be done with some dynamic sql, but I couldn't figure out how to exactly.
Can you check my code?


CREATE OR REPLACE PROCEDURE LOGMINER2 IS
tmp_num NUMBER;
v_TBL_NM1 VARCHAR2(64) := '/home/oracle/oradata/SFS/arch/1_';
v_TBL_NM2 VARCHAR2(64) := '_734026431.dbf'
v_TBL_NM VARCHAR2(64);
v_log_mnr VARCHAR2(256);

BEGIN

tmp_num := 32311;
V_TBL_NM := CONCAT(v_TBL_NM1, TO_CHAR(tmp_num), v_TBL_NM2);
v_log_mnr := 'DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => ''||V_TBL_NM||'', OPTIONS => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
DBMS_LOGMNR.END_LOGMNR;

EXECUTE IMMEDIATE v_log_mnr;
tmp_num := tmp_num +1;

END LOGMINER2;

Edited by: user9359279 on May 6, 2011 6:23 PM
1024640
Perhabs this could help you ;-)
CREATE OR REPLACE PROCEDURE LOGMINER2
IS
  v_TBL_NM VARCHAR2(64);
BEGIN
  SELECT name 
  INTO v_TBL_NM
  FROM V$ARCHIVED_LOG 
  WHERE recid = (SELECT max(recid) FROM V$ARCHIVED_LOG);
  
  DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => V_TBL_NM, OPTIONS => DBMS_LOGMNR.NEW) ;
  DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG) ;
  DBMS_LOGMNR.END_LOGMNR;
END LOGMINER2;
/

begin
dbms_scheduler.create_job(
job_name => 'YOUR_SCHEMA.JOB_LOGMINER2'
  ,job_type => 'STORED_PROCEDURE'
  ,job_action => 'YOUR_SCHEMA.LOGMINER2'
  ,start_date => sysdate
  ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=30'
  ,enabled => TRUE
  ,comments => 'Automatic execution of LOGMINER2');
end;
/
s197oo302
Thank you. I like to run logminer and write to unix file.
In the logminer, I like to write only SQL_REDO parts.


Can you check my procedure, I could succeed to write something to file , but I couldn't write SQL_REDO column to file by whole.
It has error ORA-06650: line 7, colmun 3: PLS-00306: wrong number or types of arguments in call to 'LOGMINER4'

CREATE OR REPLACE PROCEDURE SYS.LOGMINER4
IS
v_LOG_NM VARCHAR2(64);
f utl_file.file_type;
BEGIN
SELECT name
INTO v_LOG_NM
FROM V$ARCHIVED_LOG
WHERE recid = (SELECT max(recid) FROM V$ARCHIVED_LOG);

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => v_LOG_NM, OPTIONS => DBMS_LOGMNR.NEW) ;
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG) ;



f := utl_file.fopen ('/home/oracle/oradata/SFS/logmine','sample.txt', 'W');
for rec in (SELECT sql_redo
FROM V$LOGMNR_CONTENTS
where SEG_OWNER='ORAASFS' and OPERATION IN ('UPDATE', 'INSERT', 'UPDATE') and TABLE_NAME LIKE 'TM_%') loop
utl_file.put_line (f, rec.sql_redo);
end loop;
utl_file.fclose(f);

utl_file.fclose(f);
DBMS_LOGMNR.END_LOGMNR;

END LOGMINER4;
/

Edited by: user9359279 on May 16, 2011 3:17 PM
1024640
Hi,
I don't know how logminer works, but you should write records to file in loop. And add some exception handling ;-)
CREATE OR REPLACE PROCEDURE SYS.LOGMINER3
IS
v_LOG_NM VARCHAR2(64);
f utl_file.file_type;

BEGIN
SELECT name 
INTO v_LOG_NM
FROM V$ARCHIVED_LOG 
WHERE recid = (SELECT max(recid) FROM V$ARCHIVED_LOG);

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => v_LOG_NM, OPTIONS => DBMS_LOGMNR.NEW) ;
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG) ;

f := utl_file.fopen ('/home/oracle/oradata/SFS/logmine','sample.txt', 'W');

FOR rec_sql_redo in (
  SELECT SQL_REDO 
  FROM V$LOGMNR_CONTENTS
  where SEG_OWNER='ORAASFS' 
  and OPERATION IN ('UPDATE', 'INSERT', 'UPDATE') 
  and TABLE_NAME LIKE 'TM_%'
  order by timestamp
)
LOOP
  utl_file.put_line(f, rec_sql_redo);
END LOOP;


utl_file.fclose(f);
DBMS_LOGMNR.END_LOGMNR;

END LOGMINER3;
/
s197oo302
I made the procedure like this. It work fine. Thank you.

CREATE OR REPLACE PROCEDURE SYSTEM.P_LOGMINER_RESYNC
IS
v_LOG_NM VARCHAR2(256);
f utl_file.file_type;
BEGIN
SELECT name
INTO v_LOG_NM
FROM V$ARCHIVED_LOG
WHERE recid = (SELECT max(recid) FROM V$ARCHIVED_LOG);

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => v_LOG_NM, OPTIONS => DBMS_LOGMNR.NEW) ;
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG) ;


f := utl_file.fopen ('OUTPUT','sample.txt', 'W');
for rec in (SELECT SQL_REDO
FROM V$LOGMNR_CONTENTS
where SEG_OWNER='ORAASFS' and OPERATION IN ('UPDATE', 'INSERT', 'DELETE') and TABLE_NAME LIKE 'TM_SFS_%' order by timestamp) loop
utl_file.put_line (f, rec.SQL_REDO);
end loop;
utl_file.fclose(f);

DBMS_LOGMNR.END_LOGMNR;

END P_LOGMINER_RESYNC;
/
791525
Hi Buddy,
Could please send me what are the Pre as well as Post Requirement for the Logminer..

Thanks
Arun
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 1 2017
Added on May 22 2017
5 comments
756 views