Oracle Generate Merge Script

I have found this script and its so useful, I had to share it again.

Anytime you want to sync your data from the production database to the test database you can use this script.

JUST BE VERY CAREFUL TO NOT UPDATE YOUR PRODUCTION DATA WITH TEST DATA.

I like to run it on the test database, to be safe I create a DB link with a user that just have only Read Only access on the production data, to ensure you don’t update production data.

If you have more than one test database its always advisable to test it first between test databases.

This script wont update any data it will only generate the merge statement that you can use to update your data.

RazorFink Table Merge


--------------------------------------------------------------------------------
--Meta SQL for scaffolding table merge statements
--  Usage:
--    Execute SQL
--    export/copy lines to sql file
--    make necessary edits
--  TODO:
--    make all column references explicit
--------------------------------------------------------------------------------

WITH
    target AS
    (SELECT
        upper(trim(:owner))      AS owner,
        upper(trim(:table_name)) AS table_name
      FROM
        dual
    ),
    all_cols AS
    (SELECT
        atc.owner       AS owner,
        atc.table_name  AS table_name,
        atc.column_name AS column_name,
        atc.column_id   AS column_id,
        atc.data_type   AS data_type,
        atc.data_length AS data_length
      FROM
        all_tab_cols atc,
        target
      WHERE
        atc.owner           = target.owner      AND
        atc.table_name      = target.table_name AND
        atc.hidden_column  != 'YES'             AND
        atc.virtual_column != 'YES'
      ORDER BY
        column_id
    ),
    pk_cols AS
    (SELECT
        ac.owner           AS owner,
        ac.table_name      AS table_name,
        acc.column_name    AS column_name,
        acc.position       AS position,
        all_cols.column_id AS column_id
      FROM
        all_constraints ac,
        all_cons_columns acc,
        all_cols
      WHERE
        ac.owner            = all_cols.owner AND
        ac.table_name       = all_cols.table_name AND
        ac.constraint_type  = 'P' AND
        acc.owner           = ac.owner AND
        acc.table_name      = ac.table_name AND
        acc.constraint_name = ac.constraint_name AND
        acc.column_name     = all_cols.column_name
      ORDER BY
        acc.position
    ),
    data_cols AS
    (SELECT
        owner,
        table_name,
        column_name,
        column_id
      FROM
        all_cols
    MINUS
    SELECT
        owner,
        table_name,
        column_name,
        column_id
      FROM
        pk_cols
      ORDER BY
        column_id
    ),
    sql_parts AS
    (SELECT
        'SELECT'                 AS sql_select,
        'FROM'                   AS sql_from,
        'WHERE'                  AS sql_where,
        'ORDER BY'               AS sql_order_by,
        ';'                      AS sql_semi,
        'MERGE INTO'             AS sql_merge_into,
        'USING'                  AS sql_using,
        'ON'                     AS sql_on,
        'WHEN MATCHED THEN'      AS sql_when_matched,
        'UPDATE SET'             AS sql_update_set,
        'WHEN NOT MATCHED THEN'  AS sql_when_not_matched,
        'INSERT'                 AS sql_insert,
        'VALUES'                 AS sql_values,
        '('                      AS sql_paren_open,
        ')'                      AS sql_paren_close,
        chr(10)                  AS sql_lf,
        '  '                     AS sql_t,
        MAX(LENGTH(column_name)) AS max_col_length
      FROM
        dual,
        all_cols
    )
  --------------------------------------------------------------------------------
  SELECT
      STATEMENT
    FROM
      (SELECT
          1              AS statement_order,
          rownum         AS row_order,
          sql_merge_into AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          2      AS statement_order,
          rownum AS row_order,
          sql_parts.sql_t
          ||lower(owner)
          ||'.'
          ||lower(table_name)
          ||' o' AS STATEMENT
        FROM
          target,
          sql_parts
      UNION
      SELECT
          3         AS statement_order,
          rownum    AS row_order,
          sql_using AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          4      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          5      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_select AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          6      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          ||sql_t
          || rpad(lower(all_cols.column_name), sql_parts.max_col_length, ' ')
          ||rpad(' AS '
          ||all_cols.column_name
          ||(
            CASE
              WHEN lead(all_cols.column_id) over (order by all_cols.column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END), sql_parts.max_col_length+6, ' ')
          ||'-- '
          ||all_cols.data_type
          ||sql_parts.sql_paren_open
          ||all_cols.data_length
          ||sql_parts.sql_paren_close AS STATEMENT
        FROM
          all_cols,
          sql_parts
      UNION
      SELECT
          7      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          ||sql_from
        FROM
          sql_parts
      UNION
      SELECT
          8      AS statement_order,
          rownum AS row_order,
          sql_parts.sql_t
          ||sql_parts.sql_t
          ||sql_parts.sql_t
          ||sql_parts.sql_t
          || lower(target.owner
          ||'.'
          ||target.table_name) AS STATEMENT
        FROM
          target,
          sql_parts
      UNION
      SELECT
          9      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          || sql_order_by AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          10       AS statement_order,
          position AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          ||sql_t
          || lower(column_name)
          || (
            CASE
              WHEN lead(position) over (order by position) IS NOT NULL
              THEN ','
              ELSE NULL
            END )
        FROM
          pk_cols,
          sql_parts
      UNION
      SELECT
          11     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_close
          ||' n' AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          12     AS statement_order,
          rownum AS row_order,
          sql_on AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          13     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          14       AS statement_order,
          position AS row_order,
          sql_t
          ||sql_t
          || rpad('o.'
          ||lower(column_name), max_col_length+2, ' ')
          ||' = '
          ||'n.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by position) IS NOT NULL
              THEN ' AND'
              ELSE NULL
            END) AS STATEMENT
        FROM
          pk_cols,
          sql_parts
      UNION
      SELECT
          15     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_close AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          16               AS statement_order,
          rownum           AS row_order,
          sql_when_matched AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          17     AS statement_order,
          rownum AS row_order,
          sql_t
          || sql_update_set AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          18     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          || rpad('o.'
          ||lower(column_name), max_col_length+2, ' ')
          ||' = '
          ||'n.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END) AS STATEMENT
        FROM
          data_cols,
          sql_parts
      UNION
      SELECT
          19                   AS statement_order,
          rownum               AS row_order,
          sql_when_not_matched AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          20     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_insert AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          21     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          22     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          || 'o.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END) AS STATEMENT
        FROM
          all_cols,
          sql_parts
      UNION
      SELECT
          23     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_close AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          24     AS statement_order,
          rownum AS row_order,
          sql_t
          || sql_values
        FROM
          sql_parts
      UNION
      SELECT
          25     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          26     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          || 'n.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END) AS STATEMENT
        FROM
          all_cols,
          sql_parts
      UNION
      SELECT
          27     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_close AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          28       AS statement_order,
          rownum   AS row_order,
          sql_semi AS STATEMENT
        FROM
          sql_parts
      )
    ORDER BY
      statement_order,
      row_order ;

PHP 7.0 Apache Oracle 12 Instantclient OCI8 install apt-get

If you need to do this from scratch I have followed these how-to-install-and-configure-php-70-or-php-71-on-ubuntu-16-04 insructions to install PHP7.0 and apache.

Once installed then
Follow these instructions to compile oci8 extentions Oracle tech note OCI8

Download the instant client from oracle’s web site here : Oracle instant client download
I downloaded the .ZIP files since I dont have RPM installed.
I then extracted it to ~/Downloads/instantclient_12_2
NOTE: Download the instantclient-basic-linux.x64-12.2.0.1.0.zip and instantclient-sdk-linux.x64-12.2.0.1.0.zip

cd ~Downloads
unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip

Now we need to create the paths

sudo mkdir /usr/lib/oracle
sudo mkdir /usr/lib/oracle/12.2
sudo mkdir /usr/lib/oracle/12.2/client64
sudo mkdir /usr/lib/oracle/12.2/client64/lib
cd ~/Downloads/instantclient_12_2
sudo cp * /usr/lib/oracle/12.2/client64/lib/
cd /usr/lib/oracle/12.2/client64/lib/
ln -s libclntsh.so.12.1 libclntsh.so
sudo mkdir /usr/include/oracle
sudo mkdir /usr/include/oracle/12.2
sudo mkdir /usr/include/oracle/12.2/client64
cd ~/Downloads/instantclient_12_2/sdk/include
sudo cp * /usr/include/oracle/12.2/client64
export ORACLE_HOME=/usr/lib/oracle/12.2

Edit ld.so.conf and include oracle libs. Add a file called oracle.conf under /etc/ld.so.conf.d

cd /etc/ld.so.conf.d
sudo vi oracle.conf
PRESS ESC i
/usr/lib/oracle/12.2/client64/lib
PRESS ESC :wq

Now to download and compile OCI8

sudo pecl install oci8 --with-oci8=instantclient,/usr/lib/oracle/12.2/client64/lib

On My machine it compiled it under ‘/usr/lib/php/20151012/oci8.so’ but look at the last few lines it should echo it.

First lets install a page to test that the configuration works.

sudo vi /var/www/html/t.php ( or xed /var/www/html/t.php if you dont know vi)
Press ESC i and paste

Press ESC :qw

now open your favorite browser and go to http://localhost/t.php and you should see a information page of php

to configure php do:

cd /etc/php/7.0/mods-available
vi oci8.ini

Edit oci8.ini with content

extension=oci8.so

Now restart

sudo service apache2 restart

My Installation now gave an error

apachectl configtest
if you get an error about libaio.so.1: cannot open shared object file: No such file or directory

sudo apt-get install libaio1 libaio-dev

Now we need to give the path to the libraries.
Edit /etc/apache2/envvars and add the flowing to the bottom of the file

ORACLE_HOME=/usr/lib/oracle/12.2/client64
LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib
LD_LIBRARY_PATH64=/usr/lib/oracle/12.2/client64/lib/
#TNS_ADMIN=$ORACLE_HOME/network/admin
#NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
#ORACLE_SID=your_db

To Test

php --ri oci8

Fixing Timezone Issue on Oracle SQL Developer

If you login with SQL Developer and you get the following:

An error was encountered performing the requested operation:

ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
Vendor code 604

Do the Following:
edit file
sqldeveloper\ide\bin\ide.conf
and add
AddVMOption -Duser.timezone=”+02:00″
at the bottom of the file
OR AddVMOption -Duser.timezone=”GMT+2″

(Just change it according to your time zone…..)

Start SQL developer and To fix date time format go to :
Tools -> Preferences -> database -> nls and change date format to YYYY/MM/DD HH24:MI:SS (Or any of your desired time formats)

And to fix startup errors go to
{USER HOME}\AppData\Roaming\SQL Developer
and rename system4.1.0.19.07 to system4.1.0.18.07

Xen Client VM to work with Oracle Virtual Box

To convert my Xen Client VM to Oracle Virtual Box I did the following:
UPDATE : 30 March 2017
A more easier way is to export the VM in the normal way that will export a system.vhd file.

Now just Extract the system.vhd to a folder on the PC. Go then to Oracle Virtual Box, select new VM
NewVM

Make sure you select Use Existing Virtual Hard Disk and browse to the system.vhd.

Click on the Create VM. After that BEFORE YOU RUN IT, go to settings and ADD an IDE controller.
After that add the system.vhd to the IDE controller and remove it from the SATA Controller.
it should look like:
Storage Settings VM

Save and start the VM,it should work fine.

I have exported my Windows 7 Xen Client VM using XenConvert XenConvert_Install2.5.exe.
Although this is used for P2V I used it to convert my Xen Virtual Machine to Open Virtualization Format.
Note this will ask you to specify the type and select Open Virtualization Format.

When the process is done you will end up with YourVirtualMachineName.ova, if you try to import into Virtual Box you will get an error:

The Appliance YourVirtualMachineName.ova could not be imported.
Document labelled UTF-16 but has UTF-8 content.
Location: ‘YourVirtualMachineName.ova’, line 1 (0), column 36.

Details:
Error code: VBOX_E_FILE_ERROR (0x80BB0004)
Component: Appliance
Interface: IAppliance {SOMEXXXXXNUMBER}

Now I untarred the .ova by typing tar -xvf YourVirtualMachineName.ova on your linux console. (Maybe try 7zip if you are running windows).

This will leave you with a YourVirtualMachineName.vhd. You can use one of the many Other Links on the internet to see how to import a VHD to virtual box. Now the problem is when you start it, it starts up and then a quick blue screen flash and it dies. Mine gave Stop error code 0x0000007B I think…

To solve this stop the virtual machine. Go to the Storage settings. Delete the SATA Controller with the attached VHD disk. Dont delete the disk though just the connection to the SATA. Then go and create a new IDE controller. Add the Virtual Disk YourVirtualMachineName.vhd and remember to also add an CD Rom. Whola!