Home » Developer & Programmer » Forms » how to get data from excel to oracle forms
|
|
|
|
|
|
| Re: how to get data from excel to oracle forms [message #120978 is a reply to message #120910] |
Wed, 25 May 2005 04:58  |
Gurusubramanyam
Messages: 79
Registered: July 2001
|
Member |
|
|
Hi,
You can use DDE to fetch the data from excel.
Here is an example of FORMS6i procedure code that reads the data from excel file and it inserts into to an oracle table.It handles the "Address" data type which is one column in the target table. You can customize this code according to your needs.
Hope this will be helpful to you.
Thanks & Regards,
G.S
PROCEDURE Proc_fetch_Excel(V_TAB_NAME IN VARCHAR2) IS
v_dir varchar2(50) ;
f_in_file Text_IO.File_Type;
v_data_value VARCHAR2(4000);
v_filename VARCHAR2(30000);
v_data varchar2(200);
i number :=0;
rec_mom mom%rowtype;
CURSOR Cur_tab(v_tab_name varchar2) IS
SELECT COLUMN_NAME ,DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME=V_TAB_NAME
ORDER BY COLUMN_ID;
n_col number:=1;
v_ff varchar2(50);
v_data_value1 varchar2(30000);
v_columns varchar2(30000);
v_insert_col varchar2(30000);
v_data_type varchar2(30000);
v_data_type1 varchar2(30000);
BEGIN
BEGIN
v_filename:=GET_FILE_NAME(v_dir,File_Filter=>'EXCEL Files (*.xls)|*.xls|');
for i in cur_tab(v_tab_name)
loop
BEGIN
if v_columns is null then
v_columns:= i.column_name;
v_data_type:=i.data_type;
else
v_columns:= v_columns||','||i.column_name;
v_data_type:=v_data_type||','||i.data_type;
end if;
n_col:=n_col+1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
end loop;
v_data_type1:=v_data_type;
BEGIN
DECLARE
AppID PLS_INTEGER;
ConvID PLS_INTEGER;
Buffer VARCHAR2(10000);
R NUMBER:=1;
V_TAB VARCHAR2(20);
N_POS NUMBER;
v_col1 varchar2(100);
v_col2 varchar2(100);
v_col3 varchar2(100);
v_col4 varchar2(100);
v_col5 varchar2(100);
v_col6 varchar2(100);
v_col7 varchar2(100);
v_col8 varchar2(100);
n_count_address number;
BEGIN
if instr(nvl(v_filename,'aa'),'.xls')=0 then
message('Invalid file type');
message('Invalid file type');
raise form_trigger_failure;
end if;
AppID := DDE.App_Begin('C:\program files\microsoft office\office\excel.exe '||v_filename,DDE.APP_MODE_MiniMIZED);
BEGIN
if instr(nvl(v_filename,'aa'),'.xls')=0 then
message('Invalid file type');
message('Invalid file type');
raise form_trigger_failure;
else
ConvID := DDE.Initiate('EXCEL', v_filename);
end if;
SELECT CHR(9)INTO V_TAB FROM DUAL;
LOOP
BUFFER:=NULL;
DDE.Request(ConvID, 'R'||R||'C1:R'||R||'C'||n_col, Buffer, DDE.CF_TEXT,30000);
IF substr(buffer,1,instr(buffer,chr(9)))= V_TAB and length(substr(buffer,1,instr(buffer,chr(9))))=1 THEN
EXIT;
END IF;
loop
if substr(v_data_type,1,instr(v_data_type,',')-1) like '%NUMBER%' then
if v_insert_col is not null then
v_insert_col:=v_insert_col||','||NVL(substr(buffer,1,instr(buffer,chr(9))),''||'NULL'||'');
v_insert_col:=nvl(replace(v_insert_col,chr(9)),''||'NULL'||'');--,''||'NULL'||'');
elsif v_insert_col is null then
v_insert_col:=NVL(substr(buffer,1,instr(buffer,chr(9))),''''||'NULL'||'''');
end if;
elsif substr(v_data_type,1,instr(v_data_type,',')-1) like '%CHAR%' then
if v_insert_col is not null then
v_insert_col:=v_insert_col||','||nvl(''''||substr(buffer,1,instr(buffer,chr(9))-1)||'''',''||'NULL'||'');
v_insert_col:=nvl(replace(v_insert_col,chr(9)),''||'NULL'||'');--,''||'NULL'||'');
elsif v_insert_col is null then
v_insert_col:=nvl(''''||substr(buffer,1,instr(buffer,chr(9))-1)||'''',''||'NULL'||'');
end if;
elsif substr(v_data_type,1,instr(v_data_type,',')-1) like '%DATE%' then
if v_insert_col is not null then
v_insert_col:=v_insert_col||','||nvl(''''||substr(buffer,1,instr(buffer,chr(9))-1)||'''',''||'NULL'||'');
v_insert_col:=nvl(replace(v_insert_col,chr(9)),''||'NULL'||'');--,''||'NULL'||'');
elsif v_insert_col is not null then
v_insert_col:=nvl(''''||substr(buffer,1,instr(buffer,chr(9))-1)||'''',''||'NULL'||'');
end if;
elsif substr(v_data_type,1,instr(v_data_type,',')-1) like '%ADDRESS%' then
select count(*) into n_count_address from user_type_attrs
where type_name ='ADDRESS';
v_insert_col:=v_insert_col||','||'address(';
loop
v_insert_col:=v_insert_col||','||nvl(''''||substr(buffer,1,instr(buffer,chr(9))-1)||'''','NULL');
n_count_address:=n_count_address-1;
if n_count_address=0 then
exit;
end if;
if instr(v_insert_col,'(,')>0 then
v_insert_col:=replace(v_insert_col,'(,','(');
end if;
buffer:=substr(buffer,instr(buffer,chr(9))+1);
end loop;
null;
v_insert_col:=v_insert_col||')';
end if;
BUFFER:=substr(buffer,instr(buffer,chr(9))+1);
v_data_type:=substr(v_data_type,instr(v_data_type,',')+1);
if instr(v_data_type,',')=0 then-- and length(v_data_type)>=4 then
v_data_type:=v_data_type||',';
end if;
if ltrim(rtrim(v_data_type)) is null then
exit;
end if;
end loop;
v_data_type:=v_data_type1;
synchronize;
DECLARE
connection_id EXEC_SQL.CONNTYPE;
bIsConnected BOOLEAN;
cursorID EXEC_SQL.CURSTYPE;
sqlstr VARCHAR2(1000);
nIgn PLS_INTEGER;
nRows PLS_INTEGER := 0;
nTimes PLS_INTEGER := 0;
mynum NUMBER;
begin
connection_id := EXEC_SQL.DEFAULT_CONNECTION;
bIsConnected := EXEC_SQL.IS_CONNECTED;
IF bIsConnected = FALSE THEN
RETURN;
END IF;
cursorID := EXEC_SQL.OPEN_CURSOR;
sqlstr := 'insert into ' ||V_TAB_name||'('||V_COLUMNS||')
values ('||V_INSERT_COL||')';
:txt_try:=sqlstr;
commit;
EXEC_SQL.PARSE(cursorID, sqlstr, exec_sql.V7);
nIgn := EXEC_SQL.EXECUTE(cursorID);
EXEC_SQL.CLOSE_CURSOR(cursorID);
EXEC_SQL.CLOSE_CONNECTION;
exception
when others then
MESSAGE(SQLERRM);
MESSAGE(SQLERRM);
END;
V_INSERT_COL:=NULL;
R:=R+1;
END LOOP;
dde.app_end(appid);
commit;
display_error;
END;
end;
end;
end;
END;
|
|
|
|
Goto Forum:
Current Time: Wed Sep 18 21:26:44 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
