Wednesday, February 28, 2007

I have some simple solutions to validate Malaysian IC / (NewIc) in PL/SQL. This solution may not be the best but at least helps a little.
The format
YYMMDD######

CREATE OR REPLACE FUNCTION is_validic(char_in VARCHAR2) RETURN VARCHAR2 IS
n NUMBER;
incorrect EXCEPTION;
BEGIN
n:=0;
if to_date(substr(char_in,1,6),'YYMMDD') !=to_date(substr(char_in,1,6),'YYMMDD') then
n:=n+1;
end if;

if length(nvl(char_in,'0')) !=12 then
n:=n+1;
end if;

if n=0 then
return 'TRUE';
else
return 'FALSE';
end if;

EXCEPTION
WHEN OTHERS THEN
RETURN 'FALSE';
END is_validic;

Monday, February 12, 2007

Loading data into non-empty table
This information was gethered from
http://www.cise.ufl.edu/~jhammer/classes/Oracle/Oracle_bulk_loader.htm and is useful for others.

LOAD DATAINFILE APPEND INTO TABLE FIELDS TERMINATED BY ''()

If the tables you are loading into already contain data, you have three options:

APPEND
REPLACE
TRUNCATE


APPEND

If data already exists in the table, SQL*Loader appends the new rows to it. If data does not already exist, the new rows are simply loaded. You must have SELECT privilege to use the APPEND option.

REPLACE

With REPLACE, all rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table.

The row deletes cause any delete triggers defined on the table to fire. If DELETE CASCADE has been specified for the table, then the cascaded deletes are carried out.

TRUNCATE

Using this method, SQL*Loader uses the SQL TRUNCATE statement to achieve the best possible performance. For the TRUNCATE statement to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, SQL*Loader returns an error.

Once the integrity constraints have been disabled, DELETE CASCADE is no longer defined for the table. If the DELETE CASCADE functionality is needed, then the contents of the table must be manually deleted before the load begins.
About SQL*Loader / Oracle Bulk Loader
  1. SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads. http://www.orafaq.com/faq/what_is_sql_loader_and_what_is_it_used_for
  2. Example


I have a data files of inventories you want to load into your oracle database. my_data.txt

09-NOV-2006|Noke Shirt|68254701000037
09-NOV-2006|Adibas Pant|71565900330059
09-NOV-2006|Umbros Shoe|69840500270016


I want to load the data into this table below

SQL> CREATE TABLE my_inventory
2 (salesdate DATE,
3 product_name VARCHAR(50),
4 barcode VARCHAR(20));

Then the control file control.ctl

LOAD DATA
INFILE 'my_data.txt'
APPEND INTO TABLE my_inventory
FIELDS TERMINATED BY '|'
(salesdate ,
product_name,
barcode
)

To load the data simply invoke sqlldr command from my Oracle bin.
c:/ORACLE_HOME/bin/sqlldr /@ control=control.ctl

I will see below output created during the loading

Commit point reached - logical record count 1
Commit point reached - logical record count 2

Saturday, February 10, 2007

Finding the latest / max records
I've just came up with aniother problem of getting a set of results. Finding the latest records, Example:
IDMobilenocreated
1+9099001-FEB-06
2+9099002-FEB-06
3+9099102-FEB-06
4+9099101-FEB-06

The results must be like this , only 1 mobileno with the latest creation date
IDMobilenocreated
2+9099002-FEB-06
3+9099102-FEB-06

The answer:
select t.*
from (select a.*,dense_rank() over (partition by mobileno order by created desc) dr from my_table a) t
where dr=1;