is it true that whenever u perform alter table...u invalid table -elated procedure?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: is it true that whenever u perform alter table...u invalid table -elated procedure?

  1. #1
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456

    is it true that whenever u perform alter table...u invalid table -elated procedure?

    hi,

    I have got someone telling me that whenever we perform any Alter Table.....it is effectively invalidating all procedures related to that table? is this TRUE? as I dont wish to run the risk/trouble so I hope anyone can help to clarify here?

    thanks in advance

    rgds,
    nick

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    yes, nothing to worry since procedures are recompiled automatically when they are called again

  3. #3
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    hi pando,

    thks for the info...so am i right to say it is almost "transparent" to any oracle user that call a procedure related to that table right? as in no error prompting and so on....almost as per normal ?

    thks
    nick

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: is it true that whenever u perform alter table...u invalid table -elated procedure?

    Originally posted by ngwh
    hi,

    I have got someone telling me that whenever we perform any Alter Table.....it is effectively invalidating all procedures related to that table? is this TRUE?
    No, not quite. Only some of the ALTER TABLE comands (basicaly only those which change the table's "visible" structure) indeed cause dependant procedures to be invalidated, but there are many ALTER TABLE clauses that actually have no effect on related procedures..
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    ohh really? infact I am going to run Alter Table add partition with a new date sub-range wonder if that would invalidate those procedures???
    hope that u can enlighten me further

    thks
    nick

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Why dont u try on ur test DB
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Originally posted by ngwh
    ohh really? infact I am going to run Alter Table add partition with a new date sub-range wonder if that would invalidate those procedures???
    hope that u can enlighten me further

    thks
    nick
    dont think so, AFIAK procedures, packages, functins are invalid if structure of table changes such as adding/deleting columns, change column definitions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width