Forum Stats

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

Discussions

Categories

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
in SQL & PL/SQL 
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:
· Share on TwitterShare on Facebook

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    Answer ✓
    https://community.oracle.com/tech/developers/discussion/4498913/need-to-fetch-the-table-details-using-stored-procedure-when-we-give-table-name-as-input


    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.

    · Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    Answer ✓
    https://community.oracle.com/tech/developers/discussion/4498913/need-to-fetch-the-table-details-using-stored-procedure-when-we-give-table-name-as-input


    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.

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook