Recover the source code of a dropped Oracle Package

This was a life saver!!!

I have accidentally dropped a package.

Fortunately, I discovered it almost immediately and I was able to recover its source by using the following statements:

select text
from all_source as of timestamp sysdate-1/24
where name 'my_package_name' and type='PACKAGE'
and owner=;

select text
from all_source as of timestamp sysdate-1/24
where name 'my_package_name' and type='PACKAGE BODY'
and owner=;

Obviously, the next step was to commit it in SVN :-)

How to find which datafiles can be shrinked

The last time I was struggling to find available space in an Oracle database I run into this article from AskTom.

I copy the script in here so that I can have it ready for the next time I'll need it. Note, that the script identifies the limit up to which each datafile can be shrinked to and produces all necessary ALTER statements required to shrink each one of them.

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

How to retrieve and use Oracle TIMESTAMPs in Java

A few rules about using TIMESTAMPs in Java:

1. Never make assumptions about things like the timezone in the database and/or the application server.
2. It is preferable to retrieve the TIMESTAMP (or Date) field from the database as a string using the appropriate format. You can then easily convert it to java.sql.Timestamp and use it in Java.
• If you have to compare values that are stored in the database, it is better to do it in your SELECT statement and return something appropriate (i.e. time difference) to Java.
• If you must return something to use it for a comparison in Java, then prefer to return the TIMESTAMP after you have ensured that this is converted to GMT (UTC) time.

How to do it:

• Retrieve the GMT value of a TIMESTAMP as a string that can be converted to java.sql.Timestamp. In the following example change_tm is a Timestamp filed. We are using SYS_EXTRACT_UTC to convert it to GMT.

SELECT 
     TO_CHAR(CHANGE_TM, 'yyyy-mm-dd hh24:mi:ss') CHANGE_TM,
     TO_CHAR(SYS_EXTRACT_UTC(CHANGE_TM), 'yyyy-mm-dd hh24:mi:ss') GMT_CHANGE_TM
FROM TIME_TBL
WHERE  ID = :b1;



• Convert the selected value to a java.sql.Timestamp by simply calling Timestamp.valueof().
• Compare the selected value with the current time of the application server using a Calendar. The Calendar will provide useful information like the offset from GMT.

     Calendar nowCal =GregorianCalendar.getInstance();
     System.out.println("Date now: " + nowCal.get(Calendar.YEAR) + "/" +
                         nowCal.get(Calendar.MONTH) + "/" +
                         nowCal.get(Calendar.DAY_OF_MONTH) + " " +
                         nowCal.get(Calendar.HOUR_OF_DAY) + ":" +
                         nowCal.get(Calendar.MINUTE) + ":" +
                         nowCal.get(Calendar.SECOND) +
                         " Offset from GMT " +
                         nowCal.get(Calendar.ZONE_OFFSET) +
                         " Timezone: " +
                         nowCal.getTimeZone().getDisplayName());

     long now = nowCal.getTimeInMillis();

     System.out.println("Diff before now and last change time: " +
                    ((now - rec.getGmtChangeTm().getTime() - nowCal.get(Calendar.ZONE_OFFSET)) / 1000 / 60) +
                    " minutes");

How to calculate the size of tables and indexes

I have bookmarked these two posts from AskTom:

1. How to calculate the actual size of a table, and
2. dbms_space.free_space

Who's blocking who on what

The following queries are based on this excellent post.
 
/* Who's blocking who */
SELECT s1.username ||
       '@' ||
       s1.machine ||
       ' ( SID=' ||
       s1.SID ||
       ' )  is blocking ' ||
       s2.username ||
       '@' ||
       s2.machine ||
       ' ( SID=' ||
       s2.SID ||
       ' ) ' AS blocking_status
FROM   v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE  s1.SID = l1.SID
AND    s2.SID = l2.SID
AND    l1.BLOCK = 1
AND    l2.request > 0
AND    l1.id1 = l2.id1
AND    l2.id2 = l2.id2;


