Category Archives: Oracle

how to insert double quotes in oracle sql queries?

Recently I came across a requirement to insert a word with double-quotes as values to a column from my Oracle Database table using SQL.

SQL> insert into DEFINITION_TABLE values(6,'Amount of Money Written off','The purpose of
this object is to calculate the amount of money classified as '“uncollectable”' by the bank. It should
ideally be as low as possible, because it directly impacts the financial health of the bank in a negative manner.','Amount of Money Writt
en off= Total amount of money classified as '“uncollectable”'','#','Null','Null','Actual Amount of Money Written off vs Predicted Amount of Money Written off,Amount of Money Written off by Product Type,Amount of Money Written off by Product Type on a Monthly Basis,Amount of Money Written off by Region,Amount of Money Written off by R
egion on a Monthly Basis','Quantitative, Lagging','Credit Cards','Minimization of losses',1);

insert into DEFINITION_TABLE values(6,'Amount of Money Written off','The purpose of this

ERROR at line 1:
ORA-00911: invalid character

So now how to do that????

SQL> insert into GLOSRY_KPI_DEFINITION_TABLE values(6,'Amount of Money Written off','[The purpose of
this KPI is to calculate the amount of money classified as "uncollectable" by the bank. It should ideally be as low as possible, because it directly impacts the financial health of the bank in a negative manner.]','[Amount of Money Written off= Total amount of money classified as "uncollectable"]','#','Null','Null','Actual Amount of
Money Written off vs Predicted Amount of Money Written off,Amount of Money Written off by Product Type,Amount of Money Written off by Product Type on a Monthly Basis,Amount of Money Written off by Region,Amount of Money Written off by Region on a Monthly Basis','Quantitative, Lagging','Credit Cards','Minimization of losses',1);

1 row created.

Use SQUARE BRACKETS [] to wrap the whole column value

insert into TABLE values (‘normal value’, ‘[this column value contains “double” quotes]’, ‘normal value’);

Happy Learning and Sharing 🙂

java.lang.AbstractMethodError: oracle.jdbc.driver.OracleDatabaseMetaData.locatorsUpdateCopy()Z

java.lang.AbstractMethodError: oracle.jdbc.driver.OracleDatabaseMetaData.locatorsUpdateCopy()Z
org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:143)
org.hibernate.service.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:75)
org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:159)
org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:131)
org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:73)
org.hibernate.cfg.Configuration.buildSettingsInternal(Configuration.java:2283)
org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2279)
org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1748)
org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1788)
org.springframework.orm.hibernate4.LocalSessionFactoryBuilder.buildSessionFactory(LocalSessionFactoryBuilder.java:242)
org.springframework.orm.hibernate4.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:372)
org.springframework.orm.hibernate4.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:357)
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1545)
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1483)
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:524)
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:461)
org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:295)
org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223)
org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:292)
org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:194)
org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:589)
org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:932)
org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:479)
org.springframework.web.servlet.FrameworkServlet.configureAndRefreshWebApplicationContext(FrameworkServlet.java:647)
org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:598)
org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:661)
org.springframework.web.servlet.FrameworkServlet.initWebApplicationContext(FrameworkServlet.java:517)
org.springframework.web.servlet.FrameworkServlet.initServletBean(FrameworkServlet.java:458)
org.springframework.web.servlet.HttpServletBean.init(HttpServletBean.java:138)
javax.servlet.GenericServlet.init(GenericServlet.java:158)
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1070)
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
java.lang.Thread.run(Unknown Source)

Solution

Switching to Oracle 10g jdbc driver solved the problem immediately, without any configuration change requirements.

Download link: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-10201-088211.html

Remember that Oracle recommends you to use 10 series drivers even if you are connecting to a 8 or 9 series database server!

Could not load JDBC driver class [oracle.jdbc.driver.OracleDriver

I am trying to connection my java web application to the Oracle9i database.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in ServletContext resource [/WEB-INF/config/sdnext-servlet.xml]: Error setting property values; nested exception is org.springframework.beans.PropertyBatchUpdateException; nested PropertyAccessExceptions (1) are:
PropertyAccessException 1: org.springframework.beans.MethodInvocationException: Property 'driverClassName' threw exception; nested exception is java.lang.IllegalStateException: Could not load JDBC driver class [oracle.jdbc.driver.OracleDriver]

Solution

Make sure you have the ojdbc14.jar in your run time classpath.

Download the same from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc9201-092698.html

Happy Learning 🙂

Oracle Database 12c – a new kind of DB is ready for download :-)

We all know that Oracle has launched a cloud version of the Database and Oracle announced now that Oracle Database 12c is now available for download off the Oracle Technology Network.

where “c” stands for Cloud.  Its Oracle’s significant investments in delivering cloud-ready products.

Oracle Database 12c introduces a new, multitenant architecture that makes it easy to deploy and manage database clouds. Oracle 12c is a first multitenant database in the market.

“multi-tenancy” means – where multiple companies share the same hardware.  For example, Amazon’s cloud is actually its own IT infrastructure, rented out to whoever else wants to use it. That’s what makes cloud computing so affordable.

Oracle 12c can be called as “Container Database” – It’s function is to hold lots of other databases, keeping their data separate, but allowing them to share underlying hardware resources like memory or file storage.
12c is for software-as-a-service that let multiple customers access a single database. It’s also geared toward large enterprises who may have hundreds of Oracle databases. It would let them consolidate their databases onto less hardware, saving them money on that and making all of those databases easier to manage.

Some of the Innovative features in Oracle Database 12c are,

  • Pluggable databases—for consolidating multiple databases quickly
  • Automatic data optimization—for compressing data at a higher density—maximize resource efficiency and flexibility.
  • Improve database and application continuity.
  • Redact sensitive data

Try downloading Oracle 12c from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

More about Oracle Database 12c features can be found at the below link

http://www.oracle.com/technetwork/database/plug-into-cloud-wp-12c-1896100.pdf

oracle.jdbc.driver.OracleDatabaseMetaData.getDatabaseMajorVersion

 

I was facing the below issue when I use Oracle 9 JDBC driver along with Hibernate 3.6,

 

Initial SessionFactory creation failed.java.lang.AbstractMethodError: oracle.jdbc.driver.OracleDatabaseMetaData.getDatabaseMajorVersion()I
Exception in thread “main” java.lang.NullPointerException
at org.hibernate.tutorial.test.EventManager.createAndStoreEvent(EventManager.java:20)
at org.hibernate.tutorial.test.EventManager.main(EventManager.java:14)

 

Solution

Switching to Oracle 10.2.0.2 jdbc driver solved the problem immediately, without any configuration change requirements.

 

Download link: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-10201-088211.html

 

Remember that Oracle recommends you to use 10 series drivers even if you are connecting to a 8 or 9 series database server!

 

Thanks.

 

My sample tnsnames.ora file

# TNSNAMES.ORA Network Configuration File: C:\oracle\ora90\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
GDN90A =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sarwangres)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GDN90A)
)
)
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sarwangres)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

# TNSNAMES.ORA Network Configuration File: C:\oracle\ora90\network\admin\tnsnames.ora# Generated by Oracle configuration tools.
GDN90A =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = sarwangres)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = GDN90A)    )  )
INST1_HTTP =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = sarwangres)(PORT = 1521))    )    (CONNECT_DATA =      (SERVER = SHARED)      (SERVICE_NAME = MODOSE)      (PRESENTATION = http://HRService)    )  )
EXTPROC_CONNECTION_DATA =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))    )    (CONNECT_DATA =      (SID = PLSExtProc)      (PRESENTATION = RO)    )  )

How to get SID (instance name) of Oracle?

Here is mine;

SQL> select sys_context(‘userenv’,’db_name’) from dual;
SYS_CONTEXT(‘USERENV’,’DB_NAME’)
——————————————————————————–
GDN90A

Other Information:

SQL> sho user

USER is “SCOTT”

SQL> select * from global_name;

GLOBAL_NAME

————————————————————

GDN90A.US.ORACLE.COM