tag:blogger.com,1999:blog-7849367040589270673.post3545752132266437955..comments2024-03-21T22:50:39.997-07:00Comments on Obsessed with Oracle PL/SQL: Wait, did the PL/SQL compiler just REMOVE my code?Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-7849367040589270673.post-11646807846282754532016-07-15T09:38:13.663-07:002016-07-15T09:38:13.663-07:00ARGH. I can't even type my sort of email addre...ARGH. I can't even type my sort of email address right. I meant to type: steven dot feuerstein at oracle dot com. But no worries. I got your email via my stevenfeuerstein domain. Thanks!Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-50749786566294200272016-07-14T10:48:45.556-07:002016-07-14T10:48:45.556-07:00Scott, I am pretty use that Oracle will not accept...Scott, I am pretty use that Oracle will not accept code from outside contributors but why don't you follow up via email steven at feuerstein dot com, and we can explore further.Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-39779218825097300642016-07-14T10:30:17.381-07:002016-07-14T10:30:17.381-07:00Thank ya Steven.
And actually I have a few update...Thank ya Steven.<br /><br />And actually I have a few updates to push up to github now that you mention it. In point of fact, once I get a couple things tidied up I'd like to talk to someone about providing a bit of my code (not yet on github) to Oracle. Who should I talk to on that front?Scott Swankhttps://www.blogger.com/profile/01742896204029056345noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-80319528052575940422016-07-11T04:08:57.344-07:002016-07-11T04:08:57.344-07:00Thanks Steven!
Thanks Steven!<br />Rahulhttps://www.blogger.com/profile/08881838984929045271noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-15012120459902503422016-07-10T08:04:30.703-07:002016-07-10T08:04:30.703-07:00re: how is the procedural part of my code executed...re: how is the procedural part of my code executed<br /><br />I suggest that the best answer to this question is: don't worry about it. You don't need to know about or concern yourself with the internal operations of the compiler (which can change with each new release anyway). It does the right thing and ensures that the logical result of your code remains unchanged.<br /><br />re: context switches and inbuilt functions like nvl,decode, sum, avg<br /><br />These functions are implemented directly in the SQL engine. There is no context switch for the built-in SQL functions.Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-51781268871599905792016-07-10T01:32:54.797-07:002016-07-10T01:32:54.797-07:00Hi Steven,
As you have mentioned, The function ca...Hi Steven,<br /><br />As you have mentioned, The function call get replaced by the the code itself and that hasnt any associated context switches. So, if i have the below function with a huge code which gets replaced at the occurence of Function call, how is the procedural part of my code executed. A small description (any web link with detailed description) would be appreciated here.<br /><br />Function F return number is <br />begin<br /><br />IF condition_1 THEN<br /> SELECT INTO value1<br />ELSIF condition_2 THEN<br /> statements_2<br />[ ELSIF condition_3 THEN<br /> statements_3<br />]...<br />[ ELSE<br /> else_statements<br />]<br />END IF;<br /><br />For rec in 1..10<br />loop<br />sum:=sum+rec;<br />end loop;<br /><br />Return sum+value1;<br />End if;<br /><br /><br />Also, with reference to context switches, I always had a doubt of how does the inbuilt functions like nvl,decode, sum, avg etc. work ( i am aware of the standard package which sets the environment), but do we really call a PLSQL code in the background and are there any associated context switches.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-79633578639877606262016-07-09T05:22:51.193-07:002016-07-09T05:22:51.193-07:00Thanks, Scott. That is a very concise and clear ex...Thanks, Scott. That is a very concise and clear explanation. Maybe you should write a book! :-)<br /><br />But I do see you've been busily adding useful code to Github! Check it out folks: https://github.com/scott-swank/plsql-utilSteven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-71878913561359994342016-07-08T16:59:42.830-07:002016-07-08T16:59:42.830-07:00If you in-line a procedure or function you never h...If you in-line a procedure or function you never have to allocate a stack frame for it. I.e. a local scope for the procedure's state: variables, etc. And then you don't have to remove that stack frame after exiting the procedure/function.<br /><br />This can be a significant win if you're calling a procedure in a loop (allocate frame, de-allocate frame, repeat), and the more state associated with the procedure the bigger the gain.Scott Swankhttps://www.blogger.com/profile/01742896204029056345noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-70450121338361584652016-07-08T04:20:45.296-07:002016-07-08T04:20:45.296-07:00Context switches do not come into play here, since...Context switches do not come into play here, since there is no switch between the SQL and PL/SQL engines. Instead, inlining avoids the overhead of looking up the code block for the procedure or function or (as it turns out) cursor. Instead, the code is simple "there", inline in the current block.Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-15350122035640645902016-07-07T21:29:01.468-07:002016-07-07T21:29:01.468-07:00Trust the compiler, but, still not sure what is th...Trust the compiler, but, still not sure what is the advantage of inline function here. Did we avoid the context switch using inline function ( I am sure no), What different it is when we open a cursor and return the value returned by SQL engine to the PLSQL record, compared to using a function which in turn calls the SQL which does the same thing.Rahulhttps://www.blogger.com/profile/08881838984929045271noreply@blogger.com