Inserting rowtype record, which is passed as a parameter in PL/SQL in Oracle. How can I achieve this?

Question

I am trying to achieve below scenario. How can I achieve below insert given below?

declare
v_insSql varchar2(1000);
v_table_name varchar2(50):='TEMP_TBL';
v_row Table_T1%ROWTYPE;
/* Just to avoid everyone's confusion, both table_t1 and temp_tbl are of same structure */

BEGIN
SELECT * INTO v_row FROM Table_T1 WHERE ROWNUM = 1;
v_insSql:= 'INSERT INTO '||v_table_name||' VALUES :l_row';
/* table_name will always be passed as variable */
    EXECUTE IMMEDIATE v_insSql using v_row;
END;

Can someone pls help?

Answer 1

Don't use dynamic SQL; just use a normal SQL insert:

DECLARE
  v_insSql varchar2(1000);
  v_table_name varchar2(50):='TEMP_TBL';
  v_row Table_T1%ROWTYPE;
BEGIN
  SELECT * INTO v_row FROM Table_T1 WHERE ROWNUM = 1;
  INSERT INTO temp_tbl VALUES v_row;
END;
/

So, for some sample tables/data:

CREATE TABLE table_t1 ( a NUMBER, b NUMBER, c NUMBER );
INSERT INTO table_t1 ( a, b, c ) VALUES ( 1, 2, 3 );
CREATE TABLE temp_tbl AS SELECT * FROM table_t1 WHERE 1 = 0;

Then:

SELECT * FROM temp_tbl;

Outputs:

 A |  B |  C
-: | -: | -:
 1 |  2 |  3

db<>fiddle here

---

If you want to have dynamic table names then white-list the tables so you don't need to use dynamic SQL:

DECLARE
  v_insSql varchar2(1000);
  v_table_name varchar2(50):='TEMP_TBL';
  v_row Table_T1%ROWTYPE;
BEGIN
  SELECT * INTO v_row FROM Table_T1 WHERE ROWNUM = 1;
  IF v_table_name = 'TEMP_TBL' THEN
    INSERT INTO temp_tbl VALUES v_row;
  ELSIF v_table_name = 'TABLE_T1' THEN
    INSERT INTO table_t1 VALUES v_row;
  END IF;
END;
/

db<>fiddle here

Answer 2

You can get what your after but with the intermediate step of defining and filling a %rowtype variable. Since the source table (Table_T1) and the target table (Temp_Tbl) must have identical structure you insert directly for select.

declare                                                                
   k_sql_stmt   constant varchar2(100) := 'insert into <table_name> select * from table_t1 where rownum=1';
   l_sql_stmt   varchar2(100);
   l_table_name varchar2(30) := 'temp_tbl'; 
begin 
   l_sql_stmt := replace(k_sql_stmt,'<table_name>',l_table_name); 
   dbms_output.put_line ('Run Statement==> ''' || l_sql_stmt || '''');
   execute immediate l_sql_stmt;  
end ;