Java Solutions: March 2016

Friday, March 25, 2016

Migration from Oracle to PostgreSQL using oracle_fdw Foreign Data Wrapper on Windows - step by step guide

A. Install PostgreSQL

1. Download PostgreSQL from its Official Website.

2. For installing on Windows, launch the GUI installer wizard by executing the .exe file. The installer will create a default PostgreSQL superuser, postgres. Give it a password when asked for. A detailed installation guide can be obtained from Install PostgreSQL.

After you finished installing PostgreSQL, add full path of its bin and lib directories to the PATH environmental variable.

3. Modify the postgresql.conf. The file is located in the data directory. Open it with a text editor. Change the listen_addresses to '*' (listen_address = '*') and the port to the port number you used during installation

4. Create a new database in pgAdmin III by right clicking Databases and selecting New Database.

5. Create a new schema in pgAdmin III by right clicking the Schemas and selecting New Schema.

6. Install the pgJDBC and pgODBC. Click Start/All Programs/PostgreSQL/Application Stack Builder to launch the Application Stack Builder.

Select your PostgreSQL installation. Click Next.

Unfold Database Drivers. Check pgJDBC and psqlODBC. And click Next

On following screens, click Next or Install to install the pgJDBC and pgODBC.

The pgJDBC URL: jdbc:postgresql://<host IP address>:<port>/<database name>
The pgJDBC driver: org.postgresql.Driver

B. Install Oracle Instant Client.

The Foreign Data Wrapper for Oracle requires Oracle Instant Client to access the remote Oracle database. Otherwise, it will throw a "could not load library "C:/Program Files/PostgreSQL/9.4/lib/oracle_fdw.dll" error.

A detailed installation guide can be obtained at Install Oracle Instant Client on Windows.

C. Install Oracle Data Wrapper oracle_fdw

1. download oracle_fdw from its Official Website.

2. Unzip it to a directory.

3. Copy the three files oracle_fdw.control, oracle_fdw--1.0--1.1.sql, and oracle_fdw--1.1.sql from the share\extension directory to the PostgreSQL Home\share\extension\ directory.

4. Copy lib\oracle_fdw.dll to PostgreSQL Home\lib\ directory.

5. Create the oracle_fdw extension in PostgreSQL. Select your database in pgAdmin III. Click the Tools in the top menu bar and select Query tool. In the SQL Editor, type CREATE EXTENSION oracle_fdw WITH SCHEMA <your schema>, and execute it.

D. Migration from Oracle to PostgreSQL

1. Create the Foreign Data Wrapper server. In the SQL Editor, execute the below statements.

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//<oracle database host IP address>:<port>/<database name>');

GRANT USAGE ON FOREIGN SERVER oradb TO postgres;

2. Create user mapping by execute the following statement in the SQL Editor.

CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user '<oracle database user>', password '<oracle database password>');

3. Migrating tables.

i) Create the foreign table. A foreign table is a stub to access corresponding remote oracle database table as if it is local.

CREATE FOREIGN TABLE <foreign table name> SERVER oradb OPTIONS (table '<oracle database table name>');

ii) Migrating data to PostgreSQL.

CREATE TABLE <table name> AS (SELECT * FROM <foreign table name>);

E. Drop the User Mapping, Server, and oracle_fdw Extension.

DROP EXTENSION IF EXISTS oracle_fdw CASCADE;

With CASCADE option at the end, it will drop all objects that depend on the oracle_fdw extension.

Or you can drop each one of them separately.

  DROP USER MAPPING for postgres server oradb;

  DROP SERVER oradb;

  DROP EXTENSION IF EXISTS oracle_fdw;

--------------------------------------------------------------------------------------------------------

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

Thursday, March 24, 2016

Install Oracle Instant Client on Windows and linux

Oracle Instant Client enables you to access remote Oracle database as if it is local. Oracle Instant Client is an open source software.

Windows

A. Install Oralce Instant Client

1. Download Oracle Instant Client from its official website. Choose the version according to your Oracle database and 64 or 32 bit according to your windows.

2. Extract the files in the downloaded zip file into a folder (e.g. C:\Program Files (x86)\Oracle Instant Client\instantclient_11_1).

3. Add the Oracle Instant Client folder (C:\Program Files (x86)\Oracle Instant Client\instantclient_11_1) to the PATH environmental variable.

Create TNS_ADMIN and ORACLE_HOME environmental variables and set their values to the Oracle Instant Client folder (C:\Program Files (x86)\Oracle Instant Client\instantclient_11_1).

Restart your computer.

4. In the Oracle Instant Client folder (C:\Program Files (x86)\Oracle Instant Client\instantclient_11_1
), create the tnsnames.ora and sqlnet.ora files. Below are examples of the files.

tnsnames.ora:
It is usually located in your oracle database home/network/admin directory. You can copy from there. It looks like this.

<Oracle database name> =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <Oracle hose IP address>)(PORT = <Oracle database port>))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <service name>)
)
)

sqlnet.ora:
It tells Oracle to time out if a connection to Oracle server cannot be obtained within the specified time in seconds.

TCP.CONNECT_TIMEOUT=10

B. Create the registry keys

1. Create the ORACLE Key

Windows 64bit: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE
Windows 32bit: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

2. Create a sub key in the above key using the folder of your Oracle Instant Client. For example.

KEY_instantclient_11_1

3. Create the following string values in the above key.

ORACLE_HOME, set value to the same as your ORACLE_HOME environmental variable.
ORACLE_HOME_NAME, choose a name for it.
NLS_LANG, pick a value from this website according to your language. For example.
AMERICAN_AMERICA.WE8MSWIN1252

You are done.

Linux

A. Install Oracle Instant Client

Login as administrator.

1. Use command (uname -m) to decide the bit of your linux machine; and command (lsb_release -a or cat /etc/redhat-release ) to find the Distributor ID and release of your linux machine.

2. Create a dirctory /usr/oracle

3. Download the basic, sqlplus, and sdk packages of the oracle instant client from their Official Website according to your machine. For example, if your linux is 64 bit, download the following packages.

instantclient-basic-linux.x64-12.1.0.2.0.zip
instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
instantclient-sdk-linux.x64-12.1.0.2.0.zip

4. Run the command below.

unzip /usr/oracle/instantclient-basic-linux.x64-12.1.0.2.0.zip
unzip /usr/oracle/instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
unzip /usr/oracle/instantclient-sdk-linux.x64-12.1.0.2.0.zip

Create the appropriate libclntsh.so and libocci.so links for the version of Instant Client.

ln -s /usr/oracle/instantclient_12_1/libclntsh.so.12.1 /usr/oracle/instantclient_12_1/libclntsh.so
ln -s /usr/oracle/instantclient_12_1/libocci.so.12.1 /usr/oracle/instantclient_12_1/libocci.so

ln -s /usr/oracle/instantclient_12_1/libclntshcore.so.12.1 /usr/lib/libclntshcore.so.12.1
ln -s /usr/oracle/instantclient_12_1/libipc1.so /usr/lib/libipc1.so
ln -s /usr/oracle/instantclient_12_1/libmql1.so /usr/lib/libmql1.so
ln -s /usr/oracle/instantclient_12_1/libnnz12.so /usr/lib/libnnz12.so
ln -s /usr/oracle/instantclient_12_1/libocci.so /usr/lib/libocci.so
ln -s /usr/oracle/instantclient_12_1/libociei.so /usr/lib/libociei.so
ln -s /usr/oracle/instantclient_12_1/libocijdbc12.so /usr/lib/libocijdbc12.so
ln -s /usr/oracle/instantclient_12_1/libons.so /usr/lib/libons.so
ln -s /usr/oracle/instantclient_12_1/liboramysql12.so /usr/lib/liboramysql12.so
ln -s /usr/oracle/instantclient_12_1/libsqlplus.so /usr/lib/libsqlplus.so
ln -s /usr/oracle/instantclient_12_1/libsqlplusic.so /usr/lib/libsqlplusic.so
ln -s /usr/oracle/instantclient_12_1/libclntsh.so /usr/lib/libclntsh.so

5. Set the LD_LIBRARY_PATH and environmental variables
Modify the /root/.bash_profile file. Add :/usr/oracle/instantclient_12_1 to the end of the PATH. And add the following lines.

  export LD_LIBRARY_PATH=/usr/oracle/instantclient_12_1
export ORACLE_HOME=/usr/oracle/instantclient_12_1
  export TNS_ADMIN=/usr/oracle/instantclient_12_1

If any of the above environmental variables already exists, append the path preceded by a colon to the end of the existing path

6. Test the Oracle Instant Client.

sqlplus <oracle user>/<oracle password>@//<oracle IP address>:<port>/<Oracle database name>
(e.g. sqlplus SCOTT/TIGER@//11.15.0.112:1521/ORACLE)

-----------------------------------------------------------------------------------------------------------

oracle sql statement where clause contains a string or character

You may use any of the below methods to test if a field value contains a string or a character in Oracle.

1. SELECT NAME FROM FRUITS WHERE NAME like '%<stirng or char>%';

2. SELECT NAME FROM FRUITS WHERE NAME like 'A%<stirng or char>%LE';

3. SELECT NAME FROM FRUITS WHERE NAME like 'A%' || '<stirng or char>' || %LE';
for example:
SELECT NAME FROM FRUITS WHERE NAME like 'gl%' || unistr('\\0000') || '%cy';

4. SELECT NAME FROM FRUITS WHERE instr(NAME, <string or char>) >= 0;

-------------------------------------------------------------------------------------

org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00

When you migrate data from a different database to PostgreSQL, sometimes you will have such a problem. This error occurs because PostgreSQL does not allow \0x00, the NULL value in UTF8 encoding, in its text field.

To fix it you need to remove the "\0x00' from the sting.

1. Oracle as the source database

  UPDATE <table>
SET <column>=Replace(<column>, unistr('\\0000'))
WHERE instr(<column>, unistr('\\0000')) >= 0;

2. MySQL as the source database

  UPDATE <table>
SET <column> = replace(comment_text, 0x00, '')
WHERE <column> like concat('%', 0x00, '%');

Monday, March 21, 2016

Postgresql: View all the extensions available

In PostgreSQL, the pg_available_extensions view has all the extensions. You can use the SQL statement below to view them.

SELECT name, default_version, installed_version, comment
FROM pg_available_extensions

Thursday, March 17, 2016

SQL: Get the number of rows/records of each table or the total number of rows/records of all tables in Oracle and PostgreSQL? - resolved

A. Get the number of rows of each table.

1. Oracle

SELECT TABLE_NAME, SUM(NUM_ROWS)
FROM USER_TABLES
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME;

2. PostgreSQL

  SELECT relname, n_live_tup
FROM pg_stat_user_tables
WHERE schemaname = '<schema>'
ORDER BY relname;

B. Get the total number of rows of all tables.

1. Oracle

SELECT SUM(NUM_ROWS) FROM USER_TABLES;

2. PstgreSQL

  SELECT sum(n_live_tup) FROM pg_stat_user_tables WHERE schemaname = '<schema>';

------------------------------------------------------------------------------

Friday, March 11, 2016

SQL: TIMESTAMP and DATE usage and conversion in SQL statement

A. TIMESTAMP

1. Use CURRENT_TIMESTAMP to insert the current timestamp into your SQL statement. For example,

Insert into STAR_MOVE values ('MARS', 'HIGH', 'MIDSKY', CURRENT_TIMESTAMP);

SELECT CURRENT_TIMESTAMP, NAME, STATUS FROM STAR_MOVE;

2. Use the TO_TIMESTAMP to convert a string to timestamp.

SELECT STAR_NAME FROM STAR_MOVE
WHERE InsertedTime > TO_TIMESTAMP('23/09/2015', 'dd/mm/yyyy');

3. Uset TO_CHAR to make a timestamp a string.

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') from dual;

SELECT STAR_NAME, TO_CHAR(InsertedAt, 'DD/MM/YYYY HH24:MI')
FROM STAR_MOVE

B. DATE

1. Use SYSDATE to put the current date into your SQL satement. For example,

Insert into StartOfSeasons values ('Spring', SYSDATE);

2. Use TO_DATE to convert a string to date.

Insert into StartOfSeason('Spring', TO_DATE('2016/03/15:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));

SELECT SEASON FROM StartOfSeason
WHERE SeasonStartDate > TO_DATE('01-03-2015', 'DD-MM-YYYY');

3. Use TO_CHAR to display a date in a particular format.

SELECT NAME, TO_CHAR(SeasonStartDate, 'YYY/MM/DD') FROM StartOfSeasons;
SELECT TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS') FROM dual;

For both timestamp and date

Use TRUNC to convert a long date or timestamp to a short date.

  select trunc(to_date('2016-05-31 23:12:05', 'YYYY-MM-DD HH24:MI:SS')) from dual;
The output is 31-MAY-16.

  select trunc(to_date('2015-05-15', 'YYYY-MM-DD')) from dual;
The output is 15-MAY-15.

select trunc(current_timestamp) from dual;
The output is 11-MAR-16

select 'test' from dual where trunc(to_date('2016-05-31 23:12:05', 'YYYY-MM-DD HH24:MI:SS')) > to_date('2015-05-15', 'YYYY-MM-DD');

SQL: How to get the next value of a sequence?

To use the next value of a sequence in a SQL statement is much simpler and easier than you can imagine. Just insert the following string with your sequence name to where is needed in your SQL statement.

<Sequence Name>.nextval

Thursday, March 10, 2016

SQL: How to replace a char or a string in data returned from a SELECT or in an UPDATE?

A. Replace one char.

Lets say you have a table FRUIT in your database.

TABLE FRUIT

NAME PRICE DESCRIPTION

APPLE 2.35 #The most popular fruit.
ORANGE 1.23 #The most juicy fruit.

1. SELECT

Now, you want to list the fruit name and description. By querying your database with the SQL statement below, you will have the # sign in front of each description.

SELECT NAME, DESCRIPTION FROM FRUIT;

You can use the Replace function to remove the # sign.

SELECT NAME, Replace(DESCRIPTION, '#', '') DESCRIPTION FROM FRUIT;

In general, you can replace char x with char y by the expression Replace(<field name>, 'x', 'y').

2. UPDATE

To remove the # sign from the DESCRIPTION field in the FRUIT table.

UPDATE FRUIT SET DESCRIPTION = Replace(DESCRIPTION, '#', '');

B. Replace a string.

To replace a string works the same as replace one char. For example, you have the following row in your FRUIT table.

NAME PRICE DESCRIPTION

WATER MELON 0,99 igThe most welcome fruit in summer.

You want to replace the ig with two stars **. You can use the expression below to achieve the goal.

Replace(DESCRIPTION, 'ig', '**')

Friday, March 4, 2016

postgresql: list / get all sequences, functions, tables, views, and indexes

A. SEQUENCE

SELECT * FROM information_schema.sequences WHERE sequence_schema = '<schema>';

SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = '<schema>';

B. FUNCTION

SELECT routine_name FROM information_schema.routines WHERE routine_schema = '<schema>';

SELECT r.routine_name || '(' || p.data_type || ')' FROM information_schema.routines r INNER JOIN information_schema.parameters p ON r.specific_name = p.specific_name WHERE r.specific_schema ='<schema>';

To get a function by its name:

select pg_get_functiondef(oid) from pg_proc where proname = '<your function name>';

C. TABLE

SELECT TABLENAME FROM pg_tables WHERE SCHEMANAME = '<schema>';

SELECT table_name FROM information_schema.tables WHERE table_schema='<schema>';

D. VIEW

SELECT viewname FROM pg_views WHERE schemaname='<schema>';

SELECT table_name FROM information_schema.views WHERE table_schema='<schema>';

E. INDEX

SELECT relname FROM pg_class, pg_index WHERE pg_class.oid = pg_index.indexrelid AND relname !~ '^pg_';

-----------------------------------------------------------------------

Thursday, March 3, 2016

postgresql: get all users and alter user

Get all users:
SELECT * FROM pg_user;

SELECT usename FROM pg_user;

Rename a user:
ALTER USER bsmith RENAME TO bjohns;

Make a user superuser
ALTER USER bsmith WITH SUPERUSER;

Change a user's password:
ALTER USER bjohns PASSWORD '12345bjxy';

Set the validity of a user's password:
ALTER USER bjohns VALID UNTIL 'Dec 31 2020';

ALTER USER bjohns VALID UNTIL 'infinity';

Give a user ability to do something:
ALTER USER bjohns CREATEUSER;

ALTER USER bjohns CREATEUSER NOCREATEDB;

pg_user is a view in posgresql, having the following information about all database users.

Name	Type	Description
`usename`	`name`	User name
`usesysid`	`oid`	ID of this user
`usecreatedb`	`bool`	User can create databases
`usesuper`	`bool`	User is a superuser
`usecatupd`	`bool`	User can update system catalogs. (Even a superuser cannot do this unless this column is true.)
`userepl`	`bool`	User can initiate streaming replication and put the system in and out of backup mode.
`passwd`	`text`	Not the password (always reads as `********`)
`valuntil`	`abstime`	Password expiry time (only used for password authentication)
`useconfig`	`text[]`	Session defaults for run-time configuration variables

Reference:

1. PostgreSQL 9.2.25 Documentation: pg_user

Get all table names from oracle and postgresql

For Oracle:

SELECT TABLE_NAME FROM USER_TABLES;

For PostgreSQL:

SELECT TABLENAME FROM pg_tables WHERE SCHEMANAME = '<schema>';

SELECT table_name FROM information_schema.tables WHERE table_schema='<schema>';

---------------------------------------------------------------------------------------------------

org.postgresql.util.PSQLException: ERROR: DROP XXXXXX cannot run inside a transaction block

If your connection to database is set auto commit to false, once you commit, all the transactions will be run as a block.

Connection conn = getConnection();
conn.setAutoCommit(false);

Certain SQL statement cannot run in a transaction block. so you need to set auto commit to true of your connection for these commands to successfully execute. Examples of such commands are

CREATE database
DROP database
DROP tablespace
VACUUM

-----------------------------------------------------------------------------------------------------

Wednesday, March 2, 2016

postgresql warning: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly

After you login from the command line through psql, you see the message,

"psql (9.5.1)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help."

This is because Windows console uses a different encoding than the psql console application. To solve this you need to set the code page for your psql before login.

To reset the code page:

database => \q //log out of the database

C:\Users\dsmith> chcp 1252 //change code page to 1252
Active code page: 1252

C:\Users\dsmith> psql -d dbname -h host -p port -U username
Password for user username:
psql (9.5.1)
Type "help" for help.

Tuesday, March 1, 2016

postgresql tablespace is not empty

If you have deleted everything in the database possibly belonging to the tablespace, check the location of the tablespace and delete that directory will solve your problem.

------------------------------------------------------------------------------------------------------------------

Postgresql: How to login through psql from command line? - resolved

When trying psql, you get error like "psql: Could not connect to server: Connection refused. Is the server running on host xxxxxxxxxx and accepting TCP/IP connections on port 5432?", you would like to read this article to check if things are set and used correctly.

On the database server:

1. Modify the pg_hba.conf file to add client authentication record. The file is located at <postgresql home>/data directory. Add the following line to the file.

host all all <client ip address> trust or password

2. Modify the postgresql.conf to change the listen address. This file is also located at <postgresql home>/data directory. Change listen_addresses to listen_addresses = '*'.

On the client machine:

1. Install postgresql on your client machine. Choose to only install User Interface/psql.

2. Add your <Postgresql home>/BIN to your environmental path.

3. Set PGHOST environment variable with the value of your database server IP address. Set PGPORT to the port of the database.

4. At your command line, type the following.

If you skipped step 3:
psql postgresql://<db ip>:<port>/<database name> <username>
OR
psql -U <username> -h <db ip> -p <port>

If you did not skip step 3:
psql -U <username>

If you have installed the postgresql on your desktop using port 5433. You named the database moneyOcean and created a user: greatSea, Of course, you can always go to the program and open the SQL Shell to automatically have the psql started. However, if you really want to start psql from commanc line, you may type the following at your command line.

psql postgresql://localhost:5433/moneyOcean greatSea
OR
psql -U greatSea -p 5433

It will then prompt you for password.

------------------------------------------------------------------------------------------------------------------

SQL: How to list / get / extract the DDL of one or all tables, indexes, views, and functions?

DDL stands for Data Definition Language. It is used to create and modify the structure of database objects such as tables, views and so forth.

Here are the general methods for getting the DDL for tables, indexes, views, and functions.

A. Table

1. For one table:

SELECT DBMS_METADATA.GET_DDL('TABLE', '<table name>', '<schema>') FROM dual;

2. For all tables:

SELECT DBMS_METADATA.GET_DDL ( 'TABLE', TABLE_NAME, '<schema>' ) FROM USER_TABLES;

B. Index

1. For one index

SELECT DBMS_METADATA.GET_DDL('INDEX', '<index name>', '<schema>') FROM dual;

2. For all indexes:

SELECT DBMS_METADATA.GET_DDL ( 'INDEX', INDEX_NAME, '<schema>' ) FROM USER_INDEXES WHERE INDEX_TYPE = 'NORMAL';

C. View

1. For one view:

SELECT DBMS_METADATA.GET_DDL('VIEW', '<view name>', '<schema>') FROM dual;

2. For all views:

SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME, '<schema>') FROM USER_VIEWS;

D. Function

1. For one function:

SELECT DBMS_METADATA.GET_DDL('FUNCTION', '<function name>', '<schema>') FROM dual;

2. For all functions:

  SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME, '<schema>') FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION';

E. DDL for all

  SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER) FROM ALL_OBJECTS WHERE (OWNER = '<schema>') AND OBJECT_TYPE NOT IN('LOB', 'MATERIALIZED VIEW', 'TABLE PARTITION', 'PACKAGE BODY') ORDER BY OBJECT_TYPE, OBJECT_NAME;

-----------------------------------------------------------------------