Jonathan Lalou's Blog

Posts Tagged ‘SQL’

Case

From an Agile and TDD viewpoint, performing uni tests on DAO is a requirement. Sometimes, instead of using DBUnit datasets “out of the box”, the developper need test on actual data. In the same vein, when a bug appears on production, isolating and reproducing the issue is a smart way to investigate, and, along the way, fix it.
Therefore, how to export actual data from Oracle DB (or even MySQL, Sybase, DB2, etc.) to a DBUnit dataset as a flat XML file?

Here is a Runtime Test I wrote on this subject:

Fix

Spring

Edit the following Spring context file, setting the login, password, etc.
[xml]
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

<!– don’t forget to write this, otherwise the application will miss the driver class name, and therfore the test will fail–>
<bean id="driverClassForName" class="org.springframework.beans.factory.config.MethodInvokingFactoryBean">
<property name="targetClass" value="java.lang.Class"/>
<property name="targetMethod" value="forName"/>
<property name="arguments">
<list>
<value>oracle.jdbc.driver.OracleDriver</value>
</list>
</property>
</bean>
<bean id="connexion" class="org.springframework.beans.factory.config.MethodInvokingFactoryBean"
depends-on="driverClassForName">
<property name="targetClass" value="java.sql.DriverManager"/>
<property name="targetMethod" value="getConnection"/>
<property name="arguments">
<list>
<value>jdbc:oracle:thin:@host:1234:SCHEMA</value>
<value>myLogin</value>
<value>myPassword</value>
</list>
</property>
</bean>

<bean id="databaseConnection" class="org.dbunit.database.DatabaseConnection">
<constructor-arg ref="connexion"/>
</bean>
<bean id="queryDataSet" class="org.dbunit.database.QueryDataSet">
<constructor-arg ref="databaseConnection"/>
</bean>
</beans>[/xml]

The bean driverClassForName does not look to be used ; anyway, if Class.forName("oracle.jdbc.driver.OracleDriver") is not called, then the test will raise an exception.
To ensure driverClassForName is created before the bean connexion, I added a attribute depends-on="driverClassForName". The other beans will be created after connexion, since Spring will deduce the needed order of creation via the explicit dependency tree.

Java

[java]public class Oracle2DBUnitExtractor extends TestCase {
private QueryDataSet queryDataSet;

@Before
public void setUp() throws Exception {
final ApplicationContext applicationContext;

applicationContext = new ClassPathXmlApplicationContext(
"lalou/jonathan/Oracle2DBUnitExtractor-applicationContext.xml");
assertNotNull(applicationContext);

queryDataSet = (QueryDataSet) applicationContext.getBean("queryDataSet");

}

@Test
public void testExportTablesInFile() throws DataSetException, IOException {
// add all the needed tables ; take care to write them in the right order, so that you don’t happen to fall on dependencies issues, such as ones related to foreign keys

queryDataSet.addTable("MYTABLE");
queryDataSet.addTable("MYOTHERTABLE");
queryDataSet.addTable("YETANOTHERTABLE");

// Destination XML file into which data needs to be extracted
FlatXmlDataSet.write(queryDataSet, new FileOutputStream("myProject/src/test/runtime/lalou/jonathan/output-dataset.xml"));

}
}[/java]

A Short String

I have a table of which one field is a CLOB. Let’s say I have to insert one record with a short text. The following command is allowed:

[sql]INSERT INTO jonathan_table VALUES (1, ‘hello world!’);[/sql]

A Bigger Text

Error ORA-01704

Now, my text is larger, let’s say 5000 characters. When I launch the same query, I get the following error:

[sql]ORA-01704: string literal too long[/sql]

Indeed, Oracle/SQL*Plus have a limit on CLOB inserts: 4000 bytes.

Workaround

To pass through the limit on canonical SQL, you’ll have to use a PL/SQL procedure. The following command will be successful for any text larger than 4000 bytes, but shorter than 32767:

[sql]DECLARE
bigtext1   VARCHAR2 (32767);
BEGIN
bigtext1 := lpad(‘X’, 32000, ‘X’)
INSERT INTO jonathan_table VALUES (1, bigtext1);
END;[/sql]

An Even Bigger Text

Errors ORA-06550 and PLS-00103

