Register Custom Table in ORACLE

DECLARE
   vc_appl_short_name   CONSTANT VARCHAR2 (40) := 'C_APPS';
   vc_tab_name          CONSTANT VARCHAR2 (32) := 'C_FND_INTERFACES';
   vc_tab_type          CONSTANT VARCHAR2 (50) := 'T';
   vc_next_extent       CONSTANT NUMBER        := 512;
   vc_pct_free          CONSTANT NUMBER        := 10;
   vc_pct_used          CONSTANT NUMBER        := 70;
BEGIN   -- Start Register Custom Table
   -- Get the table details in cursor
   FOR table_detail IN (SELECT table_name, tablespace_name, pct_free, pct_used,
                              ini_trans, max_trans, initial_extent,
                              next_extent
                          FROM dba_tables
                         WHERE table_name = vc_tab_name)
   LOOP
      -- Call the API to register table
      ad_dd.register_table (p_appl_short_name => vc_appl_short_name,
                            p_tab_name        => table_detail.table_name,
                            p_tab_type        => vc_tab_type,
                            p_next_extent     => NVL(table_detail.next_extent, vc_next_extent),
                            p_pct_free        => NVL(table_detail.pct_free, vc_pct_free),
                            p_pct_used        => NVL(table_detail.pct_used, vc_pct_used)
                           );
   END LOOP; -- End Register Custom Table

   -- Start Register Columns
   -- Get the column details of the table in cursor
   FOR table_columns IN (SELECT column_name, column_id, data_type, data_length,
                               nullable
                          FROM all_tab_columns
                         WHERE table_name = vc_tab_name)
   LOOP
      -- Call the API to register column
      ad_dd.register_column (p_appl_short_name      => vc_appl_short_name,
                             p_tab_name             => vc_tab_name,
                             p_col_name             => table_columns.column_name,
                             p_col_seq              => table_columns.column_id,
                             p_col_type             => table_columns.data_type,
                             p_col_width            => table_columns.data_length,
                             p_nullable             => table_columns.nullable,
                             p_translate            => 'N',
                             p_precision            => NULL,
                             p_scale                => NULL
                            );
   END LOOP;   -- End Register Columns
   -- Start Register Primary Key
   -- Get the primary key detail of the table in cursor
   FOR all_keys IN (SELECT constraint_name, table_name, constraint_type
                      FROM all_constraints
                     WHERE constraint_type = 'P' AND table_name = vc_tab_name)
   LOOP
      -- Call the API to register primary_key
      ad_dd.register_primary_key (p_appl_short_name      => vc_appl_short_name,
                                  p_key_name             => all_keys.constraint_name,
                                  p_tab_name             => all_keys.table_name,
                                  p_description          => 'Register primary key',
                                  p_key_type             => 'S',
                                  p_audit_flag           => 'N',
                                  p_enabled_flag         => 'Y'
                                 );
      -- Start Register Primary Key Column
      -- Get the primary key column detial in cursor
      FOR all_columns IN (SELECT column_name, POSITION
                            FROM dba_cons_columns
                           WHERE table_name = all_keys.table_name
                             AND constraint_name = all_keys.constraint_name)
      LOOP
         -- Call the API to register primary_key_column
         ad_dd.register_primary_key_column
                                     (p_appl_short_name      => vc_appl_short_name,
                                      p_key_name             => all_keys.constraint_name,
                                      p_tab_name             => all_keys.table_name,
                                      p_col_name             => all_columns.column_name,
                                      p_col_sequence         => all_columns.POSITION
                                     );
      END LOOP; -- End Register Primary Key Column
   END LOOP;    -- End Register Primary Key

   COMMIT;
END;

Let’s go and validate in application.
Navigate to Application Developer –> Application –> Database –> Table
F11 –> Enter your table_name ‘C_FND_INTERFACES’ –> Ctrl + F11.
 

DBMS_SCHEDULER examples

DBMS_SCHEDULER examples

May be interesting to some of you which haven't had the time testing and working with the DBMS_SCHEDULER. I collected some examples to show what is possible with that amazing tool.

DBMS_SCHEDULER is an internal Oracle package (since Version 10g) which provides database driven jobs.
It's divided into 3 parts:

• Time schedule part - dbms_scheduler.create_schedule
• Program declaration part - dbms_scheduler.create_program
• Job (conflation) part -dbms_scheduler.create_job

Examples of the dbms_scheduler.create_schedule part:

