exceptions

SQLCODE and SQLERRM

What are SQLCODE and SQLERRM?

SQLCODE and SQLERRM are Oracle’s built-in error reporting functions in PL/SQL. When an error occurs in PL/SQL at runtime: SQLCODE returns the number of the last encountered error. SQLERRM returns the  message associated with its error-number argument. The error-number argument is optional: if omitted, SQLERRM returns the message associated with the current value of SQLCODE.

Read the Full Article →
Check if JVM is Installed in Oracle

How to Check if JVM is Installed in Oracle

I recently thought of using Java’s built-in BigInteger.gcd() method inside a PL/SQL function wrapper to calculate GCD in SQLs. And so I wrote the Java code and ran it on Oracle XE. Things didn’t go as planned.

Read the Full Article →
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 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 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 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 →
UTL_FILE: Simple write to file example

UTL_FILE: Simple Write To File Example and Debugging Common Errors

A simple example of using UTL_FILE in PLSQL to create a file and write into it. Plus, the common ORA errors you encounter with the use of UTL_FILE, and how to resolve them.

Read the Full Article →
Why no ORA-00904: invalid identifier

The Curious Case of The Missing ORA-00904

Here’s a puzzle for you to solve. You’re given these two tables – empl and dept – in which column emp_dept_id of table empl references dept_id of table dept. Note that the foreign key column names in the two tables is not identical. It’s called dept_id in table dept, emp_dept_id in table empl.

Read the Full Article →
Referencing User Defined Types over DBLink

Referencing User Defined Types over DBLink: Problem and Alternatives

Consider a PL/SQL call to a function which returns a user-defined type (UDT) – say, a SQL Type object. This works well as long as the called function resides in the same database as the calling PL/SQL, but if the called function is in a remote database, the PL/SQL call via DBLink end ups with […]

Read the Full Article →