Adding Nodes

[ErmanArslansOracleBlog]

Okay. now it is aligned.

Send me the following;

1)select * from ad_appl_tops (in a good formatted way)
2)select  * from ad_timestamps (in a good formatted way)
3)check the triggers on ad_appl_tops and ad_timestamps tables (not editioning views). Send me their scripts, (If there are any)

[latifa]

Hi Erman,

here you are the output of :

1) ad_appl_tops.txt

2) I couldn't find any trigger on these tables in all_triggers or dba_triggers views.

Regards
Latifa

[ErmanArslansOracleBlog]

Send me the output of "select  * from ad_timestamps (in a good formatted way) "

[latifa]

here you are the output from:

2)select  * from ad_timestamps;

ad_timestamps.txt

Regards,
Latifa

[ErmanArslansOracleBlog]

You don't have these two records in ad_timestamps table.

INSTANTIATED_CURRENT_VIEW_SNAPSHOT
BACKFILLED_BUGS_IN_CURRENT_VIEW_SNAPSHOT

This is strange.. Because the update in adadmrat.sql tries to update these..

update ad_timestamps
    set attribute = l_new_apps_system_name || '*' || l_new_appltop_name
    where type in ('INSTANTIATED_CURRENT_VIEW_SNAPSHOT',
                   'BACKFILLED_BUGS_IN_CURRENT_VIEW_SNAPSHOT')
    and attribute = l_new_apps_system_name || '*' || l_old_appltop_name;

addnode.pl should add these actually.


Anyways, take backup of ad_appl_tops and ad_timestamps.
Modify adadmrat.sql script.
put a commit one line above the following;

commit <<--put commit here- >>
update ad_timestamps
    set attribute = l_new_apps_system_name || '*' || l_new_appltop_name
    where type in ('INSTANTIATED_CURRENT_VIEW_SNAPSHOT',
                   'BACKFILLED_BUGS_IN_CURRENT_VIEW_SNAPSHOT')
    and attribute = l_new_apps_system_name || '*' || l_old_appltop_name;

retry... Send me the output of ad_appl_tops and ad_timestamps after that.

[latifa]

Hi Erman,

please find attached the output of sql queries --> No changes

sql_outputs.txt

Regards,
Latifa

[ErmanArslansOracleBlog]

Sorry but there is abnormal situation here.
1)You don't have these two records in ad_timestamps table.

INSTANTIATED_CURRENT_VIEW_SNAPSHOT
BACKFILLED_BUGS_IN_CURRENT_VIEW_SNAPSHOT

2)update should not cause ORA-01403.
--

Lets put some more prompts and be sure where the sql is failing.

Modify the relevant part of the sql as below; (backup original sql, put propmts as below , save sql and rerun your failing adadnode )

prompt Updating AD_APPL_TOPS

    update ad_appl_tops
    set name = l_new_appltop_name
    where applications_system_name = l_new_apps_system_name
    and name = l_old_appltop_name;

    -- Update AD_TIMESTAMPS. Assume that the "apps-sys-name" portion in
    -- ATTRIBUTE has already been updated (The portion before the
    -- asterisk '*'. adadmprf.sql does this)

prompt successfully updated AD_APPL_TOPS
prompt now updating AD_TIMESTAMPS

    update ad_timestamps
    set attribute = l_new_apps_system_name || '*' || l_new_appltop_name
    where type in ('INSTANTIATED_CURRENT_VIEW_SNAPSHOT',
                   'BACKFILLED_BUGS_IN_CURRENT_VIEW_SNAPSHOT')
    and attribute = l_new_apps_system_name || '*' || l_old_appltop_name;

prompt successfully updated AD_TIMESTAMPS