Need workaround to declare table type by passing procedure parameter instead of declaring table rowtype

Question

I am passing a parameter (source_table_name) in procedure which actually a table_name.
Also, I am declaring a collection type and using source_table_name%rwtype instead of actual_table_name%rowtype.
Its throwing an error.

Please suggest if there is any workaround.

    CREATE OR REPLACE PROCEDURE PRM_BKUP_TRNSACTION_TABLE_PROC
    (
        source_table_name     IN VARCHAR2,
        archive_date_col      IN  VARCHAR2,
        archive_form_date     IN VARCHAR2,
        archive_to_date       IN VARCHAR2
    )
    
    AS
    --Collection

    TYPE source_table_collection IS TABLE OF source_table_name%ROWTYPE; <----(passing proc parameter)
    source_data source_table_collection;

Answer 1

You can't use a variable for typing. Types have to be resolved at compilation time, not at execution time. In a situation like this, postpone compilation to occur at execute time by using dynamic SQL. Submit an entire PL/SQL anonymous block to EXECUTE IMMEDIATE, in the declaration section of which you can use whatever collection typing you need:

CREATE OR REPLACE PROCEDURE PRM_BKUP_TRNSACTION_TABLE_PROC
(
source_table_name                 IN VARCHAR2,
archive_date_col      IN  VARCHAR2,
archive_form_date          IN VARCHAR2,
archive_to_date            IN VARCHAR2

)

AS
BEGIN
  EXECUTE IMMEDIATE '
    DECLARE  
      TYPE source_table_collection IS TABLE OF '||source_table_name||'%ROWTYPE;
      source_data source_table_collection; 
      archive_date_col varchar2(128) := :archive_date_col;
      archive_form_date varchar2(30) := :archive_form_date;
      archive_to_date varchar2(30) := :archive_to_date;
    BEGIN
      NULL;
      -- do stuff
    END;
  ' USING IN archive_date_col,archive_form_date,archive_to_date;

END;

However, it sounds like you are building an archiving system to move data from tables to elsewhere. If that's the case, you might want to consider not using PL/SQL collections at all. You'll get better performance and make less severe demands on memory by simply inserting data from table to table using SQL, with PL/SQL only used to manage the operations, rather than process the data itself. You'll still end needing dynamic SQL but if you discard the use of PL/SQL collections you can write this without the more complex dynamic PL/SQL block.