Oracle - change variable value for multiple objects

Question

I was wondering if it was possible to write a script where I change the value for a variable in multiple objects.

I ran the following query to get a list of the objects that need to be changed:

select distinct owner, name, type from dba_source
where lower(text) like '%environment_v%';

Is there a script I can write to change the value of environment_v from 10 to 20 in all of those objects?

thanks

score 0 · Answer 1 · answered Dec 05 '17 at 03:06

This is a problem you almost certainly do NOT want to fully automate. The below code will find the relevant objects and return their DDL. But changing the code and running the code should be done manually.

Correctly parsing and changing code is a difficult problem. The search string you are using returns many false-positives in the SYS schemas on 12.2. - you probably do not want to change those objects.

--Create DDL statements that contain "%environment_v%".
--These statements must be modified to include the new code.
select owner, object_type, object_name, dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
    --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
    select
        owner,
        --Java object names may need to be converted with DBMS_JAVA.LONGNAME.
        --That code is not included since many database don't have Java installed.
        object_name,
        decode(object_type,
            'DATABASE LINK',      'DB_LINK',
            'JOB',                'PROCOBJ',
            'RULE SET',           'PROCOBJ',
            'RULE',               'PROCOBJ',
            'EVALUATION CONTEXT', 'PROCOBJ',
            'PACKAGE',            'PACKAGE_SPEC',
            'PACKAGE BODY',       'PACKAGE_BODY',
            'TYPE',               'TYPE_SPEC',
            'TYPE BODY',          'TYPE_BODY',
            'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',
            'QUEUE',              'AQ_QUEUE',
            'JAVA CLASS',         'JAVA_CLASS',
            'JAVA TYPE',          'JAVA_TYPE',
            'JAVA SOURCE',        'JAVA_SOURCE',
            'JAVA RESOURCE',      'JAVA_RESOURCE',
            object_type
        ) object_type
    from dba_objects 
    where
        --These objects are included with other object types.
        object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
           'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
        --Ignore system-generated types that support collection processing.
        and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
        --Exclude nested tables, their DDL is part of their parent table.
        and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
        --Exclude overflow segments, their DDL is part of their parent table.
        and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
        --Only look for objects with a specific variable.
        and (owner, object_name, object_type) in
        (
            select owner, name, type
            from dba_source
            where lower(text) like '%environment_v%'
        )
)
order by owner, object_type, object_name;

The above query is based on this answer. That answer has been edited 10 times, and I've used it on thousands of objects, so it should be a pretty good starting point for generating DDL.

Ideally you would make these changes in version-controlled text files, not directly on a database.

Oracle - change variable value for multiple objects

1 Answers1