Skip to main content

TwitterQuiz results: What could be safely deleted from this code?

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, offered several of the same observations as those above. Thanks for taking the time to tweet!

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.

Comments

  1. 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.)

    ReplyDelete
  2. Hey, 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.

    ReplyDelete
  3. I 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.
    I would keep those lines.
    I prefer source code that is detailed rather than sparse.

    ReplyDelete
  4. all irrelevant spaces and new lines can be safely deleted ;)

    But seriously, I think the revised code will result in an ora-06531 (first line after begin), because the collection l_objects is not initialised.

    ReplyDelete
  5. 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.

    ReplyDelete
    Replies
    1. But I think the first time it's not populated by the bulk collect yet.

      Delete
  6. Dear 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.

    If 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.

    ReplyDelete
  7. 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.

    You 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.

    ReplyDelete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel