Passing ARRAY structure from Java to Oracle.

Hi,

So let’s say you have atleast 20000 records you need to Insert onto your Database.

First thing comes in mind is “let’s try Insertion one by one”.

after a while you get the idea that it wasnt good idea at all.  Since you wait and wait and wait…

Effective solution would be using ARRAY structure.

Prepare ARRAY in your java code with all your contents and have a procedure in the Oracle side which recieves the ARRAY.

Minutes of waiting will turn to seconds.

 

* Note: If You setting your procedure inside a Package you must set the ARRAY_TYPE outside of it(at the schema level). Else Java wont be able to bind it.

 

Oracle side:

 

declare array type :

CREATE OR REPLACETYPE SCHEME_NAME.ARRAY_TYPE AS TABLE OF VARCHAR2(20);




 PROCEDURE PROC_NAME(v_tic_array IN ARRAY_TYPE)
   IS
    BEGIN

        FOR i IN v_tic_array.first .. v_tic_array.last LOOP
        --some logic.. for example Insertion
            INSERT INTO TABLE1
                      VALUES (v_tic_array(i));
        END LOOP;

        END PROC_NAME
Java side:

public void someName (String[] arrayName) throws Exception
{
 Connection conn = null;
 Statement stmt = null;

  try
  {
     conn = conPool.getConnection();
     ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("ARRAY_TYPE", conn);

			OraclePreparedStatement ps = (OraclePreparedStatement) conn
					.prepareStatement("{call PROC_NAME(?)}")
			ARRAY myARRAY = new ARRAY(descriptor, conn, arrayName);
			ps.setARRAY(1, myARRAY);
			ps.execute();
		}

		catch (SQLException e)
		{

			logger.error("Write your error");
		}
		finally
		{
			closeResources(conn, stmt, null);
		}

	}
}

 

[ad]

Related Articles:

(1114)

4 reasons why not to use DBLINKS

One of the big problem which I am facing every day in my organization  is the use of DBLINKS.

1. Slowing incredibly your system.

2. You cant send complex data structures(e.g Cursers)

3. You cant use regular DBLINK in XA transactions (An alternative solution:  Shared DBLINK)

4. If the remote database went down it doesn’t kill the dblink session automatically.

 

So try to work straight forward your Data source if possible . You might avoid many problems in the future.

[ad]

Related Articles:

(1114)