Database Replace Function to change profile option values

Posted on July 30, 2008. Filed under: Configuration, Database, DBA/Admin Tips, Discoverer, R12, Scripts, TroubleShooting | Tags: , , |

Heres an example of using the replace() to update profile_option_value column in fnd_profile_option_values table.

Scenario: Imagine you did a clone from DEV to TEST and in DEV there were lot of profile option values set at the responsibility and user level for the discoverer viewer url which came as is to TEST. Now you want to change the values in fnd_profile_option_values to refer to TEST.

DEV Discoverer Viewer URL: https://discodev.mydomain.com/discoverer/viewer?Connect=%5BAPPS_
SECURE]

TEST Discoverer VIewer URL: https://discotest.mydomain.com/discoverer/viewer?Connect=%5BAPPS_SECURE%5D

SQL to change the values

1. create table fnd_povbk as select * from fnd_profile_option_values; [  backup just in case you mess up ]

update fnd_profile_option_values set profile_option_value = replace(profile_option_value,’https://discodev.mydomain.com’,’https://discotest.mydomain.com’) where profile_option_value like ‘https://discodev.mydomain.com%’;

TADA !!! values replaced.

cheers,
OraclePitStop

Advertisements

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: