14 July 2010

Issue raised by player regarding 12 July quiz(505)

I received an email with the following statement: "Hello Steven and PLSQL challenge team, I reckon that the 3rd option in a recent quiz (12 Jul 2010) was incorrect, because "Any program unit" (2nd requirement in a question) may include client PL/SQL calls, like from Oracle Forms/reports where you cannot use default values for actual parameters." And here is my response: in our assumptions for the quizzes, we state: "The session and the environment in which the quiz code executes has enabled output from DBMS_OUTPUT, and can reference only those datatypes, programs and database objects defined in the context of the quiz or are available in a default installation of the Oracle instance." Therefore, while what you say may be true, it does not apply to this or any other PL/SQL Challenge quiz, unless we explicitly introduce the idea of running client-side PL/SQL in that quiz. Please let me know what you think.

1 comment:

  1. Hello Steven,

    The gist of question was related to database, i.e. "the session and the environment in which the quiz code executes". Yes, we've added the default value formal parameter to procedure at server side. However, we should take care, because such change can be dramatic to client PL/SQL calls of this function.
    Even though, everything is fine with server calls of this function we will definitely have great headache in a client. Actually it's a shame to Oracle corp that it doesn't synchronise both PL/SQL versions - server and client.
    The idea of my concern is that "any program unit" will not be fine after such changes at server side and we should be very careful about them (we cannot see client invalid objects ) and to make such changes safer, i.e. using two methods like

    procedure test(
    l_param1 varchar2
    , l_param2 varchar2 := null);

    procedure test(
    l_param1 varchar2)
    end test;

    Sergey Porokh