Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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
Now I feel stupid but the solution turned out to be simple. Rather than putting the name into a parameter field I found that you can put it in a formula column as follows:
function CF_User_NameFormula return char is
begin
return(user);
end;
Received on Fri Aug 27 1999 - 17:53:05 CDT