Re: update

Silvia Figueiredo wrote
> a column "identity" that is defined in as varchar,
> i think that was loaded as a number.

You _think_ it is loaded as a number. What makes you think this? Are you lacking leading zeroes? What exactly is the problem?

If number xx should be loaded as varchar 0000xx (which in my opinion would be a bad primary key) then try something like

    update my_table
    set my_column = lpad( my_column, 6, '0' );

Maybe my_column has leading blanks in your current situation:

    update my_table
    set my_column = lpad( ltrim(my_column), 6, '0' );

... or could even be negative:

    update my_table
    set my_column = to_char( to_number(my_column), 'S000000');

The latter would only work if *all* rows hold a number in my_column, for otherwise to_number will fail. The main thing is that you should first get a clear picture of your problem...

> "identity" is a foreign key in many tables!

If it is, then it is not defined as such in your database, for otherwise you could not have loaded the child records...

> I can fixed the probelm by severoul ways (delete and load again that
> information...), but is there a right way of doing it ?

Sure you should adjust your SQL*Loader script to prevent from the problem occuring during the next load again. So, give us some details on your datafile and loader script. If it is comma seperated, then you might use something like

    my_column "lpad(:my_column, 6, '0')"

or any other SQL statement enclosed in double quotes. Note the colon prefixing :my_column in the SQL statement.

Arjan. Received on Mon Apr 19 1999 - 15:35:55 CDT