I need to use a reserved word as parameter name in proc called from URL

629166

Let me preface this post by saying I already know this is terrible to attempt, but it's short term fix only.

Ok, so I have to create a procedure that will be called by a vendors application via a API that calls our URL to send data over. The terrible part is
that the API they have uses the parameter FROM=vendor_data A change is on the way so in the future the API won't use FROM as a paramter, so this isn't something I want to do, but it's a workaround.

So the nastiness is this..., I can create a procedure that'll compile when I enclose FROM in double quotes "FROM" as my input parameter
but if I try to call my procedure via URL (as their application will do) the procedure isn't working. I searched for someway to do an inline
replace of the FROM parameter to something else, but no luck. I'm open to all positive comments. I cannot go outside of Oracle
to do this, so I can't call a shell script and replace. Basically I need some way to use a reserved word as a parameter name, and then be able to call
that proc from a URL, or someway to change the FROM in the URL inline. Any help on this admittedly whacky situation would be appreciated much.

I tried ...\myproc?from=text
...\myproc?"from"=text
...\myproc?'from'=text

proc is simple test procedure

create or replace procedure myproc
("from" in varchar2 default 0)
is
v_from varchar2(30);
begin
v_from:="FROM";
insert into test(col1) values(v_from);
end;

**** Update
I didn't get any more replies but came to a solution that I thought I'd post. It's much better, more elegant and maybe can help others.

So instead of using FROM as the parameter name I did some research and decided I can use flexible parameters. Basically you end up having
2 input parameters for a procedure, one holds a parameter name the other holds the parameter value. They get loaded into arrays
and you access the values with regular name_array(1), value_array(1), etc. ?v=parameter&v2=value

Once I figued I could use flexible parameter it took me tons of research to find out the actual syntax. I'll post some examples for others
later, but was suprised with the lack of resources consideriing how "flexible" they are.

Thanks again for the replies. Cheers.

Edited by: Mitz on Jul 29, 2009 11:37 PM

joelkallman-Oracle

Mitz,

The parameters names in the URL are case-insensitive and implicitly uppercase. When you enclosed your parameter in lowercase in double-quotes, it's recognized only as a lowercase parameter name.

Change the parameter name to uppercase and try again, as in:

create or replace procedure myproc
("FROM" in varchar2 default 0)
is
v_from varchar2(30);
begin
v_from:="FROM";
insert into test(col1) values(v_from);
end;

Joel

629166

Joel,

Thanks for the quick reply, I really appreciate it. I changed the procedure to use "FROM" instead of "from". Again it compiles fine, and runs
from SQL Workshop so proc is fine, but when I try to run from the URL it doesn't run. (404 not found).

How exactly should the URL string look?

..../tester.myproc?FROM=test ----doesn't work.
.../tester.myproc?from=test ---doesn't work

Thanks!

60437

Mitz - Did you add the procedure name to the wwv_flow_epg_include_mod_local function and then recompile that function? You may need to do that.

Scott

629166

Scott,

Thanks for the reply. I'm not familiar with the wwv_flow_epg_include_mod_local, however I know that the
myproc is available via URL. I passed the my procedure name(myproc) on to the dba a while back to make it "accessible" so, I'm assuming that he
added it to this the www_flow_epg_mod_local (assuming this has something to do with access control).

If I modify myproc procedure and remove "FROM" as the input variable, and replace with say,
IN_FROM I can then call the procedure via the URL ./myproc?in_from=test without any problems.

I'm pretty confident that it's the "FROM" that is the hurdle and not a security or setup issue. The proc is fine to call from the URL until I got the curveball that the only available parameter was FROM. How the URL should be when inputing to that parameter?

Edited by: Mitz on Jul 25, 2009 7:36 PM

Edited by: Mitz on Jul 25, 2009 9:16 PM

629166

Use Flexible Parameters. I'll do another post that explains how to use these.