4 Replies Latest reply: Sep 7, 2012 6:13 AM by Tomas Albinsson RSS

    Overloaded procedure - Error

    tullio0106
      I found the following behaviour, if a procedure is overloaded ApexListener calls one version of the overloaded procedutr without trying to match parameters.
      Is it correct ?
      Can I avoid such behaviour ?
      Tks
      Tullio
        • 1. Re: Overloaded procedure - Error
          Udo
          Hello Tullio,

          could you outline your example a little more? It would be interesting to know the overloaded procedure signatures and how APEX Listener is supposed to execute them.

          -Udo
          • 2. Re: Overloaded procedure - Error
            tullio0106
            I've a procedure LOG_ME_IN in a pachage with 2 different signatures LOG_ME_IN and LOG_ME_IN(PC_USERNAME IN VARCHAR2, PC_PASSWORD IN VARCHAR2).
            I've a <form action="http://xxxxxxxxxxxxxx/apex/PK_AUTO_LOGIN.LOG_ME_IN> with 2 input fields PC_USERNAME e PC_PASSWORD.
            However, on submit it calls sometime the version without parameters and sometimes with parameters.
            I didn't understand when it chooses the correct one and when it chooses the wrong one but I suspect it depends upon compilation of the package.
            Tks
            Tullio
            • 3. Re: Overloaded procedure - Error
              Udo
              Hello Tulio,

              could you track down which procedure is called upon which form/value combination? It would be good to really have the actual form and values to be able to reproduce your scenario...
              What I can reproduce is the following scenario:

              PL/SQL Package
              create or replace package PK_AUTO_LOGIN
              is
                procedure LOG_ME_IN;
                procedure LOG_ME_IN (PC_USERNAME IN VARCHAR2, PC_PASSWORD IN VARCHAR2);
              end PK_AUTO_LOGIN;
              /
              create or replace package body PK_AUTO_LOGIN
              is
                procedure LOG_ME_IN
                as begin
                  htp.p('LOG_ME_IN <p> User: Anonymous <p> Password: (null)');
                end;
                
                procedure LOG_ME_IN (PC_USERNAME IN VARCHAR2, PC_PASSWORD IN VARCHAR2)
                as begin
                  htp.p('LOG_ME_IN <p> User:'||PC_USERNAME||'<p> Password: '||PC_PASSWORD);
                end;
              end;
              /
              I grant execute to APEX_PUBLIC_USER and can issue the follwoing calls:
              1. http://xxxxxxxxxxxxxx/apex/MY_SCHEMA.PK_AUTO_LOGIN.LOG_ME_IN
              I receive the expected result (Anonymous / (null) )
              2. http://xxxxxxxxxxxxxx/apex/MY_SCHEMA.PK_AUTO_LOGIN.LOG_ME_IN?PC_USERNAME=TEST
              I receive Status Code 500 (Internal Server Error), because of PLS-00306 (wrong number or types of arguments for 'LOG_ME_IN) )
              3. http://xxxxxxxxxxxxxx/apex/MY_SCHEMA.PK_AUTO_LOGIN.LOG_ME_IN?PC_USERNAME=Test&PC_PASSWORD=pass
              I receive the expected result ( Test / pass )
              4. http://xxxxxxxxxxxxxx/apex/MY_SCHEMA.PK_AUTO_LOGIN.LOG_ME_IN?PC_USERNAME=Test&OTHER=foo&MORE_OTHER=bar&PC_PASSWORD=pass
              I still receive the (expected) result ( Test / pass )
              5. http://xxxxxxxxxxxxxx/apex/MY_SCHEMA.PK_AUTO_LOGIN.LOG_ME_IN?PC_USERNAME=Test&OTHER=foo
              I receive 500 again, again because of PLS-00306.

              To conclude, GET requests are all handled as expected: only procedures with matching parameter names are called.
              So, let's see what happens whit POST requests in a form

              HTML Form
              <html>
                 <head>
                    <title>Test Login</title>
                 </head>
                 <body>
                 <FORM enctype="multipart/form-data" action="http://xxxxxxxxxxxxxx/apex/MY_SCHEMA.PK_AUTO_LOGIN.LOG_ME_IN" method="POST">
                    <p>Username:<INPUT type="text" name="PC_USERNAME">
                    <p>Password:<INPUT type="text" name="PC_PASSWORD">
                    <p><INPUT type="submit" value="Login">
                 </FORM>
                 </body>
              </html>
              1. When submitting without any value, I get the expected result: ( <empty> / <empty> )
              2. When submitting with username only, I get the expected result: ( username / <empty> )
              3. When submitting both values, I get the expected result: ( username / password )

              To conclude: Using that form always executes the expected procedure with matching parameter names, no matter what values I submit.
              What could be wrong in your scenario:
              - Your form doesn't use matching parameter names.
              - You call the procedure through an (unstable) synoynm.
              - You recompile using different implementations and run into a metadata caching issue (again). But I think it's unlikely that APEX Listenerr would use a non-matching signature that way, though.

              So let's see what you've got...

              -Udo
              • 4. Re: Overloaded procedure - Error
                Tomas Albinsson
                Hi Udo,

                that was a really good reply/sample, sad that Tullio hasn't been back.
                I don't want to hijack this thread so tell me if you want me to start a new. I think it's near this subject though...

                I create a procedure:
                CREATE OR REPLACE PROCEDURE testing( psParam in varchar2 default null ) IS
                BEGIN
                  htp.p('psParam='||psParam);
                END testing;
                /
                and grant execute on it to public. As my procedure is in a schema called ris I call it from a browser like this:

                http://myserver/apex/ris.testing
                I get "psParam="
                http://myserver/apex/ris.testing?psParam=hello
                I get "psParam=hello"
                http://myserver/apex/ris.testing?what=else
                I get "psParam="
                http://myserver/apex/ris.testing?psParam=hello&other=4
                I get "psParam=hello"

                That's all fine. Now I change my procedure:
                CREATE OR REPLACE PROCEDURE testing( psParam in varchar2 default null, psSecond in varchar2 default null  ) IS
                BEGIN
                  htp.p('psParam='||psParam||'<br>');
                  htp.p('psSecond='||psSecond);
                END testing;
                /
                Some new calls:
                http://myserver/apex/ris.testing
                I get "psParam= psSecond=" (I'm skipping the break here)
                Notice the new output, the changed code is obviously executed.

                http://myserver/apex/ris.testing?psParam=adios
                I get "psParam=adios psSecond="

                Now the fun begins:

                http://myserver/apex/ris.testing?psParam=what&psSecond=42
                I get "psParam=what psSecond="
                http://myserver/apex/ris.testing?psSecond=42
                I get "psParam= psSecond="

                I cant explain that, hopefully you can :)
                I've noticed that I can do this

                http://myserver/apex/ris.TESTING?psParam=upper&psSecond=42
                and now I get "psParam=upper psSecond=42"
                or this
                http://myserver/apex/RIS.testing?psParam=upper&psSecond=42
                and get the same.

                It seems like "if you add a defaulted parameter you can only give it a value by changing the case of the schema or procedure".
                Or I can bounce the listener. But as my listener is installed i WebLogic Server I know of no other way than to bounce the whole server (and I hate that).

                I know we've been at this before but I feel I didn't get a final answer: Initial error in call to procedure is cached
                My listener is still 1.1.3.243.11.40 under WebLogic 10.3.3.0 and the db is EE 11.2.0.3.0


                Kind regards

                Tomas