Home » Developer & Programmer » Forms » uploading data from excel file thru OLE2
uploading data from excel file thru OLE2 [message #237916] Wed, 16 May 2007 02:24 Go to next message
Sidhant
Messages: 5
Registered: November 2006
Junior Member
Dear Members,

i hv previously successfully uploaded a excel file into oracle table.But this time my file size is large.around 10000 records..but the critical problem is, iam giving the
record count from forms as a input.If i give the count just more than 351 records the system hangs from forms..else it uploads fine.
also my excel file is getting locked after the upload

Code is below..
-- In this method the no. of fields are fixed 'cause the client's data is in a fixed format.
-- The user has to input the no. of records he has to load in the start of the program.
Declare
MyWorkbooksCollection OLE2.OBJ_TYPE;
MYAPP ole2.obj_type;
workbooks ole2.obj_type;
MYBOOK ole2.obj_type;
worksheets ole2.obj_type;
MySheetsCollection ole2.obj_type;
MySheet ole2.obj_type;
worksheet2 ole2.obj_type;
mycell ole2.obj_type;
args ole2.list_type;
e_rec MONTHLY_PLANNING_DTL%rowtype;
cnt integer;
i integer;
--j INTEGER;
file_name varchar2(200);
LC$C Varchar2(120) := '' ;
LN$Num Pls_Integer := 1000000 ;
LN$Perc Pls_Integer := LN$Num / 100 ;
LN$n Pls_integer := 0;
ANS number;

Begin

SET_APPLICATION_PROPERTY(CURSOR_STYLE,'BUSY');

MyApp:=OLE2.Create_Obj('Excel.Application');
MyWorkbooksCollection:=OLE2.GET_OBJ_PROPERTY( MyApp, 'Workbooks' );

args := OLE2.CREATE_ARGLIST;

file_name := :data_load.text_item3;
cnt := :data_load.TEXT_ITEM_REC;
--message(cnt);
--pause;
OLE2.ADD_ARG(args, file_name);
ole2.set_property(myapp,'Visible','false');
MyBook:=OLE2.Invoke_Obj( MyWorkbooksCollection,'open', args );
MySheetsCollection:=OLE2.GET_OBJ_PROPERTY( MyBook,'Worksheets');
OLE2.DESTROY_ARGLIST(args);

--cnt:=OLE2.GET_NUM_PROPERTY( MySheetsCollection, 'Count' );

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG( args, 1 );
mySheet := OLE2.GET_OBJ_PROPERTY( MySheetsCollection, 'Item', args);
OLE2.DESTROY_ARGLIST( args);

FOR i IN 1..cnt LOOP


---for customer_code

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );--row
OLE2.ADD_ARG(args, 1 );-- col
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.customer_code:= OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' ) ;

-- customer desc
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 2 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.customer_desc:= OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' ) ;

-- oa no
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 3 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.oa_no:= OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' ) ;

