Useful Oracle SQL Info

     1.Trim Example

SELECT TRIM (' 7'),

       TRIM (' 777 77  '),

       TRIM (' ' FROM '  7 7 '),

       TRIM (LEADING '0' FROM '000123'),

       TRIM (TRAILING '1' FROM 'Tech1'),

       TRIM (BOTH '1' FROM '123Tech111')

  FROM DUAL

        2. Sql to add seconds, minutes, hours to current date

SELECT SYSDATE,

       SYSDATE + (1 / (24 * 60 * 60)) "1Sec Addition",

       sysdate + interval '1' second,

       SYSDATE + (1 / (24 * 60)) "1Min Addition",

       sysdate + interval '1' minute,

       SYSDATE + (1 / 24) "1HR Addition",

       sysdate + interval '1' hour

  FROM DUAL

1 3.Converting string into rows

with t as (select 'abcd,123,defoifcd,87765' as str from dual)

    select level as n, regexp_substr(str,'[^,]+',1,level) as val

    from   t

   connect by regexp_substr(str,'[^,]+',1,level) is not null

    4.Converting string into columns

with t as (select 'abcd,123,defoifcd,87765' as str from dual)

    select max(decode(level,1,regexp_substr(str,'[^,]+',1,level))) as val1

          ,max(decode(level,2,regexp_substr(str,'[^,]+',1,level))) as val2

          ,max(decode(level,3,regexp_substr(str,'[^,]+',1,level))) as val3

          ,max(decode(level,4,regexp_substr(str,'[^,]+',1,level))) as val4

          ,max(decode(level,5,regexp_substr(str,'[^,]+',1,level))) as val5

   from   t

  connect by regexp_substr(str,'[^,]+',1,level) is not null

    5.Listagg Test

create table dept_test

(

  dno       number,

  dname     varchar2(100 byte),

  location  varchar2(100 byte)

)

set define off;

insert into dept_test   (dno, dname, location) values   (1, 'Accounts', 'NY');

insert into dept_test   (dno, dname, location) values   (2, 'Delivery', 'NJ');

insert into dept_test   (dno, dname, location) values   (3, 'Customer Support', 'NJ');

insert into dept_test   (dno, dname, location) values   (4, 'Billing', 'NY');

insert into dept_test   (dno, dname, location) values   (5, 'Payments', 'NY');

commit;

select * from  dept_test

select location, listagg (dname,',') within group (order by dname) dept_list

from  dept_test

group by location

    6. Notepad++ add quotes at begin & end of each line in a file

7.Convert String with comma separated values into Columns Using SQL

1.       select * from  test

select  c1, trim(regexp_substr(c2,'[^,]+',1,level)) c2

from   test

connect by regexp_substr(c2,'[^,]+',1,level) is not null

and c2 = prior c2

and c1 = prior c1

and prior sys_guid() is not null

order by c1

Netezza JDBC Program

Netezza JDBC Program

Steps to connect to Netezza Database using Java (JDBC) program

Download the Netezza driver nzjdbc.jar

Connect to Eclipse and upload the nzjdbc.jar using "Build Path" as shown in below images

Java Class to connect to Netezza Database and read table data

---------------------------------------------------
package sample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcNetezzaConnection {

    public static void main(String[] args) {

        Connection conn1 = null;
        Statement statement = null;

        try {
            Class.forName("org.netezza.Driver");
            conn1 = DriverManager.getConnection("jdbc:netezza://servername/databasename", "username","password");
            if (conn1 != null) {
                System.out.println("Connected with connection #1");
            }
         // Create the statement to be used to get the results.
            statement = conn1.createStatement();
            // Create a query to use.
            String query = "select tablename, owner from _v_table where owner = 'ADMIN' limit 10";
            ResultSet resultSet = statement.executeQuery(query);

            System.out.println("Printing result...");
            while (resultSet.next()) {
             String tn = resultSet.getString("tablename");
             String opg = resultSet.getString("owner");
             System.out.println("\tablename: " + tn + ",  owner: " + opg );
         }

        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (conn1 != null && !conn1.isClosed()) {
                    conn1.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

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

Output of the above class

Output from Netezza Database

Redshift JDBC Program

Redshift JDBC Program

Steps to connect Redshift database using Java JDBC program

Download the driver from

http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html

JDBC 4.1–compatible driver: https://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC41-1.1.10.1010.jar

The class name for this driver is com.amazon.redshift.jdbc41.Driver.

Connect to Eclipse, upload the Redshift JDBC driver using "Build Path" -> Add External Archieves  as shown like in below images

The following is the Java class to read data from Redshift table

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

package sample;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class JdbcRedshiftConnection {

    public static void main(String[] args) {

        Connection conn1 = null;

        Statement statement = null;

        try {

            Class.forName("com.amazon.redshift.jdbc41.Driver");

            conn1 = DriverManager.getConnection("jdbc:redshift://servername:port/databasename", "username","password");

            if (conn1 != null) {

                System.out.println("Connected with connection #1");

            }

         // Create the statement to be used to get the results.

            statement = conn1.createStatement();

            // Create a query to use.

            String query = "select schemaname,  tablename from  pg_table_def  limit 10";

            ResultSet resultSet = statement.executeQuery(query);

            System.out.println("Printing result...");

            while (resultSet.next()) {

             String tn = resultSet.getString("schemaname");

             String opg = resultSet.getString("tablename");

             System.out.println("\t schemaname: " + tn + ",  tablename: " + opg );

         }

        } catch (ClassNotFoundException ex) {

            ex.printStackTrace();

        } catch (SQLException ex) {

            ex.printStackTrace();

        } finally {

            try {

                if (conn1 != null && !conn1.isClosed()) {

                    conn1.close();

                }

            } catch (SQLException ex) {

                ex.printStackTrace();

            }

        }

    }

}

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

Sample output from the above Java Class

Sample output from Redshift Database

Oracle JDBC Program

Oracle JDBC Program

Steps to connect to Oracle Database using Java (JDBC)

Download the Oracle JDBC driver ojdbc6.jar  from

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

Go to eclipse and

Choose “ojdbc6.jar” and upload the file using above.

Create a java class as follows

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

package sample;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class JdbcOracleConnection {

    public static void main(String[] args) {

        Connection conn1 = null;

        Statement statement = null;

        try {

            Class.forName("oracle.jdbc.OracleDriver");

           String dbURL1 = "jdbc:oracle:thin:username/password@localhost:1521:XE";

            conn1 = DriverManager.getConnection(dbURL1);

            if (conn1 != null) {

                System.out.println("Connected with connection #1");

            }

            statement = conn1.createStatement();

            // Create a query to use.

            String query = "select table_name, owner  from dba_tables where rownum <=10";

            ResultSet resultSet = statement.executeQuery(query);

            System.out.println("Printing result...");

            while (resultSet.next()) {

             String tn = resultSet.getString("table_name");

             String opg = resultSet.getString("owner");

             System.out.println("\ttable_name: " + tn +",  owner: " + opg );

         }

        } catch (ClassNotFoundException ex) {

            ex.printStackTrace();

        } catch (SQLException ex) {

            ex.printStackTrace();

        } finally {

            try {

                if (conn1 != null && !conn1.isClosed()) {

                    conn1.close();

                }

            } catch (SQLException ex) {

                ex.printStackTrace();

            }

        }

    }

}

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

The sample output of class JdbcOracleConnection is as follows