Tag Archives: oracle

Common pitfalls installing Oracle XE 11 under Oracle Linux 6

First thing to check is the hostname inside /etc/hosts to ensure the install process can find the net listener. During the installation of Oracle Linux you set the hostname for example to oracle-vm.localdomain. Open with nano /etc/hosts and keep sure you have a line like this.

127.0.0.1 oracle-vm.localdomain localhost localhost.localdomain localhost4 localhost4.localdomain4

Next thing is to keep sure you have shared memory configured. Enter df -k and see if a line starts with shmfs. If not then enter

mount -t tmpfs shmfs -o size=2048m /dev/shm

and change your fstab to have shared memory available after next boot:

shmfs /dev/shm tmpfs size=2048m 0 0

Oracle XE for Linux is only available for X64 systems. Enter uname -a and see if it ends with

x86_64 x86_64 x86_64 GNU/Linux

If not reinstall your system with the x64 version of your linux distribution.

Keep sure your system is up to date with yum update.

After Download of Oracle Database Express Edition 11g unzip the File to a folder. Install the rpm with

rpm -ivH Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm

and enter

/etc/init.d/oracle-xe configure

Check if the install process outputs ends with

Starting Oracle Net Listener…Done
Configuring database…Done
Starting Oracle Database 11g Express Edition instance…Done
Installation completed successfully.

or the log files under

/u01/app/oracle/product/11.2.0/xe/config/log.

On success you have a icon called “Get started with Oracle… .desktop”. Click on the icon and mark the link as trust worthy. It will open a browser

http://localhost:9000/apex/f?p=4950

If asked for a user name and password use SYSTEM and your chosen password as credentials.

Dbunit with JUnit 4.x and Spring for testing Oracle DB Application

DBUnit is very nice for testing database content changes made by an application. You define in XML the data including the structure of your tables (dataset.xml).



	

Simple_Data is the name of the table and each column is a attribute in the xml doc with the content value e.g. id with value 1.

The Getting Started of DBUnit work with JUnit 3.8 and self handling of the JDBC Connection.

JUnit 4.x are more comfortable with annotations based test methods and Spring comes with dependency injection for separating
configuration from implementation code.

The following approach combines DBUnit with JUnit 4.4 and Spring 2.5.6 to test comfortable a Oracle 10g database.

I use Maven 2.x to define the depending libraries used by the example (pom.xml):



	4.0.0
	de.schaeftlein.dev.dbunit
	test-dbunit
	test-dbunit
	0.0.1-SNAPSHOT
	
	
		
			org.dbunit
			dbunit
			2.4.2
		
		
			org.springframework
			spring
			2.5.6
			jar
			compile
		
		
			junit
			junit
			4.4
		
		
			commons-dbcp
			commons-dbcp
			1.2.2
		
		
			org.springframework
			spring-test
			2.5.6
		
		
			org.slf4j
			slf4j-api
			1.5.6
		
		
			org.slf4j
			log4j-over-slf4j
			1.5.6
		
		
			log4j
			log4j
			1.2.14
		
		
			org.slf4j
			slf4j-log4j12
			1.5.6
		
		
			com.oracle
			ojdbc14
			10.2.0.2.0
		
	

Keep in mind that the Oracle JDBC Driver has to be downloaded manually.
The public maven repos include only the Pom definition for the oracle driver. Generate with maven command line tool the eclipse project files:

mvn clean eclipse:clean eclipse:eclipse

The JDBC datasource is defined via Spring (applicationContext.xml):

 


  
    
    
    
    
  
 

Additionally we define the expected data as well in XML for DBUnit (expectedDataSet.xml):



	

Now we can code our JUnit 4.x Test to

  1. load data before the test method
  2. change the data via JDBC to emulate a application
  3. compare the changed data with expected data
  4. clean up the database

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:applicationContext.xml"}) 
public class TestDBUnitWithSpring {

	@Autowired
	private DataSource dataSource;
	
	@Before
	public void init() throws Exception{
		// insert data into db
		DatabaseOperation.CLEAN_INSERT.execute(getConnection(), getDataSet());
	}
	