-- oa date
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 4 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.oa_date:= to_date(OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' ),'DD-MON-RRRR') ;

-- delivery date
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 5 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.delivery_date:= to_date(OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' ),'DD-MON-RRRR') ;

-- DP date
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 6 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.dp_date:= to_date(OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' ),'DD-MON-RRRR') ;

-- product code
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 7 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.product_code:= OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' ) ;

-- product desc
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 8 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.product_desc:= OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' ) ;

-- grade
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 9 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.grade_no:= OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' ) ;

-- category
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 10 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.category:= OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' ) ;

-- indent quantity
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 11 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.indent_qty := to_number(OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' )) ;

--- balance quantity

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 12 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.balance_qty := to_number(OLE2.GET_CHAR_PROPERTY(MyCell, 'Text')) ;

--- value

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 13 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.value := to_number(OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' )) ;

--- stock

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 14 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.stock := to_number(OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' )) ;

--- pending quantity
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, i );
OLE2.ADD_ARG(args, 15 );
MyCell:=OLE2.GET_OBJ_PROPERTY(MySheet,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
e_rec.pending_qty := to_number(OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' )) ;

--if e_rec.customer_code is not null then
/* INSERT INTO MONTHLY_PLANNING_DTL
VALUES (null,e_rec.customer_code,e_rec.customer_desc,e_rec.oa_no,e_rec.oa_date,e_rec.delivery_date,e_rec.dp_date,e_rec.product_code,e_rec.pr oduct_desc,e_rec.grade_no,
e_rec.category,e_rec.indent_qty,e_rec.balance_qty,e_rec.value,e_rec.stock,e_rec.pending_qty);
commit; */
--end if;
INSERT INTO MONTHLY_PLANNING_DTL
(customer_code)
VALUES (e_rec.customer_code);
commit;

END LOOP;

Clear_Message ;
For i IN 1..LN$Num Loop
If mod(i,LN$Perc) = 0 Then
LN$n := LN$n + 1 ;
LC$C := LC$C || '|' ;
Message( Ltrim(To_char( LN$n ) ) || '%' || LC$C, no_acknowledge ) ;
synchronize ;
End if ;
End loop ;

SET_APPLICATION_PROPERTY(CURSOR_STYLE,'DEFAULT');
SET_ALERT_PROPERTY('NOTE',TITLE,'Note');
SET_ALERT_PROPERTY('NOTE',ALERT_MESSAGE_TEXT,' Process Complete!! ');
clear_message;
ANS := SHOW_ALERT('NOTE');

OLE2.RELEASE_OBJ(MyCell);

OLE2.RELEASE_OBJ(mySheet);
OLE2.RELEASE_OBJ(MyApp);
OLE2.RELEASE_OBJ(MyWorkbooksCollection);

End;

----whats the problem can any one help..??
Re: uploading data from excel file thru OLE2 [message #238150 is a reply to message #237916] Wed, 16 May 2007 19:27 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have a look at:
Working with Excel via OLE2
http://www.orafaq.com/forum/t/72811/67467/
Merge two cells in Excel using ole2
http://www.orafaq.com/forum/t/77825/67467/
Error closing Excel file generated using OLE2
http://www.orafaq.com/forum/t/77351/67467/
How to wrap text in excel cell using ole2
http://forums.oracle.com/forums/thread.jspa;jsessionid=8d92200830d62336f40f5aad44efb36ee981f4e6c19e.e34QbhuKaxmMai0MaNeMb3eKb390?mess ageID=1242535&#1242535
Forms to Excel example with webutil
http://forums.oracle.com/forums/thread.jspa?messageID=1430799
Another Forms to Excel example
http://www.orafaq.com/forum/t/81537/67467/

David
Re: uploading data from excel file thru OLE2 [message #239314 is a reply to message #237916] Tue, 22 May 2007 00:46 Go to previous messageGo to next message
mustaf_82
Messages: 20
Registered: May 2007
Location: UAE
Junior Member

Hint: Check the Tablespace of this table. May be you are getting over quota
Re: uploading data from excel file thru OLE2 [message #302218 is a reply to message #237916] Sun, 24 February 2008 23:16 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi All,

Whether its possible to export the data from oracle reports3.0 to excel? How?
Thanks.

bye
Karthik
Re: uploading data from excel file thru OLE2 [message #302487 is a reply to message #237916] Mon, 25 February 2008 21:55 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Same like karthick

I need, from report to excel is it any possible?


wbr

kanish
Re: uploading data from excel file thru OLE2 [message #302732 is a reply to message #302487] Tue, 26 February 2008 18:57 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Why use Oracle Reports? Why not launch a PL/SQL script that writes a 'csv' file? Or a 'c' program if you are happier writing in 'c'?

David
Previous Topic: Displaying LOV in list item
Next Topic: How i can union cells in excel?
Goto Forum:
  


Current Time: Sun Dec 04 10:51:57 CST 2016

Total time taken to generate the page: 0.07589 seconds