REGEXP_SUBSTR extract everything before specific character

Tags

before, regex, regexp_substr, string, substr

One of the frequently asked questions about REGEXP in Oracle SQL are like “How to extract string before specific character?”. Let’s look at the following simple example:

Requirement : Extract everything after dot from string ‘123abc.xyz456‘ i.e. output should be ‘123abc‘.

SQL> WITH data AS
2 (
3    SELECT '123abc.xyz456' str FROM dual
4 )
5 SELECT str,
6        REGEXP_SUBSTR(str, '[^.]+') new_str
7 FROM data;

STR           NEW_STR
------------- -------------
123abc.xyz456 123abc

How it works :

• [^.] negated character class to match anything except for a dot
• + quantifier to match one or more of these

Hope it helps!

Docker – Oracle 12c EE on OL7

Tags

Docker, docker container, oracle docker, oracle docker container, oracle12c, oracle12c-ee

This blog post is about quickly setting up an Oracle 12c Enterprise Edition database on Oracle Linux 7 operating system in Docker.

Please note that this Docker container provided by Oracle is not persistent image, in other words it is not persistent storage for the database. That means if you drop the container the database is lost too.

Setup:

• OS Platform            : Oracle Linux Server 7.4
• Database version    : Oracle 12.2.0.1 Enterprise Edition
• Docker version        : 17.03.1-ce, build 276fd32

If you are on Windows host, install Virtual Box and then install Oracle Linux 7 as guest OS in virtual box. Remember, Docker commands will run under root user, else use sudo or add another user to the “docker” group to enable the user to use docker commands with sudo.

Let’s start:

1. Install docker:

yum install docker-engine

Let’s check the Docker version:

2. Install Git:

yum install git

3. Clone the Oracle docker images from GitHub:

git clone https://github.com/oracle/docker-images.git

4. Copy the Oracle Linux 12c database zip to docker-images/OracleDatabase/12.2.0.1

5. Go to this directory:

cd docker-images/OracleDatabase

6. Execute the buildDockerImage.sh script:

./buildDockerImage.sh -v 12.2.0.1 -e

It will create the required docker images for oracle and do complete database software installation.

7. Database creation:

sudo docker run --name orclcdb \
-p 1521:1521 -p 5500:5500 \
-e ORACLE_SID=ORCLCDB \
-e ORACLE_PDB=ORCLPDB1 \
-e ORACLE_PWD=oracle \
oracle/database:12.2.0.1-ee

8. Start the Docker container:

docker start orclcdb

Clean start looks like this:

8. Start a Bash Shell to Connect to Docker Container:

docker exec –it orclcdb bash

Bash prompt inside container looks like this:

Let’s check the Listener status:

9. Check the container logs:

docker logs orclcdb

Log looks like this:

There it is, in the end of log – “DATABASE IS READY TO USE!” Less than 10 steps.. Awesome! Isn’t it?

Hope it helps!

REGEXP_SUBSTR extract everything after specific character

Tags

after, regex, regexp_substr, string, substr

One of the frequently asked questions about REGEXP in Oracle SQL are like “How to extract string after specific character?”. Let’s look at the following simple example:

Requirement : Extract everything after dot from string ‘123abc.xyz456‘ i.e. output should be ‘xyz456‘.

SQL> WITH data AS
2 (
3    SELECT '123abc.xyz456' str FROM dual
4 )
5 SELECT str,
6        REGEXP_SUBSTR(str, '[^.]+$') new_str
7 FROM data;

STR           NEW_STR
------------- -------------
123abc.xyz456 xyz456

How it works :

• [^.] negated character class to match anything except for a dot
• + quantifier to match one or more of these
• $ anchor to restrict matches to the end of the string

Hope it helps!

ORA-12578: TNS:wallet open failed

Tags

ORA-12578, ORA-12578: TNS:wallet open failed, TNS, TNS:wallet open failed, wallet

If you get the following error message:

ORA-12578: TNS:wallet open failed

then edit your sqlnet.ora file and modify SQLNET.WALLET_OVERRIDE parameter value from TRUE to FALSE.

Change this:

SQLNET.WALLET_OVERRIDE = TRUE

to this:

SQLNET.WALLET_OVERRIDE = FALSE

and try again.

 

Hope it helps!

sqlplus / as sysdba : ORA-01031: insufficient privileges

Tags

insufficient privileges, ORA-01031, ORA-01031: insufficient privileges, sqlplus / as sysdba

ERROR:
ORA-01031: insufficient privileges

If you get the above error with / as sysdba i.e. when you are trying to connect to LOCAL instance as specified in the environment variables ORACLE_HOME and ORACLE_SID, then thee is something wrong at OS level.

One might argue that all the environment variables are perfectly set and they are logged in as oracle user, however, the issue still occurs.

Most common issue is that the oracle owner(usually oracle user) is not the part of the dba group in Linux or ORA_DBA group in Windows.

Check:

-sh-4.1$ cat /etc/group | grep dba
dba:x:8500:lalit,optena,aime

If you do not find oracle to be part of the dba group, then login as “root” user and modify the /etc/group file to add oracle user to the dba group.

-sh-4.1$ sudo su root
Password:
[root@lalit bin]# vi /etc/group

Add oracle to the dba group as comma separated users list. Save and exit ESC + colon + wq!

Verify your changes:

-sh-4.1$ cat /etc/group | grep dba
dba:x:8500:oracle,lalit,optena,aime

Now try connecting sys / as sysdba:

export ORACLE_HOME=<dbhome directory>
export ORACLE_SID=<sid name>
sqlplus / as sysdba

The best way to quickly find out the ORACLE_HOME and ORACLE_SID environment variables is see the entry in /etc/oratab:

cat /etc/oratab

Hopefully you should be able to connect to the local instance without ORA-01031.

Hope it helps!

ORA-12547: TNS :lost contact when try to connect to Oracle

Tags

ORA-12547, oracle tns connection error, TNS, tns lost contact

ERROR:
ORA-12547: TNS :lost contact when try to connect to Oracle

There could be many reasons for the above error, but one of the common issue is due to incorrect permissions to the oracle binaries in the $ORACLE_HOME/bin directory.

The “root.sh” is responsible to give the correct permissions to the oracle binaries during installation using Oracle Universal Installer (OUI). It is a mandatory step to execute the script as “root” user during installation. But later if you have messed up the permissions of the oracle binaries then you could execute it again and it should mostly fix the issue.

Check:

-sh-4.1$ ls -lrt oracle
-rwsr-s--x 1 oracle oinstall 228881982 Nov  2 11:19 oracle

The permissions should be -rwsr-s–x

If not, then change permission as below:

-sh-4.1$ cd $ORACLE_HOME/bin
-sh-4.1$ chmod 6751 oracle
-sh-4.1$ ls -lrt oracle

Try connecting now, hopefully ORA-12547 shouldn’t occur again. If the error still occurs, try executing “root.sh” in $ORACLE_HOME/bin.

Hope it helps!

root.sh : Enter the full pathname of the local bin directory

Tags

local bin directory, oracle universal installer, oui, pathname, root, root.sh, script

While installing Oracle using Oracle Universal Installer (OUI), there are few scripts which needs to be run as “root” user in the OS. One such script is the “root.sh” which resides in $ORACLE_HOME.

-sh-4.1$ cd $ORACLE_HOME
-sh-4.1$ ls -lrt root.sh
-rwxr-x--- 1 oracle oinstall 542 Nov  2 11:19 root.sh

A lot of people are stuck at the point where it prompts to enter the full pathname of the local bin directory. The /usr/local/bin directory is actually a symbolic link to ./packages/local/bin

-sh-4.1$ ls -lrt bin
lrwxrwxrwx. 1 root root 20 Oct 29 16:54 bin -> ./packages/local/bin

Resolution:

Provide the full pathname /usr/local/.packages/local/bin

Enter the full pathname of the local bin directory: [/usr/local/bin]: /usr/local/.packages/local/bin
Creating /usr/local/.packages/local/bin directory...
Copying dbhome to /usr/local/.packages/local/bin ...
Copying oraenv to /usr/local/.packages/local/bin ...
Copying coraenv to /usr/local/.packages/local/bin ...

Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

Hope it helps!

Oracle String Aggregation Techniques

Tags

aggregation, listagg, model clause, row_number, SQL, string aggregation, sys_connect_by_path

This is a frequently asked question “How to display multiple rows in a single row as a delimited string”. It is basically string aggregation.

I will demonstrate the various techniques of string aggregation in Oracle SQL using the standard SCOTT.EMP table.

** LISTAGG enhancements in 12.2 release explained in the end.

• Oracle database 9i and up:

ROW_NUMBER() and SYS_CONNECT_BY_PATH

SQL> SELECT deptno,
  2         LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
  3            KEEP (DENSE_RANK LAST ORDER BY rn),',') AS emp_name
  4  FROM
  5    (SELECT deptno,
  6      ename,
  7      ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename)    rn,
  8      ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 rn_lag
  9    FROM emp
 10    )
 11  GROUP BY deptno
 12    CONNECT BY rn_lag = PRIOR rn
 13  AND deptno        = PRIOR deptno
 14    START WITH rn = 1
 15  ORDER BY deptno;

    DEPTNO EMP_NAME
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

• Oracle database 10g and up:

MODEL clause

SQL> select deptno
  2       , rtrim(ename,',') enames
  3  from ( select deptno
  4                , ename
  5                , rn
  6  from emp
  7  model
  8       partition by (deptno)
  9       dimension by (row_number() over
 10                    (partition by deptno order by ename) rn
 11                    )
 12       measures     (cast(ename as varchar2(40)) ename)
 13       rules
 14       ( ename[any] order by rn desc = ename[cv()]||','||ename[cv()+1]
 15         )
 16         )
 17   where rn = 1
 18   order by deptno
 19  /

    DEPTNO ENAMES
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

• Oracle database 11g Release 2 and up:

LISTAGG

SQL> SELECT deptno,
  2         LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS emp_name
  3  FROM   emp
  4  GROUP BY deptno
  5  ORDER BY deptno;

    DEPTNO EMP_NAME
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

• LISTAGG Function Enhancements in Oracle Database 12c Release 2 (12.2)

Prior to 12.2, when the concatenation exceeds the limit of return datatype of LISTAGG, it throws following error:

ORA-01489: result of string concatenation is too long

In 12.2, the default behavior is similar to following clause:

ON OVERFLOW ERROR

To handle the overflow error gracefully, you could use following clause:

ON OVERFLOW TRUNCATE

For example,

SELECT deptno,
LISTAGG(ename, ',' ON OVERFLOW TRUNCATE '~~~') WITHIN GROUP (ORDER BY ename)

 
You could remove the COUNT by using following clause:

WITHOUT COUNT

For example,

SELECT deptno,
LISTAGG(ename, ',' ON OVERFLOW TRUNCATE '~~~' WITHOUT COUNT) WITHIN GROUP (ORDER BY ename)

Hope it helps!

 

Outer-joins Oracle vs ANSI syntax

Tags

+, ansi syntax, join, operator, oracle, oracle syntax, outer join, SQL

this is one of the frequently asked questions about the use of “+” operator in Oracle join syntax. For beginners it is usually a confusion to interpret the different syntax.

One of the good reasons to use ANSI syntax over the old Oracle join syntax is that, there are nil chances of accidentally creating a cartesian product. With more number of tables, there is a chance to miss an implicit join with older Oracle join syntax, however, with ANSI syntax you cannot miss any join as you must explicitly mention them.

Difference between Oracle outer join syntax and the ANSI/ISO Syntax.

LEFT OUTER JOIN –

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id = d.department_id(+);

SELECT e.last_name,
  d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

RIGHT OUTER JOIN –

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id(+) = d.department_id;

SELECT e.last_name,
  d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

FULL OUTER JOIN –

Before the native support of hash full outerjoin in 11gR1, Oracle would internally convert the FULL OUTER JOIN the following way –

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id = d.department_id(+)
UNION ALL
SELECT NULL,
  d.department_name
FROM departments d
WHERE NOT EXISTS
  (SELECT 1 FROM employees e WHERE e.department_id = d.department_id
  );

SELECT e.last_name,
  d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);

Hope it helps!

Find range of missing values in a sequence of numbers or dates

Tags

consecutive values, dates, gaps, gaps and islands, islands, numbers, oracle, range, sequence, series, SQL

One of the frequently asked questions is about Gaps and Islands problems which involve finding a range of missing values (gaps) or a range of consecutive values (islands) in a sequence of numbers or dates.

Let’s see how to find the range of missing values in a sequence of numbers or dates.

NUMBERS:

SQL> WITH DATA(num) AS(
  2  SELECT  1 FROM dual UNION
  3  SELECT  2 FROM dual UNION
  4  SELECT  3 FROM dual UNION
  5  SELECT  5 FROM dual UNION
  6  SELECT  6 FROM dual UNION
  7  SELECT  7 FROM dual UNION
  8  SELECT 10 FROM dual UNION
  9  SELECT 11 FROM dual UNION
 10  SELECT 12 FROM dual UNION
 11  SELECT 13 FROM dual UNION
 12  SELECT 20 FROM dual
 13  )
 14  SELECT MIN(missing_numbers),
 15         MAX(missing_numbers)
 16  FROM
 17    (SELECT missing_numbers,
 18      missing_numbers - row_number() OVER(ORDER BY missing_numbers) rn
 19    FROM
 20      (SELECT min_num - 1 + LEVEL missing_numbers
 21      FROM
 22        ( SELECT MIN(num) min_num , MAX(num) max_num FROM data
 23        )
 24        CONNECT BY level <= max_num - min_num + 1
 25      MINUS
 26      SELECT num FROM DATA
 27      )
 28    )
 29  GROUP BY rn
 30  ORDER BY rn;

MIN(MISSING_NUMBERS) MAX(MISSING_NUMBERS)
-------------------- --------------------
                   4                    4
                   8                    9
                  14                   19

SQL>

DATES:

SQL> WITH DATA(dates) AS(
  2  SELECT  DATE '2015-01-01' FROM dual UNION
  3  SELECT  DATE '2015-01-02' FROM dual UNION
  4  SELECT  DATE '2015-01-03' FROM dual UNION
  5  SELECT  DATE '2015-01-05' FROM dual UNION
  6  SELECT  DATE '2015-01-06' FROM dual UNION
  7  SELECT  DATE '2015-01-07' FROM dual UNION
  8  SELECT  DATE '2015-01-10' FROM dual UNION
  9  SELECT  DATE '2015-01-11' FROM dual UNION
 10  SELECT  DATE '2015-01-12' FROM dual UNION
 11  SELECT  DATE '2015-01-13' FROM dual UNION
 12  SELECT  DATE '2015-01-20' FROM dual
 13  )
 14  SELECT MIN(missing_dates),
 15         MAX(missing_dates)
 16  FROM
 17    (SELECT missing_dates,
 18   missing_dates - row_number() OVER(ORDER BY missing_dates) rn
 19    FROM
 20   (SELECT min_date - 1 + LEVEL missing_dates
 21   FROM
 22  ( SELECT MIN(dates) min_date , MAX(dates) max_date FROM data
 23  )
 24  CONNECT BY level <= max_date - min_date + 1
 25   MINUS
 26   SELECT dates FROM DATA
 27   )
 28    )
 29  GROUP BY rn
 30  ORDER BY rn;

MIN(MISSING_DATES) MAX(MISSING_DATES)
------------------ ------------------
2015-01-04         2015-01-04
2015-01-08         2015-01-09
2015-01-14         2015-01-19

SQL>

Hope it helps!

Find range of consecutive values in a sequence of numbers or dates

Tags

dates, gaps, gaps and islands, islands, numbers, oracle, range, sequence, series, SQL

One of the frequently asked questions is about Gaps and Islands problems which involve finding a range of missing values (gaps) or a range of consecutive values (islands) in a sequence of numbers or dates.

Let’s see how to find the range of consecutive values in a sequence of numbers or dates.

NUMBERS:

SQL> WITH DATA(num) AS(
  2  SELECT  1 FROM dual UNION
  3  SELECT  2 FROM dual UNION
  4  SELECT  3 FROM dual UNION
  5  SELECT  5 FROM dual UNION
  6  SELECT  6 FROM dual UNION
  7  SELECT  7 FROM dual UNION
  8  SELECT 10 FROM dual UNION
  9  SELECT 11 FROM dual UNION
 10  SELECT 12 FROM dual UNION
 11  SELECT 13 FROM dual UNION
 12  SELECT 20 FROM dual
 13  )
 14  SELECT MIN(num),
 15         MAX(num)
 16  FROM
 17    ( SELECT num, num - row_number() OVER(ORDER BY num) rn
 18      FROM DATA
 19    )
 20  GROUP BY rn
 21  ORDER BY rn;

  MIN(NUM)   MAX(NUM)
---------- ----------
         1          3
         5          7
        10         13
        20         20

SQL>

DATES:

SQL> WITH DATA(dates) AS(
  2  SELECT  DATE '2015-01-01' FROM dual UNION
  3  SELECT  DATE '2015-01-02' FROM dual UNION
  4  SELECT  DATE '2015-01-03' FROM dual UNION
  5  SELECT  DATE '2015-01-05' FROM dual UNION
  6  SELECT  DATE '2015-01-06' FROM dual UNION
  7  SELECT  DATE '2015-01-07' FROM dual UNION
  8  SELECT  DATE '2015-01-10' FROM dual UNION
  9  SELECT  DATE '2015-01-11' FROM dual UNION
 10  SELECT  DATE '2015-01-12' FROM dual UNION
 11  SELECT  DATE '2015-01-13' FROM dual UNION
 12  SELECT  DATE '2015-01-20' FROM dual
 13  )
 14  SELECT MIN(dates),
 15         MAX(dates)
 16  FROM
 17    ( SELECT dates, dates - row_number() OVER(ORDER BY dates) rn
 18      FROM DATA
 19    )
 20  GROUP BY rn
 21  ORDER BY rn;

