Skip to content
accelerando

Category Archives: Oracle

Modify memory settings of the Oracle Aurora JVM

07-Feb-14

Use the following class to view and modify the settings of the embedded JVM inside an Oracle database. The class has been tested on 10g and 11g, memory settings and information are in Megabyte.

SET DEFINE OFF
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "MemoryStatistics" AS
import oracle.aurora.vm.OracleRuntime;
 
public class MemoryStatistics {
    public final static INT MB_MULTIPLICATOR = 1024*1024;
 
	public static String getStatistics() {
		final Runtime runtime = Runtime.getRuntime();
 
		final long totalMemory = runtime.totalMemory();
		final long maxMemory = runtime.maxMemory();
		final long usedMemory = totalMemory - runtime.freeMemory();
 
        RETURN NEW StringBuffer()
        	.append(usedMemory/MB_MULTIPLICATOR)
        	.append(" / ")
        	.append(totalMemory/MB_MULTIPLICATOR)
        	.append(" MB (used / total), Maximum ")
        	.append(OracleRuntime.getMaxMemorySize()/MB_MULTIPLICATOR)
        	.append(", running on ")
        	.append(System.getProperty("java.version"))
        	.toString();
	}
 
    public static long getMaxMemorySize() {
        RETURN OracleRuntime.getMaxMemorySize() / MB_MULTIPLICATOR;
    }
 
    public static void setMaxMemorySize(final long maxMemorySize) {
        OracleRuntime.setMaxMemorySize(maxMemorySize*MB_MULTIPLICATOR);
    }
}
/
 
SHO ERR;
 
CREATE OR REPLACE PROCEDURE p_set_max_memory_size(p_max_memory_size IN NUMBER) IS LANGUAGE JAVA
    NAME 'MemoryStatistics.setMaxMemorySize(long)';
/
 
CREATE OR REPLACE FUNCTION f_get_max_memory_size RETURN NUMBER IS LANGUAGE JAVA
    NAME 'MemoryStatistics.getMaxMemorySize() return java.lang.Long';
/
 
CREATE OR REPLACE FUNCTION f_get_memory_statistics RETURN VARCHAR2 IS LANGUAGE JAVA
    NAME 'MemoryStatistics.getStatistics() return java.lang.String';
/
 
SELECT f_get_max_memory_size() FROM dual;
 
SELECT f_get_memory_statistics() FROM dual;
 
BEGIN
    p_set_max_memory_size(512);
END;
/
 
SELECT f_get_memory_statistics() FROM dual;

The memory settings is persistent across sessions, at least with my current setup.

Pretty print XML data within Oracle Databases

05-Jun-13

Just a short post without further explanation.

Pretty printing xml in Oracle 10g and 11g differences quite a bit:

10g:

SELECT XMLTYPE('<html><body><p>Hallo, Welt.</p></body></html>').EXTRACT('/') FROM dual;

11g:

SELECT XMLSERIALIZE(Document XMLTYPE('<html><body><p>Hallo, Welt.</p></body></html>') AS CLOB INDENT SIZE = 2) FROM dual;

Java implementation of Excels statistical functions NORMINV

21-Feb-13

I was recently in need of a java implementation of the inverted normal distribution function, named “NORMINV” in Excel 2003 and later: NORMINV.

I only found a JavaScript implementation in kmpm’s repository norminv.js. It has quite a history, coming over C# from C++. I have the impression nobody wants to deal with the mathematics ;)

So here is my Java implementation of NORMINV. As an added bonus it implements org.apache.poi.ss.formula.functions.FreeRefFunction to be used as a User defined function in Apache POI:

import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
 
/**
 * Java Implementation of http://support.microsoft.com/kb/827358/en-us implementing 
 * Apache POIs {@link FreeRefFunction} to be used as a user defined function.
 * 
 * Mathematics found here: 
 * https://gist.github.com/kmpm/1211922/
 * https://gist.github.com/kmpm/1211922/raw/a11e0dfc9fab493bcdadc669f3213d11f1897ebf/norminv.js
 * 
 * Register for a given workbook with:
 * 
    <code>
		final UDFFinder udfs = new DefaultUDFFinder(new String[]{ "MS_NormInv" }, new FreeRefFunction[]{ new NormInv() }) ;        
		workbook.addToolPack(new AggregatingUDFFinder(new UDFFinder[] {udfs}));
    </code>
 * @author michael.simons
 */
public class NormInv implements FreeRefFunction {
	public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {		
		try {
			final ValueEval p = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex()) ;
			final ValueEval mu = OperandResolver.getSingleValue(args[1], ec.getRowIndex(), ec.getColumnIndex()) ;
			final ValueEval sigma = OperandResolver.getSingleValue(args[2], ec.getRowIndex(), ec.getColumnIndex()) ;			
			return new NumberEval(this.compute(OperandResolver.coerceValueToDouble(p), OperandResolver.coerceValueToDouble(mu), OperandResolver.coerceValueToDouble(sigma)));
		} catch (EvaluationException e) {
			return e.getErrorEval();
		}			 		
	}
 
	/**
	 * Original C++ implementation found at http://www.wilmott.com/messageview.cfm?catid=10&threadid=38771
	 * C# implementation found at http://weblogs.asp.net/esanchez/archive/2010/07/29/a-quick-and-dirty-implementation-of-excel-norminv-function-in-c.aspx
	 *    Compute the quantile function for the normal distribution.
	 *
	 *    For small to moderate probabilities, algorithm referenced
	 *    below is used to obtain an initial approximation which is
	 *    polished with a final Newton step.
	 *    For very large arguments, an algorithm of Wichura is used.
	 *
	 *  REFERENCE
	 *
	 *    Beasley, J. D. and S. G. Springer (1977).
	 *    Algorithm AS 111: The percentage points of the normal distribution,
	 *    Applied Statistics, 26, 118-121.
	 *
	 *     Wichura, M.J. (1988).
	 *     Algorithm AS 241: The Percentage Points of the Normal Distribution.
	 *     Applied Statistics, 37, 477-484.
	 * @param p
	 * @param mu
	 * @param sigma
	 * @return
	 */
	public double compute(double p, double mu, double sigma) {
		if(p < 0 || p > 1) 
			throw new RuntimeException("The probality p must be bigger than 0 and smaller than 1");		
		if(sigma < 0)
			throw new RuntimeException("The standard deviation sigma must be positive");
		if(p == 0)
			return Double.NEGATIVE_INFINITY;		
		if(p == 1)
			return Double.POSITIVE_INFINITY;		
		if(sigma == 0)
			return mu;		
		double  q, r, val;
 
		q = p - 0.5;
 
		/* 0.075 <= p <= 0.925 */
		if(Math.abs(q) <= .425) {
			r = .180625 - q * q;
			val =
		         q * (((((((r * 2509.0809287301226727 +
		                    33430.575583588128105) * r + 67265.770927008700853) * r +
		                  45921.953931549871457) * r + 13731.693765509461125) * r +
		                1971.5909503065514427) * r + 133.14166789178437745) * r +
		              3.387132872796366608)
		         / (((((((r * 5226.495278852854561 +
		                  28729.085735721942674) * r + 39307.89580009271061) * r +
		                21213.794301586595867) * r + 5394.1960214247511077) * r +
		              687.1870074920579083) * r + 42.313330701600911252) * r + 1);
		}
		/* closer than 0.075 from {0,1} boundary */
		else {
		     /* r = min(p, 1-p) < 0.075 */
			 if (q > 0) {
				 r = 1 - p;
			 } else {
				 r = p;
			 }
 
			 r = Math.sqrt(-Math.log(r));
			 /* r = sqrt(-log(r))  < ==>  min(p, 1-p) = exp( - r^2 ) */
 
			 if (r < = 5) { /* <==> min(p,1-p) >= exp(-25) ~= 1.3888e-11 */
				 r += -1.6;
				 val = (((((((r * 7.7454501427834140764e-4 +
		                     .0227238449892691845833) * r + .24178072517745061177) *
		                   r + 1.27045825245236838258) * r +
		                  3.64784832476320460504) * r + 5.7694972214606914055) *
		                r + 4.6303378461565452959) * r +
		               1.42343711074968357734)
		              / (((((((r *
		                       1.05075007164441684324e-9 + 5.475938084995344946e-4) *
		                      r + .0151986665636164571966) * r +
		                     .14810397642748007459) * r + .68976733498510000455) *
		                   r + 1.6763848301838038494) * r +
		                  2.05319162663775882187) * r + 1);
			 } else { /* very close to  0 or 1 */
				 r += -5;
				 val = (((((((r * 2.01033439929228813265e-7 +
		                     2.71155556874348757815e-5) * r +
		                    .0012426609473880784386) * r + .026532189526576123093) *
		                  r + .29656057182850489123) * r +
		                 1.7848265399172913358) * r + 5.4637849111641143699) *
		               r + 6.6579046435011037772)
		              / (((((((r *
		                       2.04426310338993978564e-15 + 1.4215117583164458887e-7) *
		                      r + 1.8463183175100546818e-5) * r +
		                     7.868691311456132591e-4) * r + .0148753612908506148525)
		                   * r + .13692988092273580531) * r +
		                  .59983220655588793769) * r + 1);
		      }
 
		      if (q < 0.0) {
		          val = -val;
		      }
		  }
 
		  return mu + sigma * val;		
	}
}

