Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Need to fetch the table details using stored procedure when we give table name as input

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.
Best 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
-
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.
-
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.
-
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.
-
Since Oracle 18 you can use polymorphic functions:
ODCITABLE, PTF, SQL_MACRO may give an unexpected result with a variable parameter.