Package Example in Oracle

As salamo alaikum wa rahmatullah

create or replace package pck_test
is
procedure prc_inst(stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2);
procedure prc_upd(id in number,stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2);
procedure prc_del(id in number,errcode out varchar2,errbuf out varchar2);
end pck_test;

create or replace package body pck_test
is
procedure prc_inst(stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2) 
is
begin
insert into tbl_mst_stock(stk_code,stk_desc,created_dt,updated_dt)
values(stc,sdesc,sysdate,sysdate);
commit;
errcode:='0';
errbuf:='row inserted successfully';
exception
when dup_val_on_index then
errcode:='1';
errbuf:= 'duplicate key found';
end prc_inst;

procedure prc_upd(id in number,stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2) is
begin
update tbl_mst_stock
set stk_code=stc, stk_desc=sdesc, updated_dt=sysdate where stock_id=id;
errcode:='0';
errbuf:='record updated successfully';
exception
when others then
errcode:='1';
errbuf:= 'duplicate key found';
end prc_upd;

procedure prc_del(id in number,errcode out varchar2,errbuf out varchar2) is
begin
delete from tbl_mst_stock 
where stock_id=id;
commit;
errcode:='0';
errbuf:='record deleted successfully';
exception
when others then
errcode:='1';
errbuf:='no row exist';
end prc_del;


end pck_test;

--

Ma Asalaam

Passion for Oracle