Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Calling the wwsec_api from application express
Calling the wwsec_api from application express [message #206293] Wed, 29 November 2006 09:16 Go to next message
chillindan
Messages: 17
Registered: November 2006
Location: UK
Junior Member
I am trying to call the portal.wwsec_api from within an apex application. The application is parsed using the portal schema so has all the correct grants by default.

The code I am using is:

declare
l_person_rec wwsec_person%rowtype;
begin
l_person_rec := wwsec_api.person_info
(
p_user_name => 'PORTAL'
);

:P3_USER_INFO:=l_person_rec.user_name || l_person_rec.first_name;
exception
when others then
:p3_user_info:=sqlerrm;
end;

This code (minus the item references) runs fine in sqlplus, but errors when I run it in apex. I have narrowed it down to the api that is erroring as I can access the table direct.

Can anyone help with this?
Thanks
Re: Calling the wwsec_api from application express [message #206294 is a reply to message #206293] Wed, 29 November 2006 09:16 Go to previous messageGo to next message
chillindan
Messages: 17
Registered: November 2006
Location: UK
Junior Member
More info for anybody looking at this.

I have tried prefixing the api with portal, that didn't work. The code handles the exception and gives me the amazingly helpful 'User-Defined Exception' error message. I also tried creating my own procedure within the portal schema that calls the api, this executes when run from sqlplus, but again not from apex. If I replace the api calls in the procedure with simple select stuff and then call the procedure from apex it works fine.

So my code in the procedure on the database (in portal schema) looks like this

create or replace procedure get_user_info(pa_user_name wwsec_person.user_name%type,
op_first_name out wwsec_person.first_name%type) IS

l_person_rec wwsec_person%rowtype;
--l_test varchar2(256);

begin

l_person_rec := wwsec_api.person_info ( p_user_name => pa_user_name );

/*select user_name into l_test
from wwsec_person
where user_name = pa_user_name;*/

op_first_name:=l_person_rec.first_name;

exception
when others then
raise;
end;
/
show errors

I have granted execute on this procedure to apex_public_user

My code in apex that calls this procedure looks like this

declare
l_first varchar2(256);
begin
get_user_info('HANNAH', l_first);
:P3_USER_INFO:= l_first;
exception
when others then
:P3_USER_INFO:=sqlerrm;
end;

I am running out of ideas now, so any help would be appeciated.

This is the output from sqlplus when I run the code as apex_public_user

> variable apextest varchar2(256)
> execute portal.get_user_info('HANNAH', :apextest);
BEGIN portal.get_user_info('HANNAH', :apextest); END;

*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWCTX_SSO", line 1803
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWCTX_SSO", line 1637
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PORTAL.WWCTX_SSO", line 1922
ORA-06512: at "PORTAL.WWCTX_API", line 279
ORA-06512: at "PORTAL.WWERR_API_ERROR", line 99
ORA-06512: at "PORTAL.WWERR_API_ERROR", line 222
ORA-06512: at "PORTAL.WWSEC_API_PRIVATE", line 5550
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWCTX_SSO", line 1803
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWCTX_SSO", line 1637
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PORTAL.GET_USER_INFO", line 19
ORA-06512: at line 1

> spool off

Thanks
Re: Calling the wwsec_api from application express [message #206489 is a reply to message #206293] Thu, 30 November 2006 05:10 Go to previous messageGo to next message
chillindan
Messages: 17
Registered: November 2006
Location: UK
Junior Member
I have now solved this problem. If you want to access portal apis from apex you need to set a portal context like this:

portal.wwctx_api_private.set_context( p_user_name => 'portal'
, p_password => 'portal');

This shouldn't impact security as the value of p_password does not have to be your schema password it can be set to anything.
Re: Calling the wwsec_api from application express [message #206505 is a reply to message #206489] Thu, 30 November 2006 06:03 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Thanks for sharing that!
Previous Topic: grant inside trigger
Next Topic: HTML_DB
Goto Forum:
  


Current Time: Thu Mar 28 07:48:06 CDT 2024