Re: Error trying to insert username into report

Sybrand Bakker wrote:
>
> On Thu, 19 Aug 1999 10:39:15 -0800, Calvin Crumrine
> <Calvin_Crumrine_at_dced.state.ak.us> wrote:
>
> >I'm trying to insert the username into a report in Reports 6.0
> >
> >I created a function as follows:
> >
> >create function get_name return varchar2 is
> >v_name varchar2(20);
> >begin select username into v_name from v$session
> >where audsid=userenv('SESSIONID');
> >return(v_name);
> >end get_name;
> >
> >I can use that function in SQL*PLUS and it returns the username. I'm trying to use it
> >in a BeforeReport trigger as follows:
> >
> >function BeforeReport return boolean is
> >begin
> > :P_User_Name := lids.get_name;
> > return (TRUE);
> >end;
> >
> >When I try to compile it I get a dialog window that says:
> >
> >PDE-PER001 Internal error (REP-3335: unhandled internal CA error.deucx 9 8)
> >
> >The compiler error window reports:
> >
> >Error 0 at Line 0, Column 0
> >ORA-4029: error ORA-904 occurred when querying ORA_KGLR7_DEPENDENCIES
> >
> >Anybody got any idea what I'm doing wrong? Better yet, any ideas how I can do what I
> >want? Basically I want to put the username into a field on the report. What I'm
> >trying above is loading it into a user parameter which I use as the field source.
>
> This is because your access to v$session is through a role. PL/SQL
> doesn't support roles. Can't you use the USER pseudocolumn: select
> user from dual?
>
> Hth,
>
> Sybrand Bakker, Oracle DBA

Thanks for the suggestion, but I get the same error. I want to be sure I've got this straight, of course. Since you said my problem was accessing v$session what I did was modify the function.

Should I put the select statement directly in the trigger instead? (maybe my problem is accessing the function) I tried the following:

function BeforeReport return boolean is begin
  select user into :P_User_Name from dual;   return (TRUE);
end;

and I got a nearly identical error. This time ORA-904 occurred when querying ORA_KGLR7_IDL_SB4. Received on Thu Aug 19 1999 - 16:44:27 CDT