Joins

For those new to SQL, terms like INNER JOIN and OUTER JOIN can seem like fearsome foes. As the wise say, understanding conquers fear. Behind those geeky terms lie concepts rooted in simple real-world knowledge.

Here’s a quickstart guide to these two basic joins in SQL: INNER JOIN and OUTER JOIN.

Continue Reading …

{ 0 comments }

Grant Select on V$ Views

When querying v$instance or v$session[1] as a non-admin user, you might come across this error:

SQL> select version
  2  from v$instance;
from v$instance
     *
ERROR at line 2:
ORA-00942: table or view does not exist

The error suggests that the non-admin user does not have the SELECT privilege on the sys-owned v$ view. On the face of it, the fix appears as simple as logging in as sys and granting select on the v$ view to the user.

Appearances are deceptive, they say. Try that and you will get an ORA-02030 error.

Continue Reading …

{ 0 comments }

Thumbnail image for How to Find out Your Oracle Database Version

How to Find out Your Oracle Database Version

Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your database version. Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.

Read the Full Article →
Thumbnail image for Access Control List (ACL) in Oracle 11G

Access Control List (ACL) in Oracle 11G

I recently upgraded Oracle XE from 10G to 11G, and found that none of the PL/SQL code using UTL_HTTP was working after upgrade. The code failed with the error: Oracle 10G used to be happy as long as the user running network packages like UTL_HTTP had execute permission on the package. Oracle 11G and above […]

Read the Full Article →
Thumbnail image for UNPIVOT in Oracle 11G to Select Columns As Rows

UNPIVOT in Oracle 11G to Select Columns As Rows

Sometimes we need to transpose columns as rows in SQL. Oracle 11G onwards, this requirement can be easily implemented using the UNPIVOT clause. A typical scenario:

Read the Full Article →
Thumbnail image for SQL to Select Rows Conditionally Based On Column Value

SQL to Select Rows Conditionally Based On Column Value

Let’s say a table contains multiple rows for an id. The requirement is to select only one of those rows, based on the value in a "type" column which determines the row’s priority. A typical example is selecting one contact number for a customer, based on contact types.

Read the Full Article →
Thumbnail image for ORA-12560: TNS:protocol adapter error

ORA-12560: TNS:protocol adapter error

Sometimes you try to login to your Oracle XE on your home computer, and get this curt blocking response: Here’s the top reason why it might happen and how to fix it.

Read the Full Article →
Thumbnail image for ORA-01450: Maximum Key Length Exceeded – Possible Causes and Fix

ORA-01450: Maximum Key Length Exceeded – Possible Causes and Fix

ORA-01450 might crop up when a table index is being created in the database: ORA-01450: maximum key length (6398) exceeded Oracle documentation has this to say about the error: Cause: The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. The maximum index length varies by operating […]

Read the Full Article →
Thumbnail image for I want to learn Oracle. Where should I begin?

I want to learn Oracle. Where should I begin?

Here is a simple guide to learning Oracle as a developer from scratch, on your own. For an absolute beginner to Oracle, it helps you navigate your way around the sea of information available at your disposal.

Read the Full Article →
Thumbnail image for What are CBO and RBO?

What are CBO and RBO?

In large applications that juggle tons of data, it isn’t enough to get a query to run without errors. The query must also perform well – get the results in fastest possible time, using the least possible resources. This is where Oracle’s inbuilt optimizers come into play. Till Oracle 9i, Oracle server provided the options […]

Read the Full Article →