More…

How to retrieve tables of custom object types with JDBC

24-Jul-12

The following is not only a personal reminder for me but also a first try to answer some questions on Stack Overflow

You can create custom object types in an Oracle database like so:

CREATE TYPE t_demo_object AS OBJECT (
  some_number  NUMBER,
  some_string  varchar2(32)
)
/

Those types can have member functions, custom constructors and some pretty neat features inside oracle.

There also exists tables of them like so

CREATE TYPE t_demo_objects IS TABLE OF t_demo_object
/

They can be used to do stuff like pipelined functions.

Often those types are used as results of stored functions and procedures, probably like so:

CREATE OR REPLACE PROCEDURE p_generate_demo_objects(p_num IN NUMBER, p_data OUT t_demo_objects) AS 
BEGIN
	p_data := t_demo_objects();
	p_data.extend(p_num);
	FOR i IN 1..p_num LOOP
		p_data(i) := t_demo_object(i, 'Index ' || i);
	END LOOP;
END p_generate_demo_objects;

As long as this is used only within the database, everything is fine with Oracle Object Types… But often a procedure like this will be called from the outside through jdbc.

How to retrieve this object type? As an java.sql.Array of java.sql.Structs.

I’m doing it like so with minimal dependencies on the Oracle JDBC driver:

package tests.jdbc;
 
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.Struct;
import java.sql.Types;
 
import oracle.sql.StructDescriptor;
 
public class OracleTableOfResult {
	public static void main(String...a) throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		Connection connection = DriverManager.getConnection("jdbc:oracle:thin:<USER>/<PASS>@<DATABASEHOST>:1521:<SERVICE>");
 
		final String typeName = "T_DEMO_OBJECT";
		final String typeTableName = "T_DEMO_OBJECTS";
 
		// Get a description of your type (Oracle specific)
		final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection);		
		final ResultSetMetaData metaData = structDescriptor.getMetaData();
 
		// Call the procedure (or whatever else) that returns the table of a custom type
		CallableStatement cs = connection.prepareCall("{call p_generate_demo_objects(?, ?)}");
		cs.setInt(1, 5);
		// Result is an java.sql.Array...
		cs.registerOutParameter(2, Types.ARRAY, typeTableName);		
		cs.execute();
 
		// ...who's elements are java.sql.Structs
		Object[] data = (Object[]) ((Array) cs.getObject(2)).getArray();
		for(Object tmp : data) {
			Struct row = (Struct) tmp;
			// Attributes are index 1 based...
			int idx = 1;
			for(Object attribute : row.getAttributes()) {				
				System.out.println(metaData.getColumnName(idx) + " = " + attribute);											
				++idx;
			}
			System.out.println("---");
		}
		cs.close();		
		connection.close();
	}
}

In the original question is also room for improving the pl/sql procedure.

You can fill the resulting object procedural with a loop but you can also use a “bulk collect into”:

SELECT t_demo_object(colum_a, column_b) bulk collect INTO p_data FROM some_table;

which should be considerable faster on large datasets.

