OraFAQ Forum: SQL & PL/SQL » Issue with Sys

Home » SQL & PL/SQL » SQL & PL/SQL » Issue with Sys_context (Oracle 11g, windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Issue with Sys_context [message #648909]

Mon, 07 March 2016 06:33

Vijay55
Messages: 16
Registered: October 2015

Junior Member

Dear All,

There is an issue with our database APPS schema Views which using SYS_Context conditions like below

create or replace view <apps>.<view_Name>
is 
  select * from <> <>
   where
  <condition-1>
   .
   .
  <condition -n>
  AND (SYS_CONTEXT('CDR_VIEW', 'ENABLE_VIEW') = '$USERTYPE$INTERNAL'
  OR SYS_CONTEXT('CDR_VIEW', 'ENABLE_VIEW')   = '$USERTYPE$ASSOCIATED' );

.

If i remove last two conditions(Sys_context) view is working fine. but not with those conditions.

Could any help me to to understand the issue?

Thanks in advance!!

- Vijay

Report message to a moderator

Re: Issue with Sys_context [message #648910 is a reply to message #648909]

Mon, 07 March 2016 06:49

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Come on, man! What does "There is an issue" mean?

Report message to a moderator

Re: Issue with Sys_context [message #648911 is a reply to message #648909]

Mon, 07 March 2016 06:53

Vijay55
Messages: 16
Registered: October 2015

Junior Member

I am not able to see any data from view when i query it.

select * from apps.<view_name>;

Report message to a moderator

Re: Issue with Sys_context [message #648913 is a reply to message #648911]

Mon, 07 March 2016 07:01

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

Are you saying that you simply get "no rows selected"?

Report message to a moderator

Re: Issue with Sys_context [message #648914 is a reply to message #648913]

Mon, 07 March 2016 07:07

Vijay55
Messages: 16
Registered: October 2015

Junior Member

Yes. It is not populating any data. Mean time if i remove last two conditions (Sys_context) from the query, it is populating rows. Sad

Report message to a moderator

Re: Issue with Sys_context [message #648917 is a reply to message #648914]

Mon, 07 March 2016 07:33

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

What does this return:

select SYS_CONTEXT('CDR_VIEW', 'ENABLE_VIEW') from dual;

[Updated on: Mon, 07 March 2016 07:33]

Report message to a moderator

Re: Issue with Sys_context [message #648918 is a reply to message #648917]

Mon, 07 March 2016 07:36

Vijay55
Messages: 16
Registered: October 2015

Junior Member

select SYS_CONTEXT('CDR_VIEW', 'ENABLE_VIEW') from dual;

This returning NULL.

Report message to a moderator

Re: Issue with Sys_context [message #648919 is a reply to message #648918]

Mon, 07 March 2016 07:37

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

NULL is not equal to '$USERTYPE$INTERNAL' or to '$USERTYPE$ASSOCIATED'.

Report message to a moderator

Re: Issue with Sys_context [message #648920 is a reply to message #648919]

Mon, 07 March 2016 07:40

Vijay55
Messages: 16
Registered: October 2015

Junior Member

Thank you:) I understand this. But how to set/fix values for CDR_VIEW. i want to change anything in user profile or object's profile.

Report message to a moderator

Re: Issue with Sys_context [message #648922 is a reply to message #648920]

Mon, 07 March 2016 08:03

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

dbms_Session.set_context. You'll need to sort out whether it is a global context or a local context, have access to the package that controls it, and so on.

Report message to a moderator

Re: Issue with Sys_context [message #648923 is a reply to message #648920]

Mon, 07 March 2016 08:06

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is nothing to "fix" if the condition is that the context must have one of this value or the other one then you have the expected result.
If this condition (SYS_CONTEXT('CDR_VIEW', 'ENABLE_VIEW') = '$USERTYPE$INTERNAL'
OR SYS_CONTEXT('CDR_VIEW', 'ENABLE_VIEW') = '$USERTYPE$ASSOCIATED') is not necessary then remove it from your view definition.

Report message to a moderator

Re: Issue with Sys_context [message #648924 is a reply to message #648923]

Mon, 07 March 2016 08:10

Vijay55
Messages: 16
Registered: October 2015

Junior Member

This is APPS schema view. I should not remove those conditions from view. Due to some misconfiguration i'm getting this error. I am trying to figure out where to change these settings.

Report message to a moderator

Re: Issue with Sys_context [message #648925 is a reply to message #648924]

Mon, 07 March 2016 08:12

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

Has it ever worked? If so, what changed?

Report message to a moderator

Re: Issue with Sys_context [message #648926 is a reply to message #648924]

Mon, 07 March 2016 08:13

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If this is a standard view then the result is the expected one for the product.
If this is not the result you want then create your own view.
Or set or make the product set the value context somewhere.

Report message to a moderator

Re: Issue with Sys_context [message #648930 is a reply to message #648926]

Mon, 07 March 2016 16:10

Bill B
Messages: 1971
Registered: December 2004

Senior Member

The filter on the view is to filter who sees the data. If you are excluded then the application doesn't want you to have the data. This is how oracle applications words. You almost never have access to the under laying tables. You only have access to the views and they filter the views depending on how the application sets the context values.

Report message to a moderator

Previous Topic:	How to use function to extract 1 or 2 digit number from a string
Next Topic:	row to column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 00:30:08 CDT 2024