ORACLE- Difference between Date format ‘DD-Mon-YY’ and ‘DD-Mon-RR’

The ‘DD-Mon-YY’ format always specifies the two digit year as the current century. So when we specify ’25-May-08’ then the year is translate as 2008.

The ‘DD-Mon-RR’ format is the default format in ORACLE databases. Here the two digit year will be translated as below.

		If the specified two-digit year is:
		00-49	50-99
If two digits of the current year are:	00-49	The return date is in the current century	The return date is in the century before the current one
	50-99	The return date is in the century after the current one	The return date is in the current century

Example:

CREATE TABLE test_date(
colA DATE,
colB NUMBER);

INSERT INTO test_date
VALUES (TO_DATE('22-Dec-98','DD-Mon-YY'),4);

COMMIT;

INSERT INTO test_date
VALUES (TO_DATE('22-Dec-98','DD-Mon-RR'),5);
COMMIT;

So when we use formats for date in ORACLE databases we need to make sure that we are using the correct format.

ORACLE - ROW_NUMBER, FIRST_VALUE, LAST_VALUE

In this post I’ll explain the usage of the SQL functions ROW_NUMBER, FIRST_VALUE and LAST_VALUE.

ROW_NUMBER

It assigns a unique number to each row to which it is applied, in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

Syntax:

ROW_NUMBER( )
   OVER ([ query_partition_clause ] order_by_clause)

FIRST_VALUE

It returns the first value in an ordered set of values. If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS.If you specify IGNORE NULLS, then FIRST_VALUE returns the fist non-null value in the set, or NULL if all values are null.

Syntax:

FIRST_VALUE (expr [ IGNORE NULLS ])
   OVER (analytic_clause)

LAST_VALUE

It returns the last value in an ordered set of values. If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS. If you specify IGNORE NULLS, then LAST_VALUE returns the fist non-null value in the set, or NULL if all values are null.

Syntax:

LAST_VALUE(expr [ IGNORE NULLS ])
   OVER (analytic_clause)

Examples:

CREATE TABLE emp (
employee_id INTEGER
CONSTRAINT am_employees_pk PRIMARY KEY,
manager_id INTEGER,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
title VARCHAR2(20),
salary NUMBER(6, 0)
);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
1, NULL, 'James', 'Smith', 'CEO', 800000
);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
2, 1, 'Ron', 'Johnson', 'Sales Manager', 600000
);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
3, 2, 'Fred', 'Hobbs', 'Salesperson', 150000
);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
4, 2, 'Susan', 'Jones', 'Salesperson', 500000
);
-- commit the transaction
COMMIT;

• Simple query with ROW_NUMBER function.

• Simple query with FIRST_VALUE function

The above query shows the ‘Highest Salary’ of employees who are managed by the same manager.

• Simple query with the LAST_VALUE function

The above query shows the ‘Lowest Salary’ of employees who are managed by the same manager.

ROWNUM Pseudocolumn

It returns a number indicating the order in which Oracle selects the row from a table or set of joined rows.

Consider the following example.

CREATE TABLE test_rownum(
colA VARCHAR2(10),
colB NUMBER,
colC NUMBER);
INSERT INTO test_rownum
VALUES('a1',100,124);
INSERT INTO test_rownum
VALUES('a2',110,99);
INSERT INTO test_rownum
VALUES('a3',111,211);
INSERT INTO test_rownum
VALUES('a4',109,254);
INSERT INTO test_rownum
VALUES('a5',142,200);
COMMIT;

SELECT *
FROM test_rownum t
WHERE rownum <= 3;

COLA COLB COLC

---------- ---------- ----------

a1 100 124

a2 110 99

a3 111 211

In the above query returns three records from that table.

Lets check another query with the order by conditions.

SELECT *
FROM test_rownum t
WHERE rownum <= 3
ORDER BY t.colc;

COLA COLB COLC

---------- ---------- ----------

