How to use dynamic SQL to declare a column name derived from a table name?

Question

Based on Tony's answer to this question :

If I want to do something like this,

CREATE PROCEDURE A(tab IN VARCHAR2) IS
tab.col_name <column> --static declaration (column name always remains the same)
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO ' || tab(col_name) || 'VALUES(123)';
END A;

How can I use Dynamic SQL in the above case?

Answer 1

This example is passed in the table name and column name:

CREATE PROCEDURE A
  ( tab IN VARCHAR2
  , col_name IN VARCHAR2
  ) IS
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO ' || tab || '(' || col_name || ') VALUES(123)';
END A;

You need to understand that everything after EXECUTE IMMEDIATE must be a string containing some valid SQL. A good way to check this is to set it in a variable and print it on the screen:

CREATE PROCEDURE A
  ( tab IN VARCHAR2
  , col_name IN VARCHAR2
  ) IS
   v_sql VARCHAR2(2000);
BEGIN
   v_sql := 'INSERT INTO ' || tab || '(' || col_name || ') VALUES(123)';
   DBMS_OUTPUT.PUT_LINE('SQL='||v_sql);
   EXECUTE IMMEDIATE v_sql;
END A;

In SQL Plus, it should look something like this:

SQL = INSERT INTO mytable (mycolumn) (123)

( , ).

EDIT. , , , :

CREATE PROCEDURE A (tab IN VARCHAR2)
IS
   col_name VARCHAR2(30) := 'MYCOLUMN';
   v_sql VARCHAR2(2000);
BEGIN
   v_sql := 'INSERT INTO ' || tab || '(' || col_name || ') VALUES(123)';
   DBMS_OUTPUT.PUT_LINE('SQL='||v_sql);
   EXECUTE IMMEDIATE v_sql;
END A;