view plainprint?

1. begin  
2. -- daily from Monday to Sunday at 22:00 (10:00 p.m.)  
3. dbms_scheduler.create_schedule  
4. (schedule_name => 'INTERVAL_DAILY_2200',  
5.  start_date=> trunc(sysdate)+18/24, -- start today 18:00 (06:00 p.m.)  
6.  repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',  
7.  comments=>'Runtime: Every day (Mon-Sun) at 22:00 o'clock');  
8.   
9. -- run every hour, every day  
10. dbms_scheduler.create_schedule(    
11. schedule_name  => 'INTERVAL_EVERY_HOUR',    
12.   start_date    => trunc(sysdate)+18/24,    
13.   repeat_interval => 'freq=HOURLY;interval=1',    
14.   comments     => 'Runtime: Every day every hour');    
15.   
16. -- run every 5 minute, every day  
17. dbms_scheduler.create_schedule(  
18. schedule_name  => 'INTERVAL_EVERY_5_MINUTES',  
19.   start_date    => trunc(sysdate)+18/24,  
20.   repeat_interval => 'freq=MINUTELY;interval=5',  
21.   comments     => 'Runtime: Every day all 5 minutes');  
22.   
23. -- run every minute, every day  
24. dbms_scheduler.create_schedule(  
25. schedule_name  => 'INTERVAL_EVERY_MINUTE',  
26.   start_date    => trunc(sysdate)+18/24,  
27.   repeat_interval => 'freq=MINUTELY;interval=1',  
28.   comments     => 'Runtime: Every day every minute');  
29.   
30. -- run every Sunday at 18:00 (06:00 p.m.)  
31. dbms_scheduler.create_schedule  
32. (schedule_name => 'INTERVAL_EVERY_SUN_1800',  
33.  start_date=> trunc(sysdate)+18/24,  
34.  repeat_interval=> 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;',  
35.  comments=>'Runtime: Run at 6pm every Sunday');  
36. end;  

Example of the dbms_scheduler.create_program part:

view plainprint?

1. begin  
2. -- Call a procedure of a database package  
3. dbms_scheduler.create_program  
4. (program_name=> 'PROG_COLLECT_SESS_DATA',  
5.  program_type=> 'STORED_PROCEDURE',  
6.  program_action=> 'pkg_collect_data.prc_session_data',  
7.  enabled=>true,  
8.  comments=>'Procedure to collect session information'  
9.  );  
10. end;  

Example of the dbms_scheduler.create_job part:

view plainprint?

1. begin  
2. -- Connect both dbms_scheduler parts by creating the final job  
3. dbms_scheduler.create_job  
4.  (job_name => 'JOB_COLLECT_SESS_DATA',  
5.   program_name=> 'PROG_COLLECT_SESS_DATA',  
6.   schedule_name=>'INTERVAL_EVERY_5_MINUTES',  
7.   enabled=>true,  
8.   auto_drop=>false,  
9.   comments=>'Job to collect data about session values every 5 minutes');  
10. end;  

Examples to change dbms_scheduler settings:

view plainprint?

1. begin  
2. -- change start time  
3. DBMS_SCHEDULER.SET_ATTRIBUTE(  
4.    name => 'INTERVAL_EVERY_5_MINUTES',  
5.    attribute => 'start_date',  
6.    value => to_date('22.06.2009 12:15','dd.mm.yyyy hh24:mi')  
7. );  
8.   
9. -- change repeat interval  
10. DBMS_SCHEDULER.SET_ATTRIBUTE(  
11.    name => 'INTERVAL_EVERY_MINUTE',  
12.    attribute => 'repeat_interval',  
13.    value => 'freq=MINUTELY;interval=2'  
14. );   
15. end;  

Example to run job immediate:

view plainprint?

1. begin  
2. dbms_scheduler.run_job('JOB_COLLECT_SESS_DATA',TRUE);  
3. end;  

Example to restart job:

view plainprint?

1. begin  
2. dbms_scheduler.disable('JOB_COLLECT_INST_INFO');  
3. dbms_scheduler.enable('JOB_COLLECT_INST_INFO');  
4. end;  

Select job status:

view plainprint?

1. -- All jobs  
2. select * from user_scheduler_jobs;  
3.   
4. -- Get information to job  
5. select * from user_scheduler_job_log order by log_date desc;  
6.   
7. -- Show details on job run  
8. select * from user_scheduler_job_run_details;