Last week, I posted this on Twitter:
What code can be removed w/o changing text shown after execution?
Who Said What?
evrocs_nl putting data in collection before the select, because the bulk collect will clear the collection first (unless really old oracle)
Yes! A BULK COLLECT always empties the target collection. If the query returns no data, the collection remains empty. Otherwise its contents are replaced by the result set of the query.
MDWidlake All of it as you forgot to turn serveroutput on anyway. Where do I collect my Kewpie doll?
Oh, Martin, you clever fellow. There's always one in a crowd. But sort of good point. This exercise was taken from the PL/SQL Challenge, whose PL/SQL quiz assumptions include that SERVEROUTPUT is always on.
ddfdba indx pls_integer:=100; l_empty objects_t; := l_empty
Yes! There is no need to declare a variable for the iterator used in a FOR loop; it is declared implicitly by PL/SQL. There is, furthermore, need to assign an "empty" collection to l_objects to initialize it. A BULK COLLECT automatically initializes a nested table, if it has not already been initialized.
gurcan_orhan where's the exception (when others then null;)
Well, Gurcan, your text was not relevant to the exercise at hand, but it is always an important question to ask a programmer about their subprogram:
"Where's the exception section?"
The answer might well be "We handle exceptions in an outer block." and that could be a fine answer, but it never hurts to check.
patch72 both declarations of indx. l_empty variable. Assignment with l_empty. l_object(100) := 'BLIP'; l_objects.delete.
Yes! Some of this was already addressed, but Patrick points out that there is no need to delete all the elements from the collection. Why not? Because PL/SQL is such a fantastic, hard-working programming language. The l_objects collection is declared within the nested subprogram. Consequently, the PL/SQL runtime engine automatically releases the memory for l_objects when the execute of get_objects terminates. Nice work, PL/SQL!
yalimgerger, chetanr009, I_am_mvrolijk offered several of the same observations as those above. Thanks for taking the time to tweet!
rtripathi6 suggested removing "The FOR indx IN 1 .. l_objects.COUNT loop." But I do not see how the subprogram will compile without it. Perhaps you can clarify with a comment?
Here's a pretty picture driving home all the code that could be removed from this block:
Lessons Learned
PL/SQL is a purpose-built language for writing high performance, easy to maintain and very secure applications on top of Oracle SQL and the Oracle Database.
Because of its tight focus, the PL/SQL development team has been able to optimize syntax, garbage cleanup, and more.
The more you understand what PL/SQL does for you, the more easily you will be able to leverage PL/SQL to deliver applications on-time and on-budget.
What code can be removed w/o changing text shown after execution?
Who Said What?
evrocs_nl putting data in collection before the select, because the bulk collect will clear the collection first (unless really old oracle)
Yes! A BULK COLLECT always empties the target collection. If the query returns no data, the collection remains empty. Otherwise its contents are replaced by the result set of the query.
MDWidlake All of it as you forgot to turn serveroutput on anyway. Where do I collect my Kewpie doll?
Oh, Martin, you clever fellow. There's always one in a crowd. But sort of good point. This exercise was taken from the PL/SQL Challenge, whose PL/SQL quiz assumptions include that SERVEROUTPUT is always on.
ddfdba indx pls_integer:=100; l_empty objects_t; := l_empty
Yes! There is no need to declare a variable for the iterator used in a FOR loop; it is declared implicitly by PL/SQL. There is, furthermore, need to assign an "empty" collection to l_objects to initialize it. A BULK COLLECT automatically initializes a nested table, if it has not already been initialized.
gurcan_orhan where's the exception (when others then null;)
Well, Gurcan, your text was not relevant to the exercise at hand, but it is always an important question to ask a programmer about their subprogram:
"Where's the exception section?"
The answer might well be "We handle exceptions in an outer block." and that could be a fine answer, but it never hurts to check.
patch72 both declarations of indx. l_empty variable. Assignment with l_empty. l_object(100) := 'BLIP'; l_objects.delete.
Yes! Some of this was already addressed, but Patrick points out that there is no need to delete all the elements from the collection. Why not? Because PL/SQL is such a fantastic, hard-working programming language. The l_objects collection is declared within the nested subprogram. Consequently, the PL/SQL runtime engine automatically releases the memory for l_objects when the execute of get_objects terminates. Nice work, PL/SQL!
yalimgerger, chetanr009, I_am_mvrolijk offered several of the same observations as those above. Thanks for taking the time to tweet!
rtripathi6 suggested removing "The FOR indx IN 1 .. l_objects.COUNT loop." But I do not see how the subprogram will compile without it. Perhaps you can clarify with a comment?
Here's a pretty picture driving home all the code that could be removed from this block:
Lessons Learned
PL/SQL is a purpose-built language for writing high performance, easy to maintain and very secure applications on top of Oracle SQL and the Oracle Database.
Because of its tight focus, the PL/SQL development team has been able to optimize syntax, garbage cleanup, and more.
The more you understand what PL/SQL does for you, the more easily you will be able to leverage PL/SQL to deliver applications on-time and on-budget.
No contest on the syntactical requirements nor the short hand PL/SQL allows. This is especially good to know about for someone reading working code who might think a change is needed, for example, to do the explicit delete. But this does raise the subjective question of which code is more clear to a human reader. (I do wonder whether the object code produced ends up being identical and whether there is a hitch in the compiler code path that takes more time to generate the implicit variable space and allocation compared to the explicit declaration. This parenthetical bit is just the musings of a geek who worked on operating systems and compilers long ago when computers were much slower and such things mattered more.)
ReplyDeleteHey, rsiz, thanks for your interesting comments. I do not know about the object code issues. But I really doubt after all these years that implicit vs explicit would result in a degradation of performance. As to the subjective question "which code is more clear to the human reader": definitely should be considered a factor. But I would say that generally with a programming language, one should learn and assume understanding of the fundamentals. For more complex features and behavior, a comment or even an explicit although perhaps theoretically unnecessary line of code could be added.
ReplyDeleteI like and would retain all that you've struck out. Those lines make it the code to the next person who reviews the code, someone who could either have been working 20years on PLSQL or someone for whom it is the first day on the job, fresh out of college or training academy or someone who has a background in some other programming language.
ReplyDeleteI would keep those lines.
I prefer source code that is detailed rather than sparse.
all irrelevant spaces and new lines can be safely deleted ;)
ReplyDeleteBut seriously, I think the revised code will result in an ora-06531 (first line after begin), because the collection l_objects is not initialised.
Rop, initialization is not required for a nested table or varray populated by a BULK COLLECT. The PL/SQL engine takes care of that for us.
ReplyDeleteBut I think the first time it's not populated by the bulk collect yet.
DeleteDear Hemant, if the code reviewer does NOT understand the code in its minimal number of rows (except white spaces used to format the code for fast understanding) then he/she should consider change his/her job, because IT is by far not the best option.
ReplyDeleteIf he/she is a newbie this is a great opportunity to learn.
Besides that, there is a general principle named KISS, an acronym for "Keep it simple, stupid"; I always try to avoid the situation where I am the faulty target of this principle.
Yes! Yes! You are totally correct, Rop. I cannot check the count of the collection if it has not been initialized, and yet that line is needed to maintain the same output.
ReplyDeleteYou got me!
And the reason is that I crossed off the INDEX BY PLS_INTEGER - turning it into a nested table - which none of my tweeters did. Once I do that, initialization is required!
Ironically enough for me, that is precisely what is marked as an INCORRECT choice on the question itself (link in Twitter post at top of this blog post!).
I will replace the graphic, keeping the INDEX BY clause intact.