Convert Between Gregorian & Hijri Calendars Using Oracle’s PL/SQL

Working with Hijri calendar in Oracle can get frustrating especially with date conversion from/to Gregorian where results can be off by 2 days in some cases.

Here is a solution that does date conversion through lookup and totally eliminates the dependency on Oracle’s to_date function. If a converted date happens to be off, just update the lookup table with correct values.

1. Create Lookup Table

drop table date_conversion;

create table date_conversion (
seq number(10),
h_date varchar2(10),
g_date varchar2(10),
constraint date_conversion_pk primary key (seq) using index tablespace users
) pctfree 0;

create index date_conversion_h_ix on date_conversion (h_date) tablespace users;

create index date_conversion_g_ix on date_conversion (g_date) tablespace users;

2. Insert data in Lookup Table

You can insert as many years as you like, 10 years account for about 3,500 rows only.

Just as an example:

insert into date_conversion values (1, ‘1419/01/01’, ‘1998/04/28’);
insert into date_conversion values (2, ‘1419/01/02’, ‘1998/04/29’);
.
.
insert into date_conversion values (3715, ‘1429/06/24’, ‘2008/06/29’);

commit;

In real life, you would have to do this through a loop or a more efficient way.

3. Create Stored Procedures

These functions will be called to perform calendar conversion and date math.

CREATE OR REPLACE
FUNCTION hijri_add(in_h_date varchar2, in_days in number)
return varchar2 IS

out_date varchar2(10);

begin

select h_date into out_date
from date_conversion
where seq =
(select seq + in_days
from date_conversion
where h_date = in_h_date);

return out_date;

end;
/

CREATE OR REPLACE
FUNCTION to_gregorian(in_h_date varchar2)
return varchar2 IS
out_date varchar2(10);
begin
select g_date into out_date
from date_conversion
where h_date = in_h_date;
return out_date;
end;
/

CREATE OR REPLACE
FUNCTION to_hijri(in_g_date varchar2)
return varchar2 IS
out_date varchar2(10);
begin
select h_date into out_date
from date_conversion
where g_date = in_g_date;
return out_date;
end;
/

Examples

select to_hijri(to_char(sysdate, ‘YYYY/MM/DD’)) hijri_date from dual;

HIJRI_DATE
————-
1429/06/24

select to_gregorian (‘1429/06/24’) “Gregorian Date” from dual;

Gregorian Date
—————
2008/06/29

select hijri_add (‘1429/06/02’, 22) “New Date” from dual;

New Date
———–
1429/06/24

Performance Enhancement

This table is ideal to be placed in keep cache, also remember to cache the indexes too. Alternately, you can cache the entire table in PL/SQL table. This will consume some of PGA memory depends on the amount of data in the lookup table (PL/SQL table will be cached for every session not just 1 time).

Convert Between Gregorian & Hijri Calendars Using Oracle to_date

There are two ways to convert from/to Hijri Calendar (also called Hijrah Calendar):

1) Use Oracle’s function to_date. This is discussed in this entry
2) Build your own conversion functions. You can read about this here

Oracle will convert from nls_calendar in to_date function to nls_calendar session parameter in your session. If both calendars are the same value then no conversion will occur.

Here are some examples:

— Show nls_calendar nls_session_parameters
SQL> select value from nls_session_parameters
2 where parameter = ‘NLS_CALENDAR’;

VALUE
———————
GREGORIAN

— Convert from Hijri calendar to Gregorian Calendar
SQL>
SQL> select col1 hijrah_date,
to_date(col1,’dd/mm/yyyy’, ‘nls_calendar=”Arabic Hijrah”’) to_gregorian
2 from hijrah_date;

HIJRAH_DATE TO_GREGOR
——————– ———
03/01/1429 12-JAN-08
03/02/1429 11-FEB-08

— Convert from Gregorian Calendar to Hijri
SQL> alter session set nls_calendar=’Arabic Hijrah’;

Session altered.

SQL> select value from nls_session_parameters
2 where parameter = ‘NLS_CALENDAR’;

VALUE
———————
Arabic Hijrah

SQL>
SQL> select col1 gregorian_date, to_date(col1,’dd/mm/yyyy’, ‘nls_calendar=”Gregorian”’) to_hijrah
2 from gregorian_date;

GREGORIAN_DATE TO_HIJRAH
——————– ———————
25-Jun-2008 20 جمادي الثانية 1429
17-Mar-2008 09 ربيع الأول 1429

— Convert from Greorgian Calendar to Hijri and show date in English

alter session set nls_calendar=’English Hijrah’;

select col1 hijrah_date, to_date(col1,’dd/mm/yyyy’, ‘nls_calendar=”GREGORIAN”’) to_gregorian
from gregorian_date;

Session altered.

HIJRAH_DATE TO_GREGORIAN
——————– ————————-
25-Jun-2008 20 Jamada El Thaniah 1429
17-Mar-2008 09 Rabi’ Awwal 1429

