9 Replies Latest reply: Sep 20, 2013 1:14 AM 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

      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

          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

            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

              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

                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

                  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

                    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

                      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?
                        Billy~Verreynne

                        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

                          Thank Blue and Fank,

                           

                          I have completed the task successfully with your great help.