Re: DBMS_RLS: Fine-Grained Access Control - Table name substitution

If you are running Oracle 9, then the following MIGHT be workable and appropriate:

Put the db_link name into a context variable, e.g.

    dbms_session.set_context('batch_control','db_linkname','dblink1');

Create a pipelined function for each table structure that returns an object table that matches the underlying table structure, and takes a single text parameter.

Create a view that selects from the function, roughly create or replace vlew v_tab1 as

    select * from

        table(pipeline_function_tab1(
            sys_context('batch_control','db_linkname')
        )

The pipeline function needs to use dynamic SQL to open a cursor to the right table at the right database link, and start piping data from it.

For batch load purposes this can run at array processing speeds across the layers, but you might have some latching issues at high concurrency.

--
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


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

____Finland__September 22nd - 24th
____Norway___September 25th - 26th


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA_(CA, TX)_August
____USA__October
____UK___November


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


"Bepy" <ramengo_at_hotmail.com> wrote in message
news:bek149$m50$1_at_lacerta.tiscalinet.it...


> In an ideal environment that could be a good solution, but... we're

on a


> E-Business Suite environment and neither dblink names nor other

schema


> objects are under our control.

>

> I'm facing the sensation there's no way in Oracle to have

parametrized views


> with dynamic SQL...

>

> Thanks to everybody.

>

>