How to Import from Excel to Oracle with SQL Developer

For our example I’ll be using HR.EMPLOYEES to build the XLS file. I have created a blank copy of the table in another schema and want to import the data from my excel file over.

Step 0: The Empty Oracle Table and your Excel File

You have an Oracle table and you have one or more Excel files.

 Data here but not there!?!

Step 1: Mouse-right click – Import Data

 Yes, it's that easy.

Step 2: Select your input (XLS) file

Yes, we also support XLSX, CSV, etc

Step 3: Verify the data being read

Mind the headers!

Does your Excel file have column headers? Do we want to treat those as a row to the table? Probably not. The default options take care of this. You can also choose to preview more than 100 rows.
Here’s what it looks like if you uncheck the ‘Header’ box

 Sometimes you may want the column headers as a row in the table?

Sometimes your Excel file has multiple headers, or you may need to only import a certain subset of the spreadsheet. Use the ‘Skip Rows’ option to get the right data.

Step 4: Create a script or import automatically 

 Script or do it for me?

For this exercise the ‘Insert’ method will be used.

Step 5:

 Choose the Excel columns to be imported

You may have an Excel file with 100 columns but your table only has 30. This is where you tell SQL Developer what columns are to be used for the import. You can also change up the column order, which may make the next step a bit easier.

Step 6: 

 If you’re not paying attention and just letting the wizard guide you home, then now is the time to wake up. There’s a good chance the column order of the Excel file won’t match the definition of your table. This is where you will tell SQL Developer what columns in the spreadsheet match up to what columns in the Oracle table.

Step 7: Verify your settings

Hit the ‘verify’ button. Fix any mistakes.

SQL Developer is telling you it doesn’t know how to reconcile the data for this DATE column. We need to know what the DATE FORMAT is.

So we need to go back to the Column definition wizard and inspect the HIRE_DATE column settings.

You need to look at how the dates are stored in the spreadsheet and write them in terms that Oracle can understand. This will be used on the INSERTs via a TO_DATE() function that will turn your Excel string into an actual DATE value.
After correcting this, go back to the Verification screen and see if that fixes the problem.

Step 8: 

Everything looks right!

Click on the ‘Finish’ button.

Step 9: Verify the import look at your new table data

  The data is there and the dates look right!

Note the ‘Log’ panel. SQL Developer is processing the records in batches of 50. No errors and the data is there!
 Source: http://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/

VARRAY in Oracle and Java

Inserting a VARRAY Value into an Oracle Table Using a Prepared Statement:

try {
    // Create an oracle.sql.ARRAY object to hold the values
    oracle.sql.ArrayDescriptor arrayDesc =
        oracle.sql.ArrayDescriptor.createDescriptor("number_varray", connection);
    int arrayValues[] = {123, 234};
    oracle.sql.ARRAY array = new oracle.sql.ARRAY(arrayDesc, connection, arrayValues);

    // Create a prepared statement for insertion into varray_table
    PreparedStatement ps =
        connection.prepareStatement("INSERT INTO varray_table VALUES(?)");

    // Set the values to insert
    ((oracle.jdbc.driver.OraclePreparedStatement)ps).setARRAY(1, array);

    // Insert the new row
    ps.execute();
} catch (SQLException e) {
}

Creating a VARRAY Type in an Oracle Database

try {
    Statement stmt = connection.createStatement();

    // Create a 10-element VARRAY of NUMBERs
    stmt.execute("CREATE TYPE number_varray AS VARRAY(10) OF NUMBER(12, 2)");

    // Create a table with a column to hold the new VARRAY type
    stmt.execute("CREATE TABLE VARRAY_TABLE(col_number_array number_varray)");
} catch (SQLException e) {
}

Getting a VARRAY Value from an Oracle Table

try {
    // Create a statement
    Statement stmt = connection.createStatement();

    // Select rows from varray_table
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM varray_table");

    // Get the VARRAY values from each row
    while (resultSet.next()) {
        // Get the VARRAY value in the first column
        oracle.sql.ARRAY array = ((oracle.jdbc.driver.OracleResultSet)resultSet).getARRAY(1);

        // Get the VARRAY elements; values.length is the number of values in the VARRAY
        java.math.BigDecimal[] values = (java.math.BigDecimal[])array.getArray();
    }
} catch (SQLException e) {
}

Inserting a VARRAY Value into an Oracle Table

try {
    // Create a statement
    Statement stmt = connection.createStatement();

    // Insert a row with an array of two values
    stmt.execute ("INSERT INTO varray_table VALUES(number_varray(123, 234))");
} catch (SQLException e) {
} 

Source: http://www.exampledepot.com/

Three Ways To Transpose Rows Into Columns in Oracle SQL

Have you ever been in a situation where you as a developer knows that your data is stored in your table rows, but your customer don’t care…wanting to present the data as columns?In this tutorial I will discuss three different variants on how to transpose table rows into columns. By using SUM + DECODE, the new Oracle 11g PIVOT operator, and by using WITH + sub SELECTs.

Introduction

According to Wikipedia®, one definition of the word “transpose”  is:

A matrix obtained from a given matrix by interchanging each row and the corresponding column.

There are many technical variants on what transposing really is, but in our case it is simply to present row values as columns. This is very helpful if you i.e. want to present data in a crosstab, or matrix. An example can i.e. be that you have your order table where each row has an order date, and you want to present the sales per month (extracted from the order date) in columns. A very common situation in Data Warehouse reporting.
This functionality has existed a long time in Excel (transpose when you use “Paste Special..“), reporting tools like Web Intelligence (Business Objects), etc.
In Oracle it has been possible to mimic this functionality for a long time…but until version 11g, it had not been a thought through functionality.
We are going to look at three of the most common ways to do row to column transposing.

Examples

For our three examples we are going to use the following query as a base:

SQL> SELECT    o.order_mode
  2          ,o.order_status
  3          ,SUM(o.order_total) order_total
  4  FROM    orders o
  5  GROUP BY o.order_mode, o.order_status
  6  ORDER BY o.order_mode
  7          ,o.order_status
  8  /

ORDER_MODE ORDER_STATUS ORDER_TOTAL
---------- ------------ -----------
direct                0    163131.3
direct                1    227569.5
direct                2    166169.5
direct                3    206659.4
direct                4     56352.5
direct                5    172586.2
direct                6      115968
direct                7     33617.1
direct                8    545300.5
direct                9    205674.2
direct               10       10601
online                0     25976.7
online                2    103834.4
online                3     56381.7
online                4    700068.1
online                5    183261.2
online                6     90411.8
online                8    322192.5
online                9     57062.4
online               10    225236.7

20 rows selected

 What we want to do is to present the data like this:
ORDER_MODE     STAT_0     STAT_1     STAT_2     STAT_3     STAT_4     STAT_5     STAT_6     STAT_7     STAT_8     STAT_9    STAT_10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DIRECT       163131.3   227569.5   166169.5   206659.4    56352.5   172586.2     115968    33617.1   545300.5   205674.2      10601
ONLINE        25976.7              103834.4    56381.7   700068.1   183261.2    90411.8              322192.5    57062.4   225236.7

Ok, let’s get down to some business…

Example # 1a: Transpose rows into columns using SUM and DECODE to summarize data

Before version 11g of Oracle this method of transposing data was probably the most proficient. It is actually the exact query that generated the data result shown above.

SQL> SELECT    UPPER(o.order_mode) order_mode
  2          ,SUM(DECODE(o.order_status, 0, o.order_total)) stat_0
  3          ,SUM(DECODE(o.order_status, 1, o.order_total)) stat_1
  4          ,SUM(DECODE(o.order_status, 2, o.order_total)) stat_2
  5          ,SUM(DECODE(o.order_status, 3, o.order_total)) stat_3
  6          ,SUM(DECODE(o.order_status, 4, o.order_total)) stat_4
  7          ,SUM(DECODE(o.order_status, 5, o.order_total)) stat_5
  8          ,SUM(DECODE(o.order_status, 6, o.order_total)) stat_6
  9          ,SUM(DECODE(o.order_status, 7, o.order_total)) stat_7
 10          ,SUM(DECODE(o.order_status, 8, o.order_total)) stat_8
 11          ,SUM(DECODE(o.order_status, 9, o.order_total)) stat_9
 12          ,SUM(DECODE(o.order_status, 10, o.order_total)) stat_10
 13  FROM    orders o
 14  GROUP BY o.order_mode
 15  ORDER BY 1
 16  /

