Re: Error trying to insert username into report

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 Received on Thu Aug 19 1999 - 16:08:42 CDT