2 rows selected.

Hazem Ameen
Senior Oracle DBA

Arguments Against Big Datafiles

I was running a health check on 10gR2 database and came across a tablespace with a single data file sized at 50 GB. We normally select datafile standard size between 2-6 GB range depending on how big the total database size is expected to be.

Here are some points why:

  1. The bigger the file, the harder it gets to tune I/O contention issues especially when your disks are not stripped. In this case, you will have to move high I/O tables to a different tablespace (possible downtime because of unusable indexes).
  2. ASM sometimes do I/O rebalancing among disks. Imagine doing 50 GB copy from one disk to another and the impact on your system.
  3. Recovering big datafiles is definitely another concern because of the prolonged downtime.
  4. Running dbv (dbverify) will take much longer unless you try to run it in parallel.

Hopefully this will slow you down from using “create bigfile tablespace”

Hazem Ameen
Senior Oracle DBA

Storing Arabic Characters in Western Character-set (WE8ISO8859P1) Database

I’ve seen an entire production system running with this setting:
DB Character-set: WE8ISO8859P1
Client NLS_LANG: WE8ISO8859P1

They were storing Arabic characters just fine.

It confused me as how this configuration could store Arabic correctly, and as such, I had to involve Oracle support. The answer from Oracle support was it could work but it is not supported.

Please don’t try it even though it’s good to know this can work.

The main issue you will face with this configuration is exchanging data with other systems whether using db link or exp/imp/data pump. Oracle simply will not do character-set conversion like it would do between other supported Arabic character-set (AR8ISO8859P6, AR8MSWIN1256, etc…).

Here are the steps to move data in such situation:

1. From source database WE8ISO8859P1 spool data to text file comma delimited.

2. From target database with character-set AR8MSWIN1256 create external table with character-set AR8MSWIN1256 (example below)

3. Perform an insert statement at the target.

Example of an external table with AR8MSWIN1256 character-set

SQL> CREATE TABLE read_in_arabic2
2 ( col1 VARCHAR2(255),
3 col2 VARCHAR2(255)
4 )
5 ORGANIZATION EXTERNAL
6 ( TYPE ORACLE_LOADER
7 DEFAULT DIRECTORY “DUMP_DIR”
8 ACCESS PARAMETERS
9 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET AR8MSWIN1256
10 BADFILE dump_dir:’atext.bad’
11 LOGFILE dump_dir:’atext.log’
12 READSIZE 1048576
13 FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ LDRTRIM
14 MISSING FIELD VALUES ARE NULL
15 REJECT ROWS WITH ALL NULL FIELDS
16 (
17 col1 CHAR(255) TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘,
18 col2 CHAR(255) TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
19 )
20 )
21 LOCATION
22 ( “DUMP_DIR”:’atext.txt’
23 )
24 )
25 REJECT LIMIT UNLIMITED PARALLEL 4;

Hazem Ameen
Senior Oracle DBA

Updating Millions of Rows (Merge vs. Bulk Collect)

For a 9.2.0.5 database, I have been asked to add few columns and update them with new values from another table. Base table contained 35 million rows.

To accomplish this, we researched 3 solutions: update via insert, merge statement and pl/sql with bulk collect.

 

Update via Insert

With this method, you don’t execute any updates, instead you create a new table with the old and new data.

Here are the steps:

  1. Create a new empty table with both old and new columns.
  2. Insert data using append hint into the new table.
  3. Recreate indexes on new table.
  4. Gather statistics on new table.
  5. Rename old table or drop.
  6. Rename new table to old name.

Pros:

  • The fastest method to finish.
  • Not complicated, steps are straight forward.

Cons:

  • When executing steps 5 & 6 there is a sub-second where the table is not accessible. Even that tiny bit is not acceptable in our environment.
  • If you have materialized view log or CDC (change data capture), you would have to drop it and then recreate it.

We didn’t benchmark this method because it wouldn’t work in our environment due the sub-second unavailability of base table.

 

Merge Statement

Use Oracle built-in function merge introduced in 9i.

Pros:

  • Easiest to implement, just 1 SQL statement.

Cons:

  • Merge statement was very slow. It took 17 hours to complete which was unacceptable.

 

Bulk Collect

Using rowid to perform bulk updates on the base table.

Here are the steps:

  1. Add/populate rowid of the base table to new table containing new data.
  2. If possible, try to sort the new data in the same way like base table. In my case, we sorted the new data by the primary key. This will reduce multiple reads/writes of the same base table blocks.

Pros:

  • It took 74 minutes down from 17 hours

Cons:

  • Writing pl/sql was a bit of pain and must be adjusted and retested every time there is a change.
  • You might have to drop materialized view log and change data capture tables and then recreate them again after the operation finishes.

Here is sample code for bulk collect

DECLARE
-- source table cursor (only columns to be updated)

CURSOR base_table_cur IS
SELECT rowid, col1, col2
FROM   base_table;

