Re: using dynanic views from a table trigger

Since v$instance doesn't change over the lifetime of a session, an alternative strategy might be to use a database logon trigger to set some variables into a context, and then use sys_context() instead of executing SQL against a view.

It is also possible that if you check the information available in the 'userenv' context ...

    sys_context('userenv',{variable name}) you may find that the information you want is already available.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearances:
 March 2004 Hotsos Symposium - The Burden of Proof
 March 2004 Charlotte NC OUG - CBO Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1075823772.711238_at_yasure...


> Michel Cadot wrote:

>

> > "Jon" <aaa_at_bbb.ccc> a écrit dans le message de

news:bvo0kk$8hf$1_at_unbe.sarenet.es...


> >

> >>I like to retrieve some columns of a dynamic view: V$INSTANCE from a

trigger


> >>but I do not know the name of the role I need to own ot the privileges

> >>requiered to accomplish this task .. thanks

> >>

> >>

> >

> >

> > If you want to retrieve data from v$instance in a trigger you must have

the select


> > privilege on it directly and not via a role.

> >

> > Regards

> > Michel Cadot

>

> Meaning someone with the appropriate privilege needs to do the following:

>

> GRANT SELECT ON v_$instance TO <schema_name>

>

> Note that the grant is on the object itself ... v_$ ... not on the

> synonym v$.

>

> -- 

> Daniel Morgan

> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

> damorgan_at_x.washington.edu

> (replace 'x' with a 'u' to reply)

>