Retrieving POST data from external source in a PL SQL Procedure

Question

EDIT ~ I have answered my own question below in the EDIT section, not answering because i feel wrong if i get points for answering my own post =/

I am trying to find a way to pass parameters to this procedure via a URL

    create or replace procedure extinput(KEY in varchar2 := 'KEY',NAME in varchar2 := 'NAME') is
    l_apex_url varchar2(4000);

    begin
    htp.p('Hello extinput');
    htp.p(KEY);
    --htp.p('NAME');
    htp.p(NAME);
    --l_apex_url:= 'http://myhost:myport/pls/apex/extinput;
    --l_apex_url:= 'google.com'; 
    --owa_util.redirect_url(l_apex_url);

    end extinput;

If i plug the URL:

    http://horizon.lcc.edu:7777/pls/apex/extinput

into my browser then the page dispays:

    Hello extinput KEY NAME

Which is fine for confirming that i can call the procedure via a url, but now im working on passing some variables to its parameters.

I want to know if I will have to put the values in the URL [GET] (really would rather not) or if there is a way to reteive the [POST] data from a form on an external server like so:

    <form Method="POST" action="http://horizon.lcc.edu:7777/pls/apex/extinput" name="form1">
        <P><b>This form has three parameters, which matches the number of parameters the procedure view_http_post_fixed has.</b>
        <table><tr><td>Session Id:</td>
                       <td><input type="text" name="SESSION_ID" value="9582274473829998340">        </td>
            </tr>
            <tr><td>Key:</td>
                <td><input type="text" name="KEY" id="KEY" value="1109"></td>
            </tr>
            <tr><td>Name:</td>
                <td> <input type="text" name="NAME" id="NAME" value="Jeff Eberhard"></td>
            </tr></table>
    <input value="Submit" type="submit">
    </form>

Any help or "point's in the right direction" will be GREATLY appreciated =)

$$ EDIT $$

Ok so i have successfully gotten passed post data in my pl sql procedure

    create or replace procedure extinput(KEY in varchar2 := 'KEY',NAME in varchar2 := 'NAME') 
    is
        l_apex_url varchar2(4000);
    begin
        htp.p('Hello extinput');

        htp.p(KEY);
        htp.p(NAME);

        insert into post_table (col2, col3 ) values(KEY, NAME);
    end extinput;

Coming to this conclusion makes me feel like i made this question more complicated then it needed to be. Sorry if that is so, but atleast this problem is now solved.

Errors were being thrown because i was sending 3 parameters but only coded two into the procedure. So either remove an input from the form, or add a third parameter area. here is the finalized form:

    <form Method="POST" action="http://horizon.lcc.edu:7777/pls/apex/extinput" name="form1">
        <P><b>This form has three parameters, which matches the number of parameters the procedure view_http_post_fixed has.</b>
        <table>
            <tr><td>Key:</td>
                <td><input type="text" name="KEY" id="KEY" value="1109"></td>
            </tr>
            <tr><td>Name:</td>
                <td> <input type="text" name="NAME" id="NAME" value="Jeff Eberhard"></td>
            </tr></table>
    <input value="Submit" type="submit">
    </form>

Urgh. Is this an academic question, or are you actually building a form processing web page using a PLSQL stored proc? — atrain, Oct 04 '11 at 16:29
I dont quite understand what you mean by "academic question", this isnt for a school assignment or anything if thats what you are asking. I am adding a button to seperate web application, which will take data from the form in that app, and [hopefully] bring it to this procedure via the form action URL . The procedure is invisible and will process the data then direct the user to a page displaying either a confirmation of success, or notice of failure. — Kamron K., Oct 04 '11 at 17:00
Great that you solved it! I do think you should answer your own question though, and accept it! Doesn't matter how 'stupid' or small it might be - solved is solved! — Tom, Oct 14 '11 at 09:50

score 2 · Answer 1 · answered Oct 05 '11 at 14:03

2

There is a rather lengthy discussion on this at http://awads.net/wp/2005/11/30/http-post-from-inside-oracle/

Also, don't forget Access Control Lists if you are looking to use this on Oracle 11.

answered Oct 05 '11 at 14:03

Greg Reynolds

9,736
13
49
60

Havnt looked into Access Control Lists, but the discussion you posted looks as if it is exactly the opposite of what i need aka [Post Oracle to the internet] i need from the internet to Oracle – Kamron K. Oct 09 '11 at 20:17

score 2 · Answer 2 · answered Oct 06 '11 at 07:08

2

From your comment i believe you could solve this by using branches, since all you really want to do is redirect the user when he submits his form. Or even better, don't branch, and simply create an on submit process, and define a success and failure message - no seperate page required.

answered Oct 06 '11 at 07:08

Tom

6,988
1
26
40

This would work if both pages were internal, but they are from two seperate servers/sites. I have the users directed to a stored PLSQL procedure which will collect post data (eventually) store it in a database and then redirect the user afterwards to the landing page in my site. – Kamron K. Oct 09 '11 at 20:13
Ah, i'm sorry, the external part wasn't totally clear to me! I've been trying to think of some solutions but keep bumping my head on everything! Is the domain of your apex application different from the domain of your url? If not, then you could consider an application process ajax callback. If yes, maybe a page with a before headers process, but you'd have to try. Other than that, i'm clueless :( – Tom Oct 10 '11 at 07:57
Ya, at this point I still have yet to find an answer to this question (starting to wonder if its possible) if all else fails it is back to JSP =/ but i am determined – Kamron K. Oct 11 '11 at 15:32

score 2 · Answer 3 · answered Oct 12 '11 at 21:40

2

I think the demo at http://htmldb.oracle.com/pls/otn/f?p=9487:65 may answer your question.

answered Oct 12 '11 at 21:40

Jeff E.

21
1

Ya i edited this because i ended up answering my own question. The article you gave me is actually where i began my path towards my answer XD Thank You though – Kamron K. Oct 13 '11 at 15:06

score 0 · Accepted Answer · answered Oct 15 '11 at 17:10

Ok so i have successfully gotten passed post data in my pl sql procedure

    create or replace procedure extinput(KEY in varchar2 := 'KEY',NAME in varchar2 := 'NAME') 
    is
        l_apex_url varchar2(4000);
    begin
        htp.p('Hello extinput');

        htp.p(KEY);
        htp.p(NAME);

        insert into post_table (col2, col3 ) values(KEY, NAME);
    end extinput;

Coming to this conclusion makes me feel like i made this question more complicated then it needed to be. Sorry if that is so, but atleast this problem is now solved.

Errors were being thrown because i was sending 3 parameters but only coded two into the procedure. So either remove an input from the form, or add a third parameter area. here is the finalized form:

    <form Method="POST" action="http://horizon.lcc.edu:7777/pls/apex/extinput" name="form1">
        <P><b>This form has three parameters, which matches the number of parameters the procedure view_http_post_fixed has.</b>
        <table>
            <tr><td>Key:</td>
                <td><input type="text" name="KEY" id="KEY" value="1109"></td>
            </tr>
            <tr><td>Name:</td>
                <td> <input type="text" name="NAME" id="NAME" value="Jeff Eberhard"></td>
            </tr></table>
    <input value="Submit" type="submit">
    </form>

Retrieving POST data from external source in a PL SQL Procedure

4 Answers4