Are variables allowed in a PL/SQL INSERT query?

Question

Is it allowed to use Insert query with variables?

Like: Insert Into var_1 (col1_Var_Name, col2_vAr_Name) Values (var_3, var_4)

?

Answer 1

Table and column names cannot be passed to a statement as variables. One way to accomplish what you seem to be trying to do is to build the statement as a string and then execute it dynamically, as in:

DECLARE
  strStmt   VARCHAR2(2000);
  strTable  VARCHAR2(30) := 'SOME_TABLE';
  strCol1   VARCHAR2(30) := 'COL1';
  strCol2   VARCHAR2(30) := 'COL2';
  strCol3   VARCHAR2(30) := 'COL3';
  nVal1     NUMBER       := 42;
  strVal2   VARCHAR2(30) := 'HELLO';
  nVal3     NUMBER       := 100;
BEGIN
  strStmt := 'INSERT INTO ' || strTable ||
               '(' || strCol1 || ',' ||
                      strCol2 || ',' ||
                      strCol3 || ')' ||
               ' VALUES (' ||
                         nVal1   || ','    ||
                         '''' || strVal2 || ''',' ||
                         nVal3 || ')';

  EXECUTE IMMEDIATE strStmt;
END;

Share and enjoy.

Answer 2

DECLARE
   TYPE type_name IS TABLE OF
      (column_type |
      variable%TYPE |
      table.column%TYPE
         [NOT NULL]
            INDEX BY BINARY INTEGER;

fOR MORE INFO Declare Table type variable