Home » Developer & Programmer » Data Integration » convert varchar2 to number datatype in warehouse builder (oarcle 10g , owb 10g)
convert varchar2 to number datatype in warehouse builder [message #298799] Thu, 07 February 2008 14:39 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
HI,


i am kumar,i got one problem with data type convertion in OWB.

for instance i have to convert varchar2 data type to number datatype.

my source table contain column with varchar2, on target same column name is defined as number.

in order to populate data from source to colume i have to convert data type. how can i do that.

can any one send me guidelines to me. and some good link to read
for convert data type in OWB

regards,
kumar
Re: convert varchar2 to number datatype in warehouse builder [message #298807 is a reply to message #298799] Thu, 07 February 2008 15:15 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know Oracle Warehouse Builder, but - generally speaking - you don't have to do anything as long as there are numbers (and only numbers) stored in the VARCHAR2 column. Oracle will perform implicit conversion and successfully insert values into the NUMBER column.
SQL> create table test_n (col_n number);

Table created.

SQL> create table test_c (col_c varchar2(10));

Table created.

SQL> insert into test_c values ('1234');

1 row created.

SQL> insert into test_n select * from test_c;

1 row created.

SQL>


However, if there are characters in the VARCHAR2 column, you'll need to perform a more sophisticated strategy (discard characters and accept numbers), but that's probably another story.
SQL> insert into test_c values ('abc');

1 row created.

SQL> insert into test_n select * From test_c;
insert into test_n select * From test_c
                          *
ERROR at line 1:
ORA-01722: invalid number


SQL>
Re: convert varchar2 to number datatype in warehouse builder [message #299255 is a reply to message #298799] Mon, 11 February 2008 01:00 Go to previous message
sunilgond
Messages: 35
Registered: January 2008
Location: INDIA
Member

Hi ,


U just use one expression operator and create one output parameter,and write an expressin with new data type.


Regards,
Sunil Kumar gond
+919912125600
Satyam
Previous Topic: How do keep track of failure records while bulk insert operation in owb
Next Topic: OWB help
Goto Forum:
  


Current Time: Fri Mar 29 06:44:04 CDT 2024