Search
Calendar
September 2017
S M T W T F S
« Sep    
 12
3456789
10111213141516
17181920212223
24252627282930
Your widget title
Archives

Posts Tagged ‘SQL’

PostHeaderIcon 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:

#!/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

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

PostHeaderIcon “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.

PostHeaderIcon Ternary operator in Oracle PL/SQL

Abstract: we need the max of two dates

Read the rest of this entry »