AAA

create or replace procedure PBH_SK_BENH_CAY(b_ma varchar2, b_tim nvarchar2)
AS
    b_maN varchar2(50);
begin
-- viet anh - Liet ke theo cay -- dang lam
insert into kh_cay select ma,ten,tc,to_char(muc) from bh_sk_benh where ma_ct=b_ma 
          and (FKH_BO_UNICODE(b_tim,'C','C') = ' ' or FKH_BO_UNICODE(ten,'C','C') like '%' || FKH_BO_UNICODE(b_tim,'C','C') || '%')
          and FBH_SK_BENH_HAN(ma,'T')='C' order by ten;
end;
/
create or replace procedure PKH_CAYj
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000); b_ham varchar2(100);
    b_ctrId varchar2(100); b_cap varchar2(2); b_ma varchar2(20); b_tso varchar2(1000); cs_lke clob;
    b_tim nvarchar2(100);
begin
-- viet anh - Liet ke dang cay
delete kh_cay; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'','','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ham,ctrId,cap,ma,tso,tim');
EXECUTE IMMEDIATE b_lenh into b_ham,b_ctrId,b_cap,b_ma,b_tso,b_tim using b_oraIn;
b_ma:=nvl(trim(b_ma),' ');
b_tim:=nvl(trim(b_tim),' ');
if trim(b_tso) is null then
    b_lenh:='begin '||b_ham||'(:ma,:tim); end;';
    EXECUTE IMMEDIATE b_lenh using b_ma,b_tim;
else
    b_lenh:='begin '||b_ham||'(:ma,:tso); end;';
    EXECUTE IMMEDIATE b_lenh using b_ma,b_tso;
end if;
select JSON_ARRAYAGG(obj returning clob) into cs_lke from
    (select json_object(ten,ma,loai,tso returning clob) obj from kh_cay order by ten);
select json_object('ctrId' value b_ctrId,'cap' value b_cap,'cs_lke' value cs_lke returning clob) into b_oraOut from dual;
delete kh_cay; commit;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;