1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Table name as a variable

Discussion in 'SQL PL/SQL' started by Pety, May 22, 2014.

  1. Pety

    Pety Guest

    Hi!

    I was wondering lately how to solve this problem:

    I want to extract data from many tables that depend on table names from one particular field.
    People say that a picture is sometimes better than 1000 words. Please, take a short look at the image attached.

    The query must be dynamic because the number of tables is constantly changing(now it's about 20).

    I've found some information about dynamic SQL but I am not certain that it is the easiest way to solve this problem. Any hints would be appreciated :)


    Thank you!
     

    Attached Files:

    • 2.png
      2.png
      File size:
      13.2 KB
      Views:
      10
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,344
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It appears you have multiple tables containing the same column name and definition, which is a bit unusual for a schema. Be that as it may dynamic sql is the way to 'solve' that problem:

    Code (SQL):

    SQL> CREATE TABLE a1(
      2          id      NUMBER,
      3          VALUE   varchar2(20));
    TABLE created.
    SQL>
    SQL> CREATE TABLE b1(
      2          id      NUMBER,
      3          VALUE   varchar2(20));
    TABLE created.
    SQL>
    SQL> CREATE TABLE c1(
      2          id      NUMBER,
      3          VALUE   varchar2(20));
    TABLE created.
    SQL>
    SQL> CREATE TABLE d1(
      2          id      NUMBER,
      3          VALUE   varchar2(20));
    TABLE created.
    SQL>
    SQL> CREATE TABLE e1(
      2          id      NUMBER,
      3          VALUE   varchar2(20));
    TABLE created.
    SQL>
    SQL> CREATE TABLE f1(
      2          id      NUMBER,
      3          VALUE   varchar2(20));
    TABLE created.
    SQL>
    SQL> CREATE TABLE g1(
      2          id      NUMBER,
      3          VALUE   varchar2(20));
    TABLE created.
    SQL>
    SQL> CREATE TABLE h1(
      2          id      NUMBER,
      3          VALUE   varchar2(20));
    TABLE created.
    SQL>
    SQL> CREATE TABLE i1(
      2          id      NUMBER,
      3          VALUE   varchar2(20));
    TABLE created.
    SQL>
    SQL> CREATE TABLE tablist(
      2          tab_name        varchar2(35),
      3          id      NUMBER);
    TABLE created.
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..100 loop
      3                  INSERT INTO a1(id, VALUE)
      4                  VALUES(i, 'Berkle'||i);
      5                  INSERT INTO b1(id, VALUE)
      6                  VALUES(i, 'Cerkle'||i);
      7                  INSERT INTO c1(id, VALUE)
      8                  VALUES(i, 'Derkle'||i);
      9                  INSERT INTO d1(id, VALUE)
     10                  VALUES(i, 'Eerkle'||i);
     11                  INSERT INTO e1(id, VALUE)
     12                  VALUES(i, 'Ferkle'||i);
     13                  INSERT INTO f1(id, VALUE)
     14                  VALUES(i, 'Gerkle'||i);
     15                  INSERT INTO g1(id, VALUE)
     16                  VALUES(i, 'Herkle'||i);
     17                  INSERT INTO h1(id, VALUE)
     18                  VALUES(i, 'Ierkle'||i);
     19                  INSERT INTO i1(id, VALUE)
     20                  VALUES(i, 'Jerkle'||i);
     21          END loop;
     22
     23          commit;
     24  END;
     25  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> INSERT INTO tablist(tab_name, id)
      2  SELECT TABLE_NAME, 4 FROM user_tables WHERE TABLE_NAME LIKE '%1'
      3  AND TABLE_NAME <> 'T1';
    9 ROWS created.
    SQL>
    SQL> SELECT * FROM tablist;
    TAB_NAME                                    ID
    ----------------------------------- ----------
    A1                                           4
    B1                                           4
    C1                                           4
    D1                                           4
    E1                                           4
    F1                                           4
    G1                                           4
    H1                                           4
    I1                                           4
    9 ROWS selected.
    SQL>
    SQL> SET serveroutput ON SIZE 1000000
    SQL>
    SQL> DECLARE
      2          v_sqltxt varchar2(4000);
      3          v_id_val varchar2(20);
      4          cursor get_tab_vals IS
      5          SELECT tab_name, id
      6          FROM tablist;
      7  BEGIN
      8          FOR tabrec IN get_tab_vals loop
      9                  v_sqltxt:='select value from '||tabrec.tab_name||' where id='||tabrec.id;
     10                  EXECUTE immediate v_sqltxt INTO v_id_val;
     11                  dbms_output.put_line(v_id_val);
     12          END loop;
     13  END;
     14  /
    Berkle4
    Cerkle4
    Derkle4
    Eerkle4
    Ferkle4
    Gerkle4
    Herkle4
    Ierkle4
    Jerkle4
    PL/SQL PROCEDURE successfully completed.
    SQL>