Executing sql from PL/SQL

This is a useful utility. It just uses the execute immediate command in PL/SQL but I've wrapped it up a bit for variable substitution, and overloaded the package call so you can use it with zero to five arguments (or more if you added to the code). Also a useful display error utility -

create or replace package iri_util as

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,
p_arg2 in varchar2,p_arg3 in varchar2,p_arg4 in varchar2,p_arg5 in varchar2);

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,
p_arg2 in varchar2,p_arg3 in varchar2,p_arg4 in varchar2);

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,
p_arg2 in varchar2,p_arg3 in varchar2);

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,
p_arg2 in varchar2);

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2);

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2);

procedure display_error(p_errmess in varchar2,p_errtype in varchar2,p_errcode in varchar2);

end iri_util;


create or replace package body iri_util is

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,p_arg2 in varchar2,p_arg3 in varchar2,p_arg4 in varchar2,p_arg5 in varchar2) is

v_sql varchar2(5000);

begin

v_sql := p_sql;
v_sql := replace(v_sql,'%1',p_arg1);
v_sql := replace(v_sql,'%2',p_arg2);
v_sql := replace(v_sql,'%3',p_arg3);
v_sql := replace(v_sql,'%4',p_arg4);
v_sql := replace(v_sql,'%5',p_arg5);

if p_contflag != 'T' then
execute immediate v_sql;
end if;

exception

when others then
if p_contflag in ('C','Y') then
null;
else
raise;
end if;

end;

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,p_arg2 in varchar2,p_arg3 in varchar2,p_arg4 in varchar2) is
begin

execute_sql(p_sql,p_contflag,p_arg1,p_arg2,p_arg3,p_arg4,null);

end;

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,p_arg2 in varchar2,p_arg3 in varchar2) is
begin

execute_sql(p_sql,p_contflag,p_arg1,p_arg2,p_arg3,null,null);

end;


procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,p_arg2 in varchar2) is
begin

execute_sql(p_sql,p_contflag,p_arg1,p_arg2,null,null,null);

end;


procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2) is
begin

execute_sql(p_sql,p_contflag,p_arg1,null,null,null,null);

end;


procedure execute_sql(p_sql in varchar2,p_contflag in varchar2) is
begin

execute_sql(p_sql,p_contflag,null,null,null,null,null);

end;

procedure display_error(p_errmess in varchar2,p_errtype in varchar2,p_errcode in varchar2) is

begin

IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name(' + appShortName +', p_errmess);
FND_MESSAGE.Set_Token(p_errtype, p_errcode, FALSE);
FND_MSG_PUB.ADD;
END IF;
RAISE FND_API.G_EXC_ERROR;

end;

end iri_util;