tag:blogger.com,1999:blog-7849367040589270673.post475173472083178317..comments2024-03-21T22:50:39.997-07:00Comments on Obsessed with Oracle PL/SQL: Qualify names of variables inside SQL statements! (an ode to fine-grained dependency)Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-7849367040589270673.post-79798467314840054242016-08-25T08:49:53.836-07:002016-08-25T08:49:53.836-07:00We shall see...though at some point the benefit of...We shall see...though at some point the benefit of the minimized invalidations is overwhelmed by the complexity of handling such nuances. In addition, we want to be rather conservative and not leave things marked as valid when they really should be recompiled. Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-41851718110874792292016-08-25T08:48:45.852-07:002016-08-25T08:48:45.852-07:00I suggest that is the wrong way to think about it....I suggest that is the wrong way to think about it. In a dev environment, the database will largely take care of this for you, automatically recompiling as needed. Yes, this will happen in production, as well, but usually you don't want to rely on that. In dev, however, you make your changes and the next time someone tries to run the dependent code, it is recompiled as needed. Nothing scary there!Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-38122634621560867282016-08-25T07:29:52.764-07:002016-08-25T07:29:52.764-07:00Steven this "sometimes" is scary..... me...Steven this "sometimes" is scary..... means better to compile after any change !!!Atul Guptahttps://www.blogger.com/profile/06114446824034950377noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-69854963302878438482016-08-25T06:42:43.610-07:002016-08-25T06:42:43.610-07:00No. Sometimes it will cause invalidations - such a...No. Sometimes it will cause invalidations - such as when you do not fully qualify all references to PL/SQL variables inside the SQL statement. Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-67182781121557734042016-08-25T04:51:40.012-07:002016-08-25T04:51:40.012-07:00We might need one more level of dependency check i...We might need one more level of dependency check if column is altered in a way that it increases its size from varchar2(10) to varchar2(12) or number(1) to number(19) then it should not invalidate any procedure,functions,package & trigger..... Hoping to see in oracle version 13 or version 14Atul Guptahttps://www.blogger.com/profile/06114446824034950377noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-88186082978994537242016-08-25T03:50:03.531-07:002016-08-25T03:50:03.531-07:00Starting from Oracle 11.1 if you add new column to...Starting from Oracle 11.1 if you add new column to any table then it will not invalidate any procedure,functions,package & trigger ???<br /><br />Atul Guptahttps://www.blogger.com/profile/06114446824034950377noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-38825823646727180982016-08-12T03:24:47.160-07:002016-08-12T03:24:47.160-07:00An invalidated procedure has never been a problem ...An invalidated procedure has never been a problem to me, we have downtimes where we can install our new software and a simple recompile will solve it.<br />But you can't emphasise enough that your program will break when you add a column that is named like a variable (in your case nn) if you don't fully qualify your parameters. <br />I have to admit that I started qualifying my parameters only recently - shame on me :-) It only worked until now without bugs because we have (enforced) naming conventions that saved us from desaster.Marcushttp://matzberger.de/oraclenoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-55224959576317721442016-08-11T11:18:01.961-07:002016-08-11T11:18:01.961-07:00I recommend thinking about this rather differently...I recommend thinking about this rather differently -- but the upshot on how to write your static SQL is the same. I always stress the importance of aiming, above all else, for correctness. Only then can other things (like avoiding invalidation when you can) enter the discussion. Here, the silent name capture, that Steven shows, demonstrates the risk of a silent change in a program's semantics. You simply must avoid this. And when you do, you get the nice side-benefit of letting fine-grained dependency tracking deliver its full benefit.Anonymoushttps://www.blogger.com/profile/15568138785014592769noreply@blogger.com