Thursday, April 9, 2015

PL/SQL Brain Teaser: compile procedure with two declarations with same name?

I posted my second PL/SQL brain teaser on Twitter today @sfonplsql:

I can compile a procedure successfully even tho it contains two declarations with the same name for the identifier. Can you?

But Twitter is probably not the best way to do this, since it is not easy to capture every nuance in a tweet.

For example, Jonathan Whitehead asked: "Complier directives?"

Which leads me to "tweak" the brain teaser to fully elaborate as follows:

The Brain Teaser

I can compile a procedure successfully even though it contains two declarations with the same name for the identifier. And after compilation, if I run DBMS_PREPROCESSOR.print_post_processed_source to display the actual source code that was compiled into the database, it will show BOTH of those declarations.

OK....now, clever PL/SQL developers, can you post an example of code that will solve this teaser?

9 comments:

  1. Just don't reference them...

    CREATE OR REPLACE PROCEDURE p AS
    x NUMBER;
    x NUMBER;
    y NUMBER;
    BEGIN
    y := 5;
    END p;
    /

    ReplyDelete
  2. Nice work, Oren. Yes, that is the solution! It turns out that the PL/SQL compiler will not complain about two declarations with the same name - as long as you do not reference them in your code.

    ReplyDelete
  3. CREATE OR REPLACE PROCEDURE p AS
    x NUMBER := 8;
    x NUMBER := 9;
    y NUMBER;
    BEGIN
    DEClARE
    x NUMBER;
    BEGIN
    x := 6;
    END;
    END p;
    /

    ReplyDelete
  4. A pleasantly confusing variation on Oren's work. Nice, Atul.

    ReplyDelete
  5. Thanks Steven....

    How about


    CREATE OR REPLACE PROCEDURE p AS
    x NUMBER := -88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888889999999999999999999999999999999999999988;
    x NUMBER := 9;
    y NUMBER;
    BEGIN
    DEClARE
    x NUMBER;
    BEGIN
    x := 6;
    END;
    END p;
    /

    It gives error numeric overflow or underflow.......

    looks like Oracle Checks initial assignment

    ReplyDelete
  6. Yes, an assignment of a default value would certainly count as a usage of that variable.

    ReplyDelete
  7. Steven One more

    CREATE OR REPLACE PROCEDURE p AS
    x NUMBER := -9999999999999988;
    x NUMBER := abs(-9) * 0;
    y NUMBER;
    BEGIN
    DEClARE
    x NUMBER;
    BEGIN
    x := 6;
    END;
    END p;
    /

    It complies OK.

    ReplyDelete
  8. Well you know what means? I was wrong! :-)

    And that's what happens when you state things about code without actually verifying them. Thanks, Atul!

    ReplyDelete
  9. Hello Steven, All,
    I think that it is a compiler bug that it does allow the duplicate declaration in the first place.
    Steven, unrelated to this issue, I would thank you in advance if you can look into
    a feedback that I just posted on the plsqlchallenge site ... I know that you don't have access to e-mail ...
    Thanks a lot & Best Regards,
    Iudith

    ReplyDelete