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

CREATE TABLE test_table 
(
    col1  NUMBER(10),
    col2  NUMBER(10)
);

INSERT INTO test_table 
VALUES (1, 2);

I am writing a 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 causes an 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,
p_out_cur OUT SYS_REFCURSOR)
AS
lv_str VARCHAR2(400);
lv_count NUMBER(1);
lv_table_name VARCHAR2(255):=UPPER(iv_table_name);
BEGIN

    lv_str := 'SELECT * FROM '||lv_table_name;
    
    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
        OPEN p_out_cur FOR lv_str;
    END IF;
END sp_test;

Tool used: SQL developer(18c)

CodePudding user response：

In dynamic SQL, you do NOT terminate statement with a semicolon.

EXECUTE IMMEDIATE 'SELECT * FROM '||lv_table_name||';';
                                                 -----
                                                 remove this

Anyway, you won't get any result when you run that piece of code. If you really want to see table's contents, you'll have to switch to something else, e.g. create a function that returns ref cursor.

Sample data:

SQL> SELECT * FROM test_table;

      COL1       COL2
---------- ----------
         1          2
         3          4

Procedure you wrote is now correct:

SQL> CREATE OR REPLACE PROCEDURE sp_test (iv_table_name  IN     VARCHAR2,
  2                                       p_out_cur         OUT SYS_REFCURSOR)
  3  AS
  4     lv_str         VARCHAR2 (400);
  5     lv_count       NUMBER (1);
  6     lv_table_name  VARCHAR2 (255) := UPPER (iv_table_name);
  7  BEGIN
  8     lv_str := 'SELECT * FROM ' || lv_table_name;
  9
 10     SELECT COUNT (1)
 11       INTO lv_count
 12       FROM all_tables
 13      WHERE table_name = lv_table_name;
 14
 15     IF lv_count = 0
 16     THEN
 17        DBMS_OUTPUT.put_line ('Table does not exist');
 18     ELSE
 19        OPEN p_out_cur FOR lv_str;
 20     END IF;
 21  END sp_test;
 22  /

Procedure created.

Testing:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     l_rc    SYS_REFCURSOR;
  3     l_col1  NUMBER (10);
  4     l_col2  NUMBER (10);
  5  BEGIN
  6     sp_test ('TEST_TABLE', l_rc);
  7
  8     LOOP
  9        FETCH l_rc INTO l_col1, l_col2;
 10
 11        EXIT WHEN l_rc%NOTFOUND;
 12
 13        DBMS_OUTPUT.put_line (l_col1 || ', ' || l_col2);
 14     END LOOP;
 15  END;
 16  /
1, 2              --> contents of the
3, 4              --> TEST_TABLE

PL/SQL procedure successfully completed.

SQL>

A function (instead of a procedure with the OUT parameter):

SQL> CREATE OR REPLACE FUNCTION sf_test (iv_table_name IN VARCHAR2)
  2     RETURN SYS_REFCURSOR
  3  AS
  4     lv_str         VARCHAR2 (400);
  5     lv_count       NUMBER (1);
  6     lv_table_name  VARCHAR2 (255) := UPPER (iv_table_name);
  7     l_rc           SYS_REFCURSOR;
  8  BEGIN
  9     lv_str := 'SELECT * FROM ' || lv_table_name;
 10
 11     SELECT COUNT (1)
 12       INTO lv_count
 13       FROM all_tables
 14      WHERE table_name = lv_table_name;
 15
 16     IF lv_count = 0
 17     THEN
 18        raise_application_error (-20000, 'Table does not exist');
 19     ELSE
 20        OPEN l_rc FOR lv_str;
 21     END IF;
 22
 23     RETURN l_rc;
 24  END sf_test;
 25  /

Function created.

Testing:

SQL> SELECT sf_test ('liksajfla') FROM DUAL;
SELECT sf_test ('liksajfla') FROM DUAL
       *
ERROR at line 1:
ORA-20000: Table does not exist
ORA-06512: at "SCOTT.SF_TEST", line 18


SQL> SELECT sf_test ('TEST_TABLE') FROM DUAL;

SF_TEST('TEST_TABLE'
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

      COL1       COL2
---------- ----------
         1          2
         3          4


SQL>