Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sys_context does not work in function.

Re: sys_context does not work in function.

From: <fitzjarrell_at_cox.net>
Date: 2 Dec 2005 20:14:05 -0800
Message-ID: <1133583245.051178.241500@g47g2000cwa.googlegroups.com>


Comments embedded.
DaLoverhino wrote:
> Hello, I can do this at the sqlplus prompt:
>
> select sys_context( 'USERENV', 'CLIENT_IDENTIFIER') from dual;
>

So can I.

> Yet when I have the following in a function:
>
> f_clientid := sys_context( 'USERENV', 'CLIENT_IDENTIFIER');
>
> Oracle complains when I try to load the function:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 9/3 PL/SQL: SQL Statement ignored
> 9/86 PL/SQL: ORA-00904: "CLIENT_IDENTIFIER": invalid identifier
>
>
> I don't know what's going on, but how do I fix it?
>

I don't know what you're doing, either, as you haven't bothered to post the entire function you've written. But, I can code a simple function to return f_clientid using that same line of code and it 'loads' without error using 9.2.0.6, and also executes without issue:

SQL> create or replace function get_clientid return number is   2 f_clientid number;
  3 begin
  4 f_clientid := sys_context( 'USERENV', 'CLIENT_IDENTIFIER');
  5 return f_clientid;
  6 end;
  7 /

Function created.

SQL> select get_clientid from dual;

GET_CLIENTID


SQL>
> The function will be used as a VPD policy function and f_clientid can
> be null, which the function will be able to handle.
>

Obviously you've done something else in this function which is causing your problem. Or it may be a <ahem> 'bug' fixed by a patchset you've yet to apply, but I won't know that until you decide to post the Oracle version you're running. To be blunt with the dearth of information you've provided you can't, and won't, get much usable assistance. So, post your Oracle version, your operating system and the complete code to your function. Without those pieces of the puzzle your question cannot be answered. not even by the Amazing Kreskin.

> Any thoughts or references would be great. Thanks.

You have my thoughts. It's up to you to provide the requested details.

David Fitzjarrell Received on Fri Dec 02 2005 - 22:14:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US