Oracle, Web, Script, SQLserver, Tips & Trick

October 22, 2010

Import Dari Excel ke Oracle Tables

Filed under: oracle — sikathabis @ 3:41 am
Tags:

DECLARE
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
args OLE2.OBJ_TYPE;
ctr NUMBER(12);
cols NUMBER(2);
name_var1 VARCHAR2(2000);
name_var2 VARCHAR2(2000);
name_var3 VARCHAR2(2000);
name_var4 VARCHAR2(2000);
filename varchar2(100);
PROCEDURE OLEARG IS
args OLE2.OBJ_TYPE;
BEGIN
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,ctr); — row value
ole2.add_arg(args,cols); — column value
cell := ole2.GET_OBJ_PROPERTY(worksheet,’Cells’,args); — initializing cell
ole2.destroy_arglist(args);
END;
BEGIN
filename :=GET_FILE_NAME(‘c:\’, File_Filter=>’Excel Files (*.xls)|*.xls|’); — to pick the file
application := OLE2.CREATE_OBJ(‘Excel.Application’);
ole2.set_property(application,’Visible’,’true’);
workbooks := OLE2.GET_OBJ_PROPERTY(application, ‘Workbooks’);
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,filename); –‘c:\13s002.xls’); — file path and name
workbook := ole2.GET_OBJ_PROPERTY(workbooks,’Open’,args);
ole2.destroy_arglist(args);
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,’Sheet1′);
worksheet := ole2.GET_OBJ_PROPERTY(workbook,’Worksheets’,args);
ole2.destroy_arglist(args);
ctr := 2; –row number
cols := 1; — column number
FIRST_RECORD;
LOOP
OLEARG;
name_var1 := ole2.get_char_property(cell,’Value’); –cell value of the argument
cols := cols+1;
OLEARG;
name_var2 := ole2.get_char_property(cell,’Value’); –cell value of the argument
cols := cols+1;
OLEARG;
name_var3 := ole2.get_char_property(cell,’Value’); –cell value of the argument
cols := cols+1;
OLEARG;
name_var4 := ole2.get_num_property(cell,’Value’); –cell number value of the argument
IF ctr = 1 then
:tf1 := name_var1;
:tf2 := name_var2;
:tf3 := name_var3;
:tf4 := name_var4;
ELSE
NEXT_RECORD;
:tf1 := name_var1;
:tf2 := name_var2;
:tf3 := name_var3;
:tf4 := name_var4;
END IF;
EXIT WHEN length(name_var1) = 0 or length(name_var1) is null;
ctr := ctr + 1;
cols := 1;
END LOOP;
OLE2.INVOKE(application,’Quit’);
OLE2.RELEASE_OBJ(cell);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
END;
LINKS

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Teknologi Pendidikan

Just another WordPress.com weblog

KATAKAMI

Jurnalisme Yang Dapat Dipercaya

Tonk Kosonk Baonk Bunyinya

Penampungan Serapah Tanpa Sumpah

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Bodoh Yang Tak Kunjung Pandai

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server

neinframe

Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Keluarga Andrivito

-- Andrivito's Journal --

DRK™

This site is about Latest NEWS, Articles, Videos, Books, Course, Presentations, Tips & Tricks related to I.T Pro's

Gaurav Saxena

|| तत् त्वम् असि ||

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa

TuanSUFI

Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku

angkasa13

Ceritanya saya, Pikirannya saya, Hatinya saya

sikathabis

sikathabis semua informasi disini

%d bloggers like this: