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..??
|
] 
Blog
Search
Help
Members
Register
Login
Home
