Oracle DB to Postgres migration thoughts

Since I've started my work with Oracle (8i - so, a while ago), I can remember at least two PoC/tries to migrate production DB from Oracle RDBMS to another DB engine. Those were finished unsuccessfully with the decision to leave data and PL/SQL code as is. 

As many times mentioned by Steven Feuerstein and Tom Kyte, there is not much sense to pay for Oracle DB license and NOT to use SQL and PL/SQL to manage your data - its hard to imagine better way to do it, plus you've already paid for this. 

No matter what was the reason to start the migration, translation of PL/SQL can be the biggest challenge.

Here and in next posts I'm going to share my experience and few solution for migration issues. 

Here is an input: 

• Oracle to Postgres 
• using ora2pg as a tool to translate all the objects including PL/SQL and move the data 
• tens of thousands lines of code to move 
• small data size 

As an output, after running ora2pg, we have a lot of create statements, some of them running with no problem, rest is having two types of issues 

• compilation issue - you cannot run create statement 
• runtime issue - object is created but you have an issue when run procedure or function 

Lets consider some:

• Oracle is tolerating data type mismatches - columns with foreign keys, joined columns, parameters of stored programs - in Postgres mismatches can cause an exception
• dynamic sql statements are ignored by ora2pg
• merge statement is not working in some of Postgres versions, but  we can use INSERT .. CONFLICT
• LPAD function have different parameter types. Most likely when you see "no such function" error after migration, first thing to check is the parameter order and type
• + operator is not working with date datatype out of the box, intervals can be a solution
• 
  

 Lets continue in future posts and here is few links to cover other issues:

• https://learn.microsoft.com/en-us/azure/postgresql/migrate/how-to-migrate-from-oracle
• https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
  
• https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.html
  

How-to: apex LDAP authentication for dummies

Here is tree very simple steps to authenticate users of your Apex application using their AD accounts.
   1. Ask your LDAP administrator to create directory (a group) and give to you

• DN String for this group
• ip address and port (probably, 389) address of AD server

   2. Go to Home>Application Builder>Application 106>Shared Components>Authentication Schemes>Create Authentication Scheme

• choose "From scratch" and enter any name, open it for editing
• fill it with info from your admin, adding left to DN stiring this: "CN=%LDAP_USER%,"

   3. Test it

Links:
OTN1
OTN2
How-To Document (oracle.com)
apache directory studio (to browse AD) 

Oracle Apex admin page

I desided to make new application in separate workspace.
I spend ten minutes trying to find link to the admin page.

So, admin page have the same url as login page to existing workspace, but it is on the page ten (for login in workspace it was 1).

http://3.196.171.20:7780/pls/cuod/f?p=4550:10

Create db link without editting tnsnames.ora

Often database link is the best way to transport rows, current values of sequences or to know differences between rows in tables in remote databases: developing, test, production, etc

To create db link you need a 'create database link' privilege. As a database developer, i always ask DBA to grant this privilege when he create my developing schemas (with connect, resource and create view privileges).

When i want to access remote database with db link i need to add it's description to a tnsnames.ora file. As a developer i have to ask DBA to do it: i don't have an access to tnsnames.ora in database server. Sometimes it can takes hours.

When i plan to create db link, run some query and drop it, i create it this way:

SQL> CREATE DATABASE LINK xe_myrudw
  2    CONNECT TO hr IDENTIFIED BY hr USING '
  3    (DESCRIPTION =
  4      (ADDRESS = (PROTOCOL = TCP)(HOST = myrudw-ccfz91j.r2.money.ge.com)(PORT = 1521))
  5      (CONNECT_DATA =
  6        (SERVER = DEDICATED)
  7        (SERVICE_NAME = XE)
  8      )
  9    )
 10  ';

Database link created.

SQL> SELECT 1 a FROM DUAL@XE_MYRUDW;

         A
----------
         1

without adding connect string to tnsnames.ora.

Delete all data from schema

I use this

WITH const AS
  (SELECT rconst.table_name,
    rconst.constraint_name
     FROM user_constraints rconst,
    user_constraints pconst
    WHERE rconst.constraint_type = 'R'
  AND rconst.r_constraint_name   = pconst.constraint_name
  AND pconst.constraint_type     = 'P'  
  )
SELECT 'ALTER TABLE '||const.table_name||' DISABLE CONSTRAINT '||const.constraint_name||';' rnme FROM const UNION ALL
SELECT 'TRUNCATE TABLE '||ut.table_name||';' FROM user_tables ut UNION ALL 
SELECT 'ALTER TABLE ' ||const.table_name ||' ENABLE CONSTRAINT ' ||const.constraint_name ||';' rnme FROM const;

to generate script to clean schema.

• All rows from all tables will be removed.
• You can NOT rollback this script even if you replace "truncate" with "delete" (alter statement permorms commit). But you can use flashback to query truncated data.
• You can write a pl\sql block with loop, using this select as a cursor and execute immediate command.

concatenate, group by

A hour ago i read post "String Aggregation Techniques".
I'm working with Oracle 10G, but i have never used COLLECT to concatenate

Look, it is pretty simple, don't require any user-defined aggregate function, types, analytic functions.

SQL> SELECT dept.DEPARTMENT_NAME DEP,
      COLLECT (emp.LAST_NAME) LAST_NAMES
       FROM departments dept,
            employees emp
      WHERE dept.DEPARTMENT_ID = emp.DEPARTMENT_ID
    GROUP BY dept.DEPARTMENT_NAME;

DEP                            LAST_NAMES
------------------------------ -------------------------------------------------

Administration                 SYSTPdOAhPUUuTY2R3kKsEORiAg==('Whalen')
Marketing                      SYSTPdOAhPUUuTY2R3kKsEORiAg==('Hartstein', 'Fay')

Of course if need to write a query for some report in UI or for some data extract process you shoud use another technique, but if you use 10g and need to see or show to youre colleagues same concatenated data you can use this. Without any preparation, creating new objects and so on. Or, if you are 11g R2 user, then you just can use LISTAGG analytic function.

Select from data dictionary

I often use this

SQL> SELECT VIEW_NAME FROM ALL_VIEWS
  2  WHERE VIEW_NAME LIKE '%K-E-Y-W-O-R-D%';

to find from what system or user view i can select samething what i need: objects, privilegies, users, APEX pages or processes.