OraFAQ Forum: Data Integration » convert varchar2 to number datatype in warehouse builder

Home » Developer & Programmer » Data Integration » convert varchar2 to number datatype in warehouse builder (oarcle 10g , owb 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

convert varchar2 to number datatype in warehouse builder [message #298799]

Thu, 07 February 2008 14:39

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

Report message to a moderator

Re: convert varchar2 to number datatype in warehouse builder [message #298807 is a reply to message #298799]

Thu, 07 February 2008 15:15

Littlefoot
Messages: 21823
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>

Report message to a moderator

Re: convert varchar2 to number datatype in warehouse builder [message #299255 is a reply to message #298799]

Mon, 11 February 2008 01:00

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

Report message to a moderator

Previous Topic:	How do keep track of failure records while bulk insert operation in owb
Next Topic:	OWB help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 05:32:32 CST 2024