CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
user1876832
user1876832

Reputation: 31

Dynamic table name in pl/sql

I need to select columns from a table Table_A, However there is another table which has the same schema Table_B. The query should determine the from table dynamically. For ex. if Table_A has more rows then use Table_A else use Table_B.

Query something like this select employee, salary, id from (condition to count rows and select the table )table;

Is this possible without using cursors and EXECUTE IMMEDIATE??.

Upvotes: 1

Views: 2036

Answers (2)

Craig
Craig

Reputation: 5830

I am completely guessing at what you are really trying to do, but I am thinking you want to use a synonym. I am guessing that there is some sort of event that triggers when you should be using TableA vs. TableB. Create a synonym "my_table" pointing to TableA and have your view select from "my_table". Then whenever you want your view to point to TableB instead, you just switch your synonym to point to TableB, and you don't have to do anything to the view.

For more info, read about synonyms in the Concepts Guide.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231851

Normally, you would use dynamic SQL for this sort of thing. That would involve either using the DBMS_SQL package, EXECUTE IMMEDIATE or doing an OPEN <<cursor>> FOR <<SQL statement string>>.

If you really want to use static SQL, you could query both tables and only return one set of results. I cannot envision a situation where this would really make sense but you can certainly do it

Create a FOO and a FOO2 table. FOO2 has two rows to the one row from FOO

SQL> create table foo( col1 number );

Table created.

SQL> create table foo2( col1 number );

Table created.

SQL> insert into foo values( 1 );

1 row created.

SQL> insert into foo2 values( 1 );

1 row created.

SQL> insert into foo2 values( 2 );

1 row created.

Run the query. This will return all the data from FOO2

SQL> ed
Wrote file afiedt.buf

  1  select col1
  2    from (select the_union.*,
  3                 max(cnt) over () max_cnt
  4            from (select col1, count(*) over () cnt from foo
  5                  union all
  6                  select col1, count(*) over () from foo2) the_union)
  7*  where cnt = max_cnt
SQL> /

      COL1
----------
         1
         2

Insert more rows into FOO. Now the same query will return all the data from FOO

SQL> insert into foo values( 3 );

1 row created.

SQL> insert into foo values( 5 );

1 row created.

SQL> commit;

Commit complete.

SQL> select col1
  2    from (select the_union.*,
  3                 max(cnt) over () max_cnt
  4            from (select col1, count(*) over () cnt from foo
  5                  union all
  6                  select col1, count(*) over () from foo2) the_union)
  7   where cnt = max_cnt;

      COL1
----------
         1
         3
         5

As I said, though, I cannot fathom a situation where it would actually make sense to do this.

Upvotes: 2

Related Questions