Wednesday, August 27, 2014

Oracle Multiple Table Insert

We can insert the selected results into multiple tables using one query "insert all".


SQL> create table t2 (val number);

Table created.

SQL> create table t2 (val number);

Table created.

SQL> insert all into t1 into t2 select 1 from dual;

2 rows created.

SQL> select * from t1;

  VAL
-----
    1

SQL> select * from t2;

  VAL
-----
    1
However, multiple table insert does not work for remote tables. We can only insert data into one remote table at a time.
SQL> insert all into t1 into t3@DL_ANOTHER_DB select 1 from dual;
insert all into t3 into t2@DL_APEX select 1 from dual
                           *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

SQL> insert  into t2@DL_APEX select 1 from dual;

1 row created.

Sunday, August 24, 2014

Oracle Character Return in a String

If a string is too long to display, we can put character returns using "chr(10)" as shown below. As you can see, we can format the long string better. When we generate a long message such as an email from PL/SQL, chr(0) is very helpful.

SQL> select 'hello world!' as message from dual;

MESSAGE
------------
hello world!

SQL> select 'hello'||chr(10)||'world!' as message from dual;

MESSAGE
------------
hello
world!

Schedule Oracle Procedure

We may want to run our Oracle procedures on a regular basis. For example, every day at 10PM, I want to get new transactions from a database schema, calculate the risk score and insert the result into a table. I first created a procedure, proc_all, and then I use DBMS_SCHEDULER.create_job() to schedule the job. This procedure will run automatically every day at 10pm.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'my_proc',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN proc_all; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=22;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');
END;
/
If we want to stop the job, we can drop it using DBMS_SCHEDULER.drop_job()
begin DBMS_SCHEDULER.drop_job(job_name => 'my_proc'); end;
We can query views DBA_SCHEDULER_JOBS or USER_SCHEDULER_JOBS to get the status of scheduled jobs.
SQL> select JOB_NAME,JOB_ACTION from USER_SCHEDULER_JOBS;

JOB_NAME                         JOB_ACTION
-------------------------- ------------------------------------------------------------------
MY_PROC                   BEGIN proc_all; END;
PROC_CLEAR_TBL            BEGIN execute immediate 'truncate table tbl_tobe_alerted'; END;

Friday, August 22, 2014

Refresh an Oracle Materialized View

An Oracle materialized view is a very useful feature in situations including data refresh on regular basis. When we create a materialized view, it takes a snapshot of query result and store it physically. This is different from a view where only query logic is stored with the view. Once the materialized view is created, its content does not change until it is refreshed. When we need to refresh the materialized view, one way to do it is to use dbms_mview.refresh().

SQL> create materialized view mv_test_refresh as select sysdate dt 
     from dual;

Materialized view created.
SQL> select to_char(dt, 'YYYYMMDD:HH24:Mi:SS') from mv_test_refresh;

TO_CHAR(DT,'YYYYM
-----------------
20140801:10:42:47

SQL> exec dbms_mview.refresh('mv_test_refresh');

PL/SQL procedure successfully completed.

SQL> select to_char(dt, 'YYYYMMDD:HH24:Mi:SS') from mv_test_refresh;

TO_CHAR(DT,'YYYYM
-----------------
20140801:10:50:31
Alternatively, we can create a refresh group to include materialized views that we want to refresh. Then we can run dbms_refresh.refresh() to refresh all materialized views included in the group.
SQL> exec dbms_refresh.make(name=>'test_grp', list=>'mv_test_refresh', 
next_date=>sysdate, interval=>'null');

SQL> exec dbms_refresh.refresh('test_grp');

PL/SQL procedure successfully completed.

SQL> select to_char(dt, 'YYYYMMDD:HH24:Mi:SS') from mv_test_refresh;

TO_CHAR(DT,'YYYYM
-----------------
20140801:10:54:35
We can also schedule when a materialized view will be refreshed automatically. That will be the topic of another post.