Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Albert ChaoMay 25 2022
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.

This post has been answered by BluShadow on May 25 2022
Jump to Answer
Comments
Post Details
Added on May 25 2022
4 comments
5,066 views