Note that it is not possible to use package level array types of record types in JDBC:

Oracle JDBC does not support RAW, DATE, and PL/SQL RECORD as element types.

Accessing PL/SQL Index-by Tables

Sadly, we have a legacy application that makes extensive use of such types… We had to rewrite most of the procedures.

Oracle, JBDC, CallableStatements and named parameters

23-Jul-12

Update: Please take a note of comment #5 by DSurber. He works for Oracle and sheds some light on named parameters. In short: The names of the parameter do not refer to named placeholders in statement (like in JPA for example) but to PL/SQL named parameters!. Thank you very much.

You might have wondered what happened here:

Well easy, i had fun with the Oracle JDBC Driver and named parameters. I modified one call, added a parameter and set this parameter not in order… No problem, i thought, as i was using a callable statement with named parameter. As i it turns out, named parameters (“:blah”) are not so named, after all, but the order of setting them does matter, see the following tests:

package tests.jdbc;
 
import java.sql.*;
 
import org.junit.*;
 
public class OracleCallableStatementTest {
	protected static Connection connection;
 
	@BeforeClass
	public static void setup() throws Exception {	
		try{
			Class.forName("oracle.jdbc.OracleDriver");
			connection = DriverManager.getConnection("jdbc:oracle:thin:<USER>/<PASS>@<DATABASEHOST>:1521:<SERVICE>");
			PreparedStatement ps = connection.prepareStatement("Create table callable_statement_test(column1 varchar(16), column2 varchar(16))");
			ps.executeUpdate();
			ps.close();
			ps = connection.prepareStatement(
					"CREATE OR REPLACE procedure p_callable_test (p1 IN VARCHAR, p2  IN  VARCHAR) AS " + 
							"BEGIN " + 
							"  INSERT INTO callable_statement_test(column1, column2) values(p1, p2); " + 
							"END;"
					);
			ps.executeUpdate();
			ps.close();
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		}		
	}
 
	@Before
	public void prepareTable() throws SQLException {
		Statement st = connection.createStatement();
		st.executeUpdate("Truncate table callable_statement_test");
		st.close();
	}
 
	private void verify() throws SQLException {
		final Statement st = connection.createStatement();
		ResultSet rs = st.executeQuery("Select * from callable_statement_test");
		rs.next();
 
		String column1 = rs.getString("column1");
		String column2 = rs.getString("column2");
 
		rs.close();
		st.close();		
 
		Assert.assertEquals("1", column1);
		Assert.assertEquals("2", column2);		
	}
 
	@Test
	public void testCallableStatementParameterInOrder() throws SQLException {		
		final CallableStatement ps = connection.prepareCall("{Call p_callable_test(:p1, :p2)}");		
		ps.setString("p1", "1");	
		ps.setString("p2", "2");					
		ps.execute();
		ps.close();
 
		verify();
	}	
 
	@Test
	public void testCallableStatementParameterNoOrder() throws SQLException {
		final CallableStatement ps = connection.prepareCall("{Call p_callable_test(:p1, :p2)}");
		ps.setString("p2", "2");	
		ps.setString("p1", "1");							
		ps.execute();
		ps.close();		
 
		verify();
	}	
 
	@AfterClass
	public static void tearDown() throws SQLException {
		if(connection == null)
			throw new RuntimeException("No connection...");
 
		PreparedStatement ps = connection.prepareStatement("Drop procedure p_callable_test");
		ps.executeUpdate();
		ps.close();
 
		ps = connection.prepareStatement("Drop table callable_statement_test");
		ps.executeUpdate();
 
		connection.close();
	}
}

In this test i actually used the same names of parameters like in the stored procedure and you clearly see p1 should be inserted in column1 and p2 in column2.

#testCallableStatementParameterInOrder succeeds, but #testCallableStatementParameterNoOrder fails. The only difference is that i set p2 before p1… To me, this is a severe bug as named parameters imply a very different behavior.

I strongly advice my colleagues to be very careful about using plain named parameters. The order in which they are set is important!

I could reproduce this behavior with OJDBC 11.1.0.7 and 11.2.0.3 against Oracle 10g and 11g. The example does not work at all with OJDBC 10.2.x.

Close
E-mail It