Before updating a row in a database table, you might want to check for concurrency conflict – another transaction should not be updating the same row simultaneously.
This can be achieved by locking the row in Oracle before update. Here’s how.
ORA Errors
Oracle database errors – probable causes, how to resolve them or work around them.
JSON Conditionals: JSON_EXISTS, JSON_TEXTCONTAINS
In the last two articles, we saw the means and reasons for storing JSON data in Oracle and ways of retrieving JSON data from Oracle. In this article, we will explore ways of implementing true/false tests on JSON data using conditionals: JSON_EXISTS, JSON_TEXTCONTAINS.
JSON conditionals check for the existence of specified paths/values within JSON documents. They are typically applied as row filters in the SQL WHERE clause.
DBMS_REDEFINITION: Convert Non-Partitioned Table to Partitioned [pre 12.2]
Oracle 12.2 has introduced a number of new features that ease partitioning, the most-awaited perhaps is the ALTER TABLE MODIFY syntax to convert a non-partitioned table to partitioned.
What do you do if your database version is pre-12.2? Partitioning a non-partitioned table in pre-12.2 databases is trickier, not impossible. Try DBMS_REDEFINITION: here’s how.
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.
How to Check if JVM is Installed in Oracle
I recently thought of using a Java method in PL/SQL. And so I wrote the Java stored procedure and ran it on Oracle XE.
Things didn’t go as planned.
DATE Format SQL for Dates Stored as Strings
It is well-acknowledged that attributes in the database should use the correct datatypes (numbers should go into NUMBER columns, dates into DATE columns, etc). Storing date values in VARCHAR2 columns is an open invitation for bugs and issues due to date format such as the one I’m about to describe. Besides, using string-type columns to represent dates rules out your ability to build upon features like range interval partitioning. Yes – ideally you should never have to convert VARCHAR2 to date.
It is equally well-acknowledged that we do not live in an ideal world. In some situations, we do not control the database design; we have to live with what exists and provide a solution to the problem.
This article addresses one such problem — how to compare two dates when they are stored as strings with different DATE formats.
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: log in as sys and grant select on v$ views to the user.
Appearances are deceptive, they say. Try that and you will get an ORA-02030 error.
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:
declare * ERROR at line 1: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 47
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 are not so easy to please (and rightly so!) — they enforce extra security, which means you need more access control configuration to get this working.
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 system.
The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns.
Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22. Numeric length = (precision/2) + 1. If negative, add +1.
Action: Select columns to be indexed so the total index length does not exceed the maximum index length for the operating system.
The action suggests choosing index columns differently so as to remain within the index length limit.
Things are not always so simple though. When faced with this error while installing Oracle’s standard products such as FMW components or OBIEE, it is not in one’s hands to follow this advice and fiddle with the index columns.
How does one fix this error, then? This post suggests possible root causes and solution for ORA-01450 when changing the index itself is not a viable option.