codeboxer.com

A really clean dynamic insert proc for PL/SQL ETL packages. Pass in the table name, an array of field names and an array of values. Done.

PROCEDURE insert_row( p_tblname in varchar2, p_colname in v_array, p_value in v_array)
IS
--This procedure dynamically builds an insert string and executes.
v_stmt clob;
v_ret  number;
v_cntflag number := 1; --default start counter
no_parse_error exception;
pragma exception_init(no_parse_error,-1003); -- no parse error

BEGIN

-- VALIDATION CHECK
 IF p_tblname IS NULL THEN RETURN;
 ELSIF p_colname IS NULL OR p_colname.COUNT = 0 THEN RETURN;
 END IF;

--Build SQL statement for insert
v_stmt := 'insert into ' || p_tblname ||'@optional_dblink.server.xxx'|| ' ( ' || p_colname(1);
    
    for i in 2 .. p_colname.count
    loop
      v_stmt := v_stmt || ', ' || p_colname(i);
    end loop;

if upper(p_colname(1)) = 'ID' and p_value(1) = '-999' then
  v_cntflag := 2;
  v_stmt := v_stmt || ') values ('||p_tblname||'_SEQ.NEXTVAL@optional_dblink.server.xxx';
else
  v_cntflag := 1;
  v_stmt := v_stmt || ') values ( :bv1';
end if;

    for i in 2 .. p_colname.count
    loop
      v_stmt := v_stmt || ', :bv' || i;
    end loop;

v_stmt := v_stmt || ')';
--dbms_output.put_line(v_stmt);

        -- this check is done to ensure that there is no hard parse by storing previously
        -- parsed queries into a global array variable within this session
        -- Using dbms_sql instead of execute immediate because the number of bind variables is unknown.
        p_cursor := parse_tbl.first;
        while ( p_cursor IS NOT NULL and (parse_tbl(p_cursor) != v_stmt) )
        loop
            p_cursor := parse_tbl.next(p_cursor);
        end loop;
                                                                                                    
      
        if ( p_cursor IS NULL or (parse_tbl(p_cursor) != v_stmt) )
        then
            p_cursor := dbms_sql.open_cursor;
            dbms_sql.parse(  p_cursor, v_stmt, dbms_sql.native );
            parse_tbl(p_cursor) := v_stmt;
        end if;

    for i in v_cntflag .. p_value.count
    loop   
      --dbms_output.put_line('val'||i||'-'||p_value(i));
      dbms_sql.bind_variable( p_cursor, ':bv' || i, p_value(i) );
    end loop;
    
    v_ret := dbms_sql.execute( p_cursor );

EXCEPTION
WHEN no_parse_error then
            p_cursor := dbms_sql.open_cursor;
            dbms_sql.parse(p_cursor, v_stmt, dbms_sql.native );
            parse_tbl(p_cursor) := v_stmt;
            for i in v_cntflag .. p_value.count
            loop   
              dbms_sql.bind_variable( p_cursor, ':bv' || i, p_value(i) );
            end loop;
            v_ret := dbms_sql.execute( p_cursor );
 
WHEN OTHERS THEN
log_exception('insert_row:' || p_tblname,   p_tblname,   SQLCODE || sqlerrm,   SUBSTR(query_text,   1,   400));
END insert_row;