CODEWITHSUNDEEP
databaseoracle-databasehard-coding
Ahmed Atia
Ahmed Atia

Reputation: 17960

How to refer dynamically to another database user?

I've a case in which I need to refer to another database user. I've to hard code database user name in view while referring to it.

SELECT * FROM  eg001t3.DUAL; // example.

Is there a way to refer to that db user (eg001t3) from view dynamically or based on a database setup?

Upvotes: 0

Views: 477

Answers (4)

dpbradley
dpbradley

Reputation: 11915

Another option that might work for you (depending on the application environment from which you need to do this) is to temporarily change your namespace to the schema of interest:

  1. alter session set current_schema = eg001t3;
  2. select * from whateverTableBelongsToEG001T3; -- no schema qualifier needed here
  3. alter session set current_schema = ... -- back to your connecting schema name

set current_schema doesn't bypass the Oracle privilege model in any way - you'll still need at least SELECT on the other schema's tables of interest.

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

I add a new answer to demonstrate another method suggested by jva. All the tables must share a common structure (so that Oracle will be able to know the datatype of the columns of the view at compile time).

Setup:

-- create 2 schemas
CREATE USER u1 IDENTIFIED BY u1;
CREATE USER u2 IDENTIFIED BY u2;
GRANT RESOURCE TO u1;
GRANT RESOURCE TO u2;

-- one table in each schema
CREATE TABLE u1.t AS 
   SELECT 2 * ROWNUM ID, 'foo' DATA FROM dual CONNECT BY LEVEL <= 5;
CREATE TABLE u2.t AS 
   SELECT 2 * ROWNUM - 1 ID, 'bar' DATA FROM dual CONNECT BY LEVEL <= 5;
GRANT SELECT ON u2.t TO u1;

-- the common structure
CREATE TYPE u1.t_row AS OBJECT (ID NUMBER, DATA VARCHAR2(3));
/
CREATE TYPE u1.t_row_list AS TABLE OF u1.t_row;
/

CREATE OR REPLACE PACKAGE u1.test_pck IS
   schema_name VARCHAR2(30) := 'U1';
   FUNCTION select_t RETURN u1.t_row_list PIPELINED;
END test_pck;
/

--Definition of the pipelined function and the view:
CREATE OR REPLACE PACKAGE BODY u1.test_pck IS

   FUNCTION select_t RETURN u1.t_row_list PIPELINED IS
      l_rc     SYS_REFCURSOR;
      l_id     NUMBER;
      l_data   VARCHAR2(3);
   BEGIN
      OPEN l_rc FOR 'SELECT id, data 
                       FROM ' || dbms_assert.schema_name(schema_name) || '.t';
      LOOP
         FETCH l_rc
            INTO l_id, l_data;
         EXIT WHEN l_rc%NOTFOUND;
         PIPE ROW (u1.t_row(l_id, l_data));
      END LOOP;
      CLOSE l_rc;
   END select_t;

END test_pck;
/

CREATE OR REPLACE VIEW u1.v AS 
SELECT ID, DATA 
  FROM TABLE(u1.test_pck.select_t);

You would then define the global variable in the package containing the schema name and then query the view:

SQL> EXEC u1.test_pck.schema_name := 'U1';

PL/SQL procedure successfully completed
SQL> SELECT * FROM u1.v;

        ID DATA
---------- ----
         2 foo
         4 foo
         6 foo
         8 foo
        10 foo
SQL> EXEC u1.test_pck.schema_name := 'U2';

PL/SQL procedure successfully completed
SQL> SELECT * FROM u1.v;

        ID DATA
---------- ----
         1 bar
         3 bar
         5 bar
         7 bar
         9 bar

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

In pl/sql you would use EXECUTE IMMEDIATE or DBMS_SQL to dynamically reference objects.

Exemple with EXECUTE IMMEDIATE:

SQL> VARIABLE dyn_user VARCHAR2(30);
SQL> EXEC :dyn_user := 'SYS';

PL/SQL procedure successfully completed
dyn_user
---------
SYS
SQL> DECLARE
  2     ln NUMBER;
  3  BEGIN
  4     EXECUTE IMMEDIATE 'SELECT 1
  5                          FROM ' || dbms_assert.schema_name(:dyn_user) 
  6                                 || '.DUAL'
  7        INTO ln;
  8     dbms_output.put_line(ln);
  9  END;
 10  /

1

PL/SQL procedure successfully completed

You can also use dynamically built REF CURSOR:

SQL> DECLARE
  2     lc SYS_REFCURSOR;
  3     ln NUMBER;
  4  BEGIN
  5     OPEN lc FOR 'SELECT 1
  6                    FROM ' || dbms_assert.schema_name(:dyn_user) || '.DUAL
  7                   CONNECT BY level <= 2';
  8     LOOP
  9        FETCH lc
 10           INTO ln;
 11        EXIT WHEN lc%NOTFOUND;
 12        dbms_output.put_line(ln);
 13     END LOOP;
 14     CLOSE lc;
 15  END;
 16  /

1
1

As shown you can use DBMS_ASSERT to validate your input.

Upvotes: 3

cagcowboy
cagcowboy

Reputation: 30828

There may be more elegant options, but you could either create synonyms on the fly or use Dynamic SQL / EXECUTE IMMEDIATE.

Upvotes: 1

Related Questions