Home » Developer & Programmer » Application Express & MOD_PLSQL » Invoke external stored procedure from apex (Apex 4.0)
Invoke external stored procedure from apex [message #478755] Tue, 12 October 2010 06:57 Go to next message
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 #478813 is a reply to message #478755] Tue, 12 October 2010 12:50 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens if you substitute '#OWNER#.test' with 'test' (i.e. omit OWNER)? (Possibly, create a synonym and grant EXECUTE on that procedure from owner to user who's calling it, if necessary).
Re: Invoke external stored procedure from apex [message #478857 is a reply to message #478813] Wed, 13 October 2010 01:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #479712 is a reply to message #479016] Mon, 18 October 2010 15:59 Go to previous messageGo to next message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
This is your best ref ==> http://htmldb.oracle.com/pls/otn/f?p=11933:63:
Re: Invoke external stored procedure from apex [message #479747 is a reply to message #479712] Tue, 19 October 2010 03:16 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi Andrew,

OP is asking for calling External Procedure, not Apex Process.
Or may be I didn't found the point you want to show.
Can you please provide some light on what you want to show over there according to OP's requirement.

regards,
Delna
Re: Invoke external stored procedure from apex [message #479841 is a reply to message #479747] Tue, 19 October 2010 13:09 Go to previous messageGo to next message
andrew again
Messages: 2574
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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:36
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'))


[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 Go to previous messageGo to next message
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 ! Smile

[Updated on: Wed, 26 February 2014 06:53]

Report message to a moderator

Re: Invoke external stored procedure from apex [message #608915 is a reply to message #608896] Wed, 26 February 2014 12:33 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank 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?
Re: Invoke external stored procedure from apex [message #608928 is a reply to message #608915] Thu, 27 February 2014 03:46 Go to previous messageGo to next message
imontero
Messages: 4
Registered: February 2014
Junior Member
Littlefoot wrote on Wed, 26 February 2014 18:33
Thank 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! Smile

[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 Go to previous message
Littlefoot
Messages: 19770
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!
Previous Topic: Unable to Download file from DB application
Next Topic: Conditional Column Formatting in an Interactive Report?
Goto Forum:
  


Current Time: Sun Nov 23 18:28:26 CST 2014

Total time taken to generate the page: 0.07950 seconds