type base_type  IS TABLE OF base_table_cur%ROWTYPE  INDEX BY PLS_INTEGER;

base_tab base_TYPE;

-- new data

CURSOR new_data_cur IS
SELECT row_id, new_col1, new_col2
FROM   new_data;

type new_data_type  IS TABLE OF new_data_cur%ROWTYPE  INDEX BY PLS_INTEGER;

new_data_tab new_data_TYPE;

type row_id_type  IS TABLE OF ROWID INDEX BY PLS_INTEGER;

row_id_tab row_id_TYPE;

TYPE rt_update_cols IS RECORD
(
new_col1 new_data.new_col1%type,
new_col2 new_data.new_col1 %type
);

TYPE update_cols_type IS TABLE OF rt_update_cols INDEX BY PLS_INTEGER;

update_cols_tab update_cols_type;

dml_errors EXCEPTION;

PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN

open base_table_cur;
open new_data_cur;

loop

fetch base_table_cur bulk collect into base_tab limit 5000;
fetch new_data_cur bulk collect into new_data_tab limit 5000;

FOR i in base_tab.FIRST .. base_tab.LAST LOOP
  row_id_tab(i) := new_data_tab(i).row_id;
  update_cols_tab(i).col1 := new_data_tab(i).new_col1;
  update_cols_tab(i).col2 := new_data_tab(i).new_col2;
end loop;

FORALL i IN base_tab.FIRST .. base_tab.LAST SAVE EXCEPTIONS

update (select col1, col2
              from base_table )
set row = update_cols_tab(i)
where rowid = row_id_tab(i);

commit;

exit when base_tab.count < 5000;

end loop;

commit;

close base_table_cur;
close new_data_cur;

EXCEPTION

WHEN dml_errors THEN

FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP

dbms_output.put_line('Output your error here');

END LOOP;

END;
/

 

Conclusion

Use insert via update if sub-second of downtime is acceptable

Use merge if you have small set data

Use pl/sql bulk collect if you have large set of data and even sub-second of downtime is not acceptable

Hazem Ameen
Senior Oracle DBA

Working in Saudi Arabia: Project Managers Mayhem

So you get off the plane coming from Chicago all happy, excited and energetic to start your first job in the kingdom of Saudi Arabia. You feel nothing can go wrong, things in your life are going in the right direction and under control.

Few weeks later in the job, you start telling yourself; there something really wrong about the project manager’s way of managing. There is hardly any project plan, you can totally forget about risk assessment, nor any high level design, because in most cases he lacks basic understanding on how technology works.

His e-mails usually consist of 3 types: “fyi (for your information)”, “fya (for your action)” and “any update”. If this is not enough, once in a while, he resends your email exactly without changing a word and puts his name on it. Isn’t this plagiarism?! But then when you talk with him, he feels he didn’t cross any line. You wonder if this is the work culture or the individual.

You totally convince yourself that it’s just matter of time before something big and bad happens to this person, and sure enough something big happens but not bad; he gets promoted. And then you watch everybody congratulating him on a well deserved promotion while you stand dumfounded.

Now after a few years of unforgettable experiences like this, it always makes my day when a newbie walks up to me and says: “shouldn’t the project manager come up a project plan” or “shouldn’t the project manager lead this activity”.

Here are some advices I give people:

It takes a while to adjust and adapt to the Saudian work culture, don’t become bitter and sarcastic. Give it time, it gets better. The tough period is the first six months.

Take it easy and always talk to people who where there before you, don’t quit on the spot or become constantly angry and not easy to talk to.

Try to befriend some Saudians, meet and talk with them outside the job environment. This will help you to understand the culture better, and you will not have the mentality of us vs. them.

From my experience here, I found out that the Saudian culture is generally laid back and relaxed, and there is also lots of job security more than I ever had back in the States. Surround yourself with positive, non-bitter people or your life will become very negative and sometimes unbearable.

Hazem Ameen
Senior Oracle DBA

How to Save Hindi Numbers in Your Database

A while back, I received a requirement to save Hindi numbers in our database.

It took some research and here’s how I solved it:

If your character-set is UTF8 then you can save Hindi numbers in varchar2 column type.

If your character-set is AR8MSWIN1256 or AR8ISO8859P6, then you can’t save Hindi numbers in varchar2 columns. You only can save them in nvarchar columns. In this case your national character-set (UTF16 or UTF8) would come in play.

Most likely you won’t be able to type Hindi numbers in SQLPlus, Toad, etc…, instead you can use unistr function to insert and query back the data.

Here is quick example to demonstrate how to insert and select numbers 123:

Insert into hindi_table (col1) values (unistr (‘\0661\0662\0663’));

Select col1 from hindi_table where col1 = unistr (‘\0661\0662\0663’);

Remember either varchar2/UTF8 character-set or nvarchar2/(UTF16 or UTF8) national character-set

Hazem Ameen
Senior Oracle DBA