MIN(DATES MAX(DATES
--------- ---------
01-JAN-15 03-JAN-15
05-JAN-15 07-JAN-15
10-JAN-15 13-JAN-15
20-JAN-15 20-JAN-15

SQL>

Hope it helps!

UTL_MATCH string comparision technique in Oracle

Tags

compare strings, data entry, edit_distance, edit_distance_similarility, jaro_winkler, jaro_winkler_similarity, match strings, string difference, string similarity, utl_match

The UTL_MATCH package facilitates matching two records. It comes very handy for data entry validation and string matching.

Setup

DROP TABLE t_utl_match;

CREATE TABLE t_utl_match (
id   NUMBER,
col1 VARCHAR2(15),
col2 VARCHAR2(15))
);

INSERT INTO t_utl_match VALUES (1, 'Dunningham', 'Cunnigham');
INSERT INTO t_utl_match VALUES (2, 'Abroms', 'Abrams');
INSERT INTO t_utl_match VALUES (3, 'Lampley', 'Campley');
INSERT INTO t_utl_match VALUES (4, 'Marhta', 'Martha');
INSERT INTO t_utl_match VALUES (5, 'Jonathon', 'Jonathan');
INSERT INTO t_utl_match VALUES (6, 'Jeraldine', 'Geraldine');
INSERT INTO t_utl_match VALUES (7, 'Bat Man', 'Cat Woman');
COMMIT;

UTL_MATCH Subprograms

• EDIT_DISTANCE Function

a.k.a. Levenshtein Distance, calculates the number of changes required to transform string-1 into string-2

For example,

SQL> SELECT id,
  2         text1,
  3         text2,
  4         UTL_MATCH.edit_distance(text1, text2) AS ed
  5  FROM   t_utl_match
  6  ORDER BY id;

        ID TEXT1           TEXT2                   ED
---------- --------------- --------------- ----------
         1 Dunningham      Cunnigham                2
         2 Abroms          Abrams                   1
         3 Lampley         Campley                  1
         4 Marhta          Martha                   2
         5 Jonathon        Jonathan                 1
         6 Jeraldine       Geraldine                1
         7 Bat Man         Cat Woman                4

7 rows selected.

SQL>

• EDIT_DISTANCE_SIMILARITY Function

Calculates the number of changes required to transform string-1 into string-2, returning a value between 0 (no match) and 100 (perfect match)

For example,

SQL> SELECT id,
2         text1,
3         text2,
4         UTL_MATCH.edit_distance_similarity(text1, text2) AS eds
5  FROM   t_utl_match
6  ORDER BY id;

ID TEXT1           TEXT2                  EDS
---------- --------------- --------------- ----------
1 Dunningham      Cunnigham               80
2 Abroms          Abrams                  84
3 Lampley         Campley                 86
4 Marhta          Martha                  67
5 Jonathon        Jonathan                88
6 Jeraldine       Geraldine               89
7 Bat Man         Cat Woman               56

7 rows selected.

SQL>

• JARO_WINKLER Function

Calculates the measure of agreement between string-1 and string-2

For example,

SQL> SELECT id,
2         text1,
3         text2,
4         UTL_MATCH.jaro_winkler(text1, text2) AS jw
5  FROM   t_utl_match
6  ORDER BY id;

ID TEXT1           TEXT2                   JW
---------- --------------- --------------- ----------
1 Dunningham      Cunnigham       8.963E-001
2 Abroms          Abrams          9.222E-001
3 Lampley         Campley         9.048E-001
4 Marhta          Martha          9.611E-001
5 Jonathon        Jonathan          9.5E-001
6 Jeraldine       Geraldine       9.259E-001
7 Bat Man         Cat Woman       7.566E-001

7 rows selected.

SQL>

• JARO_WINKLER_SIMILARITY Function

Calculates the measure of agreement between string-1 and string-2, returning a value between 0 (no match) and 100 (perfect match)

For example.

DROP TABLE t_utl_match;

SQL> SELECT id,
2         text1,
3         text2,
4         UTL_MATCH.jaro_winkler_similarity(text1, text2) AS jws
5  FROM   t_utl_match
6  ORDER BY id;

ID TEXT1           TEXT2                  JWS
---------- --------------- --------------- ----------
1 Dunningham      Cunnigham               89
2 Abroms          Abrams                  92
3 Lampley         Campley                 90
4 Marhta          Martha                  96
5 Jonathon        Jonathan                95
6 Jeraldine       Geraldine               92
7 Bat Man         Cat Woman               75

7 rows selected.

SQL>

See the documentation for more details about UTL_MATCH.

Hope it helps!

Why not use WM_CONCAT function in Oracle?

Tags

12c, oracle, string aggregation, undocumented feature, wm_concat

String aggregation related questions are frequently asked questions and every now and then someone provides a solution using WM_CONCAT function. And it really annoys me to repeat the same thing every time.

Any application which has had been relying on WM_CONCAT function will not work once upgraded to 12c. Since, it has been removed from the latest 12c version.

SQL> select banner from v$version where rownum = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> SELECT object_name
  2  FROM dba_objects
  3  WHERE owner='WMSYS'
  4  AND object_name LIKE 'WM\_%' ESCAPE '\';

OBJECT_NAME
----------------------------------------------------------------------------
WM_REPLICATION_INFO
WM_RDIFF
WM_PERIOD
WM_PERIOD
WM_OVERLAPS
WM_MEETS
WM_LESSTHAN
WM_LDIFF
WM_INTERSECTION
WM_INSTALLATION
WM_GREATERTHAN
WM_EVENTS_INFO
WM_ERROR
WM_ERROR
WM_EQUALS
WM_DDL_UTIL
WM_DDL_UTIL
WM_CONTAINS
WM_COMPRESS_BATCH_SIZES
WM_COMPRESSIBLE_TABLES

20 rows selected.

SQL>

You will receive an “invalid identifier” error:

SQL> SELECT banner FROM v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> SELECT deptno, wm_concat(ename) FROM emp;
SELECT deptno, wm_concat(ename) FROM emp
               *
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier

Therefore, there is no point relying on an undocumented feature which is no more made available in latest versions.

Row Generator between two dates in Oracle SQL

Tags

calendar, day name, day number, list of dates, month name, oracle date generator, row generator, week number

This is a frequently asked question in most of the forums. Recently saw such question in Stack Overflow Generate Month Name, Day name, Week number and Day number between two dates. Typical questions are like “How to generate a list of dates between two dates“. Or, “How to generate the months, Week Number and Days between two dates“.

This could be achieved with a simple hierarchical query and proper datetime format mask. Such query is usually known as Row Generator method.

Let’s say we have two dates,  1st Jan 2015  and 20th Feb 2015 as start date and end date respectively. The requirement is to generate a list as shown in the below demonstration:

In SQL*Plus,

SQL> def date_start = '2015-01-01'
SQL> def date_end   = '2015-02-20'
SQL>
SQL> COLUMN the_date FORMAT A11
SQL> COLUMN month_name FORMAT A10
SQL> COLUMN day_name FORMAT A10
SQL> COLUMN the_week FORMAT A8
SQL> COLUMN the_day FORMAT A7
SQL>
SQL> WITH DATA AS
  2    (SELECT to_date('&date_start', 'YYYY-MM-DD') date1,
  3      to_date('&date_end', 'YYYY-MM-DD') date2
  4    FROM dual
  5    )
  6  SELECT TO_CHAR(date1+LEVEL-1, 'DD-MON-YYYY') the_date,
  7         TO_CHAR(date1+LEVEL-1, 'fmMonth') month_name,
  8         TO_CHAR(date1+LEVEL-1, 'fmDay') day_name,
  9         TO_CHAR(date1+LEVEL-1, 'IW') the_week,
 10         TO_CHAR(date1+level-1, 'D') the_day
 11  FROM data
 12    CONNECT BY LEVEL <= date2-date1+1
 13  /
old   2:   (SELECT to_date('&date_start', 'YYYY-MM-DD') date1,
new   2:   (SELECT to_date('2015-01-01', 'YYYY-MM-DD') date1,
old   3:     to_date('&date_end', 'YYYY-MM-DD') date2
new   3:     to_date('2015-02-20', 'YYYY-MM-DD') date2

THE_DATE    MONTH_NAME DAY_NAME   THE_WEEK THE_DAY
----------- ---------- ---------- -------- -------
01-JAN-2015 January    Thursday   01       5
02-JAN-2015 January    Friday     01       6
03-JAN-2015 January    Saturday   01       7
04-JAN-2015 January    Sunday     01       1
05-JAN-2015 January    Monday     02       2
06-JAN-2015 January    Tuesday    02       3
07-JAN-2015 January    Wednesday  02       4
08-JAN-2015 January    Thursday   02       5
09-JAN-2015 January    Friday     02       6
10-JAN-2015 January    Saturday   02       7
11-JAN-2015 January    Sunday     02       1
12-JAN-2015 January    Monday     03       2
13-JAN-2015 January    Tuesday    03       3
14-JAN-2015 January    Wednesday  03       4
15-JAN-2015 January    Thursday   03       5
16-JAN-2015 January    Friday     03       6
17-JAN-2015 January    Saturday   03       7
18-JAN-2015 January    Sunday     03       1
19-JAN-2015 January    Monday     04       2
20-JAN-2015 January    Tuesday    04       3
21-JAN-2015 January    Wednesday  04       4
22-JAN-2015 January    Thursday   04       5
23-JAN-2015 January    Friday     04       6
24-JAN-2015 January    Saturday   04       7
25-JAN-2015 January    Sunday     04       1
26-JAN-2015 January    Monday     05       2
27-JAN-2015 January    Tuesday    05       3
28-JAN-2015 January    Wednesday  05       4
29-JAN-2015 January    Thursday   05       5
30-JAN-2015 January    Friday     05       6
31-JAN-2015 January    Saturday   05       7
01-FEB-2015 February   Sunday     05       1
02-FEB-2015 February   Monday     06       2
03-FEB-2015 February   Tuesday    06       3
04-FEB-2015 February   Wednesday  06       4
05-FEB-2015 February   Thursday   06       5
06-FEB-2015 February   Friday     06       6
07-FEB-2015 February   Saturday   06       7
08-FEB-2015 February   Sunday     06       1
09-FEB-2015 February   Monday     07       2
10-FEB-2015 February   Tuesday    07       3
11-FEB-2015 February   Wednesday  07       4
12-FEB-2015 February   Thursday   07       5
13-FEB-2015 February   Friday     07       6
14-FEB-2015 February   Saturday   07       7
15-FEB-2015 February   Sunday     07       1
16-FEB-2015 February   Monday     08       2
17-FEB-2015 February   Tuesday    08       3
18-FEB-2015 February   Wednesday  08       4
19-FEB-2015 February   Thursday   08       5
20-FEB-2015 February   Friday     08       6

51 rows selected.

In the above demo, the format used for week number is ‘IW’. If you want the week number to change for each month, then use ‘W`:

SQL> def date_start = '2015-01-01'
SQL> def date_end   = '2015-02-20'
SQL>
SQL> COLUMN the_date FORMAT A11
SQL> COLUMN month_name FORMAT A10
SQL> COLUMN day_name FORMAT A10
SQL> COLUMN the_week FORMAT A8
SQL> COLUMN the_day FORMAT A7
SQL>
SQL> WITH DATA AS
  2    (SELECT to_date('&date_start', 'YYYY-MM-DD') date1,
  3      to_date('&date_end', 'YYYY-MM-DD') date2
  4    FROM dual
  5    )
  6  SELECT TO_CHAR(date1+LEVEL-1, 'DD-MON-YYYY') the_date,
  7         TO_CHAR(date1+LEVEL-1, 'fmMonth') month_name,
  8         TO_CHAR(date1+LEVEL-1, 'fmDay') day_name,
  9         TO_CHAR(date1+LEVEL-1, 'W') the_week,
 10         TO_CHAR(date1+level-1, 'D') the_day
 11  FROM data
 12    CONNECT BY LEVEL <= date2-date1+1
 13  /
old   2:   (SELECT to_date('&date_start', 'YYYY-MM-DD') date1,
new   2:   (SELECT to_date('2015-01-01', 'YYYY-MM-DD') date1,
old   3:     to_date('&date_end', 'YYYY-MM-DD') date2
new   3:     to_date('2015-02-20', 'YYYY-MM-DD') date2

THE_DATE    MONTH_NAME DAY_NAME   THE_WEEK THE_DAY
----------- ---------- ---------- -------- -------
01-JAN-2015 January    Thursday   1        5
02-JAN-2015 January    Friday     1        6
03-JAN-2015 January    Saturday   1        7
04-JAN-2015 January    Sunday     1        1
05-JAN-2015 January    Monday     1        2
06-JAN-2015 January    Tuesday    1        3
07-JAN-2015 January    Wednesday  1        4
08-JAN-2015 January    Thursday   2        5
09-JAN-2015 January    Friday     2        6
10-JAN-2015 January    Saturday   2        7
11-JAN-2015 January    Sunday     2        1
12-JAN-2015 January    Monday     2        2
13-JAN-2015 January    Tuesday    2        3
14-JAN-2015 January    Wednesday  2        4
15-JAN-2015 January    Thursday   3        5
16-JAN-2015 January    Friday     3        6
17-JAN-2015 January    Saturday   3        7
18-JAN-2015 January    Sunday     3        1
19-JAN-2015 January    Monday     3        2
20-JAN-2015 January    Tuesday    3        3
21-JAN-2015 January    Wednesday  3        4
22-JAN-2015 January    Thursday   4        5
23-JAN-2015 January    Friday     4        6
24-JAN-2015 January    Saturday   4        7
25-JAN-2015 January    Sunday     4        1
26-JAN-2015 January    Monday     4        2
27-JAN-2015 January    Tuesday    4        3
28-JAN-2015 January    Wednesday  4        4
29-JAN-2015 January    Thursday   5        5
30-JAN-2015 January    Friday     5        6
31-JAN-2015 January    Saturday   5        7
01-FEB-2015 February   Sunday     1        1
02-FEB-2015 February   Monday     1        2
03-FEB-2015 February   Tuesday    1        3
04-FEB-2015 February   Wednesday  1        4
05-FEB-2015 February   Thursday   1        5
06-FEB-2015 February   Friday     1        6
07-FEB-2015 February   Saturday   1        7
08-FEB-2015 February   Sunday     2        1
09-FEB-2015 February   Monday     2        2
10-FEB-2015 February   Tuesday    2        3
11-FEB-2015 February   Wednesday  2        4
12-FEB-2015 February   Thursday   2        5
13-FEB-2015 February   Friday     2        6
14-FEB-2015 February   Saturday   2        7
15-FEB-2015 February   Sunday     3        1
16-FEB-2015 February   Monday     3        2
17-FEB-2015 February   Tuesday    3        3
18-FEB-2015 February   Wednesday  3        4
19-FEB-2015 February   Thursday   3        5
20-FEB-2015 February   Friday     3        6

51 rows selected.

SQL>

Hope it helps!

Reverse the order of comma separated string using Oracle SQL

Tags

reverse comma separated string, reverse delimited string order, reverse indices in string, reverse order of string, reverse string

There was a question on Stack Overflow about “Oracle:how to reverse the string ‘ab,cd,ef’ to ‘ef->cd->ab’”

Now, the question might look straight forward to simply reverse the string. However, it is not just reversing the complete string, but reversing the order of the indices. Like ‘WORD1, WORD2, WORD3’ to be reversed as ‘WORD3, WORD2, WORD1’

It could be done with a mix of string split and string aggregation.

Using:

• REGEXP_SUBSTR : To split the comma delimited string into rows
• LISTAGG : To aggregate the values

You can have a look at “Split comma delimited strings in a table using Oracle SQL” to understand how string split works.

Now, let’s see how to reverse the order of indices:

SQL> WITH DATA AS(
  2  SELECT 1 ID, 'word1,word2,word3' text FROM dual UNION ALL
  3  SELECT 2 ID, '1,2,3' text FROM dual UNION ALL
  4  SELECT 3 ID, 'a,b,c' text FROM dual
  5  )
  6  SELECT ID,
  7    listagg(text, ',') WITHIN GROUP (
  8  ORDER BY rn DESC) reversed_indices
  9  FROM
 10    (SELECT t.id,
 11      rownum rn,
 12      trim(regexp_substr(t.text, '[^,]+', 1, lines.COLUMN_VALUE)) text
 13    FROM data t,
 14      TABLE (CAST (MULTISET
 15      (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, ',')+1
 16      ) AS sys.odciNumberList ) ) lines
 17    ORDER BY ID
 18    )
 19  GROUP BY ID
 20  /

        ID REVERSED_INDICES
---------- ------------------------------
         1 word3,word2,word1
         2 3,2,1
         3 c,b,a

SQL>

Hope it helps!

Returning the sequence number of Identity column after Insert

Tags

identity column, oracle 12c, return identity column after insert, return sequence after insert, return sequence number, returning, sequence

One of the frequently asked questions about sequences is “How to return the last sequence inserted?” Now, with the introduction of Identity column, I saw a similar question in Stack Overflow, Returning the value of identity column after insertion in Oracle

Whether Identity column or a typical sequence, the answer remains same.

RETURNING identity_id INTO variable_id;

Let’s look at a test case:

SQL> set serveroutput on
SQL> CREATE TABLE t
  2    (ID NUMBER GENERATED ALWAYS AS IDENTITY, text VARCHAR2(50)
  3    );

Table created.

SQL>
SQL> DECLARE
  2    var_id NUMBER;
  3  BEGIN
  4    INSERT INTO t
  5      (text
  6      ) VALUES
  7      ('test'
  8      ) RETURNING ID INTO var_id;
  9    DBMS_OUTPUT.PUT_LINE('ID returned is = '||var_id);
 10  END;
 11  /
ID returned is = 1

PL/SQL procedure successfully completed.

SQL>

SQL> select * from t;

        ID TEXT
---------- --------------------------------------------
         1 test

SQL>

Hope it helps!

Why DELETE takes longer than INSERT

Tags

delete, DELETE slower than INSERT, DELETE takes longer than INSERT, DELETE vs INSERT, insert, performance, performance between DELETE and INSERT, performance of delete versus insert

There was a question on Stack Overflow why does delete take longer than insert.

The question is too broad to be addressed as there could be many reasons and statistics behind the two operations. However, the question was asked very generally. And in general, it is true with most of the RDBMS. A DELETE is slower compared to an INSERT operation.

Let’s get into the details.

The autotrace statistics are good enough to find few reasons straight away.

Setup

SQL> CREATE TABLE t
  2    (A NUMBER
  3    );

Table created.

SQL>

INSERT autotrace statistics

SQL> SET AUTOTRACE ON
SQL> INSERT INTO t(A) SELECT LEVEL FROM dual CONNECT BY LEVEL <=1000;

1000 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825

------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL      | T    |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(LEVEL<=1000)


Statistics
----------------------------------------------------------
         43  recursive calls
         63  db block gets
         32  consistent gets
          0  physical reads
      19748  redo size
        857  bytes sent via SQL*Net to client
        864  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
       1000  rows processed
SQL>

DELETE autotrace statistics

SQL> SET AUTOTRACE ON
SQL> DELETE FROM t WHERE ROWNUM <= 1000;

1000 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 325486485

--------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | DELETE STATEMENT    |      |     1 |     3   (0)| 00:00:01 |
|   1 |  DELETE             | T    |       |            |          |
|*  2 |   COUNT STOPKEY     |      |       |            |          |
|   3 |    TABLE ACCESS FULL| T    |     1 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=1000)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          8  recursive calls
       1036  db block gets
         15  consistent gets
          0  physical reads
     253264  redo size
        859  bytes sent via SQL*Net to client
        835  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>

When you DELETE a row the whole row goes to rollback segments and is also written into redo log.

When you do an INSERT the redo size is quite less compared to a DELETE.

Let’s do a small test, I will use DBMS_UTILITY.get_time to compare the timings. I will do a test first with an index, another without an index.

INSERT and DELETE without index:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2    l_start NUMBER;
  3    l_loops NUMBER := 100000;
  4  BEGIN
  5
  6    l_start := DBMS_UTILITY.get_time;
  7
  8    FOR i IN 1 .. l_loops
  9    LOOP
 10      INSERT INTO t
 11        (a
 12        ) VALUES
 13        (i
 14        );
 15    END LOOP;
 16
 17    COMMIT;
 18
 19    DBMS_OUTPUT.put_line('Time taken for INSERT =' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 20
 21    l_start := DBMS_UTILITY.get_time;
 22
 23    FOR i IN 1 .. l_loops
 24    LOOP
 25      DELETE FROM t WHERE a = i;
 26    END LOOP;
 27
 28    COMMIT;
 29
 30    DBMS_OUTPUT.put_line('Time taken for DELETE =' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 31
 32  END;
 33  /
Time taken for INSERT =354 hsecs
Time taken for DELETE =10244 hsecs

PL/SQL procedure successfully completed.

Now, let’ first TRUNCATE the table to set the HIGH WATERMARK back to zero.

SQL> TRUNCATE TABLE t;

Table truncated.

SQL>

Create an index:

SQL> CREATE INDEX a_indx ON t(A);

Index created.

SQL>

Gather table stats:

SQL> EXEC DBMS_STATS.gather_table_stats('LALIT', 't');

PL/SQL procedure successfully completed.

INSERT and DELETE with index:

SQL> DECLARE
  2    l_start NUMBER;
  3    l_loops NUMBER := 100000;
  4  BEGIN
  5
  6    l_start := DBMS_UTILITY.get_time;
  7
  8    FOR i IN 1 .. l_loops
  9    LOOP
 10      INSERT INTO t
 11        (a
 12        ) VALUES
 13        (i
 14        );
 15    END LOOP;
 16
 17    COMMIT;
 18
 19    DBMS_OUTPUT.put_line('Time taken for INSERT =' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 20
 21    l_start := DBMS_UTILITY.get_time;
 22
 23    FOR i IN 1 .. l_loops
 24    LOOP
 25      DELETE FROM t WHERE a = i;
 26    END LOOP;
 27
 28    COMMIT;
 29
 30    DBMS_OUTPUT.put_line('Time taken for DELETE =' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 31
 32  END;
 33  /
Time taken for INSERT =1112 hsecs
Time taken for DELETE =1474 hsecs

PL/SQL procedure successfully completed.

SQL>

So, in either case, DELETE operation takes longer than INSERT.

Hope it helps!

Performance difference between Explicit and Implicit Cursors in Oracle PL/SQL

Tags

cursor performance oracle, cursors, difference between explicit and implicit cursors, explicit cursor, explicit versus implicit cursor, implicit cursor

There are many articles over the web about the difference between an explicit and implicit cursor. Theoretically, an implicit cursor is nicer and way faster. For an implicit cursor, Oracle takes care of OPEN, FETCH and CLOSE. Which, otherwise needs to be done explicitly while using an explicit cursor.

Let’s look at a demonstration to practically see the performance difference between an Explicit and Implicit cursor. I would use DBMS_UTILITY.GET_TIME to get the delta value of the start and end times of the execution.

In SQL*Plus, I execute the following anonymous block which has two cursors involved, first an explicit cursor and next is a CURSOR FOR loop.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_rec    all_objects%ROWTYPE;
  3    l_start  NUMBER;
  4
  5    CURSOR cur IS
  6      SELECT *
  7      FROM   all_objects;
  8  BEGIN
  9    l_start := DBMS_UTILITY.get_time;
 10
 11    OPEN  cur;
 12    LOOP
 13      FETCH cur
 14      INTO  l_rec;
 15
 16      EXIT WHEN cur%NOTFOUND;
 17
 18      -- Do something.
 19      NULL;
 20    END LOOP;
 21    CLOSE cur;
 22
 23    DBMS_OUTPUT.put_line('Explicit Cursor Loop: ' ||
 24                         (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 25
 26    l_start := DBMS_UTILITY.get_time;
 27
 28    FOR rec IN (SELECT * FROM all_objects) LOOP
 29      -- Do something.
 30      NULL;
 31    END LOOP;
 32
 33    DBMS_OUTPUT.put_line('Implicit Cursor Loop: ' ||
 34                         (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 35  END;
 36  /
Explicit Cursor Loop: 167 hsecs
Implicit Cursor Loop: 108 hsecs

PL/SQL procedure successfully completed.

SQL>

So the difference is quite significant. The cursor for loop took much less time as compared to the explicit cursor. The time in hsecs is hundredths of a second.

Split comma delimited strings in a table in Oracle

Tags

comma delimited values in table, comma separated column values, split column to rows, split comma delimited string, split comma delimited values in table, split comma separated column values into rows, split string

NOTE : This post is about splitting multiple comma delimited column values in a table. If you have to apply it on a single value then look at Split single comma delimited string into rows in Oracle

In my other post I summarized various methods to split a comma delimited string into rows using Oracle SQL. However, those SQLs would only take care of a single row, i.e. when you have a single value of comma delimited string. Usually, at our work place, we deal with tables. So, this post is to demonstrate “How to split comma delimited column values in a table into multiple rows in Oracle”

Basic setup:

SQL> DROP TABLE t PURGE;

Table dropped.

SQL> CREATE TABLE t
  2    (id NUMBER generated always AS identity, text VARCHAR2(4000)
  3    );

Table created.
SQL> INSERT INTO t (text) VALUES ('word1, word2, word3');

1 row created.

SQL> INSERT INTO t (text) VALUES ('word4, word5, word6');

1 row created.

SQL> INSERT INTO t (text) VALUES ('word7, word8, word9');

1 row created.

SQL> INSERT INTO t (text) VALUES ('word10, word11, word12, word13');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t;

        ID TEXT
---------- ------------------------------
         1 word1, word2, word3
         2 word4, word5, word6
         3 word7, word8, word9
         4 word10, word11, word12, word13

SQL>

Now, let’s see the various methods to split the comma delimited values into rows from the table.

Using REGULAR EXPRESSIONS

Old Oracle Join Syntax for cross join :

INSTR in CONNECT BY clause

SQL> SELECT t.id,
  2      trim(regexp_substr(t.text, '[^,]+', 1, lines.column_value)) text
  3  FROM t,
  4    TABLE (CAST (MULTISET
  5    (SELECT LEVEL FROM dual
  6            CONNECT BY instr(t.text, ',', 1, LEVEL - 1) > 0
  7    ) AS sys.odciNumberList ) ) lines
  8  ORDER BY id, lines.column_value
  9  /

        ID TEXT
---------- ------------------------------
         1 word1
         1 word2
         1 word3
         2 word4
         2 word5
         2 word6
         3 word7
         3 word8
         3 word9
         4 word10
         4 word11
         4 word12
         4 word13

13 rows selected.

SQL>

REGEXP_SUBSTR in CONNECT BY clause

SQL> SELECT t.id,
  2      trim(regexp_substr(t.text, '[^,]+', 1, lines.column_value)) text
  3  FROM t,
  4    TABLE (CAST (MULTISET
  5    (SELECT LEVEL FROM dual
  6      CONNECT BY regexp_substr(t.text , '[^,]+', 1, LEVEL) IS NOT NULL
  7    ) AS sys.odciNumberList ) ) lines
  8  ORDER BY id, lines.column_value
  9  /

        ID TEXT
---------- ------------------------------
         1 word1
         1 word2
         1 word3
         2 word4
         2 word5
         2 word6
         3 word7
         3 word8
         3 word9
         4 word10
         4 word11
         4 word12
         4 word13

13 rows selected.

SQL>

REGEXP_COUNT in CONNECT BY clause

SQL> SELECT t.id,
  2      trim(regexp_substr(t.text, '[^,]+', 1, lines.column_value)) text
  3  FROM t,
  4    TABLE (CAST (MULTISET
  5    (SELECT LEVEL FROM dual
  6            CONNECT BY LEVEL <= regexp_count(t.text, ',')+1
  7    ) AS sys.odciNumberList ) ) lines
  8  ORDER BY id, lines.column_value
  9  /

        ID TEXT
---------- ------------------------------
         1 word1
         1 word2
         1 word3
         2 word4
         2 word5
         2 word6
         3 word7
         3 word8
         3 word9
         4 word10
         4 word11
         4 word12
         4 word13

13 rows selected.

SQL>

ANSI JOIN syntax :

REPLACE in CONNECT BY clause

SQL> SELECT id, text FROM(
  2   SELECT t.ID,
  3     trim(regexp_substr(t.text, '[^,]+', 1, lines.COLUMN_VALUE)) text,
  4     lines.column_value cv
  5    FROM t
  6    CROSS JOIN
  7    (SELECT *
  8      FROM TABLE (CAST (MULTISET
  9        (SELECT LEVEL
 10        FROM dual
 11          CONNECT BY LEVEL <=
 12          (SELECT COUNT(REPLACE(text, ','))  FROM t
 13          )
 14        ) AS sys.odciNumberList ) )
 15      ) lines
 16    )
 17  WHERE text IS NOT NULL
 18  ORDER BY id, cv
 19  /

        ID TEXT
---------- ------------------------------
         1 word1
         1 word2
         1 word3
         2 word4
         2 word5
         2 word6
         3 word7
         3 word8
         3 word9
         4 word10
         4 word11
         4 word12
         4 word13

13 rows selected.

SQL>

Using XMLTABLE

SQL> SELECT id,
  2           trim(COLUMN_VALUE) text
  3  FROM t,
  4       xmltable(('"'
  5      || REPLACE(text, ',', '","')
  6      || '"'))
  7  /

        ID TEXT
---------- ------------------------------
         1 word1
         1 word2
         1 word3
         2 word4
         2 word5
         2 word6
         3 word7
         3 word8
         3 word9
         4 word10
         4 word11
         4 word12
         4 word13

13 rows selected.

SQL>

Using MODEL clause

SQL> WITH
  2    model_param AS
  3    (
  4     SELECT ID,
  5            text AS orig_str ,
  6            ','
  7            || text
  8            || ','                                 AS mod_str ,
  9            1                                      AS start_pos ,
 10            Length(text)                           AS end_pos ,
 11           (LENGTH(text) -
 12            LENGTH(REPLACE(text, ','))) + 1        AS element_count ,
 13            0                                      AS element_no ,
 14            ROWNUM                                 AS rn
 15      FROM   t )
 16      SELECT id,
 17             trim(Substr(mod_str, start_pos, end_pos-start_pos)) text
 18      FROM (
 19            SELECT *
 20            FROM   model_param
 21            MODEL PARTITION BY (id, rn, orig_str, mod_str)
 22            DIMENSION BY (element_no)
 23            MEASURES (start_pos, end_pos, element_count)
 24            RULES ITERATE (2000)
 25            UNTIL (ITERATION_NUMBER+1 = element_count[0])
 26          ( start_pos[ITERATION_NUMBER+1] =
 27                    instr(cv(mod_str), ',', 1, cv(element_no)) + 1,
 28            end_pos[ITERATION_NUMBER+1] =
 29                    instr(cv(mod_str), ',', 1, cv(element_no) + 1) )
 30          )
 31       WHERE    element_no != 0
 32  ORDER BY ID,
 33                mod_str ,
 34                element_no
 35  /

        ID TEXT
---------- ------------------------------
         1 word1
         1 word2
         1 word3
         2 word4
         2 word5
         2 word6
         3 word7
         3 word8
         3 word9
         4 word10
         4 word11
         4 word12
         4 word13

13 rows selected.

SQL>

Hope it helps!

Why *_PROCEDURES view doesn’t show the PROCEDURE_NAME

Tags

*_procedures, all_procedures, dba_procedures, metadata, oracle procedure dynamic view, package_name, procedure_name, user_procedures, view

Recently I saw a question in Stack Overflow “Unable to find procedure in DBA_PROCEDURES view“. Similar questions are:

1. Why can’t I see my procedure in user_procedures view?
2. Why is the procedure_name filter on all_procedures view returns no rows.

Test case :

SQL> show user
USER is "LALIT"
SQL> CREATE OR REPLACE
  2  PROCEDURE new_proc
  3  AS
  4  BEGIN
  5    NULL;
  6  END;
  7  /

Procedure created.

SQL>
SQL> SELECT owner,
  2    object_name,
  3    procedure_name,
  4    object_type
  5  FROM all_procedures
  6  WHERE owner='LALIT'
  7  AND procedure_name='NEW_PROC';

no rows selected

SQL>

From documentation,

ALL_PROCEDURES lists all functions and procedures, along with associated properties. For example, ALL_PROCEDURES indicates whether or not a function is pipelined, parallel enabled or an aggregate function. If a function is pipelined or an aggregate function, the associated implementation type (if any) is also identified.

It doesn’t clarify whether it would list a STAND ALONE PROCEDURE and a procedure wrapped in a PACKAGE the same way or does it consider it differently. Since, the procedure_name would not list the name of a stand alone procedure as seen in the test case in the question above.

PROCEDURE_NAME column will only have the procedure name for the procedures which are part of a PACKAGE. For STAND ALONE PROCEDURES you need to use OBJECT_NAME.

SQL> show user
USER is "LALIT"
SQL> CREATE OR REPLACE
  2  PROCEDURE new_proc
  3  AS
  4  BEGIN
  5    NULL;
  6  END;
  7  /

Procedure created.

SQL>
SQL> SELECT owner,
  2    object_name,
  3    procedure_name,
  4    object_type
  5  FROM all_procedures
  6  WHERE owner='LALIT'
  7  AND object_name='NEW_PROC';

OWNER OBJECT_NAME     PROCEDURE_NAME  OBJECT_TYPE
----- --------------- --------------- ---------------
LALIT NEW_PROC                        PROCEDURE

SQL>

You could get the list of procedures using procedure_name only if it is wrapped in a package.

SQL> -- package
SQL> CREATE OR REPLACE
  2  PACKAGE new_pack
  3  IS
  4    PROCEDURE new_proc;
  5  END new_pack;
  6  /

Package created.

SQL>
SQL> -- package body with a procedure
SQL> CREATE OR REPLACE
  2  PACKAGE BODY new_pack
  3  IS
  4  PROCEDURE new_proc
  5  IS
  6  BEGIN
  7    NULL;
  8  END;
  9  END new_pack;
 10  /

Package body created.

SQL> SELECT owner,
  2    object_name,
  3    procedure_name,
  4    object_type
  5  FROM all_procedures
  6  WHERE owner='LALIT'
  7  AND procedure_name='NEW_PROC';

OWNER OBJECT_NAME     PROCEDURE_NAME  OBJECT_TYPE
----- --------------- --------------- -----------
LALIT NEW_PACK        NEW_PROC        PACKAGE

SQL>

Now you could see the procedure_name as the actual procedure, and the object_name as the package_name.

Of course, mostly in production systems we would have packages, and not stand alone procedures. But, while testing and demos, we do compile and run stand alone procedures. So, it is good to know how Oracle maintains the information in *_PROCEDURES views.

Hope it helps!

IDENTITY column autoincrement functionality in Oracle 12c

Tags

12c identity column, autoincrement, autonumber, identity column, primary key, sequence, trigger

Oracle 12c has introduced the IDENTITY column which is the automatic method over the old trigger-sequence based approach to auto-populate the primary key of a table. In my previous post, Auto-increment primary key in Pre 12c releases (Identity functionality) , I demonstrated the trigger-sequence based approach. now let’s see the new way in 12c –

TABLE with IDENTITY COLUMN

    SQL> CREATE TABLE t
      2    (
      3      ID NUMBER GENERATED ALWAYS AS IDENTITY
      4      START WITH 1000 INCREMENT BY 1,
      5      text VARCHAR2(50)
      6    );
    
    Table created.
    
    SQL>

INSERT

    SQL> INSERT INTO t
      2    ( text
      3    ) VALUES
      4    ( 'This table has an identity column'
      5    );
    
    1 row created.

    SQL> INSERT INTO t
      2    ( text
      3    ) VALUES
      4    ( 'This table has an identity column'
      5    );
    
    1 row created.
    
    SQL>

Let’s see if we have the ID column auto-incremented with the desired values-

    SQL> COLUMN text format A40
    SQL>
    SQL> SELECT * FROM t;
    
       ID TEXT
    ----- ----------------------------------------
     1000 This table has an identity column
     1001 This table has an identity column
    
    SQL>

So, the ID column now starts with value 1000 and increments by 1 with subsequent inserts.

Oracle creates a sequence to populate the identity column. You can find it named as ISEQ$$

    SQL>
    SQL> SELECT sequence_name,
      2    min_value,
      3    max_value,
      4    increment_by
      5  FROM user_sequences;
    
    SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY
    ------------------------------ ---------- ---------- ------------
    ISEQ$$_94087                            1 1.0000E+28            1
    
    SQL>

More information about the identity columns, use the ALL_TAB_IDENTITY_COLS view.

    SQL>
    SQL> SELECT table_name,
      2    column_name,
      3    generation_type,
      4    identity_options
      5  FROM all_tab_identity_cols
      6  WHERE owner = 'LALIT'
      7  ORDER BY 1,
      8    2;
    
    TABLE_NAME           COLUMN_NAME     GENERATION IDENTITY_OPTIONS
    -------------------- --------------- ---------- --------------------------------------------------
    
    T                    ID              ALWAYS     START WITH: 1000, INCREMENT BY: 1, MAX_VALUE:
                                                     9999999999999999999999999999, MIN_VALUE: 1, CYCLE
                                                    _FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N
    
    
    SQL>

Hope it helps!

Auto-increment primary key in Pre 12c releases (Identity functionality)

Tags

autoincrement, autonumber, identity column, primary key, sequence, trigger

Oracle 12c has introduced the IDENTITY COLUMN functionality which provides an automatic functionality of a trigger-sequence approach for populating the primary key of a table. However, for Pre 12c releases, developers need to stick to the old trigger-sequence approach.

TABLE

SQL> CREATE TABLE t (
  2    ID           NUMBER(10)    NOT NULL,
  3    text  VARCHAR2(50)  NOT NULL);

Table created.

SQL>

PRIMARY KEY to be populated by the sequence

SQL> ALTER TABLE t ADD (
  2    CONSTRAINT id_pk PRIMARY KEY (ID));

Table altered.

SQL>

SEQUENCE to support the primary key

SQL> CREATE SEQUENCE t_seq
  2  START WITH 1000
  3  INCREMENT BY 1;

Sequence created.

SQL>

TRIGGER If you do not want to have the sequence in the INSERT , you could automate it via TRIGGER.

SQL> CREATE OR REPLACE TRIGGER t_trg
  2  BEFORE INSERT ON t
  3  FOR EACH ROW
  4  WHEN (new.id IS NULL)
  5  BEGIN
  6    SELECT t_seq.NEXTVAL
  7    INTO   :new.id
  8    FROM   dual;
  9  END;
 10  /

Trigger created.

SQL>

INSERT

SQL> INSERT INTO t(text) VALUES('auto-increment test 1');

1 row created.

SQL> INSERT INTO t(text) VALUES('auto-increment test 2');

1 row created.

SQL>

Let’s see if we have the ID column auto-incremented with the desired values-

SQL> SELECT * FROM t;

   ID TEXT
----- --------------------------------------------------
 1000 auto-increment test 1
 1001 auto-increment test 2

SQL>

So, the ID column now starts with value 1000 and increments by 1 with subsequent inserts.

Hope it helps!

SQL to Search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA

Tags

all_tab_columns, search all columns, search all tables, search database for value

One of the tricky questions asked in almost all the Oracle forums “How to search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA”. One such question is asked in Stack Overflow http://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle. The most common answer is using PL/SQL to loop through all the column_name in ALL_TAB_COLUMNS view to check for the given value.

It could be done in a single SQL statement. Let’s look at the SQL solution.

Search for the value KING in SCOTT schema.

SQL> variable val varchar2(10)
SQL> exec :val := 'KING'

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
  2    SUBSTR (table_name, 1, 14) "Table",
  3    SUBSTR (column_name, 1, 14) "Column"
  4  FROM cols,
  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  6    || column_name
  7    || ' from '
  8    || table_name
  9    || ' where upper('
 10    || column_name
 11    || ') like upper(''%'
 12    || :val
 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
 14  ORDER BY "Table"
 15  /

Searchword  Table          Column
----------- -------------- --------------
KING        EMP            ENAME

SQL>

Search for the value 20 in SCOTT schema.

SQL> variable val NUMBER
SQL> exec :val := 20

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
  2    SUBSTR (table_name, 1, 14) "Table",
  3    SUBSTR (column_name, 1, 14) "Column"
  4  FROM cols,
  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  6    || column_name
  7    || ' from '
  8    || table_name
  9    || ' where upper('
 10    || column_name
 11    || ') like upper(''%'
 12    || :val
 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
 14  ORDER BY "Table"
 15  /

Searchword  Table          Column
----------- -------------- --------------
20          DEPT           DEPTNO
20          EMP            DEPTNO
20          EMP            HIREDATE
20          SALGRADE       HISAL
20          SALGRADE       LOSAL

SQL>

Credit : Michaels

Hope it helps!

Varying IN list of values in WHERE clause

Tags

varying IN list, where in comma seperated values, where IN string list, where string values

One of the frequent question on Oracle forums is about the Varying IN list in WHERE clause. One such question was asked here http://stackoverflow.com/questions/27738359/select-from-table-with-varying-in-list-in-where-clause/27739139#27739139.

Let’s understand the issue.

Test case :

//DDLs to create the test tables

CREATE TABLE temp(ids VARCHAR2(4000));
CREATE TABLE temp_id(data_id NUMBER);

//DMLs to populate test data
INSERT INTO temp VALUES('1, 2, 3');

INSERT INTO temp_id VALUES(1);
INSERT INTO temp_id VALUES(2);
INSERT INTO temp_id VALUES(3);
INSERT INTO temp_id VALUES(4);
INSERT INTO temp_id VALUES(5);

Reason :

IN (‘1, 2, 3’) is NOT same as IN (1, 2, 3) OR IN(‘1’, ‘2’, ‘3’)

Hence,

SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp);

is same as

SELECT * FROM temp_id WHERE data_id IN('1, 2, 3');

which would thrown an error ORA-01722: invalid number –

SQL> SELECT * FROM temp_id WHERE data_id IN('1, 2, 3');
SELECT * FROM temp_id WHERE data_id IN('1, 2, 3')
*
ERROR at line 1:
ORA-01722: invalid number

SQL> SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp);
SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp)
*
ERROR at line 1:
ORA-01722: invalid number

Above is NOT same as

SELECT * FROM temp_id WHERE data_id IN(1, 2, 3);

which would give you correct output –

SQL> SELECT * FROM temp_id WHERE data_id IN(1, 2, 3);

DATA_ID
----------
1
2
3

SQL>

So, ‘1, 2, 3’ is a single string, a single value in that in list.

Solution :

For such requirement, we can achieve it like this –

SQL> SELECT * FROM temp;

IDS
--------------------------------------------------------------
1, 2, 3

SQL> SELECT * FROM temp_id;

DATA_ID
----------
1
2
3
4
5

SQL>
SQL> WITH data AS
2 (SELECT to_number(trim(regexp_substr(ids, '[^,]+', 1, LEVEL))) ids
3 FROM temp
4 CONNECT BY instr(ids, ',', 1, LEVEL - 1) > 0
5 )
6 SELECT * FROM temp_id WHERE data_id IN
7 (SELECT ids FROM data
8 )
9 /

DATA_ID
----------
1
2
3

SQL>

Alternatively, you can create your own TABLE function or a Pipelined function to achieve this. Your goal should be to split the comma-separated IN list into different rows. How you do it is up to you!

Flashback DML changes on a table after COMMIT

Tags

flashback, flashback commit, flashback dml, flashbak table, rollback after commit, rollback dml, scn

I see frequent questions regarding “How to Rollback database changes after commit” or “How to flashback my table to previous state”. One such questions was asked here http://stackoverflow.com/questions/27309730/how-to-rollback-my-db-changes-after-a-commit/27309814#27309814

Basically, there is nothing like a ROLLBACK after a COMMIT. However, you can do a FLASHBACK on the table in the following ways :-

There are two things,

1.Flashback by SCN

SELECT column_list
FROM table_name
AS OF SCN scn_number;

2.Flashback by TIMESTAMP

SELECT column_list
FROM table_name
AS OF TIMESTAMP TO_TIMESTAMP('the timestamp value');

To get current_scn and systimestamp, query :

SELECT current_scn, SYSTIMESTAMP
FROM v$database;

Let’s see an example :

To flashback the table to the old scn, use FLASHBACK TABLE..TO SCN clause.

SQL> DROP TABLE string_ex PURGE;
Table dropped.

SQL> CREATE TABLE string_ex (sl_ps_code VARCHAR2(20) );

Table created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14ASM0002');

1 row created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14SFT0018');

1 row created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14SFT0019');

1 row created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14SFT0062');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

CURRENT_SCN SYSTIMESTAMP
-------------------- --------------------------------------------
13818123201277 29-OCT-14 03.02.17.419000 PM +05:30

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

CURRENT_SCN SYSTIMESTAMP
-------------------- --------------------------------------------
13818123201280 29-OCT-14 03.02.22.785000 PM +05:30

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

CURRENT_SCN SYSTIMESTAMP
-------------------- --------------------------------------------
13818123201282 29-OCT-14 03.02.26.781000 PM +05:30

SQL> SELECT * FROM string_ex;

SL_PS_CODE
---------------
AR14ASM0002
AR14SFT0018
AR14SFT0019
AR14SFT0062

SQL>

I have four rows in the table.

SQL> ALTER TABLE string_ex ENABLE ROW MOVEMENT;

Table altered.

SQL>

Row movement is required.

SQL> DELETE FROM string_ex WHERE ROWNUM =1;

1 row deleted.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM string_ex;

SL_PS_CODE
---------------
AR14SFT0018
AR14SFT0019
AR14SFT0062

I deleted a row now and committed the changes.

SQL> FLASHBACK TABLE string_ex TO SCN 13818123201277;

Flashback complete.

Flashback is complete

SQL> SELECT * FROM string_ex;

SL_PS_CODE
---------------
AR14ASM0002
AR14SFT0018
AR14SFT0019
AR14SFT0062

SQL>

I now have my table to old state and the row is back

Hope it helps!

Quoting string literal technique for single-quotation marks

Tags

dynamic sql single quotes, q'[]', q-quote, q-quoting, Quoting string literal, quoting string literal mechanism, quoting string literal technique, single quotes

I have seen many questions on the Oracle forums on how to avoid the error due to single-quotation marks in the string literal. One such question was http://stackoverflow.com/questions/27373158/how-to-anticipate-and-escape-in-oracle/27373394#27373394 in stack Overflow.

Oracle SQL doesn’t allow single-quotes within a string literal itself enclosed within single-quotes. So, what’s the workaround? Usually, before introduction of 10g, developers used to embed the single-quotes using two single-quotes around it.

However, the new feature of quoting string literals makes the life of developers far easy than before.

Let’s look at the Quoting string literal technique. The syntax is q'[…]’, where the “[“ and “]” characters can be any of the following as long as they do not already appear in the string.

• !
• [ ]
• { }
• ( )
• < >

You don’t have to worry about the single-quotation marks within the string.

Let’s look at an example.

Suppose we have a column value as abc’de, so you could simply write the SQL as,

SELECT q'[abc'de]' FROM DUAL;

Another example using an anonymous PL/SQL block :-

SQL> set serveroutput on
SQL> DECLARE
  2    v VARCHAR2(1024);
  3  BEGIN
  4    v := q'[It's a ' single quote]';
  5    DBMS_OUTPUT.PUT_LINE(v);
  6  END;
  7  /
It's a ' single quote

PL/SQL procedure successfully completed.

SQL>

Let’s see how it makes working with dynamic SQL so easy :-

SQL> set serveroutput on
SQL> DECLARE
  2    var_sql VARCHAR2(2000);
  3    var_empno NUMBER;
  4  BEGIN
  5    var_sql := q'[SELECT empno FROM emp WHERE ename = 'SCOTT']';
  6    EXECUTE IMMEDIATE var_sql INTO var_empno;
  7    DBMS_OUTPUT.PUT_LINE( 'Employee number of SCOTT is '|| var_empno );
  8  END;
  9  /
Employee number of SCOTT is 7788

PL/SQL procedure successfully completed.

SQL>

It saves a lot of time for developers. Gone are those days when we(developers) used to verify the dynamic sql using dbms_output in development DB, just to make sure things are at place before moving into production.

Hope it helps!

DBMS_UTILITY.COMMA_TO_TABLE

Tags

comma to table, convert comma delimited string to rows, dbms_utility, dbms_utility.comma_to_table

The DBMS_UTILITY package provides various utility subprograms. One such useful utility is COMMA_TO_TABLE procedure, which converts a comma-delimited list of names into a PL/SQL table of names.

Let’s see a small demo how to use it in PL/SQL.

SQL> set serveroutput on;
SQL> DECLARE
  2    l_tablen BINARY_INTEGER;
  3    l_tab DBMS_UTILITY.uncl_array;
  4    CURSOR cur
  5    IS
  6      SELECT 'word1, word2, word3, word4, word5, word6' val FROM dual;
  7    rec cur%rowtype;
  8  BEGIN
  9    OPEN cur;
 10    LOOP
 11      FETCH cur INTO rec;
 12      EXIT
 13    WHEN cur%notfound;
 14      DBMS_UTILITY.comma_to_table (
 15      list => rec.val, tablen => l_tablen, tab => l_tab);
 16      FOR i IN 1 .. l_tablen
 17      LOOP
 18        DBMS_OUTPUT.put_line(i || ' : ' || trim(l_tab(i)));
 19      END LOOP;
 20    END LOOP;
 21    CLOSE cur;
 22  END;
 23  /
1 : word1
2 : word2
3 : word3
4 : word4
5 : word5
6 : word6

PL/SQL procedure successfully completed.

SQL>

Split single comma delimited string into rows in Oracle

Tags

comma delimited string, comma delimiter, comma separated string, split comma delimited string to rows, split string, sql split comma separated list of values

NOTE : This post is about splitting a single comma delimited string. If you have to apply it on a table with multiple rows having comma delimited strings, then look at Split comma delimited strings in a table using Oracle SQL

This is one of the most common questions in most of the Oracle SQL and PL/SQL forums. Although, there are several examples and demo over the web, I thought to summarize all the approaches together at one place.

1. Regular expressions
2. XML table
3. MODEL clause
4. Table function
5. Pipelined table function

Using REGULAR EXPRESSIONS

INSTR in CONNECT BY clause

SQL> WITH DATA AS
  2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3    )
  4  SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5  FROM DATA
  6  CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0
  7  /

STR
----------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

REGEXP_SUBSTR in CONNECT BY clause

SQL> WITH DATA AS
  2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3    )
  4  SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5  FROM DATA
  6  CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL
  7  /

STR
----------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

REGEXP_COUNT in CONNECT BY clause

SQL> WITH DATA AS
  2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3    )
  4      SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5      FROM DATA
  6      CONNECT BY LEVEL <= regexp_count(str, ',')+1   
  7  / 
STR 
---------------------------------------- 
word1 
word2 
word3 
word4 
word5 
word6 

6 rows selected. 

SQL>

Using XMLTABLE

SQL> WITH DATA AS
  2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3    )
  4  SELECT trim(COLUMN_VALUE) str
  5    FROM DATA, xmltable(('"' || REPLACE(str, ',', '","') || '"'))
  6  /
STR
------------------------------------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

Using MODEL clause

SQL> WITH t AS
  2  (
  3    SELECT 'word1, word2, word3, word4, word5, word6' str
  4    FROM   dual ) ,
  5  model_param AS
  6  (
  7  SELECT str                                           orig_str ,
  8         ','
  9         || str
 10         || ','                                        mod_str,
 11         1                                             start_pos,
 12         Length(str)                                   end_pos,
 13         (Length(str) - Length(Replace(str, ','))) + 1 element_count,
 14         0                                             element_no,
 15         ROWNUM                                        rn
 16  FROM   t )
 17  SELECT   trim(Substr(mod_str, start_pos, end_pos-start_pos)) str
 18  FROM     (
 19  SELECT *
 20   FROM   model_param MODEL PARTITION BY (rn, orig_str, mod_str)
 21  DIMENSION BY (element_no)
 22  MEASURES (start_pos, end_pos, element_count)
 23  RULES ITERATE (2000)
 24  UNTIL (ITERATION_NUMBER+1 = element_count[0])
 25  (start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, 
 26                                   cv(element_no)) + 1,
 27   end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, 
 28                                 cv(element_no) + 1) ) )
 29  WHERE    element_no != 0
 30  ORDER BY mod_str ,
 31           element_no
 32  /

STR
------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

Using Table function

SQL> CREATE OR REPLACE TYPE test_type
  2  AS
  3    TABLE OF VARCHAR2(100)
  4  /

Type created.

SQL> CREATE OR REPLACE
  2    FUNCTION comma_to_table(
  3        p_list IN VARCHAR2)
  4      RETURN test_type
  5    AS
  6      l_string VARCHAR2(32767) := p_list || ',';
  7      l_comma_index PLS_INTEGER;
  8      l_index PLS_INTEGER := 1;
  9      l_tab test_type     := test_type();
 10    BEGIN
 11      LOOP
 12        l_comma_index := INSTR(l_string, ',', l_index);
 13        EXIT
 14      WHEN l_comma_index = 0;
 15        l_tab.EXTEND;
 16        l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string, 
 17                                          l_index, 
 18                                          l_comma_index - l_index
 19                                          )
 20                                   );
 21        l_index            := l_comma_index + 1;
 22      END LOOP;
 23      RETURN l_tab;
 24    END comma_to_table;
 25  /

Function created.

SQL> sho err
No errors.

SQL> SELECT * FROM 
  2  TABLE(comma_to_table('word1, word2, word3, word4, word5, word6'))
  3  /

COLUMN_VALUE
----------------------------------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

Using Pipelined Function

SQL> CREATE OR REPLACE TYPE test_type
  2  AS
  3    TABLE OF VARCHAR2(100)
  4  /

Type created.

SQL> CREATE OR REPLACE
  2    FUNCTION comma_to_table(
  3        p_list IN VARCHAR2)
  4      RETURN test_type PIPELINED
  5    AS
  6      l_string LONG := p_list || ',';
  7      l_comma_index PLS_INTEGER;
  8      l_index PLS_INTEGER := 1;
  9    BEGIN
 10      LOOP
 11        l_comma_index := INSTR(l_string, ',', l_index);
 12        EXIT
 13      WHEN l_comma_index = 0;
 14        PIPE ROW ( TRIM(SUBSTR(l_string, 
 15                               l_index, 
 16                               l_comma_index - l_index)));
 17        l_index := l_comma_index + 1;
 18      END LOOP;
 19      RETURN;
 20    END comma_to_table;
 21  /

Function created.

SQL> sho err
No errors.

SQL> SELECT * FROM   
  2  TABLE(comma_to_table('word1, word2, word3, word4, word5, word6'))
  3  /

COLUMN_VALUE
----------------------------------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

Hope it helps!

SYS_OP_C2C Oracle internal function, implicit conversion and performance impact

Tags

internal function sys_op_c2c, SYS_OP_C2C, TO_NCHAR, varchar2 to nvarchar2 implicit conversion

SYS_OP_C2C is an internal function which does an implicit conversion of varchar2 to national character set using TO_NCHAR function.

SQL> CREATE TABLE t AS SELECT 'a'||LEVEL col FROM dual CONNECT BY LEVEL 
SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE col = 'a10';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     5 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("COL"='a10')

13 rows selected.

SQL>

So far so good. Since there is only one row with value as ‘a10’, optimizer estimated one row.

Let’s see with the national characterset conversion.

SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE col = N'a10';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |    50 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    10 |    50 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter(SYS_OP_C2C("COL")=U'a10')

13 rows selected.

SQL>

What happened here? We can see filter(SYS_OP_C2C(“COL”)=U’a10′), which means an internal function is applied and it converts the varchar2 value to nvarchar2. The filter now found 10 rows.

This will also suppress any index usage, since now a function is applied on the column. We can tune it by creating a function-based index to avoid full table scan.

SQL> create index nchar_indx on t(to_nchar(col));

Index created.

SQL>
SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE to_nchar(col) = N'a10';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1400144832

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |    10 |    50 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T          |    10 |    50 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | NCHAR_INDX |     4 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access(SYS_OP_C2C("COL")=U'a10')

14 rows selected.

SQL>

Usually, such scenarios occur when the data coming via an application is nvarchar2 type, but the table column is varchar2. Thus, Oracle applies an internal function in the filter operation. My suggestion is, to know your data well, so that you use similar data types during design phase.

Oracle 12c Post Installation Mandatory Steps

Tags

12.1.0.2., 12102, 12c, 12c post installation steps, cdb, connect to pdb, discard state, install 12c, open pdb, pdb, pluggable database, preserve pdb state, save state, unlock scott in 12c

Since 12c was made available for Windows 64bit OS Desktop class, people started using it immediately. And, some/few/most/all must have encountered issues while installation and especially post installation. The most important of all is understanding the multitenant database architecture. Those who have no idea about the difference between Pluggable Databases (PDB) and Container Databases (CDB), run into trouble immediately post installation of 12c.

I won’t be getting deeper into the concepts, I would just demonstrate and explain the mandatory steps for 12c installation.

OS Platform : Windows 7 64bit, desktop class
DB version : 12.1.0.1

1.

Oracle universal installer will take you to Step 6, “Typical Install Configuration” where you would see a check box to “Create as Container database and to name the Pluggable database. If you check the option, the installer creates a single container database (CDB) to host multiple separate pluggable databases (PDB). Name the PDB properly, I prefer to append PDB to the Global database name to remember it easily.

For example,

Global Database Name : ORCL
Pluggable database name : PDBORCL

2.

Edit your tnsnames.ora file to add the PDB details. Based on above example :

PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

Where,
addressname = PDBORCL
hostname = localhost
port = 1521
sid = pdborcl

3.

To open all/specific PDBs immediately after logon, create a AFTER STARTUP system level trigger in CDB.

Since, the PDBs are not open through a CDB start. Let’s see :

SHUTDOWN IMMEDIATE;
STARTUP;

SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBP6                          MOUNTED

So, in order to have all the PDBs automatically open, do this :

Do, “SQLPLUS / AS SYSDBA”, and then execute :

CREATE OR REPLACE TRIGGER open_pdbs 
  AFTER STARTUP ON DATABASE 
BEGIN 
   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN'; 
END open_pdbs;
/

It creates a after startup system level trigger in CDB.

From 12.1.0.2 onward, you can preserve the PDB startup state through CDB restart. The 12.1.0.2 patchset introduced SAVE STATE and DISCARD STATE options:

ALTER PLUGGABLE DATABASE pdb_name OPEN;
ALTER PLUGGABLE DATABASE pdb_name SAVE STATE;

To discard the saved state:

ALTER PLUGGABLE DATABASE pdb_name DISCARD STATE;

4.

The most common misunderstanding is about “SQLPLUS / AS SYSDBA” usage.

Since we have checked the option to create a single CDB, the “SQLPLUS / AS SYSDBA” command will always log into CDB. Usually developers used to unlock the “SCOTT” account directly after logging as SYSDBA. But here is the trick :

“SCOTT” and other sample schemas are in the PDB and not in the CDB. So, you need to login as sysdba into PDB.

sqlplus SYS/password@PDBORCL AS SYSDBA

SQL> ALTER USER scott ACCOUNT UNLOCK IDENTIFIED BY tiger;

sqlplus scott/tiger@pdborcl

SQL> show user;
USER is "SCOTT"

Find all columns having at least a NULL value from all tables in the schema

Tags

all null columns, column with null values, null columns in table, NULL value

Spending a good amount of time answering the questions on multiple Oracle forums, like OraFAQ, Stack Overflow, OTN, dBforums etc. I could now figure out the most common questions by amateurs in Oracle SQL and PL/SQL. My recent blog posts are focused on such questions. And one among them is “How to find all columns having at least a NULL value from all tables in the schema“.

Now, in SQL it would be a huge task to manually code all the column names in the WHERE clause to check IS NULL condition. However, the manual effort could be minimized to a great extent using an UDF(user defined function) and query on dynamic view ALL_TAB_COLUMNS. Let me demonstrate using the SCOTT schema :

• Create a simple function FIND_NULL_COL, it would return 1 if found any NULL value in any column.

SQL> CREATE OR REPLACE FUNCTION FIND_NULL_COL(
  2      TABLE_NAME  VARCHAR2,
  3      COLUMN_NAME VARCHAR2)
  4    RETURN NUMBER
  5  IS
  6    cnt NUMBER;
  7  BEGIN
  8    CNT :=1;
  9    EXECUTE IMMEDIATE 'select count(*) from ' ||TABLE_NAME||' where '
 10                                              ||COLUMN_NAME||' is null'
 11    INTO cnt;
 12    RETURN
 13    CASE
 14    WHEN CNT > 0 THEN
 15      1
 16    ELSE
 17      0
 18    END;
 19  END;
 20  /

Function created.

• Now, use the UDF in your SQL.

SQL> SELECT c.TABLE_NAME,
  2         c.COLUMN_NAME,
  3         FIND_NULL_COL(c.TABLE_NAME,c.COLUMN_NAME) null_status
  4  FROM all_tab_columns c
  5  WHERE C.OWNER    ='SCOTT'
  6  AND c.TABLE_NAME = 'EMP'
  7  ORDER BY C.OWNER,
  8    C.TABLE_NAME,
  9    C.COLUMN_ID
 10  /

TABLE_NAME COLUMN_NAME NULL_STATUS
---------- ----------- -----------
EMP        EMPNO                 0
EMP        ENAME                 0
EMP        JOB                   0
EMP        MGR                   1
EMP        HIREDATE              0
EMP        SAL                   0
EMP        COMM                  1
EMP        DEPTNO                0

8 rows selected.

So, NULL_STATUS 1 is the list of column names which has at least a NULL value.

If you do not want to create the function, then there is another way to get the list of columns having NULL value.

• To get columns which are completely NULL.

Using the *_TAB_COLUMNS view. However, you MUST gather statistics before querying the view to get accurate results.

SQL> EXEC DBMS_STATS.gather_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

SQL> SELECT t.table_name,
  2    t.column_name
  3  FROM user_tab_columns t
  4  WHERE t.nullable   = 'Y'
  5  AND t.num_distinct = 0;;

TABLE_NAME COLUMN_NAME
---------- -----------
BONUS      ENAME
BONUS      JOB
BONUS      SAL
BONUS      COMM

• To get columns which have at least one NULL value.

Using the *_TAB_COLUMNS view. However, you MUST gather statistics before querying the view to get accurate results.

SQL> EXEC DBMS_STATS.gather_table_stats('SCOTT','EMP');

SQL> SELECT column_name,
  2    num_nulls
  3  FROM user_tab_columns
  4  WHERE NUM_NULLS <> 0
  5  AND table_name   ='EMP';

COLUMN_NAME  NUM_NULLS
----------- ----------
MGR                  1
COMM                11

Hope it helps!

Why are junk values/special characters/question marks displayed on my client?

Tags

characterset, junk values, NLS_CHARACTERSET, nls_database_parameters, NLS_LANG, NLS_LANGUAGE, NLS_TERRITORY, question marks, special characters, v$nls_parameters

There have been numerous questions on almost all the Oracle discussion forums about “Why are junk values displayed on my client” or ” Why do I see question marks or special characters in the table”. And, 99% of the cases are due to the mismatch between the locale-specific NLS characterset and the database characterset. Or else, the NLS_LANG value is not correctly set in the OS environmental variable.

You need to follow these simple steps :

1. Check the locale-specific NLS characterset :

select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET';

1. Check the database characterset :

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

If you see a mismatch between the two, then set the locale-specific NLS characterset to that of database characterset.

If the above charactersets match, then you need to set the NLS_LANG value in the OS environmental variable.

For Windows OS, the format is [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]

Follow the instruction in the documentation regarding setting up the NLS_LANG : Setting the NLS_LANG Environment Variable for Oracle Databases

Hope it helps!

Find specific/similar words in a string using REGULAR EXPRESSION in Oracle

Tags

all occurrences of string, Extract specific words in a string using REGULAR EXPRESSION in Oracle, extract word from string, Find all occurrences of word, regexp_count, regexp_substr

This is supposed to be one of the most common question among all the Oracle discussion forums, and especially folks looking for REGULAR EXPRESSION over web, would have definitely come across such a question. I find such questions at least once a day on OraFAQ, stack overflow, OTN forums…

So, let me give a small demonstration about how to find all the occurrences of specific/similar word in a string. As an example, I will take a huge string literal which itself is a complex select query having multiple join clause. So, the task is to find all the table names. in this case, the driving factor is that the word will always have initial characters as ‘table’, followed by digits or characters.

The string :

SELECT b.col1, 
       a.col2, 
       Lower(a.col3) 
FROM   table1 a 
       inner join table2 b 
               ON a.col = b.col 
                  AND a.col = b.col 
       inner join (SELECT col1, 
                          col2, 
                          col3, 
                          col4 
                   FROM   tablename) c 
               ON a.col1 = b.col2 
WHERE  a.col = 'value' 

To find :

table1, table2 , tablename

Let’s see how :

SQL> WITH DATA AS
  2    (SELECT q'[select b.col1,a.col2,lower(a.col3) from table1 a inner join table2 b on
  3  a.col = b.col and a.col = b.col inner join (select col1, col2, col3,col4 from tablename )
  4  c on a.col1=b.col2 where a.col = 'value']' str
  5    FROM DUAL
  6    )
  7  SELECT LISTAGG(TABLE_NAMES, ' , ') WITHIN GROUP (
  8  ORDER BY val) table_names
  9  FROM
 10    (SELECT 1 val,
 11      regexp_substr(str,'table[[:alnum:]]+',1,level) table_names
 12    FROM DATA
 13      CONNECT BY level <= regexp_count(str,'table')
 14    )
 15  /

TABLE_NAMES
--------------------------------------------------------------------------------
table1 , table2 , tablename

Let me explain in detail, how that query works :

• The REGEXP_SUBSTR looks for the words ‘table’, it could be followed by a number or string like 1,2, name etc.
• To find all such words, I used connect by level technique, but it gives the output in different rows.
• Finally, to put them in a single row as comma separated values, I used LISTAGG.
• Oh yes, and that q'[]’ is the string literal technique.

Hope it helps!

V$RESERVED_WORDS & X$KWDDEF – Interesting facts!

It’s a lazy Friday and I saw an interesting question in Stackoverflow about “Why are there two “null” keywords in Oracle’s v$reserved_words view?” And I spent next 2 hours digging into knowing the reason. Here are my findings:

We need to understand how to interpret the view v$reserved _words. To determine whether a particular keyword is reserved in any way, check the RESERVED, RES_TYPE, RES_ATTR, and RES_SEMI columns.

SQL> column keyword format A10;
SQL> select * from v$reserved_words where keyword is null;

KEYWORD        LENGTH R R R R D     CON_ID
---------- ---------- - - - - - ----------
                    0 Y N N N N          0
                    0 N N N N N          0

Those two rows does not have ‘Y’ for all the columns. Yes, one of the row has RESERVED as ‘Y’ but the length is 0. Also, none of the attributes are ‘Y’.

From documentation,

RESERVED    VARCHAR2(1) A value of Y means that the keyword cannot be used as an identifier. A value of N means that it is not reserved.
RES_TYPE    VARCHAR2(1) A value of Y means that the keyword cannot be used as a type name. A value of N means that it is not reserved.
RES_ATTR    VARCHAR2(1) A value of Y means that the keyword cannot be used as an attribute name. A value of N means that it is not reserved.
RES_SEMI    VARCHAR2(1) A value of Y means that the keyword is not allowed as an identifier in certain situations, such as in DML. A value of N means that it is not reserved.
DUPLICATE   VARCHAR2(1) A value of Y means that the keyword is a duplicate of another keyword. A value of N means that it is not a duplicate.

We can also check for the SQL and PL/SQL reserved words/keywords in SQL*Plus in the following way:

SQL> help reserve

 RESERVED WORDS (PL/SQL)
 -----------------------

 PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used
 for identifier names (unless enclosed in "quotes").

 An asterisk (*) indicates words are also SQL Reserved Words.

 ALL*            DESC*           JAVA            PACKAGE         SUBTYPE
 ALTER*          DISTINCT*       LEVEL*          PARTITION       SUCCESSFUL*
 AND*            DO              LIKE*           PCTFREE*        SUM
 ANY*            DROP*           LIMITED         PLS_INTEGER     SYNONYM*
 ARRAY           ELSE*           LOCK*           POSITIVE        SYSDATE*
 AS*             ELSIF           LONG*           POSITIVEN       TABLE*
 ASC*            END             LOOP            PRAGMA          THEN*
 AT              EXCEPTION       MAX             PRIOR*          TIME
 AUTHID          EXCLUSIVE*      MIN             PRIVATE         TIMESTAMP
 AVG             EXECUTE         MINUS*          PROCEDURE       TIMEZONE_ABBR
 BEGIN           EXISTS*         MINUTE          PUBLIC*         TIMEZONE_HOUR
 BETWEEN*        EXIT            MLSLABEL*       RAISE           TIMEZONE_MINUTE
 BINARY_INTEGER  EXTENDS         MOD             RANGE           TIMEZONE_REGION
 BODY            EXTRACT         MODE*           RAW*            TO*
 BOOLEAN         FALSE           MONTH           REAL            TRIGGER*
 BULK            FETCH           NATURAL         RECORD          TRUE
 BY*             FLOAT*          NATURALN        REF             TYPE
 CHAR*           FOR*            NEW             RELEASE         UI
 CHAR_BASE       FORALL          NEXTVAL         RETURN          UNION*
 CHECK*          FROM*           NOCOPY          REVERSE         UNIQUE*
 CLOSE           FUNCTION        NOT*            ROLLBACK        UPDATE*
 CLUSTER*        GOTO            NOWAIT*         ROW*            USE
 COALESCE        GROUP*          NULL*           ROWID*          USER*
 COLLECT         HAVING*         NULLIF          ROWNUM*         VALIDATE*
 COMMENT*        HEAP            NUMBER*         ROWTYPE         VALUES*
 COMMIT          HOUR            NUMBER_BASE     SAVEPOINT       VARCHAR*
 COMPRESS*       IF              OCIROWID        SECOND          VARCHAR2*
 CONNECT*        IMMEDIATE*      OF*             SELECT*         VARIANCE
 CONSTANT        IN*             ON*             SEPERATE        VIEW*
 CREATE*         INDEX*          OPAQUE          SET*            WHEN
 CURRENT*        INDICATOR       OPEN            SHARE*          WHENEVER*
 CURRVAL         INSERT*         OPERATOR        SMALLINT*       WHERE*
 CURSOR          INTEGER*        OPTION*         SPACE           WHILE
 DATE*           INTERFACE       OR*             SQL             WITH*
 DAY             INTERSECT*      ORDER*          SQLCODE         WORK
 DECIMAL*        INTERVAL        ORGANIZATION    SQLERRM         WRITE
 DECLARE         INTO*           OTHERS          START*          YEAR
 DEFAULT*        IS*             OUT             STDDEV          ZONE
 DELETE*         ISOLATION


 RESERVED WORDS (SQL)
 --------------------

 SQL Reserved Words have special meaning in SQL, and may not be used for
 identifier names unless enclosed in "quotes".

 An asterisk (*) indicates words are also ANSI Reserved Words.

 Oracle prefixes implicitly generated schema object and subobject names
 with "SYS_". To avoid name resolution conflict, Oracle discourages you
 from prefixing your schema object and subobject names with "SYS_".

 ACCESS          DEFAULT*         INTEGER*        ONLINE          START
 ADD*            DELETE*          INTERSECT*      OPTION*         SUCCESSFUL
 ALL*            DESC*            INTO*           OR*             SYNONYM
 ALTER*          DISTINCT*        IS*             ORDER*          SYSDATE
 AND*            DROP*            LEVEL*          PCTFREE         TABLE*
 ANY*            ELSE*            LIKE*           PRIOR*          THEN*
 AS*             EXCLUSIVE        LOCK            PRIVILEGES*     TO*
 ASC*            EXISTS           LONG            PUBLIC*         TRIGGER
 AUDIT           FILE             MAXEXTENTS      RAW             UID
 BETWEEN*        FLOAT*           MINUS           RENAME          UNION*
 BY*             FOR*             MLSLABEL        RESOURCE        UNIQUE*
 CHAR*           FROM*            MODE            REVOKE*         UPDATE*
 CHECK*          GRANT*           MODIFY          ROW             USER*
 CLUSTER         GROUP*           NOAUDIT         ROWID           VALIDATE
 COLUMN          HAVING*          NOCOMPRESS      ROWNUM          VALUES*
 COMMENT         IDENTIFIED       NOT*            ROWS*           VARCHAR*
 COMPRESS        IMMEDIATE*       NOWAIT          SELECT*         VARCHAR2
 CONNECT*        IN*              NULL*           SESSION*        VIEW*
 CREATE*         INCREMENT        NUMBER          SET*            WHENEVER*
 CURRENT*        INDEX            OF*             SHARE           WHERE
 DATE*           INITIAL          OFFLINE         SIZE*           WITH*
 DECIMAL*        INSERT*          ON*             SMALLINT*


SQL>

Now, the interesting thing starts :

The support notes in My Oracle Support here Support and Historical Notes for “V$RESERVED_WORDS” says it is better to check the TYPE in View:X$KWDDEF

SQL> select indx, keyword, length, type from X$KWDDEF where keyword is NULL;

      INDX KEYWORD        LENGTH       TYPE
---------- ---------- ---------- ----------
      2087                     0          2
      2088                     0          1

Now how is the V$RESERVED_WORDS view formed from X$KWDDEF? This is the underlying query :

SELECT inst_id, keyword, LENGTH,
   DECODE (MOD (TRUNC (TYPE / 2), 2), 0, 'N', 1, 'Y', '?') reserved,
   DECODE (MOD (TRUNC (TYPE / 4), 2), 0, 'N', 1, 'Y', '?') res_type,
   DECODE (MOD (TRUNC (TYPE / 8), 2), 0, 'N', 1, 'Y', '?') res_attr,
   DECODE (MOD (TRUNC (TYPE / 16), 2), 0, 'N', 1, 'Y', '?') res_semi,
   DECODE (MOD (TRUNC (TYPE / 32), 2), 0, 'N', 1, 'Y', '?') duplicate
  FROM x$kwddef;

So what is TYPE actually? The TYPE column is used as a bucket for grouping words.

The keyword ‘NULL’ shouldn’t be misinterpreted with the NULL VALUE in those rows. NULL keyword is entirely different and has LENGTH = 4.

SQL> select indx, keyword, length, type from X$KWDDEF where keyword = 'NULL';

      INDX KEYWORD        LENGTH       TYPE
---------- ---------- ---------- ----------
       338 NULL                4          2

Since ‘X$KWDDEF’ has an entry for ‘NULL’ keyword as TYPE 2, these two rows could be safely ignored. I guess X$KWDDEF means Kernel word definition, just a guess!

Hope it helps!

A myth about Row Exclusive Table Lock on SELECT FOR UPDATE with no rows

Recently I came across a question in Stackoverflow about row exclusive lock after executing SELECT..FOR UPDATE with no rows selected. The fact was that the query returned no rows, but Oracle applies a Row exclusive table lock i.e. LOCKED_MODE 3. So, which are the rows exclusively locked by Oracle? Let’s see …

SQL> CREATE TABLE t AS
  2    SELECT LEVEL COL
  3    FROM   dual
  4    CONNECT BY LEVEL 
SQL> SELECT *
  2  FROM   t
  3  WHERE  col = 20
  4  FOR UPDATE;

no rows selected

SQL>
SQL> SELECT object_id,
  2         session_id,
  3         process,
  4         LOCKED_MODE
  5  FROM   v$locked_object;

 OBJECT_ID SESSION_ID PROCESS                  LOCKED_MODE
---------- ---------- ------------------------ -----------
     92553        124 8784:7948                          3

So what does the above proves? Which are the rows locked by Oracle?

Common questions/myth is :

1. Can other sessions update?
2. Can this non-existing row be inserted by other sessions?
3. Are any other DMLs allowed in this locked state?

The answer is YES. Actually there are no rows locked for update. So, yes other sessions can do the DML transactions. Let’s see :

Session 1:

SQL> SELECT *
  2  FROM   t
  3  WHERE  col = 20
  4  FOR UPDATE;

no rows selected

SQL>
SQL> SELECT object_id,
  2         session_id,
  3         process,
  4         locked_mode
  5  FROM   v$locked_object;

 OBJECT_ID SESSION_ID PROCESS                  LOCKED_MODE
---------- ---------- ------------------------ -----------
     92551        124 8784:7948                          3

Session 2: Update the table

SQL> update t set col = 20 where col = 10;

1 row updated.

Session 1:

SQL> SELECT object_id,
  2         session_id,
  3         process,
  4         locked_mode
  5  FROM   v$locked_object;

 OBJECT_ID SESSION_ID PROCESS                  LOCKED_MODE
---------- ---------- ------------------------ -----------
     92551          7 8036:1680                          3
     92551        124 8784:7948                          3

Session 2: Issues commit

SQL> commit;

Commit complete.

SQL>

Session 1:

SQL> select * from t;

       COL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        20

10 rows selected.

SQL> SELECT object_id,
  2         session_id,
  3         process,
  4         locked_mode
  5  FROM   v$locked_object;

 OBJECT_ID SESSION_ID PROCESS                  LOCKED_MODE
---------- ---------- ------------------------ -----------
     92551        124 8784:7948                          3

SQL>

So, the session_id 7 is now released from the lock. You can see the object_id, it is same table locked in all the cases.

So, yes other sessions can update, since you don’t have any rows locked for update. The same applies for any DML transactions for any other sessions.

But, if any rows are returned by the SELECT..FOR UPDATE, then those rows will be exclusively locked, and other sessions cannot do any DML on those rows and will keep waiting, until the previous session has committed or rolled back the changes.

Let’s get deeper into the reason.

SQL> CREATE TABLE t AS
  2    SELECT LEVEL col
  3    FROM   dual
  4    CONNECT BY LEVEL  SELECT *
  2  FROM   t
  3  WHERE  col = 20
  4  FOR UPDATE;

no rows selected

SQL> SELECT xidusn,
  2         xidslot,
  3         xidsqn,
  4         object_id,
  5         session_id,
  6         locked_mode
  7  FROM v$locked_object
  8  /

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- ---------- ---------- -----------
         0          0          0      92554          7           3

SQL> SELECT sid,
  2         type,
  3         id1,
  4         id2,
  5         lmode,
  6         request,
  7         ctime,
  8         block
  9  FROM   v$lock
 10  WHERE  sid = 7
 11  /

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
         7 TM      92554          0          3          0        121          0
         7 AE        133          0          4          0       6999          0
         7 TO      86406          1          3          0        185          0

The query against v$locked_object indicates that rows in the table are locked in Locked Mode 3 (i.e. Row Exclusive Mode). Since the XIDUSN, XIDSLOT and XIDSQN are all 0 this indicates that no rows are actually locked.

This can be confirmed by the query again v$lock. Ignoring the AE lock, there is a TM Lock/Enqueue on the table, to prevent structural changes to the table during the transaction. This is in Locked Mode 3 (i.e. Row Exclusive Mode). But there are no TX Locks/Enqueues at row level.

This can be compared with:

SQL> rollback;

Rollback complete.

SQL> SELECT *
  2  FROM   t
  3  WHERE  col = 1
  4  FOR UPDATE;

       COL
----------
         1

SQL> SELECT xidusn,
  2         xidslot,
  3         xidsqn,
  4         object_id,
  5         session_id,
  6         locked_mode
  7  FROM v$locked_object
  8  /

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- ---------- ---------- -----------
        10         33       1958      92554          7           3

SQL> SELECT sid,
  2         type,
  3         id1,
  4         id2,
  5         lmode,
  6         request,
  7         ctime,
  8         block
  9  FROM   v$lock
 10  WHERE  sid = 7
 11  /

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
         7 TX     655393       1958          6          0         54          0
         7 TM      92554          0          3          0         54          0
         7 AE        133          0          4          0       7124          0
         7 TO      86406          1          3          0        310          0

The query against v$locked_object again indicates that rows in the table are locked in Locked Mode 3 (i.e. Row Exclusive Mode). However, since the XIDUSN, XIDSLOT and XIDSQN are all non 0 this indicates that rows are also locked.

This can be confirmed by the query again v$lock. Again, there is a TM Lock/Enqueue on the table, to prevent structural changes to the table during the transaction in Locked Mode 3 (i.e. Row Exclusive Mode). There are also TX Lock/Enqueues in Locked Mode 6 (Exclusive Mode) so the row returned for update cannot be modified by other users.

In conclusion. Once you issue DML to modify rows in a table, or issue a SELECT…FOR UPDATE indicating your intention to modify rows in a table. Oracle will take a Row Exclusive Mode lock at the table level to ensure that no DDL is issued to structurally change the table. It will additionally Exclusively lock each row that has been modified. If no rows are modified the table level lock will still be taken.

Hope it helps!

Lock user SYS and Connect without SYSDBA

Tags

connect SYS without SYSDBA, lock sys user, O7_DICTIONARY_ACCESSIBILITY, ORA-28000: the account is locked

Many times this question comes up “Whether we could login as SYS but not as SYDBA”. Related questions are “Whether you could lock SYS account”. The answer to both the questions is YES. Let’s see how :

Before I proceed with the demonstration, let me tell about a very interesting parameter which controls restrictions on SYSTEM privileges, it is O7_DICTIONARY_ACCESSIBILITY.

If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior). The default setting of false ensures that system privileges that allow access to objects in “any schema” do not allow access to objects in the SYS schema.

For example, if O7_DICTIONARY_ACCESSIBILITY is set to false, then the SELECT ANY TABLE privilege allows access to views or tables in any schema except theSYS schema (data dictionary tables cannot be accessed). If O7_DICTIONARY_ACCESSIBILITY is set to false, then to access objects in the SYS schema, the user should have SELECT ANY DICTIONARY system privilege or the user should have been granted SELECT object privilege on the specific objects. The system privilege EXECUTE ANY PROCEDURE allows access on the procedures in any schema except the SYS schema.

If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privileges. The following roles, which can be granted to the database administrator, also allow access to dictionary objects:

• SELECT_CATALOG_ROLE
• EXECUTE_CATALOG_ROLE
• DELETE_CATALOG_ROLE

The following are 2 ways to lock the SYS account and CONNECT to SYS without SYSDBA :

 

1. With O7 parameter set to false(by default) :

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 28 13:10:48 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter O7;

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
SQL> alter user sys account lock;

User altered.

SQL> conn sys@pdborcl
Enter password:
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> conn sys@pdborcl as sysdba
Enter password:
Connected.
SQL> select account_status from dba_users where username='SYS';

ACCOUNT_STATUS
--------------------------------
LOCKED

 

2. With O7 parameter set to true :

SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;

System altered.

SQL> shutdown immediate;
Pluggable Database closed.
SQL> startup;
Pluggable Database opened.
SQL> show user
USER is "SYS"
SQL> show parameter O7

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE
SQL> conn sys@pdborcl
Enter password:
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.

 

PL/SQL Developer Settings

Tags

allround automations plsql developer, client tool settings, Oracle client tools, Oracle plsql developer settings, pl/sql developer, PL/SQL Developer Settings

Being an Oracle database application developer, we come across a lot of client tools like SQL*Plus, SQL Developer, TOAD, PL/SQL Developer etc. In this post I will be explaining the most common settings and FAQ about PL/SQ Developer. It is a nice GUI based client tool which makes life of a developer quite easy, but remember, “Only if you know how to use the tool”.

1. How to view the output from DBMS_OUTPUT.PUT_LINE.

While working in a SQL Window, you would see tabs on top, SQL, Output and Statistics. All the output from the DBMS_OUTPUT.PUT_LINE procedure is displayed in the Output tab.

If you are wondering why you do not see an output? That’s because you did not select the “Enabled” checkbox.

Just select the checkbox and re-execute your code.

2. Customizing the object browser

3. How to indent and format the code using PL/SQL Beautifier.

This is a very important tool which makes the code formatting and indentation quite handy. All you need to do is customize the settings the way you want your code to be formatted. The red error points to the PL/SQL Beautifier button.

To save your format and indentation setting, follow these steps :

a. Go to Tools –> Preferences

b. Select PL/SQL Beautifier under User Interface

c. Click on Edit.

d. Make changes and then click on Save As.

Code before and after using PL/SQL Beautifier :

4. PLAN_TABLE

Most of the GUIs are helpful to a certain extent and create the PLAN_TABLE in a local schema if it doesn’t exist there without noticing that there is a synonym to a shared PLAN_TABLE. Developers using GUIs often run into trouble and complain about “PLAN TABLE doesn’t exist”. What does it mean? It could be following reasons:

a. PLAN_TABLE doesn’t exist in your schema. So you need to create it first in your schema.

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

This tool doesn’t have the capability to do certain things for you through preferences and settings, so we need to do them manually.

Now we have to create a plan_table synonym, either private or public.

b. Another possible reason is that, the plan table generated by the tool in the current schema is an older version of itself, hence it creates an older version of the plan_table. So first thing is to drop this old version of plan table and create it as mentioned above.

c. If you do not have CREATE privileges, it is certainly NOT the tool’s issue. You have to create the plan table in a different schema and create a synonym, either public or private as mentioned above.

5. How to check the PLSQL Developer Version and other details :

a. Go to Help –> About

b. It will open a pop-up window.

6. NLS Options

One of the most common issues is with the improper settings of NLS parameters. Questions like
“But my date format is not correct” OR “I do not see the time part”

Such questions are all related to the NLS Options. All you need to do is, set the NLS settings properly per your environment needs.

We have 3 options for the DATE, TIME and NUMBER formats.
a. User defined
b. Windows format
c. Oracle format

For all the three formats, only ALTER SESSION statement and session level format mask using SQL can override. Else, the output will be displayed per the NLS settings of the client tool.

Let’s see,

User complaints that he gets output of “SELECT TRUNC(SYSDATE) FROM DUAL” as DD-MM-YYYY,

However desired format is MM/DD/YYYY.

It is due to the following settings of DATE.

User then changes it to Oracle format which is as defined by the Oracle environment (NLS_DATE_FORMAT). OR user defined as MM/DD/YYYY and gets the output displayed as required.

Play around with the NLS options for more understanding.

1. Read the following links for further clarity,
   A good explanation by Ed Stevens about common myths about NLS DATE format, settings and precendence order. http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/
2. Nice demonstration about DATE, TIMESTAMP and INTERVALS by Tim Hall. http://www.oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals.php

7. Help and User Guide

On clicking the User Guide, it will open a PDF file containing the help contents and user guide. It has lot of contents which covers the different aspects of the tool.

8. PL/SQL Developer online-forum

For any question regarding the product or any difficulties, you could also post your question in the online forum here –> http://www.allroundautomations.com/plsqldev.html

9. EXPLAIN PLAN preferences

There are lots of questions on explain plan like,
a. I don’t see ACCESS PREDICATES in the explain plan window
b. How to include FILTER PREDICATES in the explain plan
c. How to configure the EXPLAIN PLAN window

To get rid of such issues, all we need to do is, set the explain plan window preferences.

Before setting the required preferences:

It could be done in either of these 3 ways :

1. Go to, Tools –> Preferences –> Window Types –> Plan Window
2. Open explain plan window, on the bar you would see an icon at right end which looks like a spanner, click on it.
3. In your SQL window, the way you execute your SQL, press F5, it will open explain plan window, then follow step 2.

Following the steps will point to the Preferences window. Using the right arrow, select the required columns from “Available column” and move them to “Selected columns”.

How to set the explain plan preferences:

It could be done in either of these 3 ways :

1. Go to, Tools –> Preferences –> Window Types –> Plan Window
2. Open explain plan window, on the bar you would see an icon at right end which looks like a spanner, click on it.
3. In your SQL window, the way you execute your SQL, press F5, it will open explain plan window, then follow step 2.

Following the steps will point to the Preferences window. Using the right arrow, select the required columns from “Available column” and move them to “Selected columns”.

After setting the required preferences:

Now you could see the required columns added to your explain plan.

Do I need to open different window all the time for checking explain plan for different SQLs?

No.
If you want to view the explain plan of another SQL in the same plan window and do not bother about losing the explain plan of previous query, then just copy paste the required query in the existing plan window and press F5. The new explain plan is for the SQL which you just pasted.

10. Setting up keyboard shortcuts

Go to, Tools –> Preferences –> Key configuration

Hope it helps!

Order of item list in declaration part of PL/SQL anonymous block – PLS-00103

In an anonymous PL/SQL block, if you do not follow the order of the item list in the declaration part, you might just end up with PLS-00103 error. Per Oracle documentation on Anonymous Block following is the order of the item list in the declaration part :

1. Item list of  variables, cursors, types, subtypes, constants etc.
2. Functions.
3. Procedures.

Let’s see the test cases :

1. While declaring, I keep the local variable after the function :

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> set serveroutput on;
SQL> DECLARE
  2
  3      FUNCTION F_get_dt
  4      RETURN DATE
  5      IS
  6        var_dt DATE;
  7      BEGIN
  8          var_dt := SYSDATE;
  9
 10          RETURN var_dt;
 11      END f_get_dt;
 12
 13      var_curr_dt DATE DEFAULT SYSDATE;
 14
 15  BEGIN
 16      var_curr_dt:= f_get_dt;
 17
 18      IF var_curr_dt = SYSDATE THEN
 19        Dbms_output.put_line('Current Date');
 20      ELSE
 21        Dbms_output.put_line('Not Current Date');
 22      END IF;
 23  END;
 24  /
    var_curr_dt DATE DEFAULT SYSDATE;
    *
ERROR at line 13:
ORA-06550: line 13, column 5:
PLS-00103: Encountered the symbol "VAR_CURR_DT" when expecting one of the
following:
begin function pragma procedure
ORA-06550: line 23, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
end not pragma final instantiable order overriding static
member constructor map

So, Oracle doesn’t allow to do that. The local variable must be declared before the function. As, immediately after the END statement of function, it expects the BEGIN statement.

1. I keep the list in correct order :

SQL> set serveroutput on;
SQL> DECLARE
  2
  3      var_curr_dt DATE DEFAULT SYSDATE;
  4
  5      FUNCTION F_get_dt
  6      RETURN DATE
  7      IS
  8        var_dt DATE;
  9      BEGIN
 10          var_dt := SYSDATE;
 11
 12          RETURN var_dt;
 13      END f_get_dt;
 14
 15  BEGIN
 16      var_curr_dt:= f_get_dt;
 17
 18      IF var_curr_dt = SYSDATE THEN
 19        Dbms_output.put_line('Current Date');
 20      ELSE
 21        Dbms_output.put_line('Not Current Date');
 22      END IF;
 23  END;
 24  /
Current Date

PL/SQL procedure successfully completed.

I tried to investigate further about the reason behind such illogical implementation of an order for the item list. I posted a question in OraFAQ forum, Order of item list in declaration part of PL/SQL anonymous block Seems like the reason is unknown, or an internal Oracle mechanism. Whatever it is, as a pl/sql developer you must keep this in mind and not panic at a situation when you encounter this ridiculous issue.

Final verdict : PL/SQL parser state can accept only begin function pragma procedure.

Hope it helps!

Oracle EXPLAIN PLAN – How to create, display and read

Tags

dbms_xplan, display explain plan, explain plan, how to generate explain plan, ORA-02404: specified plan table not found, plan table does not exist, PLAN_TABLE

This post is to demonstrate the ways to create and display explain plan. But before we proceed with the steps of creating and displaying explain, the schema must have the PLAN_TABLE.

A lot of times developers encounters plan table not found error “ORA-02404: specified plan table not found”. What does it mean? It means the schema doesn’t have PLAN_TABLE. The best and easiest way is to run the following script to create the PLAN_TABLE :

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> DESC PLAN_TABLE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 STATEMENT_ID                                       VARCHAR2(30)
 PLAN_ID                                            NUMBER
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(4000)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_ALIAS                                       VARCHAR2(65)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 DEPTH                                              NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG
 OTHER_XML                                          CLOB
 DISTRIBUTION                                       VARCHAR2(30)
 CPU_COST                                           NUMBER(38)
 IO_COST                                            NUMBER(38)
 TEMP_SPACE                                         NUMBER(38)
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)
 PROJECTION                                         VARCHAR2(4000)
 TIME                                               NUMBER(38)
 QBLOCK_NAME                                        VARCHAR2(30)

The syntax for generating an explain plan for a query :

SQL> EXPLAIN PLAN FOR select * from emp where deptno = 10;

Explained.

You could also assign a statement_id for the generated explain plan :

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'test' FOR select * from emp where deptno =
 10;

Explained.

At this stage, the PLAN_TABLE is populated with the required data. However, in order to understand it, we need to display it in a readable format. If you just try to look into the PLAN_TABLE, you wont be able to easily interpret the explain plan. Execute the below query :

SELECT * FROM PLAN_TABLE WHERE STATEMENT_ID = 'test';

Difficult to interpret, isn’t it? You want it to be properly formatted to be readable. DBMS_XPLAN does it for you.

SQL> set linesize 132;
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------
Plan hash value: 2872589290

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |   261 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     3 |   261 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------

   1 - filter("DEPTNO"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)

17 rows selected.

SQL*Plus error logging – workaround for ROLLBACK issue

Tags

errorlogging rollback issue, SPERRORLOG, sperrorlog rollback issue, SQL*Plus error logging - workaround for ROLLBACK issue, sqlplus rollback error

In my previous post SQL*Plus error logging – New feature release 11.1, as a NOTE I stated an issue that whenever ROLLBACK is issued in the session, the feature fails to log the errors.
Thanks to Jacek Gebal for his blog “Oracle Thoughts”. I was really impressed by the workaround.

Workaround is simple but tricky.

Three simple steps :
1. Rename table SPERRORLOG to a new table.
1. Create a view as “SPERRORLOG”
2. Create a procedure with autonomous transaction.
3. Create an INSTEAD OF trigger to call above procedure. Instead of insert on SPERRORLOG, rather insert into the new table.

SQL> ALTER TABLE sperrorlog RENAME TO sperrorlog_new;
Table altered.

SQL> CREATE VIEW sperrorlog AS SELECT * FROM sperrorlog_new;

View created.

SQL> CREATE OR REPLACE PROCEDURE p_sperrorlog(
2       username VARCHAR2, timestamp TIMESTAMP, script VARCHAR2,
3       identifier VARCHAR2, message VARCHAR2, statement VARCHAR2
4 ) IS
5    PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7    INSERT INTO sperrorlog_new
8    VALUES (username, timestamp, script, identifier, message, statement);
9    COMMIT;
10 END;
11 /

Procedure created.

SQL> SHOW ERRORS;
No errors.

SQL> CREATE OR REPLACE TRIGGER r_sperrorlog
2       INSTEAD OF INSERT ON sperrorlog FOR EACH ROW
3    CALL p_sperrorlog(
4       :NEW.username,:NEW.timestamp, :NEW.script,
5       :NEW.identifier, :NEW.message, :NEW.statement)
6 /

Trigger created.

SQL> SHOW ERRORS;
No errors.

Let’s test it and see.

SQL> show errorlogging;
errorlogging is OFF

SQL> set errorlogging on;

SQL> show errorlogging;
errorlogging is ON TABLE SPERRORLOG

SQL> insert into emp(empno) values ('abcd');
insert into emp(empno) values ('abcd')
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select timestamp, script, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT

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

MESSAGE
--------------------------------------------------------------------------------

03-MAY-14 06.38.42.000000 AM

ORA-01722: invalid number

SQL> ROLLBACK;

Rollback complete.

SQL> select timestamp, script, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT

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

MESSAGE
--------------------------------------------------------------------------------

03-MAY-14 06.38.42.000000 AM

ORA-01722: invalid number

It works! Now we could capture the errors even if ROLLBACK is issued.

WHEN OTHERS – A bug

Tags

BUG IN EXCEPTION, EXCEPTION WHEN OTHERS THEN, EXCEPTION WHEN OTHERS THEN NULL, WHEN OTHER HIDES ERROR, WHEN OTHERS, WHEN OTHERS BUG, WHEN OTHERS THEN NULL

A when others is almost always a BUG unless it is immediately followed by a RAISE.

— Quote from Thomas Kyte’s excellent thread on similar topic

Remember, for errors, RAISE –> CATCH –> HANDLE

The most common mistake that Oracle PL/SQL developers often do is not handling the exceptions properly. Irrespective of the fact that the code is robust, having a weak exception handler could break the entire code and make the entire business logic prone to errors. We all know how painful it is to debug a huge piece of code just to find the line where the error occurs. Yes, it is time consuming and could be avoided most of the times if good coding practices are followed.

At first instance, why do we need an exception handler? To catch the errors, log them(optional), and finally do something about them. Isn’t it? If not, then why do we need the overhead of catching them and ultimately not doing anything?

In this post, I am restricting the scope to the ultimate bug in any pl/sql code, none other than “WHEN OTHERS THEN NULL”. Yes, it’s a bug waiting for its chance to break the atomicity of a procedural call. Let’s see why.

A simple test case to show how WHEN OTHERS hides the actual error if not re-raised:

SQL> set serveroutput on;

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2       o_val NUMBER;
3    BEGIN
4       SELECT i_val INTO o_val FROM dual;
5       DBMS_OUTPUT.PUT_LINE(o_val);
6    EXCEPTION
7       WHEN OTHERS THEN
8          NULL;
9    END;
10   /
Procedure created

SQL> SHOW ERRORS;
No errors for PROCEDURE P_TEST_OTHERS

SQL> BEGIN
2       p_test_others('a');
3    END;
4    /
PL/SQL procedure successfully completed

So the above code did not throw any error and it says the procedure successfully completed. But, is it?

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2       o_val NUMBER;
3    BEGIN
4       SELECT i_val INTO o_val FROM dual;
5       DBMS_OUTPUT.PUT_LINE(o_val);
6    EXCEPTION
7       WHEN OTHERS THEN
8          DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);
9          DBMS_OUTPUT.PUT_LINE('Message: '||SQLERRM);
10   END;
11   /
Procedure created

SQL>
SQL> BEGIN
2       p_test_others('a');
3    END;
4    /
SQLCODE: -6502
Message: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
PL/SQL procedure successfully completed

So, we do see an error being captured. However, it is only as good as knowing that the error exists, but what needs to be done with it? If such a code is a part of any job, the job would simply complete successfully. Do you to proceed blindly with errors?

You need to re-raise the error.

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2       o_val NUMBER;
3    BEGIN
4       SELECT i_val INTO o_val FROM dual;
5       DBMS_OUTPUT.PUT_LINE(o_val);
6    EXCEPTION
7       WHEN OTHERS THEN
8          DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);
9          DBMS_OUTPUT.PUT_LINE('Message: '||SQLERRM);
10         RAISE;
11    END;
12    /
Procedure created

SQL>
SQL> BEGIN
2       p_test_others('a');
3    END;
4    /
SQLCODE: -6502
Message: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
BEGIN
p_test_others('a');
END;
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "P_TEST_OTHERS", line 10
ORA-06512: at line 2

So where to use WHEN OTHERS? Confined to the following cases :

• Error logging.
when others
then
log_the_error(required_parameters);
raise;
end;
• To catch all other exceptions apart from the Named System Exceptions and Named Programmer-Defined Exceptions.
• Send an email regarding the error captured(Might be helpful during development stage or unit testing).
• To close all the opened resources such as Cursors, files etc.

ORA-06503: PL/SQL: Function returned without value

Tags

Error in return value, function return error, Function returned without value, ORA-06503, ORA-06503: PL/SQL: Function returned without value

An important thing regarding function, you would agree with me that, at least once a PL/SQL developer must have heard that “A function MUST ALWAYS RETURN a VALUE of proper datatype”. Having been said that a million times on various platforms, still developers make this mistake.

ORA-06503: PL/SQL: Function returned without value
Cause: A call to PL/SQL function completed, but no RETURN statement was executed.
Action: Rewrite PL/SQL function, making sure that it always returns a value of a proper type.

DB version : 11.2.0.2.0

Let’s see the various scenarios of this error :

Without a RETURN statement in the function body and without exception handler(most stupid way):

SQL> set serveroutput on;

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3    o_val NUMBER;
4 BEGIN
5    SELECT 100 / i_val
6       INTO o_val
7    FROM DUAL;
8 END;
9 /
Function created

SQL> select f_test(100) from dual;
select f_test(100) from dual
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "F_TEST", line 8

Now, in the above code, the mathematical logic was correct, hence there was no SQL error to override the PL/SQL error. Let’s see how ORA-01476 will override the ORA-06503 error.

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3    o_val NUMBER;
4 BEGIN
5    SELECT 100 / i_val
6       INTO o_val
7    FROM DUAL;
8 END;
9 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-01476: divisor is equal to zero
ORA-06512: at "F_TEST", line 5

Well, that’s quite obvious, isn’t it?

2.  Without a RETURN statement in the exception handler(most common mistake) :

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3    o_val NUMBER;
4 BEGIN
5    SELECT 100 / i_val
6       INTO o_val
7    FROM DUAL;
8
9 RETURN o_val;
10
11 EXCEPTION
12    WHEN OTHERS THEN
13       NULL;
14 END;
15 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "F_TEST", line 14

—————————————-/ OFF TOPIC /—————————————-

This is somewhat important to share.

EXCEPTION WHEN OTHERS THEN NULL;
–> is itself a bug in the code waiting for its chance to break the code.

At least a good developer would remember that WHEN OTHERS should be always followed by a RAISE. Re-raising the error would show us the root cause, rather than the confusing “ORA-06503: PL/SQL: Function returned without value” error.

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3    o_val NUMBER;
4 BEGIN
5    SELECT 100 / i_val
6       INTO o_val
7    FROM DUAL;
8
9 RETURN o_val;
10
11 EXCEPTION
12    WHEN OTHERS THEN
13       NULL;
14    RAISE;
15 END;
16 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-01476: divisor is equal to zero
ORA-06512: at "F_TEST", line 14

—————————————-/ OFF TOPIC /—————————————-

Now let’s put a RETURN statement at required places and the code should work fine without any error :

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8
9 RETURN o_val;
10
11 EXCEPTION
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE('Came inside Exception handler');
14 RETURN 0;
15 END;
16 /
Function created

SQL> select f_test(0) from dual;
F_TEST(0)
----------
0
Came inside Exception handler

Bottom line is that :

• A function MUST ALWAYS RETURN a value of proper datatype, no matter from the body or exception.
• We must do something with the error not just return junk. We must RAISE/log error and handle it, do something about the error so that underlying process has no impact.
• Lastly, not to forget, EXCEPTION WHEN OTHERS THEN NULL; –> is itself a bug in the code waiting for its chance to break the code.

Oracle – Insert Comma separated string values into Table

Tags

comma seperated string, comma to table, Insert Comma seperated string values into Table, split string, string to table

This is a small demonstartion of how to insert the values of a comma separated string to a table as different rows. This is not something new, Tom Kyte has already demonstrated it lot of times in AskTom. However, I just want to keep it simple, just to insert values of a comma separated string into a table.

Let’s see the demo :

SQL> SET SERVEROUTPUT ON;
SQL> CREATE TABLE TEST (COL1 VARCHAR2(10))
  2  /

Table created.

SQL> DECLARE
  2    L_INPUT VARCHAR2(4000) := 'THIS,IS,A,COMMA,SEPARATED,STRING';
  3    L_COUNT BINARY_INTEGER;
  4    L_ARRAY DBMS_UTILITY.LNAME_ARRAY;
  5  BEGIN
  6    DBMS_UTILITY.COMMA_TO_TABLE(LIST => REGEXP_REPLACE(L_INPUT, '(^|,)', '\1x'), TABLEN => L_COUNT, TAB => L_ARRAY);
  7    DBMS_OUTPUT.PUT_LINE(L_COUNT);
  8    FOR I IN 1 .. L_COUNT
  9    LOOP
 10      DBMS_OUTPUT.PUT_LINE('Element ' || TO_CHAR(I) || ' of array contains: ' || SUBSTR(L_ARRAY(I), 2));
 11      INSERT INTO TEST VALUES
 12        (SUBSTR(L_ARRAY(I), 2)
 13        );
 14      COMMIT;
 15    END LOOP;
 16  END;
 17  /
6
Element 1 of array contains: THIS
Element 2 of array contains: IS
Element 3 of array contains: A
Element 4 of array contains: COMMA
Element 5 of array contains: SEPARATED
Element 6 of array contains: STRING

PL/SQL procedure successfully completed.

SQL>  SELECT * FROM TEST
  2  /

COL1
----------
THIS
IS
A
COMMA
SEPARATED
STRING

6 rows selected.

SQL>

Hope it helps!

Understanding Oracle Deadlock

Tags

Deadlock, deadlock detected while waiting for resource, ORA-00060, oracle deadlock

Being a database application developer, a lot of times we come across deadlock errors. We know that a deadlock detected message is actually a clue to understand that it is a application design level issue. But just saying that doesn’t fix anything, and the herculean task is to replicate the deadlock situation. Proper exception handling and error logging could lead us to the database objects involved in creating the deadlock.

Let’s take a look at a simple example of deadlock situation using a single table with two rows :

SQL> CREATE TABLE t_test(col_1 NUMBER, col_2 NUMBER);

Table created
SQL> INSERT INTO t_test VALUES(1,2);

1 row inserted
SQL> INSERT INTO t_test VALUES(3,4);

1 row inserted

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM t_test;

     COL_1      COL_2
---------- ----------
         1          2
         3          4

Note the time of each transaction, I have set time on timing on for a better understanding.

SESSION : 1

12:16:06 SQL> UPDATE t_test SET col_1 = 5 WHERE col_2=2;

1 row updated.

Elapsed: 00:00:00.00

SESSION : 2

12:16:04 SQL> UPDATE t_test SET col_1 = 6 WHERE col_2=4;

1 row updated.

Elapsed: 00:00:00.00
12:16:31 SQL> UPDATE t_test SET col_1 = 7 WHERE col_2=2;

At this point, SESSION 2 keeps waiting.

SESSION : 1

12:16:15 SQL> UPDATE t_test SET col_1 = 8 WHERE col_2=4;

At this point, SESSION 2 is the victim of deadlock, SESSION 1 is still waiting.

Let’s look at the session details from SESSION 2 –

12:22:15 SQL> select sid,status,program,sql_id, state, wait_class, blocking_session_status, event from v$session where schemaname='LALIT' and program='sqlplus.exe';

       SID STATUS   PROGRAM         SQL_ID        STATE               WAIT_CLASS      BLOCKING_SE EVENT
---------- -------- --------------- ------------- ------------------- --------------- ----------- ----------------------------------------------------------------
        14 ACTIVE   sqlplus.exe     60qmqpmbmyhxn WAITED SHORT TIME   Network         NOT IN WAIT SQL*Net message to client
       134 ACTIVE   sqlplus.exe     5x0zg4qwus29v WAITING             Application     VALID       enq: TX - row lock contention

Elapsed: 00:00:00.00
12:22:18 SQL>

So, v$session details when viewed in SESSION 2, i.e. SID 14, says the status is ACTIVE.

Let’s look at the session details from another session, lets call it SESSION 3 for the sake. Remember, SESSION 1 is still waiting.

SQL> set time on timing on
12:24:41 SQL> select sid,status,program,sql_id, state, wait_class, blocking_session_status, event from v$session where schemaname='LALIT' and program='sqlplus.exe'

       SID STATUS   PROGRAM         SQL_ID        STATE               WAIT_CLASS BLOCKING_SE EVENT
---------- -------- --------------- ------------- ------------------- ---------- ----------- ------------------------------
        13 ACTIVE   sqlplus.exe     60qmqpmbmyhxn WAITED SHORT TIME   Network    NOT IN WAIT SQL*Net message to client
        14 INACTIVE sqlplus.exe                   WAITING             Idle       NO HOLDER   SQL*Net message from client
       134 ACTIVE   sqlplus.exe     5x0zg4qwus29v WAITING             Applicatio VALID       enq: TX - row lock contention
                                                                      n


Elapsed: 00:00:00.01
12:24:44 SQL>

So, for other sessions, SESSION 2, i.e. SID 14, is INACTIVE. SESSION 1 is still WAITING with event enq: TX - row lock contention.

Let’s commit SESSION 2 –

12:22:18 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
12:25:43 SQL>

At this point, the lock is released for SESSION 1, let’s commit session 1 as well –

12:16:15 SQL> UPDATE t_test SET col_1 = 8 WHERE col_2=4;

1 row updated.

Elapsed: 00:08:27.29
12:25:43 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
12:26:26 SQL>

Elapsed: 00:08:27.29 shows SESSION 1 was waiting that long till SESSION 2 was committed.

To summarize, here is the entire story of session 1 –

12:16:06 SQL> UPDATE t_test SET col_1 = 5 WHERE col_2=2;

1 row updated.

Elapsed: 00:00:00.00
12:16:15 SQL> UPDATE t_test SET col_1 = 8 WHERE col_2=4;

1 row updated.

Elapsed: 00:08:27.29
12:25:43 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
12:26:26 SQL>

To summarize, here is the entire story of session 2 –

100:16:04 SQL> UPDATE t_test SET col_1 = 6 WHERE col_2=4;

1 row updated.

Elapsed: 00:00:00.00
12:16:31 SQL> UPDATE t_test SET col_1 = 7 WHERE col_2=2;
UPDATE t_test SET col_1 = 7 WHERE col_2=2
                                  *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


Elapsed: 00:00:24.47
12:22:15 SQL> select sid,status,program,sql_id, state, wait_class, blocking_session_status, event from v$session where schemaname='LALIT' and program='sqlplus.exe';

       SID STATUS   PROGRAM         SQL_ID        STATE               WAIT_CLASS BLOCKING_SE EVENT
----------   -------- --------------- ------------- ------------------- --------------- ----------- ----------------------------------------------------------------
        14 ACTIVE   sqlplus.exe     60qmqpmbmyhxn WAITED SHORT TIME   Network         NOT IN WAIT SQL*Net message to client
       134 ACTIVE   sqlplus.exe     5x0zg4qwus29v WAITING             Application     VALID       enq: TX - row lock contention

Elapsed: 00:00:00.00
12:22:18 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
12:25:43 SQL>

Now, let’s see which transaction actually got rolled back and which got committed –

12:25:43 SQL> select * from t_test;

     COL_1      COL_2
---------- ----------
         5          2
         8          4

Elapsed: 00:00:00.00
12:30:36 SQL>

Usually, Oracle takes a second or two to detect a deadlock and throws the error.

Conclusion

In my opinion, the best way to know the session details of a deadlock is to log the details as verbose as possible. Else, it is a nightmare for a DBA to investigate without proper information logged. For that matter, even a Developer would find it to be an herculean task to rectify and fix the actual design flaw if the deadlock error details are not logged verbosely. And to conclude with a one liner statement, A deadlock is due to design flaw, Oracle is just the victim and the application being the culprit. Deadlocks are scary, but they point out the design flaws that must be rectified sooner or later.

Hope it helps!

Oracle – Case Insensitive Sorts & Compares

Tags

Case Insensitive Index, Case Insensitive Search, NLSSORT, Oracle Case Insensitive Compares, Oracle Case Insensitive Sorts, Oracle Case Insensitive Sorts & Compares

Oracle enhanced it’s case-insensitive sorts and compares feature with 10g Release 2. Version 11g has the following types of sorts:

• Binary sort
• Monolingual linguistic sort
• Multilingual linguistic sort

NLSSORT

NLSSORT returns the string of bytes used to sort char.
Both char and ‘nlsparam’ can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of RAW datatype.
The value of ‘nlsparam’ can have the form :
‘NLS_SORT = sort’
where sort is a linguistic sort sequence or BINARY. If you omit ‘nlsparam’, then this function uses the default sort sequence for your session. If you specify BINARY, then this function returns char.
If you specify ‘nlsparam’, then you can append to the linguistic sort name the suffix _ai to request an accent-insensitive sort or _ci to request a case-insensitive sort.
This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.

/–Test Cases–/
Sample data and table used for examples throughout the scope of the document:-
To get us started, here’s a simple sample table with a few rows in it.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
Connected as Lalit

SQL> CREATE TABLE NAMES AS SELECT * FROM (
  2  WITH DATA AS(
  3  SELECT 'Lalit'    NAME from dual UNION ALL
  4  SELECT 'Srikanth' NAME from dual UNION ALL
  5  SELECT 'Rahul'    NAME from dual UNION ALL
  6  SELECT 'Praveen'  NAME from dual UNION ALL
  7  SELECT 'Swetha'   NAME from dual UNION ALL
  8  SELECT 'Karthik'  NAME from dual UNION ALL
  9  SELECT 'Suresh'   NAME from dual)
 10  SELECT * from DATA);
 
Table created
 
SQL> select * from names;
 
NAME
--------
Lalit
Srikanth
Rahul
Praveen
Swetha
Karthik
Suresh
 
7 rows selected

The default: Binary sort

The default sort order is always binary, meaning that the characters are sorted in the order of their character number in the character set.

SQL> SHOW PARAMETER NLS_SORT;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string      BINARY
 
SQL> SHOW PARAMETER NLS_COMP;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_comp                             string      BINARY

So this is how the default sorting happens –

SQL> SELECT name, ascii(substr(name,1,1)) FROM names order  by name;
 
NAME     ASCII(SUBSTR(NAME,1,1))
-------- -----------------------
Karthik                       75
Lalit                         76
Praveen                       80
Rahul                         82
Srikanth                      83
Suresh                        83
Swetha                        83
 
7 rows selected
 
SQL> SELECT name, ascii(name) FROM names order  by name;
 
NAME     ASCII(NAME)
-------- -----------
Karthik           75
Lalit             76
Praveen           80
Rahul             82
Srikanth          83
Suresh            83
Swetha            83
 
7 rows selected

What happens if the data is case-sensitive, the sorting of the characters changes as per the ASCII value returned. However, internally everything happens in binary format. Let’s see this happening when we alter the case sensitivity-

SQL> update names set name='kARTHIK' where name='Karthik';
 
1 row updated
 
SQL> SELECT name, ascii(name) FROM names order  by NLSSORT(name,'NLS_SORT=BINARY');
 
NAME     ASCII(NAME)
-------- -----------
Lalit             76
Praveen           80
Rahul             82
Srikanth          83
Suresh            83
Swetha            83
kARTHIK          107
 
7 rows selected
 
SQL> SELECT name, ascii(name) FROM names order  by NLSSORT(name,'NLS_SORT=BINARY_CI');
 
NAME     ASCII(NAME)
-------- -----------
kARTHIK          107
Lalit             76
Praveen           80
Rahul             82
Srikanth          83
Suresh            83
Swetha            83
 
7 rows selected
 
SQL> rollback;
 
Rollback complete

Let’s see the difference in the binary values when case-sensitivity matters-

SQL> select name, NLSSORT(name,'NLS_SORT=BINARY') BINARY ,NLSSORT(name,'NLS_SORT=BINARY_CI') BINARY_CI FROM names ORDER BY name;
 
NAME     BINARY                      BINARY_CI
-------- -------------------         --------------------
Karthik  4B61727468696B00            6B61727468696B00
Lalit    4C616C697400                6C616C697400
Praveen  5072617665656E00            7072617665656E00
Rahul    526168756C00                726168756C00
Srikanth 5372696B616E746800          7372696B616E746800
Suresh   53757265736800              73757265736800
Swetha   53776574686100              73776574686100
 
7 rows selected

Now let’s see the use of these session parameters.

SQL> select * from names where name = 'Rahul';
 
NAME
--------
Rahul
 
SQL> select * from names where name = 'rahul'; --case does not match with actual data
NAME
--------

--no rows returned in this case

So, in such a case usually we tend to use UPPER/LOWER function on both sides of the operator.

SQL> select * from names where UPPER(NAME) = UPPER('rahul');
 
NAME
--------
Rahul

OR

SQL> select * from names where LOWER(NAME) = LOWER('RAHUL');
 
NAME
--------
Rahul

Now we got our output, but, the question is did we do it optimally?
What if the NAMES table is a MASTER TABLE of the national data for AADHAR CARD/VOTER ID. It will have billions of rows making the table size to some Gigabytes.
Usually, if the top SQLs have the name column in their predicate, then it is must to have a Balanced-tree index. Or even a bitmap-index if the mapping criteria fulfils for not null and unique conditions. In such a case, using UPPER or LOWER function will skip the Index scan and we hit a FTS case.

Let’s create a normal balance-tree index.

SQL> CREATE INDEX names_indx ON names(NAME);
 
Index created

Now let’s check the execution plan for the query using the case function (UPPER/LOWER).

SQL> explain plan for SELECT * FROM names WHERE UPPER(name)=UPPER('kArtHIk');
 
Explained

SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4048250084
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     6 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| NAMES |     1 |     6 |    15   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NLSSORT(UPPER("NAME"),'nls_sort=''BINARY_CI''')=HEXTORAW('
              6B61727468696B00') )
Note
-----
   - dynamic sampling used for this statement (level=2)
 
18 rows selected

So, we just ended up with a FTS. A very obvious solution that would strike in the mind is to create a function-based index.

SQL> create index names_fb_indx on names(upper(name));
 
Index created
 
SQL> alter session set nls_comp='BINARY';
 
Session altered
 
SQL> alter session set nls_sort='BINARY';
 
Session altered
 
SQL> explain plan for SELECT * FROM names WHERE UPPER(name)=UPPER('kArtHIk');
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3644882080
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    12 |     2   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| NAMES         |     1 |    12 |     2   (0
|*  2 |   INDEX RANGE SCAN          | NAMES_FB_INDX |     1 |       |     1   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("NAME")='KARTHIK')
Note
-----
   - dynamic sampling used for this statement (level=2)
 
18 rows selected

Although creating a virtual column in 11g and up would also suffice. But it is similar to a function-based index. But doing either of them is nothing but adding an overhead to the existing design. We need to maintain the indexes in an index table space. It will maintain the rowid, value of the data, but in the same case that of the original data in table.
So, let’s see for a better approach.

SQL> alter session set nls_comp='LINGUISTIC';
 
Session altered
 
SQL> alter session set nls_sort='BINARY_CI';
 
Session altered
SQL> SELECT * FROM names WHERE name='kArtHIk';
 
NAME
--------
Karthik
SQL> explain plan for SELECT * FROM names WHERE name='kArtHIk';
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4048250084
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     6 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| NAMES |     1 |     6 |    15   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6B61727
              468696B00') )
Note
-----
   - dynamic sampling used for this statement (level=2)
 
18 rows selected

Everything seems fine. Task is achieved, but still we can tune it to next level.

Case Insensitive Indexes

A normal index uses the default sort order, by default, and so it’s useless in a case-insensitive search – and Oracle won’t use it. For large tables, the resulting full table scan can be quite a performance penalty. Fortunately, it’s easy to create an index that uses a specific sort order. You simply create a function-based index that uses the NLSSORT function we saw above.

SQL> create index names_ci_indx on names(NLSSORT(name,'NLS_SORT=BINARY_CI'));
 
Index created
 
SQL> explain plan for SELECT * FROM names WHERE name='kArtHIk';
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2561608627
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    45 |     2   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| NAMES         |     1 |    45 |     2   (0
|*  2 |   INDEX RANGE SCAN          | NAMES_CI_INDX |     1 |       |     1   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6B61727468696B0
Note
-----
   - dynamic sampling used for this statement (level=2)
 
18 rows selected

SQL*Plus error logging – New feature release 11.1

Tags

error log, errorlogging rollback issue, Oracle Miscellaneous, Oracle SQL*Plus, SPERRORLOG, SPERRORLOG issue, sperrorlog rollback issue, SQL*Plus error logging - workaround for ROLLBACK issue, sqlplus rollback error

One of the most important things that a developer does apart from just code development is, debugging. Isn’t it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere.

As of now, any application has it’s own user defined error logging table(s). Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1

A lot of times developers complain that they do not have privilege to create tables and thus they cannot log the errors in a user defined error logging table. In such cases, it’s a really helpful feature, at least during the unit testing of the code. I made a small demonstration in SCOTT schema using the default error log table SPERRORLOG, hope this step by step demo helps to understand easily :

NOTE : SQL*Plus error logging is set OFF by default. So, you need to “set errorlogging on” to use the SPERRORLOG table.

SP2 Error

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc sperrorlog;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             VARCHAR2(1024)
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

SQL> truncate table sperrorlog;

Table truncated.

SQL> set errorlogging on;
SQL> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.27.29.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

ORA Error

SQL> truncate table sperrorlog;

Table truncated.

SQL> select * from dula;
select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.36.08.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

select * from dula
ORA-00942: table or view does not exist

Like shown above, you can capture PLS errors too.

If you want to execute it through scripts, you can do it like this, and later spool the errors into a file. I kept these three lines in the sperrorlog_test.sql file – truncate table sperrorlog; selct * from dual; select * from dula;

SQL> @D:\sperrorlog_test.sql;

Table truncated.

SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select TIMESTAMP, SCRIPT, STATEMENT, MESSAGE from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.17.000000 AM

D:\sperrorlog_test.sql;
SP2-0734: unknown command beginning "D:\sperror..." - rest of line ignored.


TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.27.000000 AM
D:\sperrorlog_test.sql
selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.


TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.27.000000 AM
D:\sperrorlog_test.sql
select * from dula
ORA-00942: table or view does not exist

SQL>

Check Oracle documentation on SPERRORLOG.

In addition to above, if you want to be particularly specific about each session’s error to be spooled into a file you could do this –

SQL> set errorlogging on identifier my_session_identifier

Above mentioned IDENTIFIER keyword becomes a column in SPERRORLOG table. It would get populated with the string value “my_session_identifier”. Now you just need to do this –

SQL> select timestamp, username, script, statement, message 2 from sperrorlog 3 where identifier = 'my_session_identifier';

To spool the session specific errors into a file, just do this –

SQL> spool error.log SQL> select timestamp, username, script, statement, message 2 from sperrorlog 3 where identifier = 'my_session_identifier'; 
SQL> spool off 

NOTE : Whenever ROLLBACK is issued in the session, the feature fails to log the errors. Let’s look at the issue.

SQL> show errorlogging;
errorlogging is OFF

SQL> set errorlogging on;

SQL> show errorlogging;
errorlogging is ON TABLE SPERRORLOG

SQL> insert into emp(empno) values ('abcd');
insert into emp(empno) values ('abcd')
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select timestamp, script, message from sperrorlog;

TIMESTAMP
--------------------------------------------------------------------------------
SCRIPT

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

MESSAGE
--------------------------------------------------------------------------------

03-MAY-14 01.23.48.000000 AM

ORA-01722: invalid number

SQL> rollback;

Rollback complete.

SQL> select timestamp, script, message from sperrorlog;

no rows selected

Now that’s something which we don’t want to happen. If we use this feature as an automated error logging for any batch process or scheduled jobs, and if our program is designed to rollback the session if an error occurs, then this feature will never work for us. It will never capture the error once the session is rolled back. But we want to know what exactly caused the error. Please visit SQL*Plus error logging – workaround for ROLLBACK issue to see the workaround for this issue.

How to generate trace file – SQL Trace and TKPROF in Oracle

Tags

Oracle SQL trace, Performance tuning, SQL, tkprof, tracing event

It is a frequently asked question in almost all the Oracle forums. There have had been numerous questions/posts regarding “But how to generate the trace file?” Well, it might seem a heck of a task, however, looking it step by step will make you understand that it is actually not that difficult.

Usually, database application developers do not have the required/necessary permissions/privileges to do all the steps that I will mention below. However, most of the steps could be done by application developer. A few steps will need a DBA privilege.

Let me take you through the steps :

Turning the tracing event ON with a proper level

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

The different levels of tracing event:-

0 – No trace. Like switching sql_trace off.

2 – The equivalent of regular sql_trace.

4 – The same as 2, but with the addition of bind variable values.

8 – The same as 2, but with the addition of wait events.

12 – The same as 2, but with both bind variable values and wait events.

Giving a proper name/number for the trace file identifier

 SQL> alter session set tracefile_identifier = 'test_plan1';

Execute the SQL query for which you want the trace

  SELECT col1, col2…..FROM t1, t2…..WHERE…..;

Turning the tracing event OFF

  SQL> alter session set events '10046 trace name context off';

Finding the directory where the trace files are generated(this is the directory which is in “init.ora” file)

SQL> SHOW PARAMETER user_dump_dest;

NAME                                 TYPE        VALUE

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

user_dump_dest                       string      /opt/oracle/diag/rdbms/lalit/trace

Finding the trace file in the UNIX directory

lalit@Orcl:DEV$ cd /opt/oracle/diag/rdbms/lalit/trace

lalit@Orcl:/opt/oracle/diag/rdbms/lalit/trace DEV$ ls -lrt *test_plan1.trc

-rw-r-----    1 oracle   dba         1076036 Jan 01 06:46 lalit_ora_30539942_test_plan1.trc

-rw-r-----    1 oracle   dba         1903274 Jan 01 06:51 lalit_ora_33030344_test_plan1.trc

So now we have the trace file, we need to get the tkprof output:

lalit@Orcl:/opt/oracle/diag/rdbms/lalit/trace DEV$ tkprof lalit_ora_30539942_test_plan1.trc tkprof.out

TKPROF: Release 11.2.0.4.0 - Development on Wed Jan 1 18:53:33 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


lalit@Orcl:/opt/oracle/diag/rdbms/lalit/trace DEV$ ls -lrt *.out

-rw-r--r--    1 oracle   dba           17273 Jan 01 06:53 tkprof.out

Analyze the tkprof.out file to find the issue with the SQL query.

From 10g on wards all the tracing options have been centralized into DBMS_MONITOR package. You can look for the package in docs http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm