Forum Stats

  • 3,814,048 Users
  • 2,258,810 Discussions
  • 7,892,511 Comments

Discussions

Query to find out the preferences and section_groups

mNem
mNem Member Posts: 1,380 Gold Trophy
edited Dec 10, 2019 11:17PM in Text

How do I find out what are the ...

1. default preferences available to the current user

2. user defined preferences and their attributes

3. user defined section_groups and their attributes

The schema has CTXAPP granted.

I am looking for queries that do NOT involve dba_* tables or views (if possible).

Thanks.

Best Answer

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Dec 10, 2019 9:15AM Answer ✓

    1. default preferences available to the current user

    If you mean pre-defined preferences then:

    SELECT pre_name FROM ctx_user_preferences WHERE pre_owner = 'CTXSYS';

    2. user defined preferences and their attributes

    SELECT pre_name, prv_value FROM ctx_user_preferences, ctx_user_preference_values WHERE pre_name = prv_preference (+);

    The outer join is necessary because some preferences will not have attributes.

    3. user defined section_groups and their attributes

    Depending on what info you're after, you'll want to play around with ctx_user_section_groups and ctx_user_sections

    A simple listing of the sections in section groups would be

    SELECT sgp_name, sec_name FROM ctx_user_section_groups, ctx_user_sections WHERE sgp_name = sec_section_group (+);

    mNem

Answers

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Dec 10, 2019 9:15AM Answer ✓

    1. default preferences available to the current user

    If you mean pre-defined preferences then:

    SELECT pre_name FROM ctx_user_preferences WHERE pre_owner = 'CTXSYS';

    2. user defined preferences and their attributes

    SELECT pre_name, prv_value FROM ctx_user_preferences, ctx_user_preference_values WHERE pre_name = prv_preference (+);

    The outer join is necessary because some preferences will not have attributes.

    3. user defined section_groups and their attributes

    Depending on what info you're after, you'll want to play around with ctx_user_section_groups and ctx_user_sections

    A simple listing of the sections in section groups would be

    SELECT sgp_name, sec_name FROM ctx_user_section_groups, ctx_user_sections WHERE sgp_name = sec_section_group (+);

    mNem