You guess it: beyond this limit of 32 KB, an error occurs. So the following script:

[sql]DECLARE
bigtext1 VARCHAR2 (42000);
BEGIN
bigtext1 := lpad(‘X’, 42000, ‘X’)
INSERT INTO jonathan_table
VALUES (1, bigtext1);
END;
[/sql]

raises such an error:

[sql]Error at line 1
ORA-06550: line 5, column 4:
PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:

. ( * % & = – + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
The symbol ";" was substituted for "INSERT" to continue.[/sql]

Fix this issue

I searched a lot to find an easy fix to go beyond the limit of 32KB. My point was that with Java for instance there is no limit of 32KB. In the same way, with TOAD I was able to update the record with many mega bytes of text, via the clipboard. After further search, I learnt that the 32KB barrier was a SQL*Plus limitation on actual strings, but the patterns insert into ... select ... from were not affected.
Here is the idea:

• create a temporary table
• split the text into blocks shorter than 32KB
• insert the blocks into the temporary table
• perform a first insert with a null CLOB
• update the record using a select on the temporary table (yet you can insert the actual value since previous step)

Here is an example:

[sql]DROP TABLE tt_jonathan_table;
CREATE GLOBAL TEMPORARY TABLE tt_jonathan_table
(
ID NUMBER(10),
pdlsuffix CLOB
) ON COMMIT PRESERVE ROWS;

TRUNCATE TABLE tt_jonathan_table;

DECLARE
bigtext1 VARCHAR2 (32767);
bigtext2 VARCHAR2 (32767);
BEGIN
bigtext1 := lpad(‘X’, 32000, ‘X’)
bigtext2 := lpad(‘Y’, 32000, ‘Y’)
INSERT INTO tt_jonathan_table
VALUES (1, bigtext1);

INSERT INTO tt_jonathan_table
VALUES (2, bigtext2);

INSERT INTO jonathan_table
(id, myClobField)
VALUES (jonathan_seq.NEXTVAL, NULL);

UPDATE jonathan_table
SET myClobField = (SELECT CONCAT (rls1.myClobField, rls2.myClobField)
FROM tt_jonathan_table rls1, tt_jonathan_table rls2
WHERE rls1.ID = 1 AND rls2.ID = 2)
WHERE myClobField is null;
END;
/

TRUNCATE TABLE tt_jonathan_table;
[/sql]

Case

I have had to access a BLOB and read its content. By principle, I dislike using binary objects, which do not suit easy tracing and auditing. Anyway, in my case, floats are stored in a BLOB, and I need read them in order to validate my current development.

You have many ways to read the content of the BLOB. I used two: SQL and Java

SQL

Start your TOAD for Oracle ; you can launch queries similar to this:

[sql]SELECT UTL_RAW.cast_to_binary_float
(DBMS_LOB.submyrecord (myrecord.myrecordess,
4,
1 + (myrecordessnameid * 4)
)
) AS myrecordessvalue
FROM mytable myrecord
WHERE myrecordessid = 123456; [/sql]
You can also run a stored procedure, similar to this:
[sql]
DECLARE
blobAsVariable BLOB;
my_vr RAW (4);
blobValue FLOAT;
bytelen NUMBER := 4;
v_index NUMBER := 5;
jonathan RAW (4);
loopLength INT;
BEGIN
SELECT myField
INTO blobAsVariable
FROM myTable
WHERE tableid = (5646546846);

DBMS_LOB.READ (blobAsVariable, bytelen, 1, jonathan);
loopLength := UTL_RAW.cast_to_binary_integer (jonathan);

FOR rec IN 1 .. loopLength
LOOP
DBMS_LOB.READ (blobAsVariable, bytelen, v_index, my_vr);
blobValue := UTL_RAW.cast_to_binary_float (my_vr);
v_index := v_index + 4;
DBMS_OUTPUT.put_line (TO_CHAR (blobValue));
END LOOP;
END;[/sql]

Java

I am still not sure to be DBA expert. Indeed I am convinced I am more fluent in Java than in PL/SQL 😉

Create a Spring configuration file, let’s say BlobRuntimeTest-applicationContext.xml:
[xml]<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

<!– $Id: BlobRuntimeTest-applicationContext.xml $ –>
<bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@myDBserver:1234:MY_SCHEMA"/>
<property name="username" value="jonathan"/>
<property name="password" value="lalou"/>
<property name="initialSize" value="2"/>
<property name="minIdle" value="2"/>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>

</beans>[/xml]

Now create a runtime test:
[java]/**
* User: Jonathan Lalou
* Date: Aug 7, 2011
* Time: 5:22:33 PM
* $Id: BlobRuntimeTest.java $
*/
public class BlobRuntimeTest extends TestCase {
private static final Logger LOGGER = Logger.getLogger(BlobRuntimeTest.class);

private static final String TABLE = "jonathanTable";
private static final String PK_FIELD = "jonathanTablePK";
private static final String BLOB_FIELD = "myBlobField";
private static final int[] PK_VALUES = {123, 456, 789};

private ApplicationContext applicationContext;
private JdbcTemplate jdbcTemplate;

@Before
public void setUp() throws Exception {
applicationContext = new ClassPathXmlApplicationContext(
"lalou/jonathan/the/cownboy/BlobRuntimeTest-applicationContext.xml");
assertNotNull(applicationContext);
jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
assertNotNull(jdbcTemplate);
}

@After
public void tearDown() throws Exception {
}

@Test
public void testGetArray() throws Exception {
for (int pk_value : PK_VALUES) {
final Blob blob;
final byte[] bytes;
final float[] floats;

blob = (Blob) jdbcTemplate.queryForObject("select " + BLOB_FIELD + " from " + TABLE + " where " + PK_FIELD + " = " + pk_value, Blob.class);
assertNotNull(blob);
bytes = blob.getBytes(1, (int) blob.length());
// process your blob: unzip, read, concat, add, etc..
// floats = ….

LOGGER.info("Blob size: " + floats.length);
LOGGER.info(ToStringBuilder.reflectionToString(floats));
}
}
}
[/java]

Case

You have a stored procedure that you would like to update. The script is OK with TOAD, but raises an error when launched via SQL*Plus.

Fix

Add a slash (‘/’) or a dot (‘.’) at the end of file.

Context

An object is marshallized and sent on TibcoRV 8.1. An instance of Mule ESB 2.2.1 listens to TibcoRV, reads the message, unmarshalls the object and builds an SQL insert query.

The query is very simple:

[sql]insert into myTable(dateColumn, stringColumn) values(…)[/sql]

It is executed in a Jdbc connector declared on Mule configuration file:

[xml] <jdbc:connector name="jdbcConnector" dataSource-ref="mySybaseDB" pollingFrequency="1000">
<jdbc:query key="writeTest"
value="INSERT INTO myTable(dateColumn, stringColumn)
VALUES(#[map-payload:myJavaDate],#[map-payload:myJavaString])"/>
</jdbc:connector>[/xml]

The DB is Sybase. The first column is of type datetime, the second one is varchar. The values are retrieved from a java.util.Date and a java.lang.String.

Case

When the query is run, I get the error:

[java]java.sql.SQLException: Cannot run this command because Java services are not enabled. A user with System Administrator (SA) role must reconfigure the system to enable Java.[/java]

(you may get a com.sybase.jdbc2.jdbc.SybSQLException instead of java.sql.SQLException)

Explanation – Fix

The error is related only to Sybase, and not to TibcoRV and Mule: by default, Sybase cannot manage Java Dates (java.util.Date). You have to start explicitly the Java services. To perform that:

• login with an username owing the rights “sa_role“
• run the SQL query: [sql]sp_configure ‘enable java’, 1[/sql]
• the restart the Sybase server

Now it should work. A similar error may occur with JBoss.

Case:

I need copy the content of all tables from one Oracle schema to another one, specifically: I must copy raw data from a production DB to a developper one.

Context:

• The DBA do not allow DBLinks
• Oracle source and destination schemas share the same structure (name of the tables, names and types of columns, etc.

Fix:

Here is a small script I wrote, of course it can be improved:
[bash]
#!/usr/bin/bash
SRC_LOGIN=XYZ
SRC_PASSWORD=XYZ
SRC_DB=production
DBDEST_LOGIN=XYZ
DEST_PASSWORD=XYZ
DEST_DB=developmentDB

echo &quot;select object_name from user_objects where object_type = ‘TABLE’ order by object_name;&quot; | \
sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB | \
grep -v OBJECT_NAME | grep -v &quot;-&quot; | grep -v &quot;^$&quot; | \
sed &quot;1,9d&quot; | tac | sed &quot;1,3d&quot; | tac | \
sort | uniq &amp;gt; allTables.txt

for table in `more allTables.txt`
do
rm -rf $table.sql
echo &quot;exporting $table&quot;
exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables=&quot;$table&quot; direct=y
echo &quot;importing $table&quot;
echo &quot;truncate table $table;&quot; | \
sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB
imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql \
buffer=10485867 tables=&quot;$table&quot; \
fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y
rm -rf $table.sql
done

rm -rf allTables.txt[/bash]

A little more explanations:

• echo "select object_name from user_objects where object_type = 'TABLE';" | sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB: this allow to retrieve the names of all the tables from the DB
• grep -v OBJECT_NAME | grep -v "-" | grep -v "^$" : this removes empty lines and useless trace from Oracle SQLPlus
• sed "1,9d" | tac | sed "1,3d" | tac: removes the 9 first lines and the 3 last ones. tac in Unix allow to reverse the content of a file
• sort | uniq : sort lines and remove duplicates
• exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y: exports the content of the table $table
• echo "truncate table $table;" | sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB: truncates destination table. This is useful in case the script must be played more than once, for instance.
• imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql buffer=10485867 tables="$table" fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y: import the content of the table

Some improvement ideas:

• handle sequences, triggers, stored procedures, etc.
• create the complete destination DB owing to source DB, ie do not assume anymore that the DBs share the same structure

Very interesting case. At firt, I spotted these errors:

SQL Error: -22, SQLState: S0002

and:

java.sql.SQLException: Table not found in statement

But here is the complete stacktrace:

"2009-10-13 12:29:17,934 ERROR hbm2ddl.SchemaExport - Unsuccessful: create table MY_ORACLE_TABLE (myOracleTableId integer not null, optimisticTimestamp timestamp not null, position double, primary key (myOracleTableId))
2009-10-13 12:29:17,934 ERROR hbm2ddl.SchemaExport - Unexpected token: POSITION in statement [create table MY_ORACLE_TABLE (myOracleTableId integer not null, optimisticTimestamp timestamp not null, position]
(...)
2009-10-13 12:29:17,949 ERROR hbm2ddl.SchemaExport - Table not found: MY_ORACLE_TABLE in statement [alter table MY_ORACLE_TABLE]
2009-10-13 12:29:17,980 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest-testFindmyOracleTableByPK_OK.xml
2009-10-13 12:29:17,996 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:17,996 DEBUG util.HibernatePersistenceTestCase - --------------- Using dataset : HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:18,355 WARN util.JDBCExceptionReporter - SQL Error: -22, SQLState: S0002
2009-10-13 12:29:18,355 ERROR util.JDBCExceptionReporter - Table not found in statement [select (...) where myOracleTable0_.myOracleTableId=?]
2009-10-13 12:29:18,371 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest-testFindmyOracleTableByPK_OK.xml
2009-10-13 12:29:18,371 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:18,387 DEBUG util.HibernatePersistenceTestCase - --------------- Using dataset : HibernateMyOracleTableDAOUnitTest.xml

org.hibernate.exception.SQLGrammarException: could not load an entity: [com.(...)myOracleTable#147]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1799)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:47)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:41)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:2730)
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:365)
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:346)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:123)
at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:161)
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:87)
at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:862)
at org.hibernate.impl.SessionImpl.load(SessionImpl.java:781)
at org.hibernate.impl.SessionImpl.load(SessionImpl.java:774)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
at $Proxy0.load(Unknown Source)
(...)HibernateMyOracleTableDAOUnitTest.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:40)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: java.sql.SQLException: Table not found in statement [select (...) where myOracleTable0.myOracleTableId=?]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:442)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:368)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:105)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
... 42 more"

I have helped you with red bold words… Can you see the issue?

Yes, indeed position is a reserved keyword in SQL! To fix this issue, you have no choice but to rename your column.

Abstract: we need the max of two dates

Read the rest of this entry »