PL/SQL to generate insert/update/delete stored procedures for Oracle tables

This pl/sql below creates function sp_generate_procs in the database. You can then call this function to generate insert/update/delete and select procedures. Example usage:

begin
:result := sp_generate_procs(p_table_name => 'candidate');
end;

Function definition:

create or replace function sp_generate_procs(p_table_name varchar2, p_owner varchar2 := null)
    return clob is
    result           clob;
    v_parameters     clob; -- column name parameters
    v_cols           clob; -- plain column names
    v_cols_variables clob; -- column name variables
    vComment         varchar2(1000);
    v_update         varchar2(1000) := 'UDPATE ';
    v_table_name     varchar2(40) := upper(p_table_name);
    v_curr_user      varchar2(100);
    v_colname        varchar2(100);
    v_pkname         varchar2(30);
    v_pktype         varchar2(30);
    v_wasPk          boolean := false;
begin
    /*open c for select *
    from   all_tab_columns
    where  table_name = p_table_name
    and    owner = v_curr_user;*/
    select c.column_name
      into v_pkname
      from user_constraints u, user_cons_columns c
     where u.constraint_type = 'P'
       and u.table_name = c.table_name
       and c.constraint_name = u.constraint_name
       and c.table_name = v_table_name;

    vComment := chr(13) || '--Procedure auto generated by sp_generate_procs on ' || sysdate ||
                chr(13);

    if p_owner is null then
        select SYS_CONTEXT('USERENV', 'CURRENT_USER') into v_curr_user from DUAL;
    else
        v_curr_user := p_owner;
    end if;

    v_update := chr(9) || 'UPDATE ' || v_table_name || ' SET ' || chr(13);

    for c in (select *
                from all_tab_columns
               where table_name = v_table_name
                 and owner = v_curr_user)
    loop
        if lower(v_pkname) = lower(c.column_name) then
            
            v_pktype  := c.data_type;
            v_colname := 'p_' || substr(c.column_name, 1, 28) || ' IN OUT ' || c.data_type;
        else
            v_colname := 'p_' || substr(c.column_name, 1, 28) || ' ' || c.data_type;
        end if;
        dbms_output.put_line(c.column_name);
        --v_colname := 'p_' || c.column_name || ' ' || c.data_type;
    
        if v_parameters is null then
            v_parameters     := chr(9) || chr(9) || v_colname;
            v_cols           := chr(9) || chr(9) || c.column_name;
            v_cols_variables := chr(9) || chr(9) || 'p_' || substr(c.column_name, 1, 28);
        else
            if (v_wasPk) then
                v_parameters := v_parameters || ', -- Primary key is IN/OUT';
                v_wasPk      := false;
            else
                v_parameters := v_parameters || ',';
            end if;
        
            v_parameters     := v_parameters || chr(13) || chr(9) || chr(9) || v_colname;
            v_cols           := v_cols || ',' || chr(13) || chr(9) || chr(9) || c.column_name;
            v_cols_variables := v_cols_variables || ',' || chr(13) || chr(9) || chr(9) || 'p_' ||
                                substr(c.column_name, 1, 28);
        end if;
    
        if lower(v_pkname) != lower(c.column_name) then
            v_update := v_update || chr(9) || chr(9) || c.column_name || '=' || 'p_' ||
                        substr(c.column_name, 1, 28) || ',' || chr(13);
        else
            v_wasPk   := true;
        end if;
    
    end loop;

    v_update := substr(v_update, 1, length(v_update) - 2);
    v_update := v_update || chr(13) || chr(9) || 'where ' || v_pkname || '= p_' || v_pkname || ';' ||
                chr(13);

    result := result || 'CREATE OR REPLACE PROCEDURE ' || v_table_name || '_SELECT (p_' || v_pkname || ' ' ||
              v_pktype || ',curs out sys_refcursor) AS ' || chr(13);
    result := result || vComment || 'begin' || chr(13);
    result := result || 'open curs for ' || chr(13);
    result := result || chr(9) || 'select ' || chr(13) || v_cols || chr(13) || chr(9) || ' from ' ||
              v_table_name || ' where ' || v_pkname || '= p_' || v_pkname || ';' || chr(13);
    result := result || chr(13);
    result := result || 'END;' || chr(13);
    result := result || '/' || chr(13);

    result := result || 'CREATE OR REPLACE PROCEDURE ' || v_table_name || '_INSERT (' || chr(13);
    result := result || v_parameters || ') AS ' || chr(13);
    result := result || vComment || 'begin' || chr(13);
    result := result || chr(9) || 'insert into ' || v_table_name || '(' || chr(13);
    result := result || v_cols || chr(13) || chr(9) || ') values (' || chr(13) || v_cols_variables || ');' ||
              chr(13);
    result := result || chr(13);
    result := result || 'END;' || chr(13);
    result := result || '/' || chr(13);

    result := result || 'CREATE OR REPLACE PROCEDURE ' || v_table_name || '_UPDATE (' || chr(13);
    result := result || v_parameters || ') AS ' || chr(13);
    result := result || 'begin' || chr(13) || vComment || chr(13);
    result := result || v_update;
    result := result || 'END;' || chr(13);
    result := result || '/' || chr(13);
    result := result || 'CREATE OR REPLACE PROCEDURE ' || v_table_name || '_DELETE(p_' || v_pkname || ' ' ||
              v_pktype || ') AS ' || chr(13);
    result := result || 'begin' || chr(13);
    result := result || chr(9) || 'delete from ' || v_table_name;
    result := result || chr(9) || ' where ' || v_pkname || '= p_' || v_pkname || ';' || chr(13);
    result := result || chr(13);
    result := result || 'END;' || chr(13);
    result := result || '/' || chr(13);
    return(result);

end sp_generate_procs;
/