Thumbnail image for Grant SELECT on V$ Views

Grant SELECT on V$ Views

When querying v$instance or v$session[1] as a non-admin user, you might come across this error: 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 […]

Read the Full Article →
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

When the need is to transpose columns as rows, Oracle 11G+ enables you to easily implement this using the UNPIVOT clause is SQL. A typical scenario:

Read the Full Article →
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 →
Thumbnail image for UTL_HTTP to Call a Web Service from PL/SQL

UTL_HTTP to Call a Web Service from PL/SQL

You can call a web service from within PL/SQL using the Oracle-supplied package UTL_HTTP. Here’s a demo of calling a public web service that returns latest city weather by ZIP.

Read the Full Article →