/* Which object and row is blocked */
SELECT SID, DO.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
       DBMS_ROWID.rowid_create (1, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#)
FROM   v$session s, dba_objects DO
WHERE  s.row_wait_obj# = DO.object_id
AND    SID IN (
           SELECT DISTINCT s2.SID
           FROM            v$lock l1, v$session s1, v$lock l2, v$session s2
           WHERE           s1.SID = l1.SID
           AND             s2.SID = l2.SID
           AND             l1.BLOCK = 1
           AND             l2.request > 0
           AND             l1.id1 = l2.id1
           AND             l2.id2 = l2.id2);

How to get the stacktrace when an exception is raised in PL/SQL (use of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE)

This article describes the use of function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which can be used to identify the exact line where an exception has occurred.

Note that the output of this function does not contain the actual error.

Therefore, when you are trying to log the details of the error, you have to log both sqlerrm and the output of this function to get the complete picture.

Example:

create or replace package body my_pack as
   procedure a as
   begin
      -- DO something that throws an exception
      ...
   end;

   procedure b as
   begin
      -- Call procedure a
      a;
   exception
   when others then
       -- Procedure a has thrown an exception
       DBMS_OUTPUT.PUT_LINE('An exception has occurred: ' || substr(sqlerrm, 1, 512));
       DBMS_OUTPUT.PUT_LINE('The trace of the error was: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
   end;
end;
/

Leap Year Check

This is a quick SQL statement to check if a year is a leap year:

SELECT :YEAR,
       DECODE (MOD (:YEAR, 4), 0, DECODE (MOD (:YEAR, 400), 0, 1, DECODE (MOD (:YEAR, 100), 0, 0, 1)), 0) AS leap_year
FROM   DUAL;

The above SQL returns 1 if :YEAR is a leap year. Else, returns 0.

How to use a flat file as an Oracle External Table

In this example, we define an External Table that corresponds to a fixed-length flat file.

A much better description can be found here.

The file must be present in an Oracle Directory.

The file is mapped to a table with a command similar to the following:

CREATE TABLE my_external_table
(
   field1    VARCHAR2(8)    ,
   field2    VARCHAR2(8)    ,
   field3    VARCHAR2(6) ,
   .....
   fieldn   VARCHAR2(40)
)
ORGANIZATION EXTERNAL 
   (
   TYPE oracle_loader
   DEFAULT DIRECTORY my_external_dir
   ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
        FIELDS
        (
        FIELD1     POSITION(1:8) ,
        FIELD2     POSITION(9:16),
        FIELD3     POSITION(17:22),
        .....
        FIELDN    POSITION(1457:1496)
       )
     )
     LOCATION ('my_flat_file.dat')
  )
REJECT LIMIT UNLIMITED;

Once the table has been created, then it is possible to perform queries as with any normal table:

select *
from my_external_table
where rownum < 10;

Note that the ACCESS PARAMETERS section can contain any valid sql*loader statement.

... and how to get all letters from a word

The function provided below returns the letters in a single word along with their position in the word:

CREATE OR REPLACE TYPE     TYP_WORD_LETTER AS OBJECT
(
  letter VARCHAR2(2),
  position  number(2)
)
/

CREATE OR REPLACE TYPE TYP_WORD_LETTER_COL
AS TABLE OF TYP_WORD_LETTER;
/

CREATE OR REPLACE FUNCTION get_word_letters (v_word VARCHAR2)
   RETURN typ_word_letter_col PIPELINED
AS
   ret_val   NUMBER;
   aletter   VARCHAR2 (2);
   pos       PLS_INTEGER  := 1;
BEGIN
   LOOP
      aletter := SUBSTR (v_word, pos, 1);
      PIPE ROW (NEW typ_word_letter (aletter, pos));
      pos := pos + 1;
      EXIT WHEN pos > LENGTH (v_word);
   END LOOP;

   RETURN;
END;
/

How to break a phrase into words in Oracle

This is a technique to get the individual words in a phrase.
It is based on the use of:

• PIPELINED functions, and
• Regular Expressions
  

First we need a object type and a corresponding collection (to be used by the PIPELINED function).

CREATE OR REPLACE TYPE     typ_word AS OBJECT
(
  text VARCHAR2(64)
)
/

CREATE OR REPLACE TYPE TYP_word_COL
AS TABLE OF TYP_word;
/

The function itself:

CREATE OR REPLACE FUNCTION get_words (v_sentence VARCHAR2)
   RETURN typ_word_col PIPELINED
AS
   occurence   PLS_INTEGER   := 1;
   aword       VARCHAR2 (64);
BEGIN
   LOOP
      aword := REGEXP_SUBSTR (v_sentence, '\w+', 1, occurence);

      IF aword IS NULL
      THEN
         RETURN;
      END IF;

      PIPE ROW (NEW typ_word(aword));
      occurence := occurence + 1;
   END LOOP;
END;
/

How to calculate time difference from TIMESTAMPs

The following script reads a table containing user actions and calculates the time difference (in hours) between the first and last action per user and work day.

The field action_tm is of type TIMESTAMP.

SELECT TRUNC (action_tm) work_day, user_nm,
       MIN (action_tm) start_time,
       MAX (action_tm) end_time,
       ROUND (((TO_CHAR (MAX (action_tm), 'SSSSS') + TO_CHAR (MAX (action_tm), 'FF') * .000001) -
               (TO_CHAR (MIN (action_tm), 'SSSSS') + TO_CHAR (MIN (action_tm), 'FF') * .000001)) /60/60,2) work_hours
FROM user_actions a
GROUP BY TRUNC (action_tm), user_nm
ORDER BY TRUNC (action_tm);

How to encrypt/decrypt a field in Oracle

Oracle provides package DBMS_OBFUSCATION_TOOLKIT which can be used for encrypting/decrypting database fields.

There are a couple of things to remember about this package though:

1. The package provides PROCEDURES to perform encryption/decryption. This alone may not be very useful if you need to use plain SQL. So I provide two functions to wrap these procedures (see below).
2. The length of both the value to be encrypted and the encryption key must be exact multiples of 8. Therefore you need to pad the value with somethig (i.e blanks) to reach the desired length.
   

These are the functions:

CREATE OR REPLACE function encrypt_val(
   input_string       VARCHAR2,
   key_string         VARCHAR2 ) return varchar2 as
   encrypted_string   VARCHAR2 (2048);
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.desencrypt (input_string => input_string, key_string => key_string,
                                        encrypted_string => encrypted_string);
   --DBMS_OUTPUT.put_line ('encrypted hex value : ' || RAWTOHEX (UTL_RAW.cast_to_raw (encrypted_string)));
   return encrypted_string;
END;
/

CREATE OR REPLACE FUNCTION decrypt_val (encrypted_string VARCHAR2, key_string VARCHAR2)
   RETURN VARCHAR2
AS
   decrypted_string   VARCHAR2 (2048);
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.desdecrypt (input_string => encrypted_string, key_string => key_string,
                                        decrypted_string => decrypted_string);
--   DBMS_OUTPUT.put_line ('decrypted string output : ' || decrypted_string);
   RETURN decrypted_string;
END;
/

Example:

The following statement reads a table containing users and encrypts the username (i.e. in order to generate a default password). The actual value to be encrypted is the user name right padded with blanks so that the total length is a multiple of 8.

SELECT user_nm val,
       encrypt_val (RPAD (user_nm, 8 * (FLOOR (LENGTH (user_nm) / 8) + 1)), 'mykey678') enc_val
FROM   ref_user;
        
To retrieve the decrypted value, you need to both decrypt and TRIM:

SELECT val, enc_val, TRIM (decrypt_val (enc_val, 'mykey678')) dec_val
FROM   (
        SELECT user_nm val,
               encrypt_val (RPAD (user_nm, 8 * (FLOOR (LENGTH (user_nm) / 8) + 1)), 'mykey678') enc_val
        FROM   ref_user       
        );

How to make indexes UNUSABLE

The following sample procedure makes indexes UNUSABLE.

This can be helpful when trying to load many thousands or millions of records in a table and you want to avoid index maintenance in order to speed up the loading process.

Make sure to set SKIP_UNUSABLE_INDEXES to TRUE before trying to insert into any table with UNUSABLE indexes. This can be done by including the following statement in your PL/SQL code:

EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE';

This procedure can handle both partitioned and non-partitioned indexes.

   PROCEDURE set_indexes_unusable
   AS
      idxs       pkg_mig_exec.vtable;
      tot_idxs   PLS_INTEGER;
      stmnt      VARCHAR2 (256);
   BEGIN
      SELECT 'alter index ' || owner || '.' || index_name || ' unusable'
      BULK COLLECT INTO idxs
      FROM   all_indexes
      WHERE  owner = 'MY_SCHEMA'
      AND    table_name IN
                  ('TABLE1', 'TABLE2', 'TABLE3')
      AND    partitioned = 'NO'
      AND    uniqueness <> 'UNIQUE';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;

-- Handle partitioned indexes

      SELECT index_owner || '.' || index_name
      BULK COLLECT INTO idxs
      FROM   all_ind_partitions
      WHERE  index_owner = 'MY_SCHEMA' AND partition_name = 'PARTITION_A';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         stmnt :=
            'alter index ' || idxs (i)
            || ' modify partition PARTITION_A unusable';
         pkg_mig_utils.log_info ('Executing : ' || stmnt);

         EXECUTE IMMEDIATE stmnt;
      END LOOP;
   END;

Unusable indexes must be rebuilt in order to become usable again.

PROCEDURE rebuild_unusable_indexes
   AS
      idxs       stmnttable;
      tot_idxs   PLS_INTEGER;
      stmnt      VARCHAR2 (1024);
   BEGIN
      SELECT 'alter index ' || owner || '.' || index_name || ' rebuild'
      BULK COLLECT INTO idxs
      FROM   all_indexes
      WHERE  owner = 'MY_SCHEMA'
      AND    status = 'UNUSABLE'
      AND    partitioned = 'NO';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;

      SELECT 'ALTER INDEX ' || index_owner || '.' || index_name || ' REBUILD PARTITION ' || partition_name
      BULK COLLECT INTO idxs
      FROM   all_ind_partitions a
      WHERE  index_owner = 'MY_SCHEMA'
      AND    partition_name = 'PARTITION_A'
      AND    status = 'UNUSABLE';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;
   END;

How to estimate table stats (table only)

DBMS_STATS.gather_table_stats (
        ownname => SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA'),
        tabname => '',
        estimate_percent => SYS.DBMS_STATS.auto_sample_size,
        method_opt => 'FOR ALL COLUMNS SIZE 1 ',
        DEGREE => NULL, CASCADE => FALSE,
        no_invalidate => FALSE);

Script to rebuild all indexes

DECLARE
   v_tbl_nm    VARCHAR2 (32) := '';
--
   TYPE oranametable IS TABLE OF VARCHAR2 (32);

   indxs       oranametable;
   tot_indxs   PLS_INTEGER;
BEGIN
   SELECT index_name
   BULK COLLECT INTO indxs
   FROM   all_indexes a
   WHERE  a.owner = SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')
   AND    a.table_name = v_tbl_nm;

   tot_indxs := SQL%ROWCOUNT;

   FOR i IN 1 .. tot_indxs
   LOOP
      EXECUTE IMMEDIATE 'alter index ' || indxs (i) || ' rebuild';
   END LOOP;
END;

How (and when) to use PIPELINED PL/SQL functions in Oracle

There have been several cases where I needed to return a list of records after performing complex processing in Oracle.

I found out that the preferred method to do it is by using PIPELINED PL/SQL functions.

The last time I have done this was a few weeks ago where I had to “dig” into a log of user actions and extract information about user’s activity on a daily basis.

Other cases where I have used the same technique in the past were involving:

• Complex validations and return of validation results (warnings, errors, etc.).
• Tree walking and joining with other tables.

In all these cases I needed to query several tables and use temporary tables to collect intermediate results.

An example of the use of PIPELINED functions can be found here.

Here I summarize the steps must be taken when I have to implement something similar.

1.       Create an Object Type with the necessary fields.

CREATE OR REPLACE TYPE TYP_DAILY_USER_TOTALS AS OBJECT (
   user_nm            VARCHAR2 (16),
   act_dt             DATE,
   tot_proc           NUMBER (6),
   tot_in_progress    NUMBER (6),
   tot_cancelled      NUMBER (6),
   tot_failed         NUMBER (6),
   tot_compl          NUMBER (6),
   avg_assign_tm      NUMBER (6),
   median_assign_tm   NUMBER (6),
   start_tm           DATE,
   end_tm             DATE
);

2.       Create a collection

CREATE OR REPLACE TYPE TYP_DAILY_USER_TOTALS_COL AS TABLE OF typ_daily_user_totals;

3.       Implement a function returning a collection

CREATE OR REPLACE FUNCTION get_daily_user_totals(v_user_nm varchar2)
   RETURN typ_daily_user_totals_col PIPELINED
AS
   PRAGMA AUTONOMOUS_TRANSACTION;

   TYPE daily_totals_rec IS RECORD (
      act_dt             DATE,
      tot_proc           NUMBER (6),
      tot_in_progress    NUMBER (6),
      tot_cancelled      NUMBER (6),
      tot_failed         NUMBER (6),
      tot_compl          NUMBER (6),
      avg_assign_tm      NUMBER (6),
      median_assign_tm   NUMBER (6),
      start_tm           DATE,
      end_tm             DATE
   );

   TYPE daily_totals_tbl IS TABLE OF daily_totals_rec;
   daily_totals   daily_totals_tbl;
BEGIN

/*
   Do whatever is needed to collect your results, i.e. in a global temporary table
*/
 
   . . . . . . .

/*
   Collect your results in a PL/SQL table
*/
 
   SELECT   act_dt,
            tot_proc,
            tot_in_progress,
            tot_cancelled,
            tot_failed,
            tot_compl,
            avg_assign_tm,
            median_assign_tm,
            start_tm,
            end_tm
   BULK COLLECT INTO daily_totals
   FROM  g_daily_user_totals;

/*
   Commit or rollback to clean up the temporary tables and close the autonomous transaction
*/

   ROLLBACK;

/*
   Loop to pipe collected results
*/

   FOR i IN 1 .. daily_totals.COUNT ()
   LOOP

      PIPE ROW (NEW typ_daily_user_totals (
           v_user_nm,
           daily_totals (i).act_dt,
           daily_totals (i).tot_proc,
           daily_totals (i).tot_in_progress,
           daily_totals (i).tot_cancelled,
           daily_totals (i).tot_failed,
           daily_totals (i).tot_compl,
           daily_totals (i).avg_assign_tm,
           daily_totals (i).median_assign_tm,
           daily_totals (i).start_tm,
           daily_totals (i).end_tm));
   END LOOP;

   RETURN;
END;


4.       Select from the function

    SELECT user_nm, act_dt, tot_proc, tot_in_progress, tot_cancelled, tot_failed, tot_compl,
           avg_assign_tm, median_assign_tm, start_tm, end_tm
    FROM   TABLE (get_daily_user_totals(:aUser));

Some notes on the above:

1. If it is necessary to write into any table (as in the example above), then we must declare our function to work as an AUTONOMOUS TRANSACTION (see PRAGMA AUTONOMOUS_TRANSACTION).
2. The TABLE resulting from the select statement above can be joined with other tables and, possibly, create a view.
3. There have been cases where I have used a view (to make life of Java developers easier). In this case, if our PIPELINED function requires a parameter, then this can be passed via a context (using SYS_CONTEXT). I do not have an example ready for this, but I remember an article in Oracle Magazine describing this technique.

PL/SQL code to validate a Greek VAT Number (ΑΦΜ)

FUNCTION check_afm (v_afm VARCHAR2)
      RETURN PLS_INTEGER
   AS
      resultvalid   PLS_INTEGER := 0;
      l_digit       PLS_INTEGER;
      s             PLS_INTEGER := 0;
      m             PLS_INTEGER;
   BEGIN
      IF LENGTH (v_afm) != 9
      THEN
         RETURN 0;
      END IF;

      IF v_afm = '000000000'
      THEN
         RETURN 0;
      END IF;

      IF    REGEXP_SUBSTR (v_afm, '\d{9}') IS NULL
         OR REGEXP_SUBSTR (v_afm, '\d{9}') != v_afm
      THEN
         RETURN 0;
      END IF;

      FOR i IN 1 .. 8
      LOOP
         l_digit := TO_NUMBER (SUBSTR (v_afm, i, 1));
--        DBMS_OUTPUT.PUT_LINE ( 'l_digit = ' || l_digit );
         s := s + l_digit * POWER (2, 9 - i);
--        DBMS_OUTPUT.PUT_LINE('s = ' || s );
      END LOOP;

      m := MOD (s, 11);

--    DBMS_OUTPUT.PUT_LINE('s = ' || s );
      IF (m = 10)
      THEN
         m := 0;
      END IF;

      IF m = ASCII (SUBSTR (v_afm, 9, 1)) - 48
      THEN
         RETURN 1;
      END IF;

      RETURN 0;
   END;

How to calculate factorial in Oracle

I found this script to calculate the factorial of number n:

---

SELECT ROUND (EXP (SUM (LN (n))))
FROM (
      SELECT LEVEL AS n
      FROM DUAL
      CONNECT BY LEVEL <= :n);

---

Unfortunately, I cannot find the blog/forum where this was originally posted.

There are other implementations, but I found that this one is executing faster.

Installing Oracle XE on a Centos based VPS

This is a summary of the steps I followed in order to be able to install Oracle XE 10g on my Centos Virtual Private Server (VPS) hosted by eapps.com.


Update: A more recent post is available concerning the installation of Oracle XE 11g in a similar environment.

The basic idea is based on the steps described in this blog entry.
The main problems that had to be addressed were the following:

1. Lack of swap space
2. Setting apex to be serviced on a port other than 8080.

So here are the steps to run as root:

1. Download the rpm from here. I have used the Universal version (oracle-xe-univ-10.2.0.1-1.0.i386.rpm).
2. Install yum (using Add Application from the Control Panel).
3. Install packages for cpio and libaio with these commands:
   1. yum install libaio
   2. yum install cpio
4. Extract the contents of the rpm into a temporary folder:
   1. rpm2cpio oracle-xe-univ-10.2.0.1-1.0.i386.rpm > oracle-xe-univ-10.2.0.1-1.0.i386.cpio
   2. cpio -idv < oracle-xe-univ-10.2.0.1-1.0.i386.cpio
5. At this point the rpm contents have been extracted and we are ready to copy them to the appropriate locations:
   1. # cp -R ./usr /
   2. # cp -R ./etc /
6. Make the following additions/changes to users/groups using the Control Panel:
   1. Create a group and name it dba.
   2. Create a new user (oracle) with dba as its primary group:
   3. Add root to the dba group.
7. Give the ownership of the /user/lib/oracle to this user :
   chown -R oracle:dba /usr/lib/oracle
   
8. Edit the nls_lang.sh script and replace #!/bin/sh by #!/bin/bash
9. Run the oracle_env.sh to simplify the process:
   . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
   
10. Change the user rights on the oracle binary :
    1. # chmod u+s $ORACLE_HOME/bin/oracle
    2. # chmod g+s $ORACLE_HOME/bin/oracle
11. Set the desired size of SGA and PGA (in the scripts they appear as substitution variables %sga_target% and %pga_aggregate_target%):
    1. # cd /usr/lib/oracle
    2. Find two (2) files using substitution variables using this: # find . -exec grep "%sga_" '{}' \; -print
    3. Replace the variables in these files with their desired values. I have used the following values:
       1. sga_target=246579200 (= 200 MB)
       2. pga_aggregate_target=67108864 (=64 MB)
12. At this point oracle software is in the correct location and we are ready to run database creation and configuration scripts. Start by executing:
    # /etc/init.d/oracle-xe configure
    
13. I have used the default port (1521) for the listener. However we need to use a port other than 8080 for apex since port 8080 is already used by Tomcat. The port of choice was 8084.

For the rest of the steps open a new shell and login as oracle. Continue with the following steps:

1. Connect internally to oracle using sqlplus to change the XDB HTTP port and register the instance with the listener (this may not be necessary but just in case):
   1. sqlplus / as sysdba
   2. SQL> EXEC DBMS_XDB.SETHTTPPORT(8084);
   3. SQL> alter system register;
2. That's all. You may want to restart oracle. Use the following to stop/start it:
   1. /etc/init.d/oracle-xe stop
   2. /etc/init.d/oracle-xe start
3. At this point if you check your listener it should look like this:
   # lsnrctl status
   
   LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-DEC-2008 08:19:09
   
   Copyright (c) 1991, 2005, Oracle. All rights reserved.
   
   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
   
   STATUS of the LISTENER
   
   ------------------------
   
   Alias LISTENER
   
   Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
   
   Start Date 22-DEC-2008 08:18:30
   
   Uptime 0 days 0 hr. 0 min. 39 sec
   
   Trace Level off
   
   Security ON: Local OS Authentication
   
   SNMP OFF
   
   Default Service XE
   
   Listener Parameter File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
   
   Listener Log File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
   
   Listening Endpoints Summary...
   
   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
   
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<your hostname>)(PORT=1521)))
   
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8084))(Presentation=HTTP)(Session=RAW))
   
   Services Summary...
   
   Service "PLSExtProc" has 1 instance(s).
   
   Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
   
   Service "XE" has 1 instance(s).
   
   Instance "XE", status READY, has 1 handler(s) for this service...
   
   Service "XEXDB" has 1 instance(s).
   
   Instance "XE", status READY, has 1 handler(s) for this service...
   
   Service "XE_XPT" has 1 instance(s).
   
   Instance "XE", status READY, has 1 handler(s) for this service...
   
   The command completed successfully
   

