Import Data from Excel to Oracle Forms [message #588117] |
Fri, 21 June 2013 01:28 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/1ddf5b1f639702698f84c4f5b8b12085?s=64&d=mm&r=g) |
mericyildirim
Messages: 6 Registered: August 2011
|
Junior Member |
|
|
Dear All,
I have designed a form to import data from an excel worksheet into an Oracle Form and It works pretty fine.
I use either Ole2.get_char_property or Ole2.get_num_property depending to the property of the column to read data but there are some columns used by both property which means that database is designed as VARCHAR2. The user can maintain either a numeric or characteristic data there. So I am looking for a solution to be able to make the program more flexible that he can read the property of the cell in excel file and decide what to use between Ole2.get_char_property and Ole2.get_num_property to read from excel file properly.
I would be happy if anyone can guide me who have experience on it ? I am also open if you have any other recommendation to solve the problem regarding reading from Varchar2 columns.
I thank you so much for all from now on.
Kind Regards,
Dr. Meriç Yıldırım
|
|
|
Re: Import Data from Excel to Oracle Forms [message #588132 is a reply to message #588117] |
Fri, 21 June 2013 03:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I never did what you are doing, so the following might not be meaningful at all. However, let me try.
Google says that OLE2.GET_NUM_PROPERTY "Gets a number value from an OLE2 Automation Object". If it raises an error when it finds anything but a number, you might use that failure and GET_CHAR_PROPERTY instead. Here's what I was thinking of (obviously, untested and possibly very stupid):
begin
x := OLE2.Get_Num_Property(Map, 'GetMapCenterX');
exception
when value_error then -- I don't know whether it is raised; trying to avoid WHEN OTHERS
x := OLE2.Get_Char_Property(Map,'GetMapCenterX');
end;
Pure SQL example (I'm familiar with) looks like this:SQL> declare
2 x number;
3 begin
4 x := 'A';
5 end;
6 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4 As expected; you can't put a character into a NUMBER variable. Fix it as follows:SQL> declare
2 x number;
3 begin
4 x := 'A';
5 exception
6 when value_error then
7 x := 1;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
|
|
|