ORDER_MODE     STAT_0     STAT_1     STAT_2     STAT_3     STAT_4     STAT_5     STAT_6     STAT_7     STAT_8     STAT_9    STAT_10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DIRECT       163131.3   227569.5   166169.5   206659.4    56352.5   172586.2     115968    33617.1   545300.5   205674.2      10601
ONLINE        25976.7              103834.4    56381.7   700068.1   183261.2    90411.8              322192.5    57062.4   225236.7

As you probably see, for each of the eleven possible order_status values, there is a “stat_x” line. If we had twelve values…well…then it would have been twelve “stat_x” fields. This is actually one of the situations where I find it easier to use a DECODE function, rather than CASE. If you want to know about the difference between CASE, and DECODE – please take a look at the post named “Oracle DECODE and CASE: What is the difference“.

Example # 1b: Transpose rows into columns using MAX and DECODE – for non-summarized data

I want to show another variant of this (above) query as well.
Let us pretend we are going to help our MLM (Multi Level Marketing) company with calculating commission for our distributors, and we want to calculate everything using three different rate scenarios to be able to measure the commission impact against each other. And, we just want to show all the rates in a nice way, grouped by each scenario.
Ok, this is the data we start out with:

SQL> col scenario format a10
SQL> SELECT    r.scenario
  2          ,r.currency
  3          ,r.rate
  4  FROM    rates r
  5  /

  SCENARIO CURRENCY       RATE
---------- -------- ----------
         1 USD            1.00
         1 CAD            1.45
         1 MYR            3.34
         1 NOK            6.08
         2 USD            1.00
         2 CAD            1.47
         2 MYR            3.47
         2 NOK            6.12
         3 USD            1.00
         3 CAD            1.51
         3 MYR            2.98
         3 NOK            5.96

12 rows selected

 To show a nice crosstab version of our data, the query below would do the trick:

SQL> SELECT    x.scenario
  2          ,MAX(DECODE(x.currency, 'CAD', x.rate)) CAD_rate
  3          ,MAX(DECODE(x.currency, 'MYR', x.rate)) MYR_rate
  4          ,MAX(DECODE(x.currency, 'NOK', x.rate)) NOK_rate
  5          ,MAX(DECODE(x.currency, 'USD', x.rate)) USD_rate
  6  FROM    (
  7              SELECT    r.scenario
  8                      ,r.currency
  9                      ,r.rate
 10              FROM    rates r
 11          ) x
 12  GROUP BY x.scenario
 13  /

  SCENARIO   CAD_RATE   MYR_RATE   NOK_RATE   USD_RATE
---------- ---------- ---------- ---------- ----------
         1       1.45       3.34       6.08          1
         2       1.47       3.47       6.12          1
         3       1.51       2.98       5.96          1

BTW – The rates shown are totally random…

Example # 2:  Transpose rows into columns using the Oracle PIVOT operator

The PIVOT and the UNPIVOT operators were introduced in Oracle version 11g. The following query will give the same result as the query above, just by using the PIVOT operator.

SQL> SELECT    UPPER(order_mode)
  2          ,stat_0
  3          ,stat_1
  4          ,stat_2
  5          ,stat_3
  6          ,stat_4
  7          ,stat_5
  8          ,stat_6
  9          ,stat_7
 10          ,stat_8
 11          ,stat_9
 12          ,stat_10
 13  FROM    (
 14              SELECT    o.order_mode
 15                      ,o.order_status
 16                      ,SUM(o.order_total) order_total
 17              FROM    orders o
 18              GROUP BY o.order_mode, o.order_status
 19          )
 20  PIVOT    (
 21              SUM(order_total) FOR order_status IN (0 stat_0, 1 stat_1, 2 stat_2, 3 stat_3, 4 stat_4, 5 stat_5, 6 stat_6, 7 stat_7, 8 stat_8, 9 stat_9, 10 stat_10)
 22  )
 23  /

