Archive for the ‘General’ Category
Copy all tables from a schema to another one
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 "select object_name from user_objects where object_type = ‘TABLE’ order by object_name;" | \
sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB | \
grep -v OBJECT_NAME | grep -v "-" | grep -v "^$" | \
sed "1,9d" | tac | sed "1,3d" | tac | \
sort | uniq > allTables.txt
for table in `more allTables.txt`
do
rm -rf $table.sql
echo "exporting $table"
exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y
echo "importing $table"
echo "truncate table $table;" | \
sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB
imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql \
buffer=10485867 tables="$table" \
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 DBgrep -v OBJECT_NAME | grep -v "-" | grep -v "^$": this removes empty lines and useless trace from Oracle SQLPlussed "1,9d" | tac | sed "1,3d" | tac: removes the 9 first lines and the 3 last ones.tacin Unix allow to reverse the content of a filesort | uniq: sort lines and remove duplicatesexp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y: exports the content of the table $tableecho "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
DBUnit and Hibernate: Beware of Collisions!
Here is an interesting case I have encountered this morning.
Context
I must test a Hibernate implementation of a DAO, using DBUnit and its XML dataset files as a minimal database. A CRUD unit test fails, I get this trace:
| WARN util.JDBCExceptionReporter – SQL Error: 0, SQLState: null ERROR util.JDBCExceptionReporter – failed batch ERROR def.AbstractFlushingEventListener – Could not synchronize database state with session org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update |
Explanation
In my dataset, I had set some primary keys at 0, 1, …, 10. Owing to my Hibernate mapping, the primary keys are generated thanks to a sequence.
On another hand, with the CRUD test, the primary keys 0, 1, …, 10 are also assigned to newly created objects.
Here is the issue: there are collisions between the XML dataset file objects and the objects created with Hibernate, because obviously two objects never share the same primary key!
“Position” is a reserved word in SQL!
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.
java.lang.IncompatibleClassChangeError: Implementing class
Case: on a call to:
JAXBContext.newInstance(Field.class, FieldSet.class);
I had a:
java.lang.IncompatibleClassChangeError: Implementing class
Complete stacktrace:
java.lang.IncompatibleClassChangeError: Implementing class at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:620) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:124) at java.net.URLClassLoader.defineClass(URLClassLoader.java:260) at java.net.URLClassLoader.access$100(URLClassLoader.java:56) at java.net.URLClassLoader$1.run(URLClassLoader.java:195) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:188) at java.lang.ClassLoader.loadClass(ClassLoader.java:306) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268) at java.lang.ClassLoader.loadClass(ClassLoader.java:251) at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319) at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:620) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:124) at java.net.URLClassLoader.defineClass(URLClassLoader.java:260) at java.net.URLClassLoader.access$100(URLClassLoader.java:56) at java.net.URLClassLoader$1.run(URLClassLoader.java:195) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:188) at java.lang.ClassLoader.loadClass(ClassLoader.java:306) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268) at java.lang.ClassLoader.loadClass(ClassLoader.java:251) at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319) at com.sun.xml.bind.v2.ContextFactory.createContext(ContextFactory.java:139) at com.sun.xml.bind.v2.ContextFactory.createContext(ContextFactory.java:117) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at javax.xml.bind.ContextFinder.newInstance(ContextFinder.java:210) at javax.xml.bind.ContextFinder.find(ContextFinder.java:368) at javax.xml.bind.JAXBContext.newInstance(JAXBContext.java:574) at javax.xml.bind.JAXBContext.newInstance(JAXBContext.java:522) (...) |
Fix: there an issue of classpath and a conflit of jars. Remove all jaxb-* jars from within your classpath, except the jaxb-impl.jar that you must keep.
java.lang.SecurityException: [Security:090398]Invalid Subject: principals=[myRole]
Short stacktrace:
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'myJmsTemplate' (...) Invocation of init method failed; nested exception is java.lang.SecurityException: [Security:090398]Invalid Subject: principals=[myRole]
Complete stacktrace
(copy paste in a text editor if the complete stack is not displayed in your browser):
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'myJmsTemplate' defined in URL [zip:C:/workarea/development/servers/wl_server/servers/XXXX/tmp/_WL_user/XXXXXXXXXXXX-ear/7gtxm8/XXXXXXXX-services-ejb.jar!/com/XXXXX/businessApplicationContext-XXXXXXXX.xml]: Cannot resolve reference to bean 'myJmsQueueConnectionFactory' while setting bean property 'connectionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'myJmsQueueConnectionFactory' defined in URL [zip:C:/workarea/development/servers/wl_server/servers/ejbtier/tmp/_WL_user/XXXXXX-ear/7gtxm8/XXXXXXXX.jar!/com/bnpparibas/primeweb/businessApplicationContextXXXXXXXXXXXX.xml]: Invocation of init method failed; nested exception is java.lang.SecurityException: [Security:090398]Invalid Subject: principals=[myRole] at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:275) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:104) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1245) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1010) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:472) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:221) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164) at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:881) (...) |
The issue appears when I try to deploy an EJB sending JMS messages from my Weblogic server, to another one, in another domain.
Fix:
- I have not fixed the issue myself, I gave pieces of advice to the teams in charge of solving them. But I assume following guidelines are OK.
- Indeed there are two issues: one on credentials and another on servers
- Servers need trust each other. More information is available here. I assume trust is granted thanks to the use of certificates.
- On another hand, credentials from my server, it is to say here “
myRole” must be accepted by distant Ldap juridiction. I assume that distant EJB environment must something like:distantEnvironment.put(InitialContext.SECURITY_PRINCIPAL, "myRole");
Now it should work!
The domain edit lock is owned by another session in exclusive mode – hence this deployment operation cannot proceed
Error:
On trying to redeploy an application myFooApplication on BEA WebLogic 9:
weblogic.management.ManagementException: [Deployer:149164]The domain edit lock is owned by another session in exclusive mode - hence this deployment operation cannot proceed.
Fix:
- Shutdown Weblogic instances (including administration application)
- Delete the lock files:
$WL_HOME/edit.lok$WL_HOME/servers/myFooApplication/tmp/myFooApplication.lok
Get the list of tables in Oracle
Case: you need the list of all tables in an Oracle DB.
Solution: run this query:
[sql]select * from user_objects where object_type = ‘TABLE’;[/sql]
You can use a similar way to filter on table names, or get other types object such us triggers, procedures and indexes.
Deploy a JMS destination queue with Spring
Abstract
Case: we have to send JMS messages to a third-party server, using Spring. Of course, we have to discriminate production, UAT and developments environments. We can decide to use one Spring configuration file per environment, but it is complex to maintain.
Here is the way I proceded:
Declare the factory
[xml]<bean id="myJmsQueueConnectionFactory">
<property name="jndiName" value="my.jms.QueueConnectionFactory"/>
<property name="jndiTemplate" ref="myJmsJndiTemplate"/>
</bean>[/xml]
Declare a Jndi Template
[xml]<bean id="myJmsJndiTemplate">
<property name="environment">
<props>
<prop key="java.naming.factory.initial">weblogic.jndi.WLInitialContextFactory</prop>
<prop key="java.naming.provider.url"><strong>${my.jms.host}</strong></prop>
</props>
</property>
</bean>[/xml]
The variable ${my.jms.host} is used to indicate the actual destination host. The value is given in a property file myConfig.properties, for instance:
my.jms.host=t3://127.0.0.1:1234
Obviously, each environnement needs its ad hoc property file!
Declare a JMS Template
It gathers the factory and the queue name.
[xml]<bean id="myJmsTemplate">
<property name="connectionFactory" ref="myJmsQueueConnectionFactory"/>
<property name="defaultDestination" ref="myJmsQueue"/>
</bean>[/xml]
Declare the destination queue
[xml]<bean id="myJmsQueue">
<property name="jndiName" value="my.jms.destination.queue.name"/>
</bean>[/xml]
Ensure the property file is in classpath
[xml]<bean id="conf">
<property name="locations">
<list>
<value>classpath*:myConfig.properties</value>
</list>
</property>
</bean>[/xml]
Restart WebLogic, deploy your EAR. Now it should work!
Notice: in case you’re not sure the distant host is up or not, you may add this attribute to your beans: lazy-init="true"
weblogic.jms.common.MessageFormatException: JMSClientExceptions: Invalid property name, “my-personnal-id”
Error:
weblogic.jms.common.MessageFormatException: JMSClientExceptions: Invalid property name, "my-personnal-id"
Fix:
Rename the property as myPersonalId for instance. Indeed, owing to JMS specification, properties on JMS messages must be written in alphanumeric characters. Therefore, hyphen '-' and periods '.' are forbidden.
Difference entre morphisme et bijection
Je remarque souvent aupres des gens avec qui je travaille une erreur, selon laquelle bijection et un morphisme (isomorphisme ou endomorphisme) sont synonymes.
Il s’agit d’une erreur, car si tout morphisme est une bijection, la reciproque n’est pas vraie. En effet, la notion de bijection met en rapports des elements d’ensembles donnes. La notion de morphisme rajoute une information au niveau des lois internes a ces ensembles. Considerant une fonction f d’un ensemble E vers un ensemble F, munis de lois internes T et ¤, on a:
f(x T y) = f(x) ¤ f(y)
Prenons un exemple simple, en notant R+ l’ensemble des reels positifs:
La fonction x –> x² est un bijection de R+ dans R+.
La fonction x –> exp(x) est egalement une bijection de R+ dans R+. Mais on a une information supplementaire: exp(x+y) = exp(x).exp(y). La fonction exp est plus qu’une bijection, c’est un morphisme de (R+, +) dans (R+, .).