Re: Calling Portal wwsec_api from Apex

[Quoted] 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

On Nov 29, 3:26 pm, danny.ro..._at_oracle.com wrote:
> 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
Received on Wed Nov 29 2006 - 16:27:38 CET