| Calling the wwsec_api from application express [message #206291] |
Wed, 29 November 2006 09:13  |
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 #206292 is a reply to message #206291] |
Wed, 29 November 2006 09:14  |
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 #206488 is a reply to message #206291] |
Thu, 30 November 2006 05:10 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
