Oracle, Web, Script, SQLserver, Tips & Trick

October 22, 2010

Script Import dari Excel ke Oracle (Oracle Form6i)

Filed under: dev6i,oracle — sikathabis @ 3:59 am
Tags:

–example
PROCEDURE ddepack IS
appid PLS_INTEGER;
convid PLS_INTEGER;
docid PLS_INTEGER;
conv_established BOOLEAN := FALSE;
buffer1 Varchar2(8);
buffer2 Varchar2(15);
buffer3 Varchar2(10);
buffer4 Varchar2(100);
buffer5 Varchar2(200);
buffer6 Varchar2(200);
buffer7 Varchar2(200);
buffer8 Varchar2(8);
buffer9 Varchar2(8);
buffer10 Varchar2(20);
buffer11 Varchar2(8);
buffer12 Varchar2(8);
DNO VARCHAR2(14);
Col1 Varchar2(8);
Col2 Varchar2(15);
Col3 Varchar2(10);
Col4 Varchar2(100);
Col5 Varchar2(200);
Col6 Varchar2(200);
Col7 Varchar2(200);
Col8 Varchar2(8);
Col9 Varchar2(8);
Col10 Varchar2(20);
Col11 Varchar2(8);
Col12 Varchar2(8);

LC VARCHAR2(6);
I NUMBER(2) := 2;
BEGIN
APPID := DDE.APP_BEGIN(‘C:\Program Files\Microsoft Office\Office10\excel.EXE’,
DDE.APP_MODE_MINIMIZED);
WHILE NOT conv_established LOOP
BEGIN
convid := DDE.INITIATE(‘excel’, ‘system’);
conv_established := TRUE;
EXCEPTION
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
conv_established := FALSE;
END;
END LOOP;
DDE.EXECUTE(convid, ‘[Open("c:\New_fa\bulk_template.xls")]’, 10000);
docid := DDE.INITIATE(‘excel’, ‘c:\New_fa\bulk_template.xls’);
LOOP
LC := ‘R’||I||’C1’;
DDE.REQUEST (docid, LC, buffer1, DDE.CF_TEXT, 10000);
Col1 := BUFFER1;
exit when SUBSTR(BUFFER1,1,LENGTH(BUFFER1)-2)= ‘END’ ;
IF SUBSTR(BUFFER1,1,LENGTH(BUFFER1)-2)= ‘END’ THEN
go_block(‘dept2’);
execute_query;
end if;
LC := ‘R’||I||’C2’;
DDE.REQUEST (docid, LC, buffer2, DDE.CF_TEXT, 10000);
col2 := BUFFER2;
LC := ‘R’||I||’C3’;
DDE.REQUEST (docid, LC, buffer3, DDE.CF_TEXT, 10000);
col3 := BUFFER3;
LC := ‘R’||I||’C4’;
DDE.REQUEST (docid, LC, buffer4, DDE.CF_TEXT, 10000);
col4 := BUFFER4;
LC := ‘R’||I||’C5’;
DDE.REQUEST (docid, LC, buffer5, DDE.CF_TEXT, 10000);
col5 := BUFFER5;
LC := ‘R’||I||’C6’;
DDE.REQUEST (docid, LC, buffer6, DDE.CF_TEXT, 10000);
col6 := BUFFER6;
LC := ‘R’||I||’C7’;
DDE.REQUEST (docid, LC, buffer7, DDE.CF_TEXT, 10000);
col7 := BUFFER7;
LC := ‘R’||I||’C8’;
DDE.REQUEST (docid, LC, buffer8, DDE.CF_TEXT, 10000);
col8 := BUFFER8;
LC := ‘R’||I||’C9’;
DDE.REQUEST (docid, LC, buffer9, DDE.CF_TEXT, 10000);
col9 := BUFFER9;
LC := ‘R’||I||’C10’;
DDE.REQUEST (docid, LC, buffer10, DDE.CF_TEXT, 10000);
col10 := BUFFER10;
LC := ‘R’||I||’C11’;
DDE.REQUEST (docid, LC, buffer11, DDE.CF_TEXT, 10000);
col11 := BUFFER11;
LC := ‘R’||I||’C12’;
DDE.REQUEST (docid, LC, buffer12, DDE.CF_TEXT, 10000);
col12 := BUFFER12;
INSERT INTO tmp_bulk_vchr_post VALUES(SUBSTR(BUFFER1,1,LENGTH(BUFFER1)-2),
to_date(SUBSTR(Col2,1,LENGTH(Col2)-2),’DD/MM/RRRR’) ,
SUBSTR(Col3,1,LENGTH(Col3)-2),
SUBSTR(Col4,1,LENGTH(Col4)-2),
SUBSTR(Col5,1,LENGTH(Col5)-2)||’ ‘||SUBSTR(Col6,1,LENGTH(Col6)-2)||’ ‘||SUBSTR(Col7,1,LENGTH(Col7)-2),
SUBSTR(Col8,1,LENGTH(Col8)-2),
SUBSTR(Col9,1,LENGTH(Col9)-2),
SUBSTR(Col10,1,LENGTH(Col10)-2),
SUBSTR(Col11,1,LENGTH(Col11)-2),
SUBSTR(Col12,1,LENGTH(Col12)-2));
COMMIT;
I := I+1;
END LOOP;
Links FAQ

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: