create or replace package gateway as g_context varchar2(4000) default null; g_id_utente number default null; procedure read(p_clob in varchar2, p_model in varchar2, p_context in varchar2, p_out out clob); procedure read(p_id in number, p_model in varchar2, p_context in varchar2, p_out out clob); procedure create_(p_clob in clob, p_model in varchar2, p_context in varchar2, p_out out clob); procedure update_(p_id in number, p_clob in clob, p_model in varchar2, p_context in varchar2, p_out out clob); procedure delete_(p_id in number, p_model in varchar2, p_context in varchar2); procedure method(p_clob in varchar2, p_method in varchar2, p_context in varchar2, p_out out clob); procedure method(p_clob in out clob, p_method in varchar2, p_context in varchar2); end gateway; / create or replace package body gateway as procedure read_context(p_context in varchar2) is v_json pljson := pljson_parser.parser(to_clob(p_context)); begin g_context := p_context; g_id_utente := v_json.get('id_utente').get_number; select username into g_username from utenti where id_utente = g_id_utente; end read_context; procedure logger(p_proc in varchar2, p_model in varchar2, p_stack in varchar2, p_clob in clob default null) is v_clob clob; begin if p_clob is not null then ut.error(p_clob); end if; dbms_lob.createtemporary(v_clob, true); v_clob := 'proc ' || p_proc || ' model ' || p_model || ' stack ' || p_stack; ut.error(v_clob); end logger; procedure read(p_clob in varchar2, p_model in varchar2, p_context in varchar2, p_out out clob) is begin read_context(p_context, p_model); dbms_lob.createtemporary(p_out, true); execute immediate 'begin ' || p_model || '.read(:x, :ret); end;' using in p_clob, in out p_out; end read; procedure read(p_id in number, p_model in varchar2, p_context in varchar2, p_out out clob) is v_json pljson; begin read_context(p_context, p_model); dbms_lob.createtemporary(p_out, true); execute immediate 'begin ' || p_model || '.read(:x, :ret); end;' using in p_id, in out p_out; end read; procedure create_(p_clob in clob, p_model in varchar2, p_context in varchar2, p_out out clob) is begin read_context(p_context, p_model); dbms_lob.createtemporary(p_out, true); p_out := p_clob; execute immediate 'begin ' || p_model || '.create_(:y); end;' using in out p_out; exception when others then logger(p_proc => 'create_', p_model => p_model, p_stack => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace, p_clob => p_clob); raise; end create_; procedure update_(p_id in number, p_clob in clob, p_model in varchar2, p_context in varchar2, p_out out clob) is begin read_context(p_context, p_model); dbms_lob.createtemporary(p_out, true); p_out := p_clob; execute immediate 'begin ' || p_model || '.update_(:x, :y); end;' using in p_id, in out p_out; exception when others then logger(p_proc => 'update_', p_model => p_model, p_stack => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace, p_clob => p_clob); raise; end update_; procedure delete_(p_id in number, p_model in varchar2, p_context in varchar2) is begin read_context(p_context, p_model); execute immediate 'begin ' || p_model || '.delete_(:x); end;' using p_id; exception when others then logger(p_proc => 'delete_', p_model => p_model, p_stack => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace); raise; end delete_; procedure method(p_clob in varchar2, p_method in varchar2, p_context in varchar2, p_out out clob) is begin read_context(p_context, p_method); dbms_lob.createtemporary(p_out, true); execute immediate 'begin ' || p_method || '(:x, :ret); end;' using in p_clob, in out p_out; exception when others then logger(p_proc => 'method', p_model => p_method, p_stack => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace, p_clob => p_clob); raise; end method; procedure method(p_clob in out clob, p_method in varchar2, p_context in varchar2) is begin read_context(p_context, p_method); execute immediate 'begin ' || p_method || '(:x, :ret); end;' using in p_clob, in out p_clob; exception when others then logger(p_proc => 'method2', p_model => p_method, p_stack => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace, p_clob => p_clob); raise; end method; end gateway; /