20 April 2011

Define, Declare...what's the difference?

Yesterday, I received the following email:

Just answered the 20 April quiz and took a little extra time than required considering I knew this concept very well. I am just curious to know what is the difference between DECLARE and DEFINE? For me it goes like this:

Consider the block below:
DECLARE
   my_variable_declared  NUMBER;
   my_variable_defined my_variable_declared;
BEGIN
   NULL;
END;
I consider "my_variable_declared NUMBER" as DECLARE and "my_variable_defined my_variable_declared" as DEFINE.

I often feel confusion when someone talks about declare/define because I am not sure if they follow my(or standard?) convention about DECLARE/DEFINE.

Could you please comment if my assumption is correct?

I selected "You cannot declare cursor variables at the package level." as correct for this quiz but was thinking YOU CAN DECLARE but CANNOT DEFINE cursor variables at package level (per my assumption). For me "TYPE cv_t IS REF CURSOR RETURN plch_employees%ROWTYPE" is declaration of cursor variable and "emp_cv cv_t" is definition of cursor variable.

My response: I don't believe there is any "standard" - one thing I have come to learn and accept about Oracle documentation is that it is not terribly precise and it does not take the trouble to define all of its terms. I don't think that is going to change any time soon.

And I must admit that I follow in those big footsteps: I am not always terribly precise about the usage of certain terms. Regarding DECLARE and DEFINE, I would say that the term "declare" is fairly unambiguous. You declare things in the declaration section of a block (which also includes the entire package specification).

"Define" is a bit more....flexible, to my mind. I believe that I generally use that term to describe the creation ("defining") of a database object or program unit. But I probably also use it to describe the process of declaring a procedure in a package and other such steps.

In terms of the way you draw the distinction above, between a REF CURSOR type and a cursor variable, that doesn't make a whole lot of sense to me. Both are declarations, one of a type and the of a variable based on that type. But if someone showed me that package specification and told me, "Look, Steven! I defined a new REF CURSOR type!" I would immediately understand what is meant. Wouldn't you?

Other thoughts?

18 April 2011

Infinite files? REALLY? (2205)

The 15 April quiz regarding infinite loops scored the following choice as incorrect:
PROCEDURE read_file (file_in IN UTL_FILE.file_type)
IS
   l_line   VARCHAR2 (32767);
BEGIN
   LOOP
      UTL_FILE.get_line (file_in, l_line);
   END LOOP;
END;
explaining: "This simple loop contains no EXIT, RAISE or RETURN statements, so at first glance one might think it will never terminate. This loop will not, however, go on and on and on. The reason is that there is no such thing as an infinitely large file (even if there is no limit to a file size, each and every file that is opened will have a finite number of lines in it). And when UTL_FILE.GET_LINE reads past the end of a file, it raises the NO_DATA_FOUND exception."

A player wrote with the following objection:

"The answer with UTL_FILE.get_line is not completely correct. At least the answer is stating "The reason is that there is no such thing as an infinitely large file ..." which is not correct. When I was answering it, pseudo files like /dev/urandom came to my mind, which make the loop really indefinite. If you are running on Win, you can check following wiki site: http://en.wikipedia.org/wiki//dev/random."

Big news to me. Has anyone had experience with this? Would anyone like to test out this hypothesis?