This content has been marked as final. Show 4 replies
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.
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:
I grant execute to APEX_PUBLIC_USER and can issue the follwoing calls:
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 receive the expected result (Anonymous / (null) )
I receive Status Code 500 (Internal Server Error), because of PLS-00306 (wrong number or types of arguments for 'LOG_ME_IN) )
I receive the expected result ( Test / pass )
I still receive the (expected) result ( Test / pass )
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
1. When submitting without any value, I get the expected result: ( <empty> / <empty> )
<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>
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...
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:
and grant execute on it to public. As my procedure is in a schema called ris I call it from a browser like this:
CREATE OR REPLACE PROCEDURE testing( psParam in varchar2 default null ) IS BEGIN htp.p('psParam='||psParam); END testing; /
I get "psParam="
I get "psParam=hello"
I get "psParam="
I get "psParam=hello"
That's all fine. Now I change my procedure:
Some new calls:
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; /
I get "psParam= psSecond=" (I'm skipping the break here)
Notice the new output, the changed code is obviously executed.
I get "psParam=adios psSecond="
Now the fun begins:
I get "psParam=what psSecond="
I get "psParam= psSecond="
I cant explain that, hopefully you can :)
I've noticed that I can do this
and now I get "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 18.104.22.168.11.40 under WebLogic 10.3.3.0 and the db is EE 22.214.171.124.0