hi, Im using OLE2 package to import data from Excel sheet. my procedure copies values into a Data Block, then it stores them into DB
application OLE2.obj_type;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
args OLE2.list_type;
....
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args,[row]);
OLE2.ADD_ARG(args, [col]);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
:block_vis.COL_ID:=OLE2.Get_Char_Property(cell,'Value');
...
ID field is a text value, and :BLOCK_VIS.COL_ID is VARCHAR2(10). I noticed that, when Excel sheet contains pseudo-numeric values (eg: 10, 20,...) , OLE2.GET_CHAR_PROPERTY reads them as numbers. furthermore, it truncates values to the first digit (ie: 10, 11,... are read as 1!). Vice versa, when values are preceeded by a single/double quote (eg: '10), they are read correctly. neither forcing excel column to Text works.
How can I fix this issue? I can not ask users to check every single cell value and put a preceeding quote.
thanks for help