oracle user rowtype variable to insert into table using execute immediate

Question

I'm using Oracle 11g. I have a function which I want to use to insert into a row into a table. One of the parameters is a table%rowtype. This is already populated with most of the correct data. One of the parameters is a varchar2 representing the target table name. I want to change the values in three columns in the variable of type table%rowtype, then insert it into the table named by the input variable of type varchar2.

  FUNCTION AddMP(vMP          IN MEASUREPOINT%ROWTYPE,
                  vNewPointNum IN MEASUREPOINT.POINTNUM%TYPE,
                 v_CalMtrName IN MEASUREPOINT.METERNAME%TYPE) RETURN VARCHAR2 IS
      v_RetVal       VARCHAR2(50) := K_OKAY;
      v_Status       VARCHAR2(50) := NULL;
      v_AssetNum     MEASUREPOINT.ASSETNUM%TYPE;
      v_SiteId       MEASUREPOINT.SITEID%TYPE;
      v_OrgId        ASSETMETER.ORGID%TYPE;
      v_ChangeDate   ASSETMETER.CHANGEDATE%TYPE;
      v_err_num      NUMBER;
      v_err_msg      VARCHAR2(255);
      v_err_string   VARCHAR2(1000) := NULL;
      v_insert_str   VARCHAR2(2000) := NULL;
      v_RowsAffected VARCHAR2(10) := NULL;
      v_NewMP        MEASUREPOINT%ROWTYPE;
   BEGIN
      v_AssetNum   := vMP.Assetnum;
      v_SiteId     := vMP.Siteid;
      v_ChangeDate := SYSDATE;
      v_OrgId      := vMP.Orgid;
      --Put new data into vMP

      tempMP                := vMP;
      tempMP.Pointnum       := vNewPointNum;
      tempMP.Metername      := v_CalMtrName;
      tempMP.Measurepointid := measurepointseq.nextval;
      tempMP.Pointnum       := vNewPointNum;

      /*      v_insert_str := ' insert \*+ ignore_row_on_dupkey_index(tab, tab_pk) *\into ' ||
                            TABLE_MEASUREPOINT || ' values :insertRecord ';
      */
          v_insert_str := ' insert /*+ ignore_row_on_dupkey_index(tab, tab_pk)     */into ' ||
                          TABLE_MEASUREPOINT || ' values tempMP ';

          EXECUTE IMMEDIATE v_insert_str;
          /*INSERT INTO MEASUREPOINT_TEMP VALUES tempMP;*/

I am getting various errors, but I suspect I actually have to enumerate all the target columns and their individual values. I would really like to not do this, but use the input variable instead after fixing three values. Is it even possible to do this? thanks in any case Jeff

Answer 1

If you know the column names of table MEASUREPOINT you can do this. Assuming columns are (col1,col2), then your insert will be:

v_insert_str := ' insert /*+ ignore_row_on_dupkey_index(tab, tab_pk)     */into ' ||
            TABLE_MEASUREPOINT || ' values ('||tempMP.col1||','||tempMP.col2||')';

Answer 2

To pass the rowtype variable into the dynamic SQL, you should use a bind variable and using:

v_insert_str := ' insert /*+ ignore_row_on_dupkey_index(tab, tab_pk)     */into ' ||
                          TABLE_MEASUREPOINT || ' values :1 ';

EXECUTE IMMEDIATE v_insert_str using tempMP;

The reason your original approaches did not work is that dynamic SQL run via execute immediate runs in it's own scope, outside of the scope of the procedure or block. In order to use local variables in the execute immediate, you must either pass them in using bind variable (as in my answer) or concatenate them (as in @Aramillo's answer). Generally, it's better to bind, if you can.