| 305500 Non oracle exception [message #474553] |
Tue, 07 September 2010 03:37  |
|
|
I am trying to upload excel file into oracle database.the following code has been writen but i am getting non-exception oracle error. the code is done on when button pressed
"
DECLARE
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
args OLE2.OBJ_TYPE;
ctr NUMBER(12);
cols NUMBER(3);
VAL1 VARCHAR2(100);
filename varchar2(100);
m_count number;
Check_file text_io.file_type;
no_file exception;
PRAGMA exception_INIT (no_file, -302000);
PROCEDURE OLEARG IS
args OLE2.OBJ_TYPE;
BEGIN
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,ctr); -- row value
ole2.add_arg(args,cols); -- column value
cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell
ole2.destroy_arglist(args);
END;
BEGIN
SYNCHRONIZE;
filename :=GET_FILE_NAME('c:\', File_Filter=>'Excel Files (*.xls)|*.xls|'); -- to pick the file
--filename :=GET_FILE_NAME(:m_table_name, File_Filter=>'Excel Files (*.xls)|*.xls|'); -- to pick the file
Check_file := TEXT_IO.FOPEN(filename,'R');
TEXT_IO.FCLOSE(Check_file);
application := OLE2.CREATE_OBJ('Excel.Application');
ole2.set_property(application,'Visible','true');
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,filename); --'c:\13s002.xls'); -- file path and name
workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);
ole2.destroy_arglist(args);
args := OLE2.CREATE_ARGLIST;
-- ole2.add_arg(args,'Sheet1');
ole2.add_arg(args,'Sheet1');
worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);
ole2.destroy_arglist(args);
--ctr := 2; --row number
ctr := 2;
cols := 1; -- column number
--FIRST_RECORD;
LOOP
OLEARG; VAL1 := ole2.get_CHAR_property(cell,'Value');
-- EXIT WHEN length(VAL1) = 0 ;--- or NVL(VAL1,0)= 0;
EXIT WHEN SUBSTR(VAL1,1,2) != 'BU' ;--- or NVL(VAL1,0)= 0;
LOAD_MAH(VAL1);
ctr := ctr + 1;
cols := 1;
END LOOP;
DELETE AIC_PAKL_BUND_DETAIL
WHERE ABPBD_AIC_BAR_CODE IS NULL;
:SYSTEM.MESSAGE_LEVEL := '25';
COMMIT;
:SYSTEM.MESSAGE_LEVEL := '0';
OLE2.INVOKE(application,'Quit');
OLE2.RELEASE_OBJ(cell);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
exception
WHEN no_file THEN
MESSAGE('file not found.');
MESSAGE('file not found.');
WHEN OTHERS THEN
MESSAGE(sqlerrm);
PAUSE;
END; "
|
|
|
|
| Re: 305500 Non oracle exception [message #474588 is a reply to message #474553] |
Tue, 07 September 2010 07:20  |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) When posting code please use [code] tags. You have been asked this before.
2) Remove the exception handler and you should be able to find out which line of code is giving the error.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