	@After
	public void after() throws Exception{
		// insert data into db
		DatabaseOperation.DELETE_ALL.execute(getConnection(), getDataSet());
	}
	
	private IDatabaseConnection getConnection() throws Exception{
	// get connection
		Connection con = dataSource.getConnection();
		DatabaseMetaData  databaseMetaData = con.getMetaData();
		// oracle schema name is the user name
		IDatabaseConnection connection = new DatabaseConnection(con,databaseMetaData.getUserName().toUpperCase());
		DatabaseConfig config = connection.getConfig();
		// oracle 10g 
		config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new Oracle10DataTypeFactory());
		// receycle bin
		config.setFeature(DatabaseConfig.FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES, Boolean.TRUE);
		return connection;
	}
	
	private IDataSet getDataSet() throws Exception{
		// get insert data
		File file = new File("src/test/resources/dataset.xml");
		return new FlatXmlDataSet(file);
	}
	
	@Test
	public void testSQLUpdate() throws Exception{
		Connection con = dataSource.getConnection();
		Statement stmt = con.createStatement();
		// get current data
		ResultSet rst = stmt.executeQuery("select * from simple_data where id = 1");
		if(rst.next()){
			// from dataset.xml
			assertEquals("value_before", rst.getString("content"));
			rst.close();
			
			// update via sql
			int count = stmt.executeUpdate("update simple_data set content='value_after' where id=1");

			stmt.close();
			con.close();

			// expect only one row to be updated
			assertEquals("one row should be updated", 1, count);

			// Fetch database data after executing the code
			QueryDataSet databaseSet = new QueryDataSet(getConnection());
			// filter data
			databaseSet.addTable("simple_data", "select * from simple_data where id = 1");
			ITable actualTable = databaseSet.getTables()[0];

			// Load expected data from an XML dataset
			IDataSet expectedDataSet = new FlatXmlDataSet(new File("src/test/resources/expectedDataSet.xml"));
			ITable expectedTable = expectedDataSet.getTable("simple_data");

			// filter unnecessary columns of current data by xml definition
			actualTable = DefaultColumnFilter.includedColumnsTable(actualTable, expectedTable.getTableMetaData().getColumns());

			// Assert actual database table match expected table
			assertEquals(1,expectedTable.getRowCount());
			assertEquals(expectedTable.getRowCount(), actualTable.getRowCount());
			assertEquals(expectedTable.getValue(0, "content"), actualTable.getValue(0, "content"));
			
		} else {
			fail("no rows");
			rst.close();
			stmt.close();
			con.close();
		}

	}
}

jdbc DataBaseMetaData + oracle 10g + dbunit + username..

If you are using DBUnit as tool for set up test environment in your oracle 10g then you have to provide the username of course. Normally it isn’t case sensitive in most tools. The jdbc driver or sqlplus let you create connection with lower case. DBUnit ask the DataBaseMetaData to get the defined tables in the schema. Schema and username are the same in the oracle world. So in DBUnit the source is

DatabaseMetaData databaseMetaData = jdbcConnection.getMetaData();
ResultSet resultSet = databaseMetaData.getTables(null, schema, "%", tableType);

where tableType is the String[] {“table”}. with schema == username lower case i get in my oracle 10.2 db nothing back. With upper case username et voila! Don’t know if this is really an standard behaviour….

JDBC Connection + Oracle 10g + recycle bin…

Since Oracle 10g dropped tables goes into a recycle bin: http://orafaq.com/node/968. These tables aren’t visible inside the admin site under the user but if you are using JDBC to access the schema. Thought first of a bug in SQL Explorer when i saw the huge list of tables starting with BIN$… Every time you drop tables more BIN$ you get… If someone needs such a future… I’m more a friend of the “know what i’m doing’ approach. The good thing is that this is a feature which you can disable:

  1. sqlplus sys/<your password> as sysdba
  2. SQL> purge dba_recyclebin;
  3. SQL> alter system set recyclebin = OFF scope=both;