a2 110 99

a1 100 124

a3 111 211

In this case the ORDER BY clause is not worked as we expect. In this case ORACLE retrieved three rows and ordered those rows using ‘colC’.

If we want to apply the rownum condition after applying ‘ORDER BY’ condition we need to embedded the ‘ORDER BY’ clause in a subquery.

Following is the rearranged query to retrieve the rows ordered by colC.

SELECT *
FROM (SELECT *
FROM test_rownum
ORDER BY colC)
WHERE rownum <= 3;

COLA COLB COLC

---------- ---------- ----------

a2 110 99

a1 100 124

a5 142 200

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

ALTER TABLE test_rownum
ADD (colD NUMBER);

UPDATE test_rownum
SET colD = rownum;

SELECT *
FROM test_rownum;

COLA COLB COLC COLD

---------- ---------- ---------- --------

a1 100 124 1

a2 110 99 2

a3 111 211 3

a4 109 254 4

a5 142 200 5

ORACLE - Setting NLS parameters and their Priorities

Setting NLS Parameters

NLS (National Language Support) parameters determine the locale-specific behavior on both the client and the server. NLS parameters can be specified in the following ways:

v As initialization parameters on the server

You can include parameters in the initialization parameter file to specify a default session NLS environment. These settings have no effect on the client side; they control only the server's behavior. For example:

NLS_TERRITORY = "CZECH REPUBLIC"

v As environment variables on the client

You can use NLS environment variables, which may be platform-dependent, to specify locale-dependent behavior for the client and also to override the default values set for the session in the initialization parameter file. For example, on a UNIX system:

% setenv NLS_SORT FRENCH

v With the ALTER SESSION statement

You can use NLS parameters that are set in an ALTER SESSION statement to override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.

ALTER SESSION SET NLS_SORT = FRENCH;

v In SQL functions

You can use NLS parameters explicitly to hardcode NLS behavior within a SQL function. This practice overrides the default values that are set for the session in the initialization parameter file, set for the client with environment variables, or set for the session by the ALTER SESSION statement. For example:

TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')

Methods of Setting NLS Parameters and Their Priorities

Priority	Method
1 (highest)	Explicitly set in SQL functions
2	Set by an ALTER SESSION statement
3	Set as an environment variable
4	Specified in the initialization parameter file

NLS Data Dictionary Views

We can check the session, instance, and database NLS parameters by querying the following data dictionary views:

v NLS_SESSION_PARAMETERS shows the NLS parameters and their values for the session that is querying the view. It does not show information about the character set.

v NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.

v NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the database. The values are stored in the database.

ORACLE SQL – Difference between VARCHAR2(5 byte) and VARCHAR2(5)

Database version: 10g Release2

VARCHAR2(5 byte) -- You can store up to 5 bytes

VARCHAR2(5) -- You can store up to 5 characters

But we need to be careful when we try to store multi-byte characters like Japanese characters. Here each character needs more than one byte to store in the database.

When we use VARCHAR2(5 byte) definition and if the Japanese character takes 3 bytes then we can save only one character to that column.

When we use VARCHAR2(5) definition and if the Japanese character takes 3 bytes still we can save only one character to that column.

CREATE TABLE test_varchar(
colA VARCHAR2(5),
colB VARCHAR2(5 byte));
Table created

INSERT INTO test_varchar
VALUES ('aaaaa','bbbbb');

1 row inserted

INSERT INTO test_varchar
VALUES ('aaaaa','気木機能性');

ORA-12899: value too large for column "TEST_VARCHAR"."COLB" (actual: 15, maximum: 5)

INSERT INTO test_varchar
VALUES ('気木機能性', 'aaaaa');

ORA-12899: value too large for column "TEST_VARCHAR"."COLA" (actual: 15, maximum: 5)

