| Invoke external stored procedure from apex [message #478755] |
Tue, 12 October 2010 06:57  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
The below is the sample procedure created.
create or replace procedure test
is
begin
htp.prn('Calling external procedure from apex');
end;
My problem is, in my apex page I am writing a javascript function to call the stored procedure using ajax like below.For test purpose I have call the javascript function in page onload event.
I am not able to figure out where I am doing the mistake!
Is there anyone who knows how to call an external procedure from
apex ( I am not writing the procedure inside an demand process
or neither I want to call it creating any anonymous block ).Is there a way?
<script type="text/javascript">
function ins_data()
{ alert('entering');
alert():
var get=new htmldb_Get(null,null,null,&APP_PAGE_ID.,null,'#OWNER#.test',null);
alert(var);
get.get();
//alert(gReturn);
get=null;
}
</script>
Thanks in advance!
Regards
Ved
|
|
|
|
|
|
| Re: Invoke external stored procedure from apex [message #478857 is a reply to message #478813] |
Wed, 13 October 2010 01:09  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
yes, I did even tried removing #OWNER#. as well. I created the test procedure and created a synonym for it and also granted execute privilege.
I even tried replacing &APP_PAGE_ID. by null ( I don't think that it would be required as you are calling an external procedure ). Not sure where I am wrong!!
Do I need to configure anything to call it from apex? DAD?
Thanks for the response!
The below is the returned value in alert message popup
test<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<HTML><HEAD>
<TITLE>404 Not Found</TITLE>
</HEAD><BODY>
<H1>Not Found</H1>
The requested URL /pls/apex/test was not found on this server.<P>
<HR>
<ADDRESS>Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server Server at #########1.###.##.### Port 7778</ADDRESS>
</BODY></HTML>
ED: masked with #
Oracle Database version is: 10.2.0.4.0 - 64bit
One thing I noticed is that the port number in the apex url for my application is not the one displayed in the alert message above. Its not matching! Does it needs to be the same? Do we need to configure anything to call external procedure from apex?
Regards
Ved
[Updated on: Wed, 13 October 2010 01:36] Report message to a moderator |
|
|
|
| Re: Invoke external stored procedure from apex [message #479016 is a reply to message #478857] |
Thu, 14 October 2010 04:44 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
var get=htmldb_Get(null,null,null,&APP_PAGE_ID.,null,'test',null)
In above, I found that I can not pass null as the last parameter.
It works fine when I am passing at least one parameter to call the procedure.
I modified the procedure 'test' with a parameter and its working fine.But what about those procedures that does not have parameters? I don't know!
There is some alternative way:
We can also call a procedure in anonymous block at item level by selecting the source as pl/sql.
If you have come up with any ideas please share.
ED:Hope that it would help others
Regards
Ved
[Updated on: Thu, 14 October 2010 04:45] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Invoke external stored procedure from apex [message #479841 is a reply to message #479747] |
Tue, 19 October 2010 13:09 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
I have no idea what OP meant an external procedure, but shows a normal pl/sql stored procedure. So that pl/sql stored proc is presumably in the local database that hosts Apex. Technically the stored proc could be over a DB link too, but htp.prn would print out on the remote DB, not the local one. You could also directly call a stored proc via a URL that could point anywhere, but if that was an Apex URL, you need to configure that in wwv_flow_epg_include_mod_local (for EPG) and that's very unlikely.
The only clean way that I'm aware of to call an on-demand pl/sql stored proc in Apex is via htmldb_Get.
|
|
|
|
| Re: Invoke external stored procedure from apex [message #479892 is a reply to message #479841] |
Wed, 20 October 2010 00:36 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
You have an existing procedure test that you want to call from apex.
say, sample procedure is..
create or replace procedure test(p_in in number)
is
begin
htp.prn('your input is:'|| p_in);
end;
write a javascript in HTML header:
<script type="text/javascript">
function ins_data(pThis)
{
var params='p_in= '+pThis;
var get=new htmldb_Get(null,null,null,&APP_PAGE_ID.,null,'test',params);
get.get();
//alert(gReturn);
get=null;
}
</script>
call the javascript:
onClick=ins_data($v('P1_INPUT_TXT'))
In the above case, I am calling the existing procedure directly.
There is also another approach as I have already mentioned in my previous post.
I have already posted the solution that I asked for. And this topic can be closed.
Thanks Andrew for the clarification.
Regards
Ved
[Updated on: Wed, 20 October 2010 00:38] Report message to a moderator |
|
|
|
| Re: Invoke external stored procedure from apex [message #608831 is a reply to message #479892] |
Tue, 25 February 2014 05:29 |
 |
imontero
Messages: 4
Registered: February 2014
|
Junior Member |
|
|
Hi,
First of all I'd like to say that this post was very helpful.
I have installed APEX 4.2, not sure if that's got anything to do or not. I am able to call an on-demand process from JavaScript just fine, however, when I try to call a procedure stored on the DB, I just can't. I've tried to debug as much as I could, but when it gets to get() it just does nothing, it doesn't even break. I've copied and pasted the code below as it is and it doesn't work for me. The ITEM triggers the onclick action and executes the javascript function, but it doesn't seem to be able to connect to the database. I've also got my procedure stored in the parsing schema. I don't know what else to do. Help please! Is there any pre set-up I might be missing?
Its_me_ved wrote on Wed, 20 October 2010 06:36You have an existing procedure test that you want to call from apex.
say, sample procedure is..
create or replace procedure test(p_in in number)
is
begin
htp.prn('your input is:'|| p_in);
end;
write a javascript in HTML header:
<script type="text/javascript">
function ins_data(pThis)
{
var params='p_in= '+pThis;
var get=new htmldb_Get(null,null,null,&APP_PAGE_ID.,null,'test',params);
get.get();
//alert(gReturn);
get=null;
}
</script>
call the javascript:
onClick=ins_data($v('P1_INPUT_TXT'))
[Updated on: Tue, 25 February 2014 05:32] Report message to a moderator |
|
|
|
| Re: Invoke external stored procedure from apex [message #608896 is a reply to message #608831] |
Wed, 26 February 2014 06:50 |
|
imontero
Messages: 4
Registered: February 2014
|
Junior Member |
|
|
Just fixed this myself.
The Embedded PL/SQL Gateway (EPG) wasn't allowed to access my procedure. You do this by modifying the APEX function wwv_flow_epg_include_mod_local.
CREATE OR REPLACE function APEX_040100.wwv_flow_epg_include_mod_local(
procedure_name in varchar2)
return boolean
is
begin
-- remove this statement when you modify this function
--
-- Administrator note: the procedure_name input parameter may be in the format:
--
-- procedure
-- schema.procedure
-- package.procedure
-- schema.package.procedure
--
-- If the expected input parameter is a procedure name only, the IN list code shown below
-- can be modified to itemize the expected procedure names. Otherwise you must parse the
-- procedure_name parameter and replace the simple code below with code that will evaluate
-- all of the cases listed above.
--
if upper(procedure_name) in (
'SCHEMA_NAME.PROCEDURE_NAME') then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;
/
http://matthiashoys.wordpress.com/2013/02/22/http-404403-executing-apex-procedure-from-url-with-oracle-11g-xe/#comment-702
I also had to GRANT EXECUTE ON schema_name.procedure_name ON anonymous;
Hope this helps to others ! 
[Updated on: Wed, 26 February 2014 06:53] Report message to a moderator |
|
|
|
|
|
| Re: Invoke external stored procedure from apex [message #608928 is a reply to message #608915] |
Thu, 27 February 2014 03:46 |
|
imontero
Messages: 4
Registered: February 2014
|
Junior Member |
|
|
Littlefoot wrote on Wed, 26 February 2014 18:33Thank you for sharing the information. However, did you correctly type the GRANT statement? Apart from ON that should be TO, was the grantee ANONYMOUS or PUBLIC?
Oups, typo! Yep I meant to say:
GRANT EXECUTE ON schema_name.procedure_name TO ANONYMOUS;
And yep, it was ANONYMOUS.
Thanks for pointing that out!
[Updated on: Thu, 27 February 2014 03:47] Report message to a moderator |
|
|
|
| Re: Invoke external stored procedure from apex [message #608944 is a reply to message #608928] |
Thu, 27 February 2014 05:26 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Aha, right - now I checked what that ANONYMOUS user is:
Quote:
Used by the PL/SQL gateway that enables a Web browser to invoke a PL/SQL stored procedure through an HTTP listener.
Right, makes sense, I thought that it was PUBLIC (which is a role and isn't related to PL/SQL gateway, I suppose).
Thank you!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
