Add a table name dynamically in a stored procedure

Question

I've been trying to find ways to change the table name dynamically in a query but not found one so far that works. The query has repetitive code (around 150 lines of code inside each case statement) so I am trying to find a better way of doing it.

My current procedure looks like the below:

CREATE OR REPLACE PROCEDURE PROC_MY_TEST (P_INPUTVAR       NUMBER,
                                          P_OUT        OUT SYS_REFCURSOR)
AS
BEGIN
   CASE P_INPUTVAR
      WHEN 1
      THEN
         OPEN P_OUT FOR
            WITH CTE
                 AS (SELECT COL_M, COL_N
                       FROM TBL_TEST
                      WHERE COL_G = 'mycondition1')
            SELECT COL_M, COL_N
              FROM TABLE_A a LEFT JOIN CTE m ON a.col1 = b.col1
             WHERE a.col2 = 'xyz' AND b.col4 = 'abc';
      WHEN 2
      THEN
         OPEN P_OUT FOR
            WITH CTE
                 AS (SELECT COL_M, COL_N
                       FROM TBL_TEST
                      WHERE COL_G = 'mycondition1')
            SELECT COL_M, COL_N
              FROM TABLE_B a LEFT JOIN CTE m ON a.col1 = b.col1
             WHERE a.col2 = 'xyz' AND b.col4 = 'abc';
      WHEN 3
      THEN
         OPEN P_OUT FOR
            WITH CTE
                 AS (SELECT COL_M, COL_N
                       FROM TBL_TEST
                      WHERE COL_G = 'mycondition1')
            SELECT COL_M, COL_N
              FROM TABLE_C a LEFT JOIN CTE m ON a.col1 = b.col1
             WHERE a.col2 = 'xyz' AND b.col4 = 'abc';
      WHEN 4
      THEN
         OPEN P_OUT FOR
            WITH CTE
                 AS (SELECT COL_M, COL_N
                       FROM TBL_TEST
                      WHERE COL_G = 'mycondition1')
            SELECT COL_M, COL_N
              FROM TABLE_D a LEFT JOIN CTE m ON a.col1 = b.col1
             WHERE a.col2 = 'xyz' AND b.col4 = 'abc';
   END CASE;
END;

A few SQL solutions suggested the below format but it gives an error message that "Table L_Temp does not exist":

CREATE OR REPLACE PROCEDURE PROC_MY_TEST (P_INPUTVAR       NUMBER,
                                          P_OUT        OUT SYS_REFCURSOR)
AS
   L_Temp   VARCHAR2 (200);   
BEGIN
   L_Temp :=
      CASE P_INPUTVAR
         WHEN 1 THEN 'TABLE_A'
         WHEN 2 THEN 'TABLE_B'
         WHEN 3 THEN 'TABLE_C'
         WHEN 4 THEN 'TABLE_D'
         ELSE NULL
      END;

   OPEN P_OUT FOR
      WITH CTE
           AS (SELECT COL_M, COL_N
                 FROM TBL_TEST
                WHERE COL_G = 'mycondition1')
      SELECT COL_M, COL_N
        FROM L_Temp a LEFT JOIN CTE m ON a.col1 = b.col1
       WHERE a.col2 = 'xyz' AND b.col4 = 'abc';
END;

Is there a way we can dynamically change table names when all the target tables contain similar column names?

*Edited * Code added for additional update statement example:

CREATE OR REPLACE PROCEDURE PROC_MY_TEST (P_INPUTVAR       NUMBER,
                                          P_OUT        OUT SYS_REFCURSOR)
AS
   L_Temp   VARCHAR2 (200);
   L_Total  NUMBER;
BEGIN
   L_Temp :=
      CASE P_INPUTVAR
         WHEN 1 THEN 'TABLE_A'
         WHEN 2 THEN 'TABLE_B'
         WHEN 3 THEN 'TABLE_C'
         WHEN 4 THEN 'TABLE_D'
         ELSE NULL
      END;

   OPEN P_OUT FOR
      WITH CTE
           AS (SELECT COL_M, COL_N
                 FROM TBL_TEST
                WHERE COL_G = 'mycondition1')
      SELECT COL_M, COL_N
        FROM L_Temp a LEFT JOIN CTE m ON a.col1 = b.col1
       WHERE a.col2 = 'xyz' AND b.col4 = 'abc';

      SELECT TOTAL INTO L_Total FROM L_Temp;

      UPDATE TBL_TEST2 
      SET a.TOTAL = l_Total 
      WHERE a.col2 = 'xyz';

END;

Answer 1

You need to create your statement as a string, then open the statement as a cursor:

CREATE OR REPLACE PROCEDURE PROC_MY_TEST (P_INPUT_VAR IN  NUMBER,
                                          P_OUT       OUT SYS_REFCURSOR)
AS
  strTable  VARCHAR2(30);
  strStmt   VARCHAR2(32767);
BEGIN
  strTable := CASE P_INPUT_VAR
                WHEN 1 THEN 'TABLE_A'
                WHEN 2 THEN 'TABLE_B'
                WHEN 3 THEN 'TABLE_C'
                WHEN 4 THEN 'TABLE_D'
              END;

  strStmt := 'WITH CTE AS (SELECT COL_M, COL_1, COL_4 ' ||
                           ' FROM TBL_TEST ' ||
                           ' WHERE COL_G = ''mycondition1'') ' ||
             'SELECT COL_M, COL_N ' ||
              ' FROM ' || strTable || ' t ' ||
              ' LEFT JOIN CTE m ' ||
                ' ON t.COL1 = m.COL_1 ' ||
              ' WHERE a.COL2 = ''xyz'' AND ' ||
                    ' m.COL_4 = ''abc''';

  OPEN P_OUT FOR strStmt;
END PROC_MY_TEST;

Note that I changed the SQL slightly because you were using table shortcuts and column names which didn't seem to match up with the rest of the code.

Answer 2

You need to use EXECUTE IMMEDIATE Statement

The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names

For example :

execute immediate '
  update ' || myTable || '
 ...
';