How to import / export Excel or Access to Oracle DB

[ping99]

Hello all,

How to import Excel or Access into Oracle DB ?

How to export Oracle Database ( Table ) to Excel or Access ?

Please assist in writing code in PL SQL .

TIA

Ping

 

[KenCunningham]

What have you got so far?

 

[ping99]

Thanks for your reply,

So far, I used the following PL/SQL code to convert table data to excel. Sample code as follows:

I always had the error OLE2.OBJ_TYPE not found or undefined which is I don't understand, please assist me.



PROCEDURE EXCEL_FILE IS

cursor dtl is select * from accounts order by account_id

row _num number;
col_num number;
cell_val number;

app_acc OLE2.OBJ_TYPE;
dbs OLE2.OBJ_TYPE;
dc OLE2.OBJ_TYPE;
args_acc OLE2.OBJ_TYPE;
app OLE2.OBJ_TYPE;
args OLE2.LIST_TYPE;
args2 OLE2.LIST_TYPE;
ws OLE2.OBJ_TYPE;
wb OLE2.OBJ_TYPE;
wss OLE2.OBJ_TYPE;
wbs OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
ctr1 NUMBER := 0;
ctr2 NUMBER := 0;
ctr3 PLS_INTEGER := 0; --NUMBER := 0;
m_row number:=1;
m_len number:=0;

m_flag number:=0;

Begin

---------------- Initialise Excel

app := OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(app, 'Visible','True');
wbs := OLE2.GET_OBJ_PROPERTY(app,'Workbooks');
wb := OLE2.INVOKE_OBJ(wbs,'Add');
wss := OLE2.GET_OBJ_PROPERTY(app,'Worksheets');
ws := OLE2.INVOKE_OBJ(wss,'Add');

---

For I in 1..2 loop

args := OLE2.CREATE_ARGLIST;

OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Column

cell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);

if I=1 then
OLE2.SET_PROPERTY(cell, 'Value','Account_id');
elsif I=2 then
OLE2.SET_PROPERTY(cell, 'Value','Account_desc');
end if;

OLE2.RELEASE_OBJ(cell);
end loop;
--- Heading

-- Data

For J in Dtl loop

For I in 1..2 loop

-- Repeat Row
args := OLE2.CREATE_ARGLIST;

OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Column

cell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);

if I=1 then
OLE2.SET_PROPERTY(cell, 'Value',J.account_id);
elsif I=2 then
OLE2.SET_PROPERTY(cell, 'Value',J.account_desc);
end if
end loop
end loop


---- End

OLE2.RELEASE_OBJ(wbs);
OLE2.RELEASE_OBJ(ws);
OLE2.RELEASE_OBJ(wbs);
OLE2.RELEASE_OBJ(wb);
OLE2.RELEASE_OBJ(app);

END;