14 Replies Latest reply: May 10, 2013 3:02 AM by BluShadow RSS

    select query problem

    Gaurav_91
      When i run this :
      SQL> select table_name from dba_tables where OWNER='SCOTT' and table_name='EMP';

      I get:

      TABLE_NAME
      ------------------------------
      EMP

      When i run this:
      SQL> select * from (select table_name from dba_tables where OWNER='SCOTT' and table_name='EMP');

      I get:
      TABLE_NAME
      ------------------------------
      EMP

      But from 2nd query i want that it show all the rows from emp table.(like it shows in select * from emp).

      How to solve this??
        • 1. Re: select query problem
          jeneesh
          Just give
          select * from scott.emp;
          As you already know the owner and the table name

          If you dont know, you have to use DYNAMIC SQL
          • 2. Re: select query problem
            John Stegeman
            You cannot do that (you cannot directly use a "variable" as a table name)

            You'd have to dynamically construct and execute a SQL statement to do that.

            What is the underlying problem you're trying to solve?
            • 3. Re: select query problem
              myOra_help
              No can't do this. You are suppose to pass TABLE name dynamically, thus you must use dynamic SQL.
              • 4. Re: select query problem
                Gaurav_91
                I know this.

                But i can't use scott.emp
                I have to pass the table name at run time.

                I am using this in function.
                But function don't use dynamic sql.
                Dynamic sql is used in procedure but i can't use procedure.
                • 5. Re: select query problem
                  John Stegeman
                  Without dynamic SQL, it cannot be done.

                  And saying that a procedure can use dynamic sql whereas a function cannot is utterly wrong.

                  proof:
                    1  create or replace function x(t in varchar2) return sys_refcursor as
                    2  a sys_refcursor;                                                   
                    3  begin                                                              
                    4  open a for 'select * from ' || t;                                  
                    5  return a;                                                          
                    6* end;                                                               
                  SQL> /                                                                  
                                                                                          
                  Function created.                                                       
                                                                                          
                  SQL> var yy refcursor                                                   
                  SQL> exec :yy := x('EMPLOYEES');                                        
                                                                                          
                  PL/SQL procedure successfully completed.      
                  • 6. Re: select query problem
                    Gaurav_91
                    Means i can't use dynamic sql
                    • 7. Re: select query problem
                      MaheshKaila
                      Hello,

                      We can achieve desire output as below,
                      select   table_name,
                               TO_NUMBER (
                                  EXTRACTVALUE (xmltype (DBMS_XMLGEN.getxml ('select count(*) X from ' || table_name)), '/ROWSET/ROW/X')
                               )
                                  COUNT
                        from   dba_tables
                       where   OWNER = 'SCOTT' and table_name = 'EMP'
                      Regards,
                      Mahesh Kaila
                      • 8. Re: select query problem
                        jeneesh
                        1004937 wrote:
                        Means i can't use dynamic sql
                        Then you cant do this..
                        • 9. Re: select query problem
                          John Stegeman
                          We can achieve desire output as below,
                          That:

                          * is rubbish
                          * doesn't return the desired output
                          * Is much more complicated than just using dynamic sql directly
                          • 10. Re: select query problem
                            Gaurav_91
                            Hi Mahesh,

                            When i executed this, i got the error:

                            SQL> select table_name,
                            2 TO_NUMBER (
                            3 EXTRACTVALUE (xmltype (DBMS_XMLGEN.getxml ('select count(*) X from ' || table_name)), '/ROWSET/ROW/X')
                            4 )
                            5 COUNT
                            6 from dba_tables
                            7 where OWNER = 'SCOTT' and table_name = 'EMP';
                            EXTRACTVALUE (xmltype (DBMS_XMLGEN.getxml ('select count(*) X from ' || table_name)), '/ROWSET/ROW/X')
                            *
                            ERROR at line 3:
                            ORA-19202: Error occurred in XML processing
                            ORA-00942: table or view does not exist
                            ORA-06512: at "SYS.DBMS_XMLGEN", line 176
                            ORA-06512: at line 1
                            • 11. Re: select query problem
                              MaheshKaila
                              Sorry I misunderstood your quetion. Query will return table count not records. Will only work on 10g + environment.
                              • 12. Re: select query problem
                                John Stegeman
                                The ONLY option you have is to use dynamic sql.
                                • 13. Re: select query problem
                                  _Karthick_
                                  Dynamic SQL is a result of BAD Database Design. So do you have your table details stored in custom tables? A thoughtful Architect would have thought it would make an application more flexible. But what they end up with is a application that perform poorly and which becomes a maintenance night mare.

                                  Basics of oracle is that the objects used in a SQL statement must be known to oracle when it parses the code. The second step of parsing semantic analysis requires it. So the only way to pass database object dynamically is using Dynamic SQL.

                                  Below is a basic example using a function.
                                  SQL> create or replace function print_table
                                    2  (
                                    3    pTablename in varchar2
                                    4  )
                                    5  return sys_refcursor
                                    6  as
                                    7    l_ref_cursor sys_refcursor;
                                    8  begin
                                    9    open l_ref_cursor for 'select * from ' || pTableName;
                                   10    return l_ref_cursor;
                                   11  end;
                                   12  /
                                   
                                  Function created.
                                   
                                  SQL> var rc refcursor
                                  SQL> exec :rc := print_table('emp')
                                   
                                  PL/SQL procedure successfully completed.
                                  SQL> print rc
                                   
                                       EMPNO ENAME  JOB              MGR HIREDATE         SAL        COM     DEPTNO
                                  ---------- ------ --------- ---------- --------- ---------- ---------- ----------
                                        7369 SMITH  CLERK           7902 02-APR-81       2975          0         20
                                        7499 ALLEN  SALESMAN        7698 20-FEB-81       1600        300         30
                                        7521 WARD   SALESMAN        7698 22-FEB-81       1250        500         30
                                        7566 JONES  MANAGER         7839 02-APR-81       2975          0         20
                                        7654 MARTIN SALESMAN        7698 28-SEP-81       1250       1400         30
                                        7698 BLAKE  MANAGER         7839 01-MAY-81       2850          0         30
                                        7782 CLARK  MANAGER         7839 09-JUN-81       2450          0         10
                                        7788 SCOTT  ANALYST         7566 19-APR-87       3000          0         20
                                        7839 KING   PRESIDENT            17-NOV-81       5000          0         10
                                        7844 TURNER SALESMAN        7698 08-SEP-81       1500          0         30
                                        7876 ADAMS  CLERK           7788 23-MAY-87       1100          0         20
                                   
                                  11 rows selected.
                                  But the underlying problem in the above code is that i have use "*" in the column list of the select statement.

                                  So the client will be totally unaware of what he is going to get. So again think twice before getting into a mess with dynamic SQL.
                                  • 14. Re: select query problem
                                    BluShadow
                                    1004937 wrote:
                                    I know this.

                                    But i can't use scott.emp
                                    I have to pass the table name at run time.
                                    Why do you? Don't you know the names of the tables on your database? That would seem like a poorly designed database or poorly designed application requirement, if you can't know at design time what the tables are called that you need to use. That results in having to use dynamically generated queries, which leads to more dyanmically generated code to process those queries, and then you'll probably be wanting a dynamically generated interface to adapt the output (and maybe input) for the user etc. and you end up with a whole load code that is unperformant, buggy, unscalable and liable to security issues.
                                    I am using this in function.
                                    But function don't use dynamic sql.
                                    Why do you think a function can't have dynamic sql...
                                    Dynamic sql is used in procedure but i can't use procedure.
                                    ... but you think a procedure can. ?

                                    There's nothing to stop you creating dynamic sql in either a function or procedure. The only differences between a function and a procedure is that a function returns a value whereas a procedure doesn't, and functions can be used in SQL statements (subject to some restrictions).