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:
   my_variable_declared  NUMBER;
   my_variable_defined my_variable_declared;
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?


  1. Hello All,
    I think that in the framework of PL/SQL we will not be wrong in any way if we consider DECLARE and DEFINE as perfectly identical, meaning essentially the same thing, namely,
    introducing a new pl/sql entity, be it a type, subtype, variable, cursor, exception, program unit,a.s.o.

    As far as I am aware, DEFINE is NOT even a reserved word in PL/SQL.

    The database uses the term CREATE for almost the same thing, and this term is maybe less ambiguous, though, it essentially also means introducing an entity that did not exist before.

    Maybe the only "nuance difference" between CREATE and DECLARE/DEFINE is that CREATE makes its new entity permanent, while DECLARE/DEFINE also "creates" an entity, but a temporary one,
    with limited scope in "space and time".

    While being consistent is always welcome, however, I don't think that at least while dealing we PL/SQL we should ever tend
    "to stumble" on this distinction.

    Best Regards,

  2. Hi, my tuppence worth to this old post :-) After picking over the way PL/SCOP output uses DECLARE and DEFINE:

    1) You DECLARE something when you make a declaration of something, but do not specify its implementation. Examples:
    1.1) a procedure specification in a package header
    1.2) a procedure header in an object type specification

    2) You DEFINE something when you specify the implementation of what you previously declared eg:
    2.1) a procedure definition in a package body

    3) You often both DECLARE and DEFINE at the same time eg
    3.1) v_myvar INTEGER;