This discussion is archived
9 Replies Latest reply: Sep 19, 2013 11:14 PM by e28ce586-4b0a-4eec-902f-638e38bae820 RSS

Table names is stored in a separate table; how can I use field name as table name?

e28ce586-4b0a-4eec-902f-638e38bae820 Newbie
Currently Being Moderated

Dear Experts,

 

My oracle version is,

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

PL/SQL Release 9.2.0.1.0 - Production

CORE    9.2.0.1.0       Production

TNS for 32-bit Windows: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 - Production

 

I have stored table names in a separate table like

Table Name: all_table

Fields are: table_id,table_name,desc

sample record: 1, EMP, EMPLOYEE DETAILS

 

Now I would like to select all contents of a perticular table its name is in all_table.

(ie)

 

select * from (select table_name from all_table where table_id=1);

 

But its not listing all details of EMP table.  Its simply showing the field name 'EMP'.

 

Please help me in this regard.

  • 1. Re: Table names is stored in a separate table; how can I use field name as table name?
    BluShadow Guru Moderator
    Currently Being Moderated

    For this you would need dynamic SQL (either EXECUTE IMMEDIATE or DBMS_SQL) but in reality people are often trying to use dynamic SQL for the wrong reasons.

     

    Explain why you are trying to query tables with a dynamic name that is selected from a table?

    Why do you have table names stored in a table?  What is the actual requirement you're trying to achieve?

  • 2. Re: Table names is stored in a separate table; how can I use field name as table name?
    BluShadow Guru Moderator
    Currently Being Moderated

    p.s. Oracle 9.2.0.1 is a very old and unsupported version of Oracle, as well as being very buggy.  9.2.0.7 was considered the first stable version of 9i, but even that is now old and unsupported.  You really need to upgrade to a recent version of the database, at least 11g, if not the new 12c version.

  • 3. Re: Table names is stored in a separate table; how can I use field name as table name?
    e28ce586-4b0a-4eec-902f-638e38bae820 Newbie
    Currently Being Moderated

    In my company they are already having a table like this.  Just I tried to do this.  Only test data i have posted.  I am not able to post the real date due to the security reasons.

    If you dont mind please describe how to use dbms_sql or execute immediate in sql.

     

    Thanks in advance.

  • 4. Re: Table names is stored in a separate table; how can I use field name as table name?
    BluShadow Guru Moderator
    Currently Being Moderated

    You wouldn't typically use it in SQL, you'd have to write PL/SQL. (there is a way in SQL, but it's not as simple to understand if you're new to this)

     

    e.g.

    SQL> ed
    Wrote file afiedt.buf

      1  declare
      2    cursor ut is
      3      select table_name from user_tables;
      4    cnt number;
      5  begin
      6    for t in ut
      7    loop
      8      execute immediate 'select count(*) from '||t.table_name into cnt;
      9      dbms_output.put_line('Table: '||t.table_name||' ('||cnt||' rows)');
    10    end loop;
    11* end;
    SQL> /
    Table: EMP (14 rows)
    Table: DEPT (4 rows)

     

    PL/SQL procedure successfully completed.

     

    If you truly have a justified reason for using dynamic SQL, then you need to look at understanding bind variables to ensure you use them correctly.

  • 5. Re: Table names is stored in a separate table; how can I use field name as table name?
    BluShadow Guru Moderator
    Currently Being Moderated

    And as an example of using the DBMS_SQL package, which gives greater flexibility over accessing whatever columns may be returned by the query...

     

    this example is from my library of examples and demonstrates a procedure that takes a query string as a parameter and executes it to write the output to a CSV file...

     

    As sys user:

    CREATE OR REPLACE DIRECTORY TEST_DIR AS '\tmp\myfiles'
    /
    GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
    /


    As myuser:

    CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2
                                         ,p_dir IN VARCHAR2
                                         ,p_header_file IN VARCHAR2
                                         ,p_data_file IN VARCHAR2 := NULL) IS
      v_finaltxt  VARCHAR2(4000);
      v_v_val     VARCHAR2(4000);
      v_n_val     NUMBER;
      v_d_val     DATE;
      v_ret       NUMBER;
      c           NUMBER;
      d           NUMBER;
      col_cnt     INTEGER;
      f           BOOLEAN;
      rec_tab     DBMS_SQL.DESC_TAB;
      col_num     NUMBER;
      v_fh        UTL_FILE.FILE_TYPE;
      v_samefile  BOOLEAN := (NVL(p_data_file,p_header_file) = p_header_file);
    BEGIN
      c := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
      d := DBMS_SQL.EXECUTE(c);
      DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
      FOR j in 1..col_cnt
      LOOP
        CASE rec_tab(j).col_type
          WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
          WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
          WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
        ELSE
          DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
        END CASE;
      END LOOP;
      -- This part outputs the HEADER
      v_fh := UTL_FILE.FOPEN(upper(p_dir),p_header_file,'w',32767);
      FOR j in 1..col_cnt
      LOOP
        v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
      END LOOP;
      --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
      UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
      IF NOT v_samefile THEN
        UTL_FILE.FCLOSE(v_fh);
      END IF;
      --
      -- This part outputs the DATA
      IF NOT v_samefile THEN
        v_fh := UTL_FILE.FOPEN(upper(p_dir),p_data_file,'w',32767);
      END IF;
      LOOP
        v_ret := DBMS_SQL.FETCH_ROWS(c);
        EXIT WHEN v_ret = 0;
        v_finaltxt := NULL;
        FOR j in 1..col_cnt
        LOOP
          CASE rec_tab(j).col_type
            WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                        v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
            WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                        v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
            WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                        v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
          ELSE
            DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
            v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
          END CASE;
        END LOOP;
      --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
        UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
      END LOOP;
      UTL_FILE.FCLOSE(v_fh);
      DBMS_SQL.CLOSE_CURSOR(c);
    END;


    This allows for the header row and the data to be written to seperate files if required.

    e.g.

     

    SQL> exec run_query('select * from emp','TEST_DIR','output.txt');

     

    Output.txt file contains:

     

    empno,ename,job,mgr,hiredate,sal,comm,deptno
    7369,"SMITH","CLERK",7902,17/12/1980 00:00:00,800,,20
    7499,"ALLEN","SALESMAN",7698,20/02/1981 00:00:00,1600,300,30
    7521,"WARD","SALESMAN",7698,22/02/1981 00:00:00,1250,500,30
    7566,"JONES","MANAGER",7839,02/04/1981 00:00:00,2975,,20
    7654,"MARTIN","SALESMAN",7698,28/09/1981 00:00:00,1250,1400,30
    7698,"BLAKE","MANAGER",7839,01/05/1981 00:00:00,2850,,30
    7782,"CLARK","MANAGER",7839,09/06/1981 00:00:00,2450,,10
    7788,"SCOTT","ANALYST",7566,19/04/1987 00:00:00,3000,,20
    7839,"KING","PRESIDENT",,17/11/1981 00:00:00,5000,,10
    7844,"TURNER","SALESMAN",7698,08/09/1981 00:00:00,1500,0,30
    7876,"ADAMS","CLERK",7788,23/05/1987 00:00:00,1100,,20
    7900,"JAMES","CLERK",7698,03/12/1981 00:00:00,950,,30
    7902,"FORD","ANALYST",7566,03/12/1981 00:00:00,3000,,20
    7934,"MILLER","CLERK",7782,23/01/1982 00:00:00,1300,,10

     

    The procedure allows for the header and data to go to seperate files if required.  Just specifying the "header" filename will put the header and data in the one file.

    Adapt to output different datatypes and styles are required.

  • 6. Re: Table names is stored in a separate table; how can I use field name as table name?
    e28ce586-4b0a-4eec-902f-638e38bae820 Newbie
    Currently Being Moderated

    Its giving error...

     

    SQL> declare cursor ut is

      2  select table_name from user_tables;

      3  cnt number;

      4  begin

      5  for t in ut

      6  loop

      7  execute immediate 'select count(*) from '||t.table_name into cnt;

      8  dbms_output.put_line('Table: '||t.table_name||' ('||cnt||' rows)');

      9  end loop;

    10  end;

    11  .

    SQL> /

    declare cursor ut is

    *

    ERROR at line 1:

    ORA-00942: table or view does not exist

    ORA-06512: at line 7

  • 7. Re: Table names is stored in a separate table; how can I use field name as table name?
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Whenever you do dynamic SQL, you should  put the entire dynmic statement into a single string variable.  During debugging, display that string before executing it.  If you get a run-time error, that will show you the statement that caused it, which often makes the cause obvious.  For example:

     

    DECLARE

        CURSOR  ut  IS

            SELECT  table_name

            FROM    user_tables;

        cnt       NUMBER;

        sql_txt   VARCHAR2 (1000);

    BEGIN

        FOR  t  IN  ut

        LOOP

            sql_txt := 'SELECT COUNT (*) '

                    || 'FROM "' || t.table_name || '"';

            dbms_output.put_line (sql_txt || ' = sql_txt'); -- For debugging

            EXECUTE IMMEDIATE  sql_txt  INTO cnt;

            dbms_output.put_line (  'Table: '

                                 || t.table_name

                                 || ' ('

                                 || cnt

                                 || ' rows)'

                                 );

        END LOOP;

    END;

    /

    You can comment out the extra put_line call when you're confident that the dyanmic statement is  working.

     

    If you have any non-standard table names (for example, names that contain spaces) you need to enclose the table names in double-quotes, as I did above.

  • 8. Re: Table names is stored in a separate table; how can I use field name as table name?
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Table names is stored in a separate table; how can I use field name as table name?

    This is where I used to point a finger at the absurdity of such an approach, the fundamental and total failure of such a data model, and laugh myself silly.

     

    But after umpteenth time of seeing this brick wall, chosen as "The Solution",  being run into at speed by some ignorant sod, it ceases to be funny - and is just  a sad, sad, spectacle.

     

    If you want to discuss a meaningful solution, as oppose to how to deal with brick walls as solutions, I'm all ears and will do my best to assist.

     

    Now excuse me as I move off to the side, allowing you to continue running into the wall, bleed, in the belief that the speed with which you hit the brick wall, makes you now so much closer to a solution.

  • 9. Re: Table names is stored in a separate table; how can I use field name as table name?
    e28ce586-4b0a-4eec-902f-638e38bae820 Newbie
    Currently Being Moderated

    Thank Blue and Fank,

     

    I have completed the task successfully with your great help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points