OPMAG Embed Oracle to PostgreSQL Migration Assessment

Your developer's hourly rate

Upload ZIP with PL/SQL files

Report type

CTO-level technical report For budgeting & executive decisions

I confirm that the uploaded data does not contain personal data

Use this demo data or paste your PL/SQL package code here

create or replace package pkg_crm_entity
authid current_user
is
  subtype t_entity_id is number(18);
  procedure validate(p_id in t_entity_id);
  procedure before_save(p_id in t_entity_id);
  procedure after_save(p_id in t_entity_id);
end;

create or replace package body pkg_crm_entity
is
  procedure validate(p_id in t_entity_id)
  is
  begin
    raise_application_error(-20001
  end;

procedure before_save(p_id in t_entity_id)
  is
  begin
    null;
  end;

procedure after_save(p_id in t_entity_id)
  is
  begin
    null;
  end;
end;

create or replace package pkg_crm_event
is
  procedure subscribe(p_event in t_event_name, p_callback varchar2);
  procedure fire(p_event in t_event_name, p_payload clob default '{}');
end;

create or replace package body pkg_crm_event
is
  type t_subtab is table of varchar2(200) index by t_event_name;

g_subs t_subtab;

procedure subscribe(p_event in t_event_name, p_callback varchar2)
  is
  begin
    g_subs(p_event) := p_callback;
  end;

procedure fire(p_event in t_event_name, p_payload clob default '{}')
  is
  begin
    if g_subs.exists(p_event) then
      execute immediate 'begin ' || g_subs(p_event) || '(:1); end;'
        using p_payload;
    end if;
  end;
end;

create or replace package pkg_crm_security
is
  function can_read(
    p_user_id number,
    p_entity_id pkg_crm_entity.t_entity_id
  )
    return boolean;

function can_write(
    p_user_id number,
    p_entity_id pkg_crm_entity.t_entity_id
  )
    return boolean;
end;

create or replace package body pkg_crm_security
is
  function can_read(
    p_user_id number,
    p_entity_id pkg_crm_entity.t_entity_id
  )
    return boolean
  is
  begin
    return p_user_id = 1;
  end;
end;

create or replace package pkg_crm_audit
is
  procedure log_change(
    p_table_name varchar2,
    p_old_row json,
    p_new_row json,
    p_user_id number
  );
end;

create or replace package body pkg_crm_audit
is
  procedure log_change(
    p_table_name varchar2,
    p_old_row json,
    p_new_row json,
    p_user_id number
  )
  is
    pragma autonomous_transaction;
  begin
    insert into crm_audit_log(
      id,
      table_name,
      old_row,
      new_row,
      user_id,
      changed_on
    )
    values (
      crm_audit_seq.nextval,
      p_table_name,
      p_old_row,
      p_new_row,
      p_user_id,
      systimestamp
    );

commit;
  end;
end;

create or replace package pkg_crm_factory
is
  function get_entity_pkg_name(p_type varchar2) return varchar2;
end;

create or replace package body pkg_crm_factory
is
  function get_entity_pkg_name(p_type varchar2)
    return varchar2
  is
  begin
    if upper(p_type) = 'CONTACT' then
      return 'pkg_crm_contact';
    elsif upper(p_type) = 'OPPTY' then
      return 'pkg_crm_opportunity';
    else
      raise_application_error(-20002, 'Unknown CRM entity type');
    end if;
  end;
end;

create table crm_audit_log (
  id number generated by default as identity primary key,
  table_name varchar2(30),
  old_row json,
  new_row json,
  user_id number,
  changed_on timestamp default systimestamp
);

create sequence crm_audit_seq;

create or replace package pkg_crm_contact
authid current_user
is
  subtype t_contact_id is pkg_crm_entity.t_entity_id;

procedure validate(p_id in t_contact_id);
  procedure before_save(p_id in t_contact_id);
  procedure after_save(p_id in t_contact_id);
end;

create or replace package body pkg_crm_contact
is
  procedure validate(p_id in t_contact_id)
  is
  begin
    if p_id is null or p_id <= 0 then
      raise_application_error(-20003, 'Invalid contact id');
    end if;
  end;

procedure before_save(p_id in t_contact_id)
  is
  begin
    validate(p_id);

if not pkg_crm_security.can_write(p_user_id => 1, p_entity_id => p_id) then
      raise_application_error(-20004, 'No write rights');
    end if;
  end;

procedure after_save(p_id in t_contact_id)
  is
  begin
    pkg_crm_event.fire('CONTACT_SAVED', '{"id":' || p_id || '}');
  end;
end;

set serverout on

begin
  pkg_crm_event.subscribe(
    'CONTACT_SAVED',
    'begin dbms_output.put_line(''Event: CONTACT_SAVED payload='' || :1); end;'
  );

execute immediate '
    begin
      pkg_crm_contact.before_save(1);
      pkg_crm_contact.after_save(1);
    end;';
end;

create table crm_contact (
  id number primary key,
  full_name varchar2(200),
  email varchar2(200),
  created_on timestamp default systimestamp,
  status varchar2(30)
);

create table crm_opportunity (
  id number primary key,
  contact_id number,
  amount number(12, 2),
  stage varchar2(30),
  created_on timestamp default systimestamp
);

create sequence crm_entity_seq;

create or replace type t_crm_contact_row as object (
  id number,
  full_name varchar2(200),
  email varchar2(200)
);

create or replace type t_crm_contact_tab as table of t_crm_contact_row;

create or replace function f_crm_contacts_pipe(
  p_status varchar2 default null,
  p_limit number default 100
)
  return t_crm_contact_tab pipelined
as
  v_row t_crm_contact_row;
begin
  for r in (
    select
      c.id,
      c.full_name,
      c.email
    from crm_contact c
    where (p_status is null or c.status = p_status)
      and rownum <= p_limit
    order by c.id
  ) loop
    v_row := t_crm_contact_row(r.id, r.full_name, r.email);
    pipe row(v_row);
  end loop;

return;
end;

create or replace trigger trg_crm_contact_audit
after insert or update or delete on crm_contact
for each row
declare
  v_old json;
  v_new json;
begin
  if inserting or updating then
    v_new := json_object(
      'id' value :new.id,
      'full_name' value :new.full_name,
      'email' value :new.email,
      'status' value :new.status
    );
  end if;

if updating or deleting then
    v_old := json_object(
      'id' value :old.id,
      'full_name' value :old.full_name,
      'email' value :old.email,
      'status' value :old.status
    );
  end if;

pkg_crm_audit.log_change(
    p_table_name => 'CRM_CONTACT',
    p_old_row => v_old,
    p_new_row => v_new,
    p_user_id => 1
  );
end;

create or replace procedure proc_crm_merge_contact(
  p_id number,
  p_full_name varchar2,
  p_email varchar2,
  p_status varchar2 default 'ACTIVE'
)
as
begin
  merge into crm_contact dst
  using (
    select
      p_id id,
      p_full_name full_name,
      p_email email,
      p_status status
    from dual
  ) src
  on (dst.id = src.id)
  when matched then
    update set
      dst.full_name = src.full_name,
      dst.email = src.email,
      dst.status = src.status
  when not matched then
    insert (
      id,
      full_name,
      email,
      status
    )
    values (
      src.id,
      src.full_name,
      src.email,
      src.status
    );
end;

create or replace procedure proc_crm_bulk_stage_update(
  p_stage varchar2,
  p_min_amount number
)
as
  type t_id_tab is table of number index by pls_integer;

l_ids t_id_tab;
  l_errors number := 0;

failure_in_forall exception;
  pragma exception_init(failure_in_forall, -24381);
begin
  select id
  bulk collect into l_ids
  from crm_opportunity
  where amount >= p_min_amount;

forall i in 1 .. l_ids.count save exceptions
    update crm_opportunity
    set stage = p_stage
    where id = l_ids(i);

exception
  when failure_in_forall then
    l_errors := sql%bulk_exceptions.count;
    raise_application_error(-20100, 'FORALL failed, errors=' || l_errors);
end;

create or replace procedure proc_crm_dyn_query(
  p_min_amount number,
  p_rc out sys_refcursor
)
as
  v_sql varchar2(4000);
begin
  v_sql :=
    'select o.id, o.contact_id, o.amount, o.stage ' ||
    'from crm_opportunity o ' ||
    'where o.amount >= :x ' ||
    'order by o.amount desc';

open p_rc for v_sql using p_min_amount;
end;
/

create or replace view v_crm_oppty_contact_oldjoin
as
select
  c.id as contact_id,
  c.full_name,
  o.id as oppty_id,
  o.amount,
  decode(o.stage, 'WON', 1, 'LOST', -1, 0) as stage_score
from crm_contact c,
     crm_opportunity o
where o.contact_id(+) = c.id;

create or replace view v_crm_contact_stats
as
select
  c.id,
  c.full_name,
  count(o.id) over (partition by c.id) as oppty_cnt,
  nvl(sum(o.amount) over (partition by c.id), 0) as total_amount,
  row_number() over (order by c.id) as rn,
  listagg(nvl(o.stage, 'NULL'), ',') within group (order by o.stage) as stages
from crm_contact c
left join crm_opportunity o
  on o.contact_id = c.id
group by
  c.id,
  c.full_name;

create or replace package pkg_crm_opportunity
authid current_user
is
  subtype t_oppty_id is pkg_crm_entity.t_entity_id;

procedure validate(p_id in t_oppty_id);
  procedure before_save(p_id in t_oppty_id);
  procedure after_save(p_id in t_oppty_id);
end;
/

create or replace package body pkg_crm_opportunity
is
  procedure validate(p_id in t_oppty_id)
  is
  begin
    if p_id is null or p_id <= 0 then
      raise_application_error(-20013, 'Invalid opportunity id');
    end if;
  end;

procedure before_save(p_id in t_oppty_id)
  is
  begin
    validate(p_id);

if not pkg_crm_security.can_write(p_user_id => 1, p_entity_id => p_id) then
      raise_application_error(-20014, 'No write rights');
    end if;
  end;

procedure after_save(p_id in t_oppty_id)
  is
  begin
    pkg_crm_event.fire('OPPTY_SAVED', '{"id":' || p_id || '}');
  end;
end;
/

begin
  dbms_scheduler.create_job(
    job_name => 'CRM_DEMO_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin proc_crm_merge_contact(1,''Demo User'',''demo@example.com'',''ACTIVE''); end;',
    start_date => systimestamp,
    enabled => false
  );
exception
  when others then
    null;
end;
/

Oracle to PostgreSQL Migration Assessment - How to Prepare Your Upload

1. Extract PL/SQL Source Code From Oracle

2. Export Code as Raw Text (.sql)

DBeaver (Recommended)

Oracle SQL Developer (Script Output + SPOOL)

3. Zip & Upload