Finally, these are the contents of the listener.ora and tnsnames.ora files I am using:

These are the contents of the listener.ora file:

# listener.ora Network Configuration File:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)

(PROGRAM = extproc)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

(ADDRESS = (PROTOCOL = TCP)(HOST = <your hostname>)(PORT = 1521))

)

)

DEFAULT_SERVICE_LISTENER = (XE)

These are the contents of the tnsnames.ora file:

# tnsnames.ora Network Configuration File:

XE =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = <your IP>)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = XE)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

I hope that the above will help other people trying to install Oracle XE on a similar environment.

Kostas

Oracle DML (and other) Logging

During my last migration project I have used the following technique to keep logs of the progress of migration as well as keep track of failed records. The whole idea was based on this article. I have extended somehow the idea to use the DML log table for general purpose logging as well. These were the steps: 1. Created the DML logging table:

CREATE TABLE MIG_LOG (
   ORA_ERR_NUMBER$ NUMBER,
   ORA_ERR_MESG$ VARCHAR2(2000 BYTE),
   ORA_ERR_ROWID$ ROWID,
   ORA_ERR_OPTYP$ VARCHAR2(2 BYTE),
   ORA_ERR_TAG$ VARCHAR2(4000 BYTE),
   LOG_ID NUMBER,
   RUN_ID NUMBER,
   LOG_DT TIMESTAMP(6),
   LOG_LVL NUMBER(1) );

