MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Object constructor quirk

with 5 comments

Never change something that works! Not really, but sometimes you feel that way. Especially, when you toast 5 to 10 minutes working through an undocumented behavior in PL/SQL. You’d think after writing it for 19 years, I’d have seen it all but not so.

I was working through a tried and true example from Chapter 14 of the Oracle Database 11g PL/SQL Programming book to prepare for teaching my class tomorrow, when I found this nice quirk. It took a few minutes to figure out what was happening, but here it is so you don’t have to look for it too. You can only use variable names that are attributes of the object type as formal parameters in object type constructors. If you try to vary it, you’ll trigger the following exception:

-------- -----------------------------------------------------------------
4/11     PL/SQL: Item ignored
4/26     PLS-00307: too many declarations OF 'HELLO_THERE' MATCH this CALL
6/5      PL/SQL: Statement ignored
6/13     PLS-00320: the declaration OF the TYPE OF this expression IS incomplete OR malformed

All I did to trigger this exception was change the who variable to make it scope specific, like iv_who for instance variable, pv_who for parameter variable, and lv_who for local variable.

This is certainly interesting. I’ve no clue why such a limitation exists. The name of a parameter list member in the constructor should be independent from the attribute of a user object.

Written by maclochlainn

June 25th, 2009 at 11:57 pm

Posted in Objects,Oracle,pl/sql,sql

5 Responses to 'Object constructor quirk'

Subscribe to comments with RSS or TrackBack to 'Object constructor quirk'.

  1. Thanks so much for this note. I was just prepping Chapter 14 and created an object using your example as a guide, and ran into the too many declarations problem. This has saved me hours of searching.


    15 Nov 11 at 11:45 am

  2. No worries, it’s actually on my list for new PL/SQL Programming book for Oracle Database 12c. Unfortunately, the beta looks like it’s 6-8 months out. I’m not aware of a problem in the Oracle Database 11g PL/SQL Programming book’s Chapter 14, did you encounter one? BTW, I’ve written a blog comment for errata on the new Oracle Database 11g & MySQL 5.6 Developer Handbook.


    15 Nov 11 at 1:49 pm

  3. Thank you very much for the information.
    I looked for an answer to this problem for a whole day, and I was about to explode because I had been unable to find a solution.

    In my limited experience, I had had problems because of variables with the same name, so I never thought the problem would be caused by parameters names.


    19 Nov 12 at 11:42 pm

  4. Thank you! It saved my day!


    29 Dec 14 at 7:02 am

  5. maclochlainn

    1 Jan 15 at 7:22 pm

Leave a Reply