Home » Developer & Programmer » Forms » Can UTL_HTTP be used in the forms. (Oracle form 12c )
Can UTL_HTTP be used in the forms. [message #677866] Thu, 17 October 2019 05:39 Go to next message
Shiv93
Messages: 34
Registered: September 2019
Member
Hi,

I'm trying to call a webservice from oracle PL/SQL package and was able to successfully invoke the webservice. But when i try to implement the same from Oracle forms am not able to call the webservice.

The code am writing is similar to the below snippet.

create or replace
procedure publish_cinema_event
( p_room_id in varchar2
, p_party_size in number
) is
req utl_http.req;
res utl_http.resp;
url varchar2(4000) := 'http://localhost:9002/cinema';
name varchar2(4000);
buffer varchar2(4000);
content varchar2(4000) := '{"room":"'||p_room_id||'", "partySize":"'||p_party_Size||'"}';

begin
req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
utl_http.set_header(req, 'user-agent', 'mozilla/4.0');
utl_http.set_header(req, 'content-type', 'application/json');
utl_http.set_header(req, 'Content-Length', length(content));

utl_http.write_text(req, content);
res := utl_http.get_response(req);
-- process the response from the HTTP call
begin
loop
utl_http.read_line(res, buffer);
dbms_output.put_line(buffer);
end loop;
utl_http.end_response(res);
exception
when utl_http.end_of_body
then
utl_http.end_response(res);
end;
end publish_cinema_event;


This works fine when im writing this piece of code as a PL/SQL package but im having issues with oracle forms because i dont think the UTL_HTTP command is being recogonized in the oracle forms. Any members have witnessed such issue ? If so it would be great if you can share your insights.

Regards,
Siva

[Updated on: Thu, 17 October 2019 05:42]

Report message to a moderator

Re: Can UTL_HTTP be used in the forms. [message #677867 is a reply to message #677866] Thu, 17 October 2019 06:12 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm surprised forms doesn't recognize it but if so there's a simple work-around - put the code in a package and call that from the form.
Re: Can UTL_HTTP be used in the forms. [message #677871 is a reply to message #677867] Thu, 17 October 2019 11:56 Go to previous messageGo to next message
Shiv93
Messages: 34
Registered: September 2019
Member
Hi Cookiemonster,

I have already done that part of putting that code in package and it's working fine.But now my team wants it to the other way like calling it from the Oracle forms and that's where I had the issue of this UTL_HTTP. In packages i can run the below command and can use the utl_http
Grant execute on UTL_HTTP to User.

But in forms I don't know how to use the utl_http.


Re: Can UTL_HTTP be used in the forms. [message #677876 is a reply to message #677871] Fri, 18 October 2019 15:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Forms is just another tool to access an Oracle database.

Its PL/SQL engine doesn't (or, should I say, didn't) recognize some features available in database's PL/SQL engine (such as analytical functions), but - UTL_HTTP is a database package. If it works in SQL*Plus, it *should* work in Forms as well.

If you granted execute privileges on it to user that runs that form, I presume that it would work.

You say that you have problems, but never told us which problems, exactly. Any error? If so, which one? Please, share ORA-xxxxx or FRM-xxxxx codes. Also, run the form in debug mode so that you could follow its execution and pinpoint the line which causes trouble.
Re: Can UTL_HTTP be used in the forms. [message #677877 is a reply to message #677876] Sat, 19 October 2019 00:32 Go to previous messageGo to next message
Shiv93
Messages: 34
Registered: September 2019
Member
Hi Littlefoot,

So the issue is i have used the same snippet which i used in the PL/SQL package in the Forms and it doesnt recogonizes the utl_http statement. And you were right about the Granting the execute permission to the user, in package i granted the user execute permission to the user like the below statement.
Grant Execute on UTL_HTTP to User

But how do i provide the same in forms?

I tried like below in forms

Begin
Grant Execute on UTL_HTTP to User
End;
But i get a compilation error of forms with above statement.
Re: Can UTL_HTTP be used in the forms. [message #677878 is a reply to message #677877] Sat, 19 October 2019 01:05 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Granting any privilege (to yourself, eh?) within Forms doesn't make sense.

You don't need that, if user you're connected to in Forms is the same user that has already been granted that privilege.

For example, suppose there are two users in your database: Scott and Mike. Connected as SYS, you did this:
grant execute on utl_http to scott;
Then, if you connected as Scott (e.g. sqlplus scott/tiger), you were able to use UTL_HTTP. On the other hand, if you connected as Mike (sqlplus mike/lion), Mike can't use it as it wasn't granted the privilege.

The same goes for Forms: if you connected as Scott, UTL_HTTP is available to you. Otherwise, if you connected as Mike, it is not which means that if Mike is going to develop that form, it has to acquire execute privilege on UTL_HTTP first - through SQL*Plus (or whichever tool you use), not Forms itself.


Re: Can UTL_HTTP be used in the forms. [message #677879 is a reply to message #677878] Sat, 19 October 2019 13:24 Go to previous messageGo to next message
Shiv93
Messages: 34
Registered: September 2019
Member
Hi Littlefoot,
I will let you know what I have done so for.

Initially I granted the execute permission on utl_http to the user where I'm gonna have the pl/sql package in pl/SQL using Oracle SQL developer.
E.g I have connected as Root user in SQL developer and had executed the below comments
Grant execute on utl_http to user

And then I created the package which has the utl_statements and it executed successfully and was able to invoke the webservice from the package.


Now I don't want the webservice to be called from package but i need the same functionality to be called from Oracle forms.
So what I have did is copied the code from package and placed it in one of the mmb(menu file) because on click of the menu I need the webservice to be called. And to your information I used to compile the menu in the Unix box where we used to have the dependency files so I don't have any user connected.

I hope i gave you some clarity on my process. Please let me know if you need further information
Re: Can UTL_HTTP be used in the forms. [message #677895 is a reply to message #677879] Mon, 21 October 2019 04:17 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You still haven't told us what forms is doing - are you getting an error? is it hanging?

And you can't compile forms without being connected to the DB as far as I'm aware, the process needs a connection.
Previous Topic: Unable to open oracle form
Next Topic: Image item clearing and image not loading
Goto Forum:
  


Current Time: Thu Mar 28 08:16:05 CDT 2024