When we used the “VARCHAR2(5)” definition we receive the “Value too large” error because by default, the character datatypes (CHAR and VARCHAR2) are specified in bytes, not characters. Hence, the specification VARCHAR2(5)in a table definition allows 5 bytes for storing character data.

How we can overcome this issue?

We can overcome this problem by switching to character semantics when defining the column size.

NLS_LENGTH_SEMANTICS enables you to create CHAR, VARCHAR2, and LONG columns using either byte or character length semantics. NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. Existing columns are not affected.

We can set the NLS_LENGTH_SEMANTICS as CHAR.

Now consider the following example.

ALTER SESSION
SET NLS_LENGTH_SEMANTICS = 'CHAR';

CREATE TABLE test_varchar2(
colC VARCHAR2(5),
colD VARCHAR2(5 byte));
Table created

INSERT INTO test_varchar2
VALUES ('aaaaa','bbbbb');

1 row inserted

INSERT INTO test_varchar2
VALUES ('気木機能性', 'aaaaa');

1 row inserted

Now we can successfully save 5 multi-byte characters into the colC in test_varchar2 table.

ORACLE- Useful SQL REGEX operators and Metacharacters

Anchoring Metacharacters

Metacharacter	Description
^	Anchor the expression to the start of a line
$	Anchor the expression to the end of a line

Quantifiers, or Repetition Operators

Quantifier	Description
*	Match 0 or more times
?	Match 0 or 1 time
+	Match 1 or more times
{m}	Match exactly m times
{m,}	Match at least m times
{m, n}	Match at least m times but no more than n times

Predefined POSIX Character Classes

Character Class	Description
[:alpha:]	Alphabetic characters
[:lower:]	Lowercase alphabetic characters
[:upper:]	Uppercase alphabetic characters
[:digit:]	Numeric digits
[:alnum:]	Alphanumeric characters
[:space:]	Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
[:punct:]	Punctuation characters
[:cntrl:]	Control characters (nonprinting)
[:print:]	Printable characters

Alternate Matching and Grouping of Expressions

Metacharacter		Description
|	Alternation	Separates alternates, often used with grouping operator ()
( )	Group	Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing
[char]	Character list	Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters

ORACLE – PLSQL- Autonomous Transactions

Oracle version: 10g and higher versions

Autonomous transactions are useful for implementing actions that need to be performed independently, regardless of whether the calling transaction commits or rolls back, such as transaction logging and retry counters.

Use the pragma AUTONOMOUS_TRANSACTION.

Example:.

SQL> CREATE TABLE test_autonomous (

colA VARCHAR2(100) );

Table created

SQL> CREATE OR REPLACE PROCEDURE Test_Auto IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO test_autonomous
VALUES('Autonomous Transaction');
COMMIT;
END Test_Auto;
/

Procedure created

SQL> SELECT *
FROM test_autonomous;

No records selected.

SQL> DECLARE
BEGIN
INSERT INTO test_autonomous
VALUES('Main Transaction');
-- call the autonomous transaction
Test_Auto();
-- rollback the main transaction
ROLLBACK;
END;
/

PL/SQL procedure successfully completed

SQL> SELECT *
FROM test_autonomous;

COLA
------------------------------------------------
Autonomous Transaction

According to the above example the autonomous transaction is not affected by the main transaction’s ROLLBACK.

When to use Autonomous Transactions?

• Logging mechanism

you need to log an error to your database log table. On the other hand, you need to roll back your core transaction because of the error. And you don't want to roll back over other log entries.

• Commits and rollbacks in your database triggers

If you define a trigger as an autonomous transaction, then you can commit and/or roll back in that code.

• Retry counter

Suppose that you want to let a user try to get access to a resource N times before an outright rejection; you also want to keep track of attempts between connections to the database. This persistence requires a COMMIT, but one that should remain independent of the transaction.

• Software usage meter

You want to keep track of how often a program is called during an application session. This information is not dependent on, and cannot affect, the transaction being processed in the application.