-- 
-- Generated by MRX on 18.06.2025 19:21:39 using Quick PL/SQL
-- 
-- 
-- Number of generated packages: 2
-- 

create or replace package simple_test_pkg
as

  /*
  
  Purpose:    this is a simple test package
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  -- this procedure does something
  procedure do_something ();

  -- this function returns something
  function get_something (p_id in number) return varchar2;

  -- this procedure execution is logged
  procedure debug_something (p_value1 in number,
                             p_value2 in number);


end simple_test_pkg;
/


create or replace package body simple_test_pkg
as

  /*
  
  Purpose:    this is a simple test package
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

procedure do_something ()
as
begin

  /*
  
  Purpose:    this procedure does something
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  -- TODO: add your own magic here...
  null;

end do_something;


function get_something (p_id in number) return varchar2
as
  l_returnvalue varchar2;
begin

  /*
  
  Purpose:    this function returns something
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  -- TODO: add your own magic here...
  null;

  return l_returnvalue;

end get_something;


procedure calculate_something ()
as
begin

  /*
  
  Purpose:    this procedure calculates something
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  -- TODO: add your own magic here...
  null;

end calculate_something;


procedure debug_something (p_value1 in number,
                           p_value2 in number)
as
  l_scope  logger_logs.scope%type := lower($$plsql_unit) || '.' || 'debug_something';
  l_params logger.tab_param;
begin

  /*
  
  Purpose:    this procedure execution is logged
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  logger.append_param(l_params, 'p_value1', p_value1);
  logger.append_param(l_params, 'p_value2', p_value2);
  logger.log('START', l_scope, null, l_params);

  -- TODO: add your own magic here...
  null;

  logger.log('END', l_scope);

exception
  when others then
    logger.log_error('Unhandled Exception', l_scope, null, l_params);
    raise;

end debug_something;



end simple_test_pkg;
/


create or replace package customer_pkg
as

  /*
  
  Purpose:    package handles customers
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  -- add new customer
  function new_customer (p_customer_name in varchar2) return number;

  -- get customer row
  function get_customer (p_customer_id in number) return xy_customer%rowtype;

  -- get customer name
  function get_customer_name (p_customer_id in number) return xy_customer.customer_name%type;

  -- set customer
  procedure set_customer (p_customer_id in number,
                          p_customer_name in varchar2);

  -- set customer row
  procedure set_customer (p_row in xy_customer%rowtype);

  -- delete customer
  procedure delete_customer (p_customer_id in number);


end customer_pkg;
/


create or replace package body customer_pkg
as

  /*
  
  Purpose:    package handles customers
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

function new_customer (p_customer_name in varchar2) return number
as
  l_returnvalue number;
begin

  /*
  
  Purpose:    add new customer
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  insert into xy_customer (customer_name)
  values (p_customer_name)
  returning customer_id into l_returnvalue;

  return l_returnvalue;

end new_customer;


function get_customer (p_customer_id in number) return xy_customer%rowtype
as
  l_returnvalue xy_customer%rowtype;
begin

  /*
  
  Purpose:    get customer row
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  begin
    select *
    into l_returnvalue
    from xy_customer
    where customer_id = p_customer_id;
  exception
    when no_data_found then
      l_returnvalue := null;
  end;

  return l_returnvalue;

end get_customer;


function get_customer_name (p_customer_id in number) return xy_customer.customer_name%type
as
  l_returnvalue xy_customer.customer_name%type;
begin

  /*
  
  Purpose:    get customer name
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  begin
    select customer_name
    into l_returnvalue
    from xy_customer
    where customer_id = p_customer_id;
  exception
    when no_data_found then
      l_returnvalue := null;
  end;

  return l_returnvalue;

end get_customer_name;


procedure set_customer (p_customer_id in number,
                        p_customer_name in varchar2)
as
begin

  /*
  
  Purpose:    set customer
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  update xy_customer
  set customer_name = p_customer_name
  where customer_id = p_customer_id;

end set_customer;


procedure set_customer (p_row in xy_customer%rowtype)
as
begin

  /*
  
  Purpose:    set customer row
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  update xy_customer
  set row = p_row
  where customer_id = p_row.customer_id;

end set_customer;


procedure delete_customer (p_customer_id in number)
as
begin

  /*
  
  Purpose:    delete customer
  
  Remarks:    
  
  Date        Who  Description
  ----------  ---  -------------------------------------
  18.06.2025  MRX  Created

  */

  delete
  from xy_customer
  where customer_id = p_customer_id;

end delete_customer;



end customer_pkg;
/