The fields UP TO ORA_ERR_TAG$ are required (in this order) by the native Oracle DML logging implementation. The fields AFTER ORA_ERR_TAG$ were added in order to keep additional information. 2. Created a table to hold runs of data loading batches:

CREATE TABLE MIG_RUN (
   RUN_ID NUMBER(20),
   RUN_AREA VARCHAR2(32 BYTE),
   START_TM TIMESTAMP(6),
   END_TM TIMESTAMP(6),
   RUN_INFO VARCHAR2(256 BYTE) );

CREATE UNIQUE INDEX PK_MIG_RUN ON MIG_RUN(RUN_ID);

ALTER TABLE MIG_RUN ADD (
   CONSTRAINT PK_MIG_RUN
   PRIMARY KEY (RUN_ID) USING INDEX);

3. Created two sequences for the log_id and run_id fields. 4. Created a package to implement logging (let's name it pkg_mig_utils - more details after a few paragraphs). 5. Created a context to keep the current RUN_ID.

CREATE OR REPLACE CONTEXT MIG_CTX USING PKG_MIG_UTILS;

6. Created the code to

• Start and stop logging
• Log info or errors (other than those produced by DML statements)

7. Added this trigger on the mig_log table to obtain values for the log_id, run_id, log_dt fields

CREATE OR REPLACE TRIGGER TRG_MIG_LOG_BI
BEFORE INSERT
ON MIG_LOG
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.run_id IS NULL THEN
      :NEW.run_id := SYS_CONTEXT ('mig_ctx', 'current_run_id');
  END IF;

  IF :NEW.log_id IS NULL THEN
      select log_id_seq.nextval
      into :NEW.log_id
      from dual;
  END IF;

  IF :new.log_lvl is null THEN
      :new.log_lvl := 3;
  END IF;

  :NEW.log_dt := SYSTIMESTAMP;

END;
/

This is some code from the package mentioned above:

CREATE OR REPLACE PACKAGE pkg_mig_utils AS
c_log_lvl_info CONSTANT PLS_INTEGER := 1;
c_log_lvl_warn CONSTANT PLS_INTEGER := 2;
c_log_lvl_error CONSTANT PLS_INTEGER := 3;

-- General funcs/procs

PROCEDURE start_log (v_area VARCHAR2, v_run_info VARCHAR2);
PROCEDURE end_log;
PROCEDURE log_msg (v_app_txt VARCHAR2, v_ora_txt VARCHAR2, v_log_lvl NUMBER DEFAULT 3);
PROCEDURE log_info (v_app_txt VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_mig_utils AS

...............

PROCEDURE start_log (v_area VARCHAR2, v_run_info VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
new_run_id mig_run.run_id%TYPE;
BEGIN
INSERT INTO mig_run(run_id, run_area, start_tm, run_info)
VALUES (mig_run_id_seq.NEXTVAL, v_area, SYSTIMESTAMP, v_run_info)
RETURNING run_id INTO new_run_id;

DBMS_SESSION.set_context ('mig_ctx', 'current_run_id', new_run_id);

COMMIT;
END;

PROCEDURE end_log AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE mig_run
SET end_tm = SYSTIMESTAMP
WHERE run_id = SYS_CONTEXT ('mig_ctx', 'current_run_id');

COMMIT;
END;

--
PROCEDURE log_info (v_app_txt VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO mig_log(ora_err_tag$, log_lvl)
VALUES (v_app_txt, 1);

COMMIT;
END;

PROCEDURE log_msg (v_app_txt VARCHAR2, v_ora_txt VARCHAR2, v_log_lvl NUMBER DEFAULT 3) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO mig_log(ora_err_tag$, ora_err_mesg$, log_lvl)
VALUES (v_app_txt, v_ora_txt, v_log_lvl);

COMMIT;
END;

END;
/

Notes on the code above: 1. Procedure start_log :

• Gets a new run_id from the corresponding sequence
• Inserts a record for the run in the mig_run table
• Puts the current run_id in the session context so that the trigger can use it to update the corresponding fields in the mig_log table.

2. Procedure end_log just updates the end time of the run. Now that the necessary pieces are in place, let's use it in actual migration code: These were some of the requirements for this code: 1. In my case I was loading data per calendar year. So I wanted to keep this info (associate runs with years). 2. The same code had to be used for multiple cases. Therefore I had to use DYNAMIC SQL to perform the necessary INSERTS. 3. Since we had to migrate millions of rows, we had to use BULK operations (BULK COLLECT and FORALL statements). However, we did not want the whole process to be interrupted when a single record failed. In summary, the whole procedure was implemented like in the following SAMPLE package. The main procedure start_mig.

CREATE OR REPLACE PACKAGE BODY pkg_mig_exec AS
-- Error handling aids;
sentence                     VARCHAR2 (4000);
max_ora_message_length       NUMBER          := 512;

g_app_error_code             PLS_INTEGER     := -20002;
app_error_detected           EXCEPTION;
PRAGMA EXCEPTION_INIT (app_error_detected, -20002);
                                                                 --
TYPE ntable IS TABLE OF NUMBER;

TYPE oranametable IS TABLE OF VARCHAR2 (32);

TYPE sqltable IS TABLE OF VARCHAR2 (4000)
INDEX BY VARCHAR2 (30);

-- A map to hold dynamic sql statements   
area_sql_table               sqltable;

PROCEDURE mig_rows (ids ntable, v_tbls oranametable, v_id_fld VARCHAR2 DEFAULT 'ID') AS
PRAGMA AUTONOMOUS_TRANSACTION;
ins_stmnt    VARCHAR2 (4000);
tot_errors   PLS_INTEGER;
BEGIN
FOR j IN 1 .. v_tbls.COUNT () LOOP

   pkg_mig_utils.LOG_INFO('Inserting into table ' || v_tbls (j));
   ins_stmnt := area_sql_table (v_tbls (j)) || ' where ' || v_id_fld || ' = :b1';
   ins_stmnt := ins_stmnt || ' LOG ERRORS INTO MIG_LOG (''' || v_id_fld || '= ''|| :b1 ) REJECT LIMIT UNLIMITED';

   FORALL i IN 1 .. ids.COUNT
      EXECUTE IMMEDIATE ins_stmnt
      USING             ids (i), ids (i);

END LOOP;

COMMIT;

END;

PROCEDURE mig_data (
v_src_tbl_nm   VARCHAR2,
v_tbls         oranametable,
v_id_fld       VARCHAR2 DEFAULT 'ID'
) AS
ids               ntable;

TYPE idcurtyp IS REF CURSOR;

c_ids             idcurtyp;
id_stmnt          VARCHAR2 (128) := 'SELECT ' || v_id_fld || ' FROM ' || v_src_tbl_nm;
tot_rows_so_far   PLS_INTEGER    := 0;
BEGIN

id_stmnt := id_stmnt || ' ORDER BY ' || v_id_fld;
sentence := 'Failed to open cursor for ids of part key: ' || v_part_key;

OPEN c_ids FOR id_stmnt;

LOOP
   sentence := 'Failed to get ids for part key: ' || v_part_key;

   FETCH c_ids
   BULK COLLECT INTO ids LIMIT 100;

   EXIT WHEN ids.COUNT = 0;

   sentence := 'Failed to migrate rows from table ' || v_src_tbl_nm;
   mig_rows (ids, v_tbls, v_id_fld);
   EXIT WHEN c_ids%NOTFOUND;
END LOOP;

CLOSE c_ids;

COMMIT;

pkg_mig_utils.log_info ('Completed migration from table ' || v_src_tbl_nm);
EXCEPTION
WHEN OTHERS THEN
   pkg_mig_utils.log_msg (sentence, SUBSTR (SQLERRM, 1, max_ora_message_length));
   raise_application_error (g_app_error_code, sentence || '-' || SUBSTR (SQLERRM, 1, max_ora_message_length));
END;

/*
    Sample procedure to init migration using dynamic statements

    In this example all data come from a single source table where all
    legacy (COBOL) data have been aggregated into a single table
*/
PROCEDURE start_mig(v_year varchar2) AS
my_tbls          oranametable    := NEW oranametable ('TABLE1', 'TABLE2');

tot_rows         NUMBER;
tot_rows_stmnt   VARCHAR2 (256);
copy_stmnt       VARCHAR2 (4000);
BEGIN
pkg_mig_utils.start_log ('MY_MIG_AREA', v_year);
pkg_mig_utils.log_info ('Starting migration ....');

-- Sample dynamic insert statements

area_sql_table('TABLE1') := 'INSERT INTO TABLE1 SELECT * FROM SRC_TABLE WHERE MIG_YEAR = ' || v_year;
area_sql_table('TABLE2') := 'INSERT INTO TABLE2 SELECT * FROM SRC_TABLE WHERE MIG_YEAR = ' || v_year;

-- Perform migration for all tables

mig_data ('SRC_TABLE', my_tbls);

pkg_mig_utils.log_info ('Completed migration ...');
pkg_mig_utils.end_log;
END;

END;
/