use trigger to convert data type on insert 1

[GROKING]

I have a stored procedure that selects and inserts columns into a table. I have one column that I need to change the dataype from CHAR to_num before it inserts in the table because the table column is a number.

Rather than creating a temp table I thought I could use a trigger to convert it before insert TO_NUM(order_number).

Is this possible and how would I do this? Do I create a variable in the trigger, convert, then insert that variable into the table? I could not find any sample code for this situation.

Thanks for the assistance.

 

[LKBrwnDBA]

You do not need a trigger, if you have a stored procedure that SELECTS and INSERTS into another table, just modify the code to select TO_NUMBER({the_column}) into the other table's column!

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Groking, Are you saying that there are data coming in from a VARCHAR column that should end up in a NUMBER column? If the data from the VARCHAR column is, in fact, numeric, then, frankly, you don't need a trigger...you don't need a TO_NUMBER function...you don't need nuthin' <smile>...Oracle will automatically do a datatype conversion for you be just referring to the VARCHAR column from within your INSERT statement:

INSERT INTO <tablename> VALUES (...,<character expression that contains a numeric value>,...);

Please advise me if I have misunderstood your premise.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[GROKING]

Yes that is good info but won't work here.

The problem is the stored_procedure is pulling the select from a non-oracle datasource "mysql 5.0" using HS service and mysql ODBC driver. Casting crashes the ODBC connection. And I can't change the source table to a different datatype.

 

[GROKING]

SantaMufasa,

You have given me a present. That great if Oracle will convert form Varchar2 to Number on the procedure insert.
Then I don't need a trigger.

Thanks a bunch!