UPPER(ORDER_MODE)     STAT_0     STAT_1     STAT_2     STAT_3     STAT_4     STAT_5     STAT_6     STAT_7     STAT_8     STAT_9    STAT_10
----------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DIRECT              163131.3   227569.5   166169.5   206659.4    56352.5   172586.2     115968    33617.1   545300.5   205674.2      10601
ONLINE               25976.7              103834.4    56381.7   700068.1   183261.2    90411.8              322192.5    57062.4   225236.7

Example # 3: Transpose rows into columns using WITH and sub SELECTS

Ok, in this example we are going to put our main query inside a WITH statement, and then query the WITH materialized resultset multiple times. Using WITH, the main resultset wil not be recomputed multiple times.

 SQL> WITH main_query AS (
  2                          SELECT    o.order_mode
  3                                  ,o.order_status
  4                                  ,SUM(o.order_total) order_total
  5                          FROM    orders o
  6                          GROUP BY o.order_mode, o.order_status
  7  )
  8  SELECT    UPPER(x.order_mode) order_mode
  9          ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 0 ) stat_0
 10          ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 1 ) stat_1
 11          ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 2 ) stat_2
 12          ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 3 ) stat_3
 13          ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 4 ) stat_4
 14          ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 5 ) stat_5
 15          ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 6 ) stat_6
 16          ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 7 ) stat_7
 17          ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 8 ) stat_8
 18          ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 9 ) stat_9
 19          ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 10 ) stat_10
 20  FROM    (
 21              SELECT    DISTINCT m.order_mode
 22              FROM    main_query m
 23          ) x
 24  /

ORDER_MODE     STAT_0     STAT_1     STAT_2     STAT_3     STAT_4     STAT_5     STAT_6     STAT_7     STAT_8     STAT_9    STAT_10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DIRECT       163131.3   227569.5   166169.5   206659.4    56352.5   172586.2     115968    33617.1   545300.5   205674.2      10601
ONLINE        25976.7              103834.4    56381.7   700068.1   183261.2    90411.8              322192.5    57062.4   225236.7

Well, there you have it. Three different ways to get the same data.

Other ways to solve the problem

Although only three different methods are presented here, there are actually other ways to transpose the data from rows to columns. One of them is to use a WITH statement, and then multiple UNIONs with a SUM of all columns at the end. I will let it be up to you to try this one out. If you really would like to know how to do this, leave a comment below, and I will give you a sample. It is a more cumbersome (reason for not initially showing a sample) way to do the transposing this way.
Yet another possibility, after my understanding, is to use XML in your query. I will give a sample on our PL/SQL site for this one.

Conclusion

As  you can see, there are a few ways to to transpose rows into columns in Oracle. If you have a different..or even better way to do this…we will all love to hear about it. The comment field is made just for you.
BTW – If you wonder about the data used in the examples, they are from the “OE” sample schema that comes with Oracle. It is normally locked and expired by default.

Hope this help you out in your work,

 Source: http://oracletuts.net/tutorials/three-ways-to-transpose-rows-into-columns-in-oracle-sql/

GZIp Compression for JEE Web Application

GZip is based on the DEFLATE algorithm, which is a combination of LZ77 and Huffman coding. gzip is often also used to refer to the gzip file format, which is:

1. a 10-byte header, containing a magic number, a version number and a time stamp. 2. optional extra headers, such as the original file name.
3. a body, containing a DEFLATE-compressed payload. 4. an 8-byte footer, containing a CRC-32 checksum and the length of the original uncompressed data.
Although its file format also allows for multiple such streams to be concatenated (zipped files are simply decompressed concatenated as if they were originally one file), gzip is normally used to compress just single files . Compressed archives are typically created by assembling collections of files into a single tar archive, and then compressing that archive with gzip. The final .tar.gz or .tgz file is usually called a tarball.
Most of the FE view generation time is tied up in downgrading all the components in the page: like html, images, stylesheets, scripts, ActiveX components and static resources.
Using gzip compression technique we can drastically reduce the size (almost 1/10th of original response content size ,as per spec) of the resources which takes less time, bandwidth to traverse through the wire interns increase the view generation performance.
It Also it provides transport layer encoding.




Steps to Implement

1. Write a GZipFilter through which all request/response should pass to/from Web app Servlet.
2. In filter Check if the requester (For web app its browser) can support gzip encoding or not. by checking "Accept-Encoding" header value of request. This check is necessary because if the requester client does not support gzip encoding then normal operation should be performed without encoding.
4, If client supposts gzip encoding set a response header "Content-Encoding" with value "gzip". It's needed for browser to understand about the meta-data of coming response content.
3. If it's supports gzip encoding set the object of GZipOutputStream in HTTPServletResponseweb app servlet.
4. Web app will write the response byte Stream in GZipOutputStream instead of simple OutputStream and browser will take care of the rest.

Source: http://hirenscafe.blogspot.com/2010/02/gzip-compression-for-web-application.html

JSF Application Tuning (WebApp performance)

SF requires two paths: one to create the tree and one to render it.One of the areas of JSF that We think suffers compared to other models hat it requires two paths instead of one. In JSP,code is compiled directly into Java byte code. That code is then directly executed when the servlet is accessed. As a result, a single path is executed to render the content to the output stream. In JSF, first a component tree is created and renders associated with it. Once that tree is constructed and JSF enters the render phase, the tree is walked completely and content is written to the output stream. Thus, JSF requires two paths: one to create the tree and one to render it.
On plus side it's a cleaner MVC architecture for performing validation, actions, and rendering.

Below are some analysis, suggestions for JSF application settings optimized JSF performance.

In JSF, when the FacesServlet is accessed, it asks its associated view handler implementation to restore or build a view or component tree.. As components are stateful, a new instance must be created per request. Thus, for a very large page with several components, it requires invoking several tag handler classes that end up retrieving the application to get the component which then creates the component by using reflection on the associated class type. This has two impacts to performance.
First, you have to create several components for a given page and each of those invocations require memory allocation, initialization, referencing, setting attributes, etc…very expensive tasks. This also impacts garbage collection by creating excess garbage per request.
Second, it has to use reflection to create the classes and reflection is generally slower than directly invoking the new operator.

Instead of keeping all component as Stateful we could mark a panel group as stateless.
@Stateless
public class UIPanel extends UIComponentBase {
// normal code
}For an input component, you might mark all input components as stateful.
@Stateful
public class UIInput extends UIOutput {
// normal code
}

Remove Serialization Overhead:
Performance measurements have shown that plain server side state saving (without serialization and without compressing state) comes with the best values. State saving at client side is having security concerns as well as overhead of serialization of entire JSF tree every time.

Web app deployment descriptor entry:
<context-param>
<param-name>javax.faces.STATE_SAVING_METHOD</param-name>
<param-value>server</param-value>
</context-param>


Reduce Session overhead:

javax.faces.ViewState is a hidden field in JSF which gets auto generated when the page is deployed as a web application.JSF keeps lots of information about the current page and previous pages in the JSF state object. This allows all of the Faces components to keep track of their state, and also allows the back button to work. Server side state saving is where the component tree and all component state are stored within the user's session. This entry within the session is tracked by writing a key in the response that is used to look up the entry on subsequent post-backs. By default the value of holding state in a single session is 15.

So, If the view is 5K big, in a single session its holding almost 5MB data.For 1000 concurrent user its almost 4.8GB data we are storing into session (Application memory) is unnecessary. It'll give out of memory for sure as 32 bit Application Server (most common open source servers) can only support max 2GB of heap size (for 64 bit it supports max 4GB heap size).

The numberOfViewsInSession parameter is the big one – this only allows three back buttons inside a faces form.Value shoud be given as per application requirement.

Web app deployment descriptor entries: (Optimize values of following parameters can vary according to application requirement)
<context-param>
<param-name>com.sun.faces.numberOfViewsInSession</param-name>
<param-value>3</param-value>
</context-param>

<context-param>
<param-name>com.sun.faces.numberOfLogicalViews</param-name>
<param-value>10</param-value>
</context-param>

Source:  hirenscafe.blogspot.com/2010/03/jsf-application-tuning-webapp.html