Category Archives: Database

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

What is Normalization in the Database?

What is Relational Database?

Relational Database is an extremely simple way of thinking about and managing the data used in business. It is nothing more than the collection of tables and columns.

Why it is called as Relational?

It’s a simple reason. Information (Data) stored in tables.  And tables are independent. Even though, we can easily relate the tables. Tables have columns, rows and names. Tables can be related to each other if they each have a column with common type of information.

What is Normalization?

Normalization is the process of organizing or putting things (information of a business) right (sensibly and logically), making them normal.  It’s the process of reorganization of the elements of the data step-by-step by assuring the normal relationships.

First Normal Form:

à Have the data into separate tables where the data in each table is of a similar type.

à Give each table a Primary Key, which uniquely identifies a row of information.

EMPLOYEE Table

———————

Name (PK)

Age

Department

Depart_Manager

Depart_Address

SKILLS table

—————-

Name (PK)

Skill (PK)

Description

For simplicity assume, Name is Primary Key in EMPLOYEE table.  Since each employee may have several Skills, both Name and Skill is the Primary Key in SKILLS table.

Here Employee’s basic information and Skill’s information separated.  Each table has given the Primary Key.  The Primary Key uniquely identifies an Employee’s information. First Normalization achieved.

Now, if we look at the SKILLS table, even though Name and Skill together form a Primary Key, the Description is only depends on Skill, regardless of whose name is there, that is, Description is only depends on a part of the Primary Key of SKILLS table.  This leads to Second Normal Form.

Second Normal Form:

Here, we take out the data that only depends on a part of the Key.  In our example, we take the Skill and Description out from the SKILLS table, to the 3rd table.  Like the following,

EMPLOYEE Table

———————

Name (PK)

Age

Department

Depart_Manager

Depart_Address

EMPLOYEE_SKILLS table

——————————-

Name (PK)

Skill

SKILLS table

—————-

Skill (PK)

Description

What we achieved here?  Two things we discarded here.

One, for example, if four employees have same Skill means, the Skill description repeated four times.

Two, if one employee has unique Skill that no other employees have and if that employee left the company, then the Skill description would vanished out if we left with First Normal Form.  So, with a Second Normal Form, the Skill and Description will remain in the database even if no employee has the skill.  Skills can be added separately even before locating anyone who has them.

 

Third Normal Form:

3rd Normal Form means, remove anything in the tables that does not depend solely on the Primary Key.

In our example, if we look at EMPLOYEE table.  Department information does not solely depend on an Employee.  The Department information is meaningful and has relation with an Employee, only until the Employee works in that Department.  If an Employee moves out from one department to another department, we need to update his row with new department’s manager name and address.  Department information is independent of whether an Employee works there or not.  Sp Department information moved out to separate table, like the following,

EMPLOYEE Table

———————

Name (PK)

Age

Department

EMPLOYEE_SKILLS table

——————————-

Name (PK)

Skill

SKILLS table

—————-

Skill (PK)

Description

DEPARTMENT table

————————-

Department (PK)

Depart_Manager

Depart_Address

Always the data in the 3rd Normal Form is already automatically in 2nd and 1st Normal Form.  The concept behind 3rd Normal Form is, simply arrange the data so that the columns in each table, other than Primary Key, are solely dependent only on the whole Primary Key.

3rd Normal Form otherwise called as “the Key, the Whole Key or Nothing but the Key”.

This whole process of organizing the information sensibly and logically is called as Normalization.  It’s not that much difficult. Analyzing the “normal” relationships among the various elements of data is called as Normalization.  We need to understand that Normalization is a part of the process of analysis, not design.  Design of a database application includes many other considerations.

 

Also refer the below link

http://www.databasedev.co.uk/database_normalization_process.html

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.

 

Database Key Terminologies

  • Key. A key is one or more data attributes that uniquely identify an entity.  In a physical database a key would be formed of one or more table columns whose value(s) uniquely identifies a row within a relational table.
  • Composite key.  A key that is composed of two or more attributes.
  • Natural key.  A key that is formed of attributes that already exist in the real world.  For example, U.S. citizens are issued a Social Security Number (SSN)  that is unique to them (this isn’t guaranteed to be true, but it’s pretty darn close in practice).  SSN could be used as a natural key, assuming privacy laws allow it, for a Person entity (assuming the scope of your organization is limited to the U.S.).
  • Surrogate key.  A key with no business meaning.
  • Candidate key.  An entity type in a logical data model will have zero or more candidate keys, also referred to simply as unique identifiers (note: some people don’t believe in identifying candidate keys in LDMs, so there’s no hard and fast rules).  For example, if we only interact with American citizens then SSN is one candidate key for the Person entity type and the combination of name and phone number (assuming the combination is unique) is potentially a second candidate key.  Both of these keys are called candidate keys because they are candidates to be chosen as the primary key, an alternate key  or perhaps not even a key at all within a physical data model.
  • Primary key.  The preferred key for an entity type.
  • Alternate key. Also known as a secondary key, is another unique identifier of a row within a table.
  • Foreign key. One or more attributes in an entity type that represents a key, either primary or secondary, in another entity type.

SQL: Write query to display top 2 salary from the given table

Write SQL query to display top 2 salary from the given table:

Inner and Outer Joins in DBMS

There are two kinds of joins, each of which either excludes or includes rows in both tables of the join that do not match.  These are:

Inner Join:  An inner join excludes the rows of either table that don’t have a matching value.

Outer Join:  An outer join includes the rows of either table that don’t have a matching value.

Inner join is used to include only rows of both tables that have matching values.  Unmatched rows are excluded and therefore those rows are not returned.

Outer Join —– Left, Right and Full

An outer join occurs when matching and no matching rows of either or both tables are contained in the join.  There are three kinds of outer joins:

Left outer join:  All matched and unmatched rows of the first table and matched rows of the second table are included in the join.

Right outer join:  Matched rows of the first table and matched and unmatched rows of the second table are included in the join.

Full outer join:  Matched and unmatched rows of both tables are included in the join.

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)    )  )