1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Number to string

Discussion in 'Oracle Webcenter Suite (formerly Oracle ECM)' started by vibecy, Aug 23, 2010.

  1. vibecy

    vibecy Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Hello there,

    One of my clients has requested a field change from number to string, as their requirements evolved. They are on Oracle UCM 10g, they GUI does not allow their admin to change it.

    How can I go about it to make the change? Just update the database design, or should I follow some additional steps to do so?

    Regards,
    vibecy
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    I don't think the type can be changed from the frontend, configuration I suggest you create a new String field from front end. Then from the backend go to the docmeta table and

    1 - Run an update to populate the new table column with the old column data

    2 - Drop the old table column

    3 - Re-name the new column to the original column name
     
  3. ericb

    ericb Active Member

    Messages:
    28
    Likes Received:
    5
    Trophy Points:
    90
    Location:
    Venlo, The Netherlands
    Hi Vibecy,

    Where is this numeric field used? Only as custom metadata field to store information in? Or are they using it in custom components, rules or profiles? In this last situation it takes more effort to change it. A comparison like xFieldNo = 1 is naturally different to xFieldString = `one`.

    I suggest that you create a new metadata field with almost same name in the UCM Configuration Manager. Only then this field should be type string. Then you update the database design & publish the schema.
    Create a pl/sql procedure who does something like this

    #
    Code (SQL):
    CREATE OR REPLACE migrate_data
    #     cursor c_metadata
    #      IS
    #       SELECT did
    #       ,        xfieldno
    #       ,        xfieldstring
    #      FROM  docmeta;
    #
    #  BEGIN
    #      FOR r_metadata IN c_metadata loop
    #           UPDATE docmeta
    #           SET xfieldstring := to_char(r_metadata.xfieldno)
    #           WHERE did = r_metadata.did;    
    #
    #      END loop;
    #
    # END;
    # /
    Execute the procedure in the database and commit the changes.

    Use the UCM Configuration Manager again to delete the numeric field and make the string-field the active field, update the database design and publish the schema.

    Allthough it might be obvious, I am still going to mention it: please try this in your test-environment first.

    I hope my suggestion is usefull for you.

    Kind regards,

    Eric