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.