Forum Stats

  • 3,825,886 Users
  • 2,260,571 Discussions
  • 7,896,720 Comments

Discussions

Need to fetch the table details using stored procedure when we give table name as input

Albert Chao
Albert Chao Member Posts: 193 Green Ribbon
CREATE TABLE test_table (
    col1  NUMBER(10),
    col2  NUMBER(10)
);

INSERT INTO test_table VALUES(1,2);

I am writing one stored procedure wherein if I give a table name as an input, that should give me the table data and column details.

For example : SELECT * FROM <input_table_name>;

But this is giving me the error that the SQL command has not ended properly even though I have taken care of this.

My Attempt:

CREATE OR REPLACE PROCEDURE sp_test(iv_table_name IN VARCHAR2)
AS
lv_count NUMBER(1);
lv_table_name VARCHAR2(255):=UPPER(iv_table_name);
BEGIN
    SELECT COUNT(1) INTO lv_count FROM all_tables WHERE table_name = lv_table_name;
    IF lv_count = 0 THEN
        dbms_output.put_line('Table does not exist');
    ELSE
        EXECUTE IMMEDIATE 'SELECT * FROM '||lv_table_name||';';
    END IF;
END sp_test;

Tool used: SQL developer(18c)

I have also asked this on Stack overflow.

Tagged:

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    Answer ✓


    What Paul says isn't strictly true.

    Your error message is the result of you including the ';' at the end of your select statement. The ';' is an indicator to parsers within code like PL/SQL or SQL*Plus that the statement is ended, but it has no place as part of the SQL statement itself....

    SQL> ed
    Wrote file afiedt.buf
    
      1  CREATE OR REPLACE PROCEDURE sp_test(iv_table_name IN VARCHAR2)
      2  AS
      3  lv_count NUMBER(1);
      4  lv_table_name VARCHAR2(255):=UPPER(iv_table_name);
      5  BEGIN
      6      SELECT COUNT(1) INTO lv_count FROM all_tables WHERE table_name = lv_table_name;
      7      IF lv_count = 0 THEN
      8          dbms_output.put_line('Table does not exist');
      9      ELSE
     10          EXECUTE IMMEDIATE 'SELECT * FROM '||lv_table_name;
     11      END IF;
     12* END sp_test;
    SQL> /
    
    Procedure created.
    
    SQL> exec sp_test('EMP');
    
    PL/SQL procedure successfully completed.
    


    So, as you can see, without the ';' on the SQL statement it works just fine.

    Your only problem is that you are selecting the data to nowhere. And that's where Paul is correct. You should select the data into something, and for that you'd need to know the structure of the results you're getting.

    It's an odd requirement to design a system for tables and columns you don't know at design time. Smacks of poor design.

    Whilst you could use the PTF (Polymorphic Table Function) as cormaco has indicated, or in previous versions of Oracle you could use Dynamic SQL (i.e. using the package DBMS_SQL to parse and describe the results)... it still begs the question why you don't know your tables and columns you want. Once you go down the route of not knowing your table structure, then everything that follows it has to be dynamic too.

    Basic example using DBMS_SQL...

    create or replace procedure run_query(p_sql IN VARCHAR2) is
      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_rowcount  number := 0;
    begin
      -- create a cursor
      c := dbms_sql.open_cursor;
      -- parse the SQL statement into the cursor
      dbms_sql.parse(c, p_sql, dbms_sql.native);
      -- execute the cursor
      d := dbms_sql.execute(c);
      --
      -- Describe the columns returned by the SQL statement
      dbms_sql.describe_columns(c, col_cnt, rec_tab);
      --
      -- Bind local return variables to the various columns based on their types
      dbms_output.put_line('Number of columns in query : '||col_cnt);
      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); -- Varchar2
          when 2 then dbms_sql.define_column(c,j,v_n_val);      -- Number
          when 12 then dbms_sql.define_column(c,j,v_d_val);     -- Date
        else
          dbms_sql.define_column(c,j,v_v_val,2000);  -- Any other type return as varchar2
        end case;
      end loop;
      --
      -- Display what columns are being returned...
      dbms_output.put_line('-- Columns --');
      for j in 1..col_cnt
      loop
        dbms_output.put_line(rec_tab(j).col_name||' - '||case rec_tab(j).col_type when 1 then 'VARCHAR2'
                                                                                  when 2 then 'NUMBER'
                                                                                  when 12 then 'DATE'
                                                         else 'Other' end);
      end loop;
      dbms_output.put_line('-------------');
      --
      -- This part outputs the DATA
      loop
        -- Fetch a row of data through the cursor
        v_ret := dbms_sql.fetch_rows(c);
        -- Exit when no more rows
        exit when v_ret = 0;
        v_rowcount := v_rowcount + 1;
        dbms_output.put_line('Row: '||v_rowcount);
        dbms_output.put_line('--------------');
        -- Fetch the value of each column from the row
        for j in 1..col_cnt
        loop
          -- Fetch each column into the correct data type based on the description of the column
          case rec_tab(j).col_type
            when 1  then dbms_sql.column_value(c,j,v_v_val);
                         dbms_output.put_line(rec_tab(j).col_name||' : '||v_v_val);
            when 2  then dbms_sql.column_value(c,j,v_n_val);
                         dbms_output.put_line(rec_tab(j).col_name||' : '||v_n_val);
            when 12 then dbms_sql.column_value(c,j,v_d_val);
                         dbms_output.put_line(rec_tab(j).col_name||' : '||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'));
          else
            dbms_sql.column_value(c,j,v_v_val);
            dbms_output.put_line(rec_tab(j).col_name||' : '||v_v_val);
          end case;
        end loop;
        dbms_output.put_line('--------------');
      end loop;
      --
      -- Close the cursor now we have finished with it
      dbms_sql.close_cursor(c);
    END;
    /
    
    
    SQL> exec run_query('select empno, ename, deptno, sal from emp where deptno = 10');
    Number of columns in query : 4
    -- Columns --
    EMPNO - NUMBER
    ENAME - VARCHAR2
    DEPTNO - NUMBER
    SAL - NUMBER
    -------------
    Row: 1
    --------------
    EMPNO : 7782
    ENAME : CLARK
    DEPTNO : 10
    SAL : 2450
    --------------
    Row: 2
    --------------
    EMPNO : 7839
    ENAME : KING
    DEPTNO : 10
    SAL : 5000
    --------------
    Row: 3
    --------------
    EMPNO : 7934
    ENAME : MILLER
    DEPTNO : 10
    SAL : 1300
    --------------
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL> exec run_query('select * from emp where deptno = 10');
    -- Columns --
    EMPNO - NUMBER
    ENAME - VARCHAR2
    JOB - VARCHAR2
    MGR - NUMBER
    HIREDATE - DATE
    SAL - NUMBER
    COMM - NUMBER
    DEPTNO - NUMBER
    -------------
    Row: 1
    --------------
    EMPNO : 7782
    ENAME : CLARK
    JOB : MANAGER
    MGR : 7839
    HIREDATE : 09/06/1981 00:00:00
    SAL : 2450
    COMM :
    DEPTNO : 10
    --------------
    Row: 2
    --------------
    EMPNO : 7839
    ENAME : KING
    JOB : PRESIDENT
    MGR :
    HIREDATE : 17/11/1981 00:00:00
    SAL : 5000
    COMM :
    DEPTNO : 10
    --------------
    Row: 3
    --------------
    EMPNO : 7934
    ENAME : MILLER
    JOB : CLERK
    MGR : 7782
    HIREDATE : 23/01/1982 00:00:00
    SAL : 1300
    COMM :
    DEPTNO : 10
    --------------
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL> exec run_query('select * from dept where deptno = 10');
    -- Columns --
    DEPTNO - NUMBER
    DNAME - VARCHAR2
    LOC - VARCHAR2
    -------------
    Row: 1
    --------------
    DEPTNO : 10
    DNAME : ACCOUNTING
    LOC : NEW YORK
    --------------
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL>
    


    Useful perhaps if you need to take the results of lots of different queries and do something like write the data in a CSV file or suchlike as you can have a single CSV writing procedure (give it the query and the filename and let it do the business)... but aside from applications like that, it's generally a sign of poor design.

Answers

  • Paulzip
    Paulzip Member Posts: 8,693 Blue Diamond

    In PL/SQL, a select statement has to be into a variable or variables (that includes a cursor instance). Yours isn't doing that.

    If you want to return a result set, your choices are something like a ref cursor, a collection type (pipelined or not) etc.

  • cormaco
    cormaco Member Posts: 1,941 Silver Crown

    Since Oracle 18 you can use polymorphic functions:

    ORACLE-BASE - Polymorphic Table Functions in Oracle Database 18c

    The return type of a Polymorphic Table Function (PTF) can be determined by input parameters. This differs from conventional table functions, where the output table type is fixed at compile time.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    Answer ✓


    What Paul says isn't strictly true.

    Your error message is the result of you including the ';' at the end of your select statement. The ';' is an indicator to parsers within code like PL/SQL or SQL*Plus that the statement is ended, but it has no place as part of the SQL statement itself....

    SQL> ed
    Wrote file afiedt.buf
    
      1  CREATE OR REPLACE PROCEDURE sp_test(iv_table_name IN VARCHAR2)
      2  AS
      3  lv_count NUMBER(1);
      4  lv_table_name VARCHAR2(255):=UPPER(iv_table_name);
      5  BEGIN
      6      SELECT COUNT(1) INTO lv_count FROM all_tables WHERE table_name = lv_table_name;
      7      IF lv_count = 0 THEN
      8          dbms_output.put_line('Table does not exist');
      9      ELSE
     10          EXECUTE IMMEDIATE 'SELECT * FROM '||lv_table_name;
     11      END IF;
     12* END sp_test;
    SQL> /
    
    Procedure created.
    
    SQL> exec sp_test('EMP');
    
    PL/SQL procedure successfully completed.
    


    So, as you can see, without the ';' on the SQL statement it works just fine.

    Your only problem is that you are selecting the data to nowhere. And that's where Paul is correct. You should select the data into something, and for that you'd need to know the structure of the results you're getting.

    It's an odd requirement to design a system for tables and columns you don't know at design time. Smacks of poor design.

    Whilst you could use the PTF (Polymorphic Table Function) as cormaco has indicated, or in previous versions of Oracle you could use Dynamic SQL (i.e. using the package DBMS_SQL to parse and describe the results)... it still begs the question why you don't know your tables and columns you want. Once you go down the route of not knowing your table structure, then everything that follows it has to be dynamic too.

    Basic example using DBMS_SQL...

    create or replace procedure run_query(p_sql IN VARCHAR2) is
      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_rowcount  number := 0;
    begin
      -- create a cursor
      c := dbms_sql.open_cursor;
      -- parse the SQL statement into the cursor
      dbms_sql.parse(c, p_sql, dbms_sql.native);
      -- execute the cursor
      d := dbms_sql.execute(c);
      --
      -- Describe the columns returned by the SQL statement
      dbms_sql.describe_columns(c, col_cnt, rec_tab);
      --
      -- Bind local return variables to the various columns based on their types
      dbms_output.put_line('Number of columns in query : '||col_cnt);
      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); -- Varchar2
          when 2 then dbms_sql.define_column(c,j,v_n_val);      -- Number
          when 12 then dbms_sql.define_column(c,j,v_d_val);     -- Date
        else
          dbms_sql.define_column(c,j,v_v_val,2000);  -- Any other type return as varchar2
        end case;
      end loop;
      --
      -- Display what columns are being returned...
      dbms_output.put_line('-- Columns --');
      for j in 1..col_cnt
      loop
        dbms_output.put_line(rec_tab(j).col_name||' - '||case rec_tab(j).col_type when 1 then 'VARCHAR2'
                                                                                  when 2 then 'NUMBER'
                                                                                  when 12 then 'DATE'
                                                         else 'Other' end);
      end loop;
      dbms_output.put_line('-------------');
      --
      -- This part outputs the DATA
      loop
        -- Fetch a row of data through the cursor
        v_ret := dbms_sql.fetch_rows(c);
        -- Exit when no more rows
        exit when v_ret = 0;
        v_rowcount := v_rowcount + 1;
        dbms_output.put_line('Row: '||v_rowcount);
        dbms_output.put_line('--------------');
        -- Fetch the value of each column from the row
        for j in 1..col_cnt
        loop
          -- Fetch each column into the correct data type based on the description of the column
          case rec_tab(j).col_type
            when 1  then dbms_sql.column_value(c,j,v_v_val);
                         dbms_output.put_line(rec_tab(j).col_name||' : '||v_v_val);
            when 2  then dbms_sql.column_value(c,j,v_n_val);
                         dbms_output.put_line(rec_tab(j).col_name||' : '||v_n_val);
            when 12 then dbms_sql.column_value(c,j,v_d_val);
                         dbms_output.put_line(rec_tab(j).col_name||' : '||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'));
          else
            dbms_sql.column_value(c,j,v_v_val);
            dbms_output.put_line(rec_tab(j).col_name||' : '||v_v_val);
          end case;
        end loop;
        dbms_output.put_line('--------------');
      end loop;
      --
      -- Close the cursor now we have finished with it
      dbms_sql.close_cursor(c);
    END;
    /
    
    
    SQL> exec run_query('select empno, ename, deptno, sal from emp where deptno = 10');
    Number of columns in query : 4
    -- Columns --
    EMPNO - NUMBER
    ENAME - VARCHAR2
    DEPTNO - NUMBER
    SAL - NUMBER
    -------------
    Row: 1
    --------------
    EMPNO : 7782
    ENAME : CLARK
    DEPTNO : 10
    SAL : 2450
    --------------
    Row: 2
    --------------
    EMPNO : 7839
    ENAME : KING
    DEPTNO : 10
    SAL : 5000
    --------------
    Row: 3
    --------------
    EMPNO : 7934
    ENAME : MILLER
    DEPTNO : 10
    SAL : 1300
    --------------
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL> exec run_query('select * from emp where deptno = 10');
    -- Columns --
    EMPNO - NUMBER
    ENAME - VARCHAR2
    JOB - VARCHAR2
    MGR - NUMBER
    HIREDATE - DATE
    SAL - NUMBER
    COMM - NUMBER
    DEPTNO - NUMBER
    -------------
    Row: 1
    --------------
    EMPNO : 7782
    ENAME : CLARK
    JOB : MANAGER
    MGR : 7839
    HIREDATE : 09/06/1981 00:00:00
    SAL : 2450
    COMM :
    DEPTNO : 10
    --------------
    Row: 2
    --------------
    EMPNO : 7839
    ENAME : KING
    JOB : PRESIDENT
    MGR :
    HIREDATE : 17/11/1981 00:00:00
    SAL : 5000
    COMM :
    DEPTNO : 10
    --------------
    Row: 3
    --------------
    EMPNO : 7934
    ENAME : MILLER
    JOB : CLERK
    MGR : 7782
    HIREDATE : 23/01/1982 00:00:00
    SAL : 1300
    COMM :
    DEPTNO : 10
    --------------
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL> exec run_query('select * from dept where deptno = 10');
    -- Columns --
    DEPTNO - NUMBER
    DNAME - VARCHAR2
    LOC - VARCHAR2
    -------------
    Row: 1
    --------------
    DEPTNO : 10
    DNAME : ACCOUNTING
    LOC : NEW YORK
    --------------
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL>
    


    Useful perhaps if you need to take the results of lots of different queries and do something like write the data in a CSV file or suchlike as you can have a single CSV writing procedure (give it the query and the filename and let it do the business)... but aside from applications like that, it's generally a sign of poor design.

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    Since Oracle 18 you can use polymorphic functions:

    ODCITABLE, PTF, SQL_MACRO may give an unexpected result with a variable parameter.