Error looging on Table in PLSQL Oracle 11g Feature

CREATE TABLE TEST(

  id           NUMBER(10)    NOT NULL,

  code         VARCHAR2(10)  NOT NULL,

  description  VARCHAR2(50),

  CONSTRAINT dest_pk PRIMARY KEY (id)

);

-- Create the error logging table.

BEGIN

  DBMS_ERRLOG.create_error_log (dml_table_name => 'TEST');

END;

It will create ERR$_TEST new table

INSERT INTO TEST

SELECT *

FROM   source

LOG ERRORS INTO err$_test ('INSERT') REJECT LIMIT UNLIMITED;

SELECT ora_err_number$, ora_err_mesg$

FROM   err$_test

WHERE  ora_err_tag$ = 'INSERT';

Difference between 2 dates or Number of years, months, days, hours between 2 dates in oracle

SELECT ename, EXTRACT(YEAR FROM (SYSDATE - hiredate) YEAR TO MONTH )
   || ' years '
   || EXTRACT(MONTH FROM (SYSDATE - hiredate) YEAR TO MONTH )
   || ' months'  "Interval"
FROM emp ;

ENAME Interval
SMITH 35 years 4 months
ALLEN 35 years 1 months
WARD 35 years 1 months
JONES 35 years 0 months
MARTIN 34 years 6 months
BLAKE 34 years 11 months
CLARK 34 years 10 months
SCOTT 29 years 0 months
KING 34 years 5 months
TURNER 34 years 7 months
ADAMS 28 years 10 months
JAMES 34 years 4 months
FORD 34 years 4 months
MILLER 34 years 2 months

SELECT ename,
   EXTRACT(DAY FROM (SYSDATE - hiredate) DAY TO SECOND )
   || ' days '
   || EXTRACT(HOUR FROM (SYSDATE - hiredate) DAY TO SECOND )
   || ' hours' "Interval"
FROM emp;


ENAME Interval
SMITH 12892 days 14 hours
ALLEN 12827 days 14 hours
WARD 12825 days 14 hours
JONES 12786 days 14 hours
MARTIN 12607 days 14 hours
BLAKE 12757 days 14 hours
CLARK 12718 days 14 hours
SCOTT 10578 days 14 hours
KING 12557 days 14 hours
TURNER 12627 days 14 hours
ADAMS 10544 days 14 hours
JAMES 12541 days 14 hours
FORD 12541 days 14 hours
MILLER 12490 days 14 hours

Pipe line function in PLSQL Oracle

This pipe line function used for call collection in select statement as a table instead of looping row by row.



create type t_row as object(
id number,
name varchar2(100),
dob date);

create type t_tab is table of  t_row;


/* Formatted on 2016/03/29 11:38 (Formatter Plus v4.8.8) */
CREATE or replace FUNCTION g_tab_rf (p_no NUMBER)
   RETURN t_tab
AS
   v_tab   t_tab := t_tab ();
BEGIN
   FOR i IN 1 .. p_no
   LOOP
      v_tab.EXTEND;
      v_tab (v_tab.LAST) := t_row (i, ' NAMe ' || i, SYSDATE + i);
   END LOOP;

   RETURN v_tab;
END;


select * from table(g_tab_rf(10));



CREATE OR REPLACE FUNCTION g_tab_prf (p_no NUMBER)
   RETURN t_tab PIPELINED
AS
BEGIN
   FOR i IN 1 .. p_no
   LOOP
      PIPE ROW (t_row (i, ' Name ' || i, SYSDATE + i));
   END LOOP;

   RETURN;
   EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    RAISE;
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('OTHERS Handler');
    RAISE;
END;


select * from table(g_tab_prf(10))
where rownum <= 5;

 --Another Example

 -- Build package containing record and table types internally.
CREATE OR REPLACE PACKAGE ptf_api AS
  TYPE t_ptf_row IS RECORD (
    id           NUMBER,
    description  VARCHAR2(50)
  );

  TYPE t_ptf_tab IS TABLE OF t_ptf_row;

  FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_ptf_tab PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY ptf_api AS

  FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_ptf_tab PIPELINED IS
    l_row  t_ptf_row;
  BEGIN
    FOR i IN 1 .. p_rows LOOP
      l_row.id := i;
      l_row.description := 'Description for ' || i;
      PIPE ROW (l_row);
    END LOOP;
 
    RETURN;
  END;
END;
/

SELECT *
FROM   TABLE(ptf_api.get_tab_ptf(10))
ORDER BY id DESC;


SELECT object_name, object_type
FROM   user_objects;




CREATE OR REPLACE FUNCTION get_stat (p_stat IN VARCHAR2) RETURN NUMBER AS
  l_return  NUMBER;
BEGIN
  SELECT ms.value
  INTO   l_return
  FROM   v$mystat ms,
         v$statname sn
  WHERE  ms.statistic# = sn.statistic#
  AND    sn.name = p_stat;
  RETURN l_return;
END get_stat;


-- Test table function.
SET SERVEROUTPUT ON
DECLARE
  l_start  NUMBER;
BEGIN
  l_start := get_stat('session pga memory');

  FOR cur_rec IN (SELECT *
                  FROM   TABLE(g_tab_rf(100000)))
  LOOP
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular table function : ' ||
                        (get_stat('session pga memory') - l_start));
                       
                       
l_start := get_stat('session pga memory');    

FOR cur_rec IN (SELECT *
                  FROM   TABLE(g_tab_prf(100000)))
  LOOP
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular table function : ' ||
                        (get_stat('session pga memory') - l_start));                  
END;
/

PLSQL Performance Tuning with Hints in Oracle

/* Append */

The APPEND_VALUES hint in Oracle 11g Release 2 now allows us to take advantage of direct-path inserts when insert statements include a VALUES clause. Typically we would only want to do this when the insert statement is part of bulk operation using the FORALL statement. We will use the following table to demonstrate the effect of the hint.
This is because during a regular (conventional-path) insert, Oracle tries to use up any free space currently allocated to the table, including space left from previous delete operations. In contrast direct-path inserts ignore existing free space and append the data to the end of the table. After preparing the base table we time how long it takes to perform conventional-path insert as part of the FORALL statement. Next, we repeat the same test, but this time use a the APPEND_VALUES hint to give us direct-path inserts.

Ex:

INSERT /*+ APPEND */ INTO forall_test
    SELECT level, TO_CHAR(level), 'Description: ' || TO_CHAR(level)
    FROM   dual
    CONNECT BY level <= l_size;

/* PARALLEL(3) */

This query will execute in multi threads like 3 parallel operations of employee table. So its will be fast.

Ex:
SELECT /*+ PARALLEL(employees 3) */ e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id=d.department_id;

Ex:
SELECT /*+ PARALLEL(4) */ hr_emp.last_name, d.department_name
FROM   employees hr_emp, departments d
WHERE  hr_emp.department_id=d.department_id;

/* FIRST_ROWS(10) */

This hint we use when we need to get only top 10 rows then will use this.

Ex: SELECT /*+ FIRST_ROWS(10) */ * FROM employees;

/* LEADING(e1) */
This hint we use when multiple table in the query and consider which table should take first preference while executing we can give leading table alias.


/* INDEX (t1 t1_idx1) */
This hint we use for forcefully consider the index while executing the query.

SELECT /*+ index(t1 t1_abc) index(t2 t2_abc) */ COUNT(*)
FROM t1, t2
WHERE t1.col1 = t2.col1;

/*+ DRIVING_SITE([@queryblock] ) */
Forces query execution to be done at a user selected  site rather than at a site selected by the database. This hint is useful if you are using distributed query optimization.

EX:
SELECT /*+ DRIVING_SITE(p1) AAA */ p1.first_name, p2.first_name, p2.last_name
FROM person p1, person@psoug_user p2
WHERE p1.person_id = p2.person_id
AND p1.first_name <> p2.first_name;

/* Ordered table1 table2*/ 
this hint is which order table should join in From clause.

How to use DBMS_PROFILER in PLSQL for running the procedure or function in Oracle Database

Ask Oracle DBA's to setup DBMS_PROFILER package before we use.

Create your PLSQL procedure or function in your instance.

SQL> execute dbms_profiler.start_profiler('YOUR_OBJECT');

PL/SQL procedure successfully completed.

SQL> exec YOUR_OBJECT;

PL/SQL procedure successfully completed.

SQL> execute dbms_profiler.stop_profiler;

PL/SQL procedure successfully completed.

See the execution timings by running this query:

select s.text ,
       p.total_occur ,
       p.total_time/1000000000 total_time,
       p.min_time/1000000000 min_time,
       p.max_time/1000000000 max_time
from plsql_profiler_data p, user_source s, plsql_profiler_runs r
where p.line# = s.line
and   p.runid = r.runid
and   r.run_comment = 'YOUR_OBJECT'
and   s.name ='YOUR_OBJECT'


Ex:

Step 1)
create table tab (col1 varchar2(30), col2 varchar2(30));

Step 2)
create or replace procedure TEST
is
 vNumber number;
begin
 for i in 1..100000 loop
   vNumber := dbms_random.random;
   insert into tab values (vNumber,vNumber);
 end loop;
end;


Step 3)
SQL> execute dbms_profiler.start_profiler('TEST');

PL/SQL procedure successfully completed.

SQL> exec TEST;

PL/SQL procedure successfully completed.

SQL> execute dbms_profiler.stop_profiler;

PL/SQL procedure successfully completed.

Step 4)
SQL> select s.text ,
  2         p.total_occur ,
  3         p.total_time/1000000000 total_time,
  4         p.min_time/1000000000 min_time,
  5         p.max_time/1000000000 max_time
  6  from plsql_profiler_data p, user_source s, plsql_profiler_runs r
  7  where p.line# = s.line
  8  and   p.runid = r.runid
  9  and   r.run_comment = 'TEST'
 10* and   s.name ='TEST'
SQL> /

TEXT                           TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
------------------------------ ----------- ---------- -------- --------
procedure binds                          1         .0       .0       .0
procedure binds                          3         .0       .0       .0
procedure binds                          0         .0       .0       .0
for i in 1..100000 loop             100001         .0       .0       .0
vNumber := dbms_random.random;      100000         .2       .0       .0
insert into t1 values (vNumber      100000        6.9       .0       .4
,vNumber);              
end;                                     1         .0       .0       .0
procedure binds                          2         .0       .0       .0

8 rows selected.

From column how can i find which table has that column in Oracle

select * from all_tab_columns
where owner = 'SCOTT'
and table_name = 'EMP'
and column_name = 'ENAME';



ALL_TAB_COLUMNS describes the columns of the tables, views, and clusters accessible to the current user. To gather statistics for this view, use the ANALYZE SQL statement or the DBMS_STATS package


DBA_TAB_COLUMNS describes the columns of all tables, views, and clusters in the database.


USER_TAB_COLUMNS describes the columns of the tables, views, and clusters owned by the current user. This view does not display the OWNER column.

To find Dependencies in PLSQL objects in Oracle

All the object will be in the table: USER_OBJECTS
All the object dependencies are in this table USER_DEPENDENCIES


SELECT D.REFERENCED_NAME, O.STATUS
FROM USER_DEPENDENCIES D, USER_OBJECTS O
WHERE D.NAME = O.OBJECT_NAME
AND O.OBJECT_NAME = :OBJECT_NAME;

Table or Index or Partition Size in Oracle PLSQL database

You can find everything from this table

select * from user_segments;


Distinct Segment Types:
LOBINDEX
TABLE SUBPARTITION
TABLE PARTITION
LOBSEGMENT
TABLE
INDEX

Bulk collect and FOR ALL in PLSQL Oracle

DECLARE
   CURSOR c_orders
   IS
      SELECT   customer_id, product_id, business_date,
               SUM (price * quantity) sales_amount
          FROM orders
      GROUP BY customer_id, product_id, business_date;

   TYPE t_orders IS TABLE OF c_orders%TYPE;

   v_orders   t_orders;
     e_bulk_exception        EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_bulk_exception, -24381);
BEGIN
   OPEN c_orders;

   LOOP
     BEGIN
      FETCH c_orders
      BULK COLLECT INTO t_orders LIMIT 50000;

      EXIT WHEN t_orders.COUNT = 0;
for all i in 1..t_orders.count SAVE EXCEPTIONS
            INSERT INTO daily_sales(customer_id, product_id, business_date,
                   sales_amount
                  )
                 VALUES (t_orders(i).customer_id, t_orders(i).product_id, t_orders(i).business_date,
                   t_orders(i).sales
                  );
         COMMIT;
      EXCEPTION
         WHEN e_bulk_exception
         THEN
           
               DBMS_OUTPUT.put_line (   'BULKCOEECT COUNT'
                                     || SQL%BULK_EXCEPTIONS.COUNT
                                    );

               FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
               LOOP
                  DBMS_OUTPUT.put_line
                                    (   'BULKCOEECT message '
                                     || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
                                     || SQL%BULK_EXCEPTIONS (i).ERROR_CODE
                                    );
               END LOOP;
       
      END;
   END LOOP;

   CLOSE c_orders;

   COMMIT;
END;

Handiling exceptions in Bulk Collections or Bulk Inserts or Bulk operations in Oracle plsql

/* Formatted on 2016/02/11 11:45 (Formatter Plus v4.8.8) */
SET SERVEROUTPUT ON

CREATE TABLE bulk_exception (
  id  NUMBER(10) NOT NULL
);


DECLARE
   TYPE t_tab IS TABLE OF bulk_exception%ROWTYPE;

   l_tab           t_tab     := t_tab ();
   l_error_count   NUMBER;
   ex_dml_errors   EXCEPTION;
   PRAGMA EXCEPTION_INIT (ex_dml_errors, -24381); -- To initialize exception
BEGIN
   -- Fill the collection.
   FOR i IN 1 .. 100
   LOOP
      l_tab.EXTEND;
      l_tab (l_tab.LAST).ID := i;
   END LOOP;

   -- Cause a failure.
   l_tab (50).ID := NULL;
   l_tab (51).ID := NULL;

   EXECUTE IMMEDIATE 'TRUNCATE TABLE bulk_exception';

   -- Perform a bulk operation.
   BEGIN
      FORALL i IN l_tab.FIRST .. l_tab.LAST SAVE EXCEPTIONS
         INSERT INTO bulk_exception
              VALUES l_tab (i);
   EXCEPTION
      WHEN ex_dml_errors
      THEN
         l_error_count := SQL%BULK_EXCEPTIONS.COUNT;  -- error count
         DBMS_OUTPUT.put_line ('Number of failures: ' || l_error_count);

         FOR i IN 1 .. l_error_count
         LOOP
            DBMS_OUTPUT.put_line
                               (   'Error: '
                                || i
                                || ' Array Index: '
                                || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
                                || ' Message: '
                                || SQLERRM
                                        (-SQL%BULK_EXCEPTIONS (i).ERROR_CODE)
                               );
         END LOOP;
   END;
END;

Oracle Hints in SQL and PLSQL

Oracle hints we use in the SQL 's to improve performance. But some time it will reduce performance too so try test possibilities and use them properly.




Some of the more useful hints are:



The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).

ORDERED - usually with USE_NL to get Oracle to not hash join




INDEX(t index_name) - where Oracle chooses the wrong index over the correct one




NO_INDEX - prevent an index from being used




INDEX_COMBINE - merging bitmap indexes (use when Oracle does not merge bitmap indexes)




FIRST_ROWS(n) - when you only want the first few rows




PARALLEL - to force parallel query on certain specific queries




GATHER_PLAN_STATISTICS - used as a handy sql trace




DYNAMIC_SAMPLING - used as alternative to statistics for large-scale warehouse queries




OPT_PARAM - used to control optimizer behavior at query level (added in 10.2.0.3)




QB_NAME - specify query block name when tuning complex queries. It helps in finding a particular query for troubleshooting (10 and up)




CARDINALITY - give the optimizer better information

[edit]Some examples




Example


SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb) */ b.application_id , b.set_of_books_id , b.personnel_id, p.vendor_id Personnel, p.segment1 PersonnelNumber, p.vendor_name Name FROM jl_br_journals j, jl_br_balances b, gl_code_combinations glcc, fnd_flex_values_vl glf, gl_periods gp, gl_sets_of_books gsb, po_vendors p WHERE ...


Note that the hints could have been also been in this format: SELECT --+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb)
SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id FROM employees WHERE employee_id = 7566;







suggesting that a FULL TABLE SCAN method be used:

SELECT /*+ FULL(x) */ FROM tab1 x WHERE col1 = 10;




Suggest that Oracle uses a specific index:

SELECT /*+ INDEX(x emp_idx1) */ ... FROM scott.emp x...




Suggest that Oracle DOES NOT USE a specific index:

SELECT /*+ NO_INDEX(x emp_idx1) */ ... FROM scott.emp x...

SQL Profile to attach the existing SQL to take best execution plan in Oracle

Oracle default it will get better plan from using cost based optimizer based on Table indexs, Data distribution and statistics. Some time even you can check is there any better plan other than this in OEM or SQLT for that query. if you see any better execution plan you can select that SQL profile and attach to that SQL from next onwords the query will execute with the better SQL profile plan we attached to it.

But based on today statistics, code, data distribution and indexes this is the better plan. In feature if any of the this changes like big data change or indexs or statistics or distribution changes our sql profile might not be the best then oracle automatically disable this profile and use the best plan give my oracle.


  

Difference between Rule Based Optimizer and Cost Based Optimizer in Oracle

What is the difference between Rule based Optimizer and Cost based Optimizer in oracle?

In brief the rule-based method means that when executing a query the database must follow certain predefined rules and matter what data is stored in affected database tables. The cost-based method means the database must decide which query execution plan to choose using best guess approach that takes into account what data is stored in db.

A long time ago the only optimizer in the Oracle database was the Rule-Based Optimizer (RBO). Basically the RBO used a set of rules to determine how to execute a query. If an index was available on a table the RBO rules said to always use the index. There are some cases where the use of an index slowed down a query. For example assume someone put an index on the GENDER column which holds one of two values MALE and FEMALE.

Then someone issues the following query:

SELECT * FROM emp WHERE gender 'FEMALE'; If the above query returned approximately 50 of the rows then using an index would actually slow things down. It would be faster to read the entire table and throw away all rows that have MALE values. Experts in Oracle query optimization have come to a rule of thumb that says if the number of rows returned is more than 5-10 of the total table volume using an index would slow things down. The RBO would always use an index if present because its rules said to.

It became obvious that the RBO armed with its set of discrete rules did not always make great decisions. The biggest problem with the RBO was that it did not take the data distribution into account. So the Cost-Based Optimizer (CBO) was born. The CBO uses statistics about the table its indexes and the data distribution to make better informed decisions.

Using our previous example assume that the company has employees that are 95 female and 5 male. If you query for females then you do not want to use the index. If you query for males then you would like to use the index. The CBO has information at hand to help make these kind of determinations that were not available in the old RBO.

What is Cost-Based Optimization?

The Oracle cost-based optimizer is designed to determine the most efficient way to carry out a SQL statement, but it can’t reach do this without good, up-to-date statistical information on the data being accessed. The optimizer can use a rules-based approach to work without statistical information, but this approach is less intelligent than the cost-based approach. With the rules-based approach, the optimizer chooses an execution plan based a set of rules about what types of operations usually execute faster than other types. With the cost-based approach, the optimizer factors in statistical information about the contents of the particular schema objects (tables, clusters, or indexes) being accessed.

Rule Based Optimizer Obsolescence

The Rule Based Optimizer (RBO) is now obsolete in Oracle 10g. The functionality is still present but no new functionality has been included in it and it is no longer supported by Oracle. It is only present to provide backwards compatibility during the migration to the query optimizer (Cost Based Optimizer). The results of this osolescence are:

- The CHOOSE and RULE options for the OPTIMIZER_MODE parameter still exist but are no longer supported.
- The default value for the OPTIMIZER_MODE parameter is ALL_ROWS.
- The CHOOSE and RULE optimizer hints still exist but are no longer supported.
- Code requiring the RBO must be migrated to use the query optimizer.

refer from:
http://www.erpgreat.com/oracle-database/rule-based-and-cost-based-optimizer.htm

The goal of SQL tuning is to execute your SQL with the absolute minimum amount of I/O.  See my related SQL Optimization tips at the end this article.

---

Oracle's cost-based SQL optimizer (CBO) is an extremely sophisticated component of Oracle that governs the execution for every Oracle query. The CBO has evolved into one of the world's most sophisticated software components, and it has the challenging job of evaluating any SQL statement and generating the "best" execution plan for the statement.

Because the CBO determines the execution speed for every Oracle query, the Oracle professional must understand how the CBO is influenced by Oracle external issues, internal statistics, and data distribution.
In this first installment of a two-part article, we will cover the following CBO topics:

• CBO parameters. We will start by reviewing the basic optimizer modes within the CBO and then           drill down and examine specific parameters that influence the behavior of the CBO.
   
• CBO statistics. We will examine the importance of gathering proper CBO statistics with    dbms_stats and review techniques for ensuring that execution plans remain stable. We will also look         at techniques for migrating statistics between systems and examine how developers can optimize their
  SQL in a test environment and confidently migrate SQL into production without fear of changing execution plans.

Refer from:
http://www.dba-oracle.com/art_otn_cbo.htm

Difference between Btree Index and Bitmap Index in Oracle or Difference between Bitmap index and Btree index

Bitmap index is used for only more duplicate values in the column. Do not user on Unique column. because it will take more space and also more physical read for retrieve information than btree index.


Btree indexes used for columns which have unique values.

In summary, bitmap indexes are best suited for DSS regardless of cardinality for these reasons:

• With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)
• With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).
• Most important, bitmap indexes in DSS systems support ad hoc queries, whereas B-tree indexes do not. More specifically, if you have a table with 50 columns and users frequently query on 10 of them—either the combination of all 10 columns or sometimes a single column—creating a B-tree index will be very difficult. If you create 10 bitmap indexes on all these columns, all the queries can be answered by these indexes, whether they are queries on all 10 columns, on 4 or 6 columns out of the 10, or on a single column. The AND_EQUAL hint provides this functionality for B-tree indexes, but no more than five indexes can be used by a query. This limit is not imposed with bitmap indexes.

In contrast, B-tree indexes are well suited for OLTP applications in which users' queries are relatively routine (and well tuned before deployment in production), as opposed to ad hoc queries, which are much less frequent and executed during nonpeak business hours. Because data is frequently updated in and deleted from OLTP applications, bitmap indexes can cause a serious locking problem in these situations.

The data here is fairly clear. Both indexes have a similar purpose: to return results as fast as possible. But your choice of which one to use should depend purely on the type of application, not on the level of cardinality.

For more information
http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html

Random String from SQL Oracle and Random number in sql PLSQL

select dbms_random.string('U',30) rnadomstring, dbms_random.value(1000,7000) rnadomint from dual ;

Get Sequence of numbers from SQL query in ORACLE SQL PLSQL

SELECT level, TO_CHAR(level), 'Description: ' || TO_CHAR(level)
    FROM   dual
    CONNECT BY level <= 1000;

How to get nth sting from comma delimiter string or How to USE REGEXP_SUBSTR in Oracle SQL PLSQL

To get nth record from Comma Delimiter String.

SELECT REGEXP_SUBSTR ('ABC,BDB,wew,sds,ety,thyrty', '[^,]+',  1,   4)    aa FROM DUAL;


4th string: sds is the output.


To get nth record from Pipe Delimiter String.

SELECT REGEXP_SUBSTR ('ABC|BDB|wew|sds|ety|thyrty', '[^|]+',  1,   4)    aa FROM DUAL;


4th string: sds is the output.


http://www.techonthenet.com/oracle/functions/regexp_substr.php

• « Previous
 
• Next »

ORACLE/PLSQL: REGEXP_SUBSTR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL REGEXP_SUBSTR function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL REGEXP_SUBSTR function is an extension of the SUBSTR function. This function, introduced in Oracle 11g, will allow you to extract a substring from a string using regular expression pattern matching.

SYNTAX

The syntax for the REGEXP_SUBSTR function in Oracle is:

REGEXP_SUBSTR( string, pattern [, start_position [,  nth_appearance [, match_parameter [, sub_expression ] ] ] ] ] )

Parameters or Arguments

string

The string to search. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

pattern

The regular expression matching information. It can be a combination of the following:

Value	Description
^	Matches the beginning of a string. If used with a match_parameter of 'm', it matches the start of a line anywhere within expression.
$	Matches the end of a string. If used with a match_parameter of 'm', it matches the end of a line anywhere within expression.
*	Matches zero or more occurrences.
+	Matches one or more occurrences.
?	Matches zero or one occurrence.
.	Matches any character except NULL.
|	Used like an "OR" to specify more than one alternative.
[ ]	Used to specify a matching list where you are trying to match any one of the characters in the list.
[^ ]	Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.
( )	Used to group expressions as a subexpression.
{m}	Matches m times.
{m,}	Matches at least m times.
{m,n}	Matches at least m times, but no more than n times.
\n	n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
[..]	Matches one collation element that can be more than one character.
[::]	Matches character classes.
[==]	Matches equivalence classes.
\d	Matches a digit character.
\D	Matches a nondigit character.
\w	Matches a word character.
\W	Matches a nonword character.
\s	Matches a whitespace character.
\S	matches a non-whitespace character.
\A	Matches the beginning of a string or matches at the end of a string before a newline character.
\Z	Matches at the end of a string.
*?	Matches the preceding pattern zero or more occurrences.
+?	Matches the preceding pattern one or more occurrences.
??	Matches the preceding pattern zero or one occurrence.
{n}?	Matches the preceding pattern n times.
{n,}?	Matches the preceding pattern at least n times.
{n,m}?	Matches the preceding pattern at least n times, but not more than m times.

start_position

Optional. It is the position in string where the search will start. If omitted, it defaults to 1 which is the first position in the string.

nth_appearance

Optional. It is the nth appearance of pattern in string. If omitted, it defaults to 1 which is the first appearance of pattern in string.

match_parameter

Optional. It allows you to modify the matching behavior for the REGEXP_SUBSTR function. It can be a combination of the following:

Value	Description
'c'	Perform case-sensitive matching.
'i'	Perform case-insensitive matching.
'n'	Allows the period character (.) to match the newline character. By default, the period is a wildcard.
'm'	expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the position of those characters in expression. By default, expression is assumed to be a single line.
'x'	Whitespace characters are ignored. By default, whitespace characters are matched like any other character.

subexpression

Optional. This is used when pattern has subexpressions and you wish to indicate which subexpression in pattern is the target. It is an integervalue from 0 to 9 indicating the subexpression to match on in pattern.

Note:

• If there are conflicting values provided for match_parameter, the REGEXP_SUBSTR function will use the last value.
• If you omit the match_behavior parameter, the REGEXP_SUBSTR function will use the NLS_SORT parameter to determine if it should use a case-sensitive search, it will assume that string is a single line, and assume the period character to match any character (not the newline character).
• If the REGEXP_SUBSTR function does not find any occurrence of pattern, it will return NULL.
• See also the SUBSTR function.

APPLIES TO

The REGEXP_SUBSTR function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g

EXAMPLE - MATCH ON WORDS

Let's start by extracting the first word from a string.

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)')
FROM dual;

Result: 'TechOnTheNet '

This example will return 'TechOnTheNet ' because it will extract all non-whitespace characters as specified by (\S*) and then the first whitespace character as specified by (\s). The result will include both the first word as well as the space after the word.

If you didn't want to include the space in the result, we could modify our example as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)')
FROM dual;

Result: 'TechOnTheNet'

This example would return 'TechOnTheNet' with no space at the end.

If we wanted to find the second word in the string, we could modify our function as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 2)
FROM dual;

Result: 'is '

This example would return 'is ' with a space at the end of the string.

If we wanted to find the third word in the string, we could modify our function as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 3)
FROM dual;

Result: 'a '

This example would return 'a ' with a space at the end of the string.

EXAMPLE - MATCH ON DIGIT CHARACTERS

Let's look next at how we would use the REGEXP_SUBSTR function to match on a single digit character pattern.

For example:

SELECT REGEXP_SUBSTR ('2, 5, and 10 are numbers in this example', '\d')
FROM dual;

Result: 2

This example will extract the first numeric digit from the string as specified by \d. In this case, it will match on the number 2.

We could change our pattern to search for a two-digit number.

For example:

SELECT REGEXP_SUBSTR ('2, 5, and 10 are numbers in this example', '(\d)(\d)')
FROM dual;

Result: 10

This example will extract a number that has two digits side-by-side as specified by (\d)(\d). In this case, it will skip over the 2 and 5 numeric values and return 10.

Now, let's look how we would use the REGEXP_SUBSTR function with a table column and search for a two digit number.

For example:

SELECT REGEXP_SUBSTR (address, '(\d)(\d)')
FROM contacts;

In this example, we are going to extract the first two-digit value from the address field in the contacts table.

EXAMPLE - MATCH ON MORE THAN ONE ALTERNATIVE

The next example that we will look at involves using the | pattern. The | pattern is used like an "OR" to specify more than one alternative.

For example:

SELECT REGEXP_SUBSTR ('Anderson', 'a|e|i|o|u')
FROM dual;

Result: 'e'

This example will return 'e' because it is searching for the first vowel (a, e, i, o, or u) in the string. Since we did not specify a match_parameter value, the REGEXP_SUBSTR function will perform a case-sensitive search which means that the 'A' in 'Anderson' will not be matched.

We could modify our query as follows to perform a case-insensitive search as follows:

SELECT REGEXP_SUBSTR ('Anderson', 'a|e|i|o|u', 1, 1, 'i')
FROM dual;

Result: 'A'

Now because we have provide a match_parameter of 'i', the query will return 'A' as the result. This time, the 'A' in 'Anderson' will be found as a match.

Now, let's quickly show how you would use this function with a column.

So let's say we have a contact table with the following data:

contact_id	last_name
1000	Anderson
2000	Smith
3000	Johnson

Now, let's run the following query:

SELECT contact_id, last_name, REGEXP_SUBSTR (last_name, 'a|e|i|o|u', 1, 1, 'i') AS "First Vowel"
FROM contacts;

These are the results that would be returned by the query:

contact_id	last_name	First vowel
1000	Anderson	A
2000	Smith	i
3000	Johnson	o

EXAMPLE - MATCH ON NTH_OCCURRENCE

The next example that we will look at involves the nth_occurrence parameter. The nth_occurrence parameter allows you to select which occurrence of the pattern you wish to extract the substring for.

First Occurrence

Let's look at how to extract the first occurrence of a pattern in a string.

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 1, 'i')
FROM dual;

Result: 'e'

This example will return 'e' because it is extracting the first occurrence of a vowel (a, e, i, o, or u) in the string.

Second Occurrence

Next, we will extract for the second occurrence of a pattern in a string.

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 2, 'i')
FROM dual;

Result: 'O'

This example will return 'O' because it is extracting the second occurrence of a vowel (a, e, i, o, or u) in the string.

Third Occurrence

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 3, 'i')
FROM dual;

Result: 'e'

This example will return 'e' because it is extracting the third occurrence of a vowel (a, e, i, o, or u) in the string.

• Tutorial Home
• « Previous
 
• Next »

Share this page:

ORACLE BASICS

(scroll to see more)

• ALIASES
• AND
• AND & OR
• BETWEEN
• COMPARISON OPERATORS
• DELETE
• DISTINCT
• EXISTS
• FROM
• GROUP BY
• HAVING
• IN
• INSERT
• INSERT ALL
• INTERSECT
• IS NOT NULL
• IS NULL
• JOIN
• LIKE
• MINUS
• NOT
• OR
• ORDER BY
• PIVOT
• REGEXP_LIKE
• SELECT
• SUBQUERY
• TRUNCATE
• UNION
• UNION ALL
• UPDATE
• WHERE
• --- end ---

ORACLE ADVANCED

(scroll to see more)

• After Delete Trigger
• After Insert Trigger
• After Update Trigger
• Alter Table
• Alter Tablespace
• Before Delete Trigger
• Before Insert Trigger
• Before Update Trigger
• Change Password
• Check Constraints
• Close Cursor
• Comments in SQL
• Commit
• Create Schema
• Create Schema Statement
• Create Table
• Create Table As
• Create Tablespace
• Create User
• Cursor Attributes
• Cursor CURRENT OF
• CURSOR FOR Loop
• Cursor FOR UPDATE
• Data Types
• Declare Cursor
• Declare Variables
• Defined Exception
• Disable All Triggers
• Disable Foreign Key
• Disable Trigger
• Drop Foreign Key
• Drop Table
• Drop Tablespace
• Drop Trigger
• Drop User
• Enable All Trigger
• Enable Foreign Key
• Enable Trigger
• Error Messages
• EXIT
• Fetch Cursor
• Find Default Tablespace
• Find Users
• Find Users Logged In
• FOR Loop
• Foreign Key
• Foreign Key (cascade)
• Foreign Key (set null)
• Functions
• Global Temp
• GOTO
• Grant/Revoke Privileges
• IF-THEN-ELSE
• Indexes
• Literals
• Local Temp
• Lock Table
• LOOP
• Open Cursor
• Primary Key
• Procedures
• REPEAT UNTIL
• Roles
• Rollback
• Sequences
• Set Default Tablespace
• Set Transaction
• SQLCODE
• SQLERRM
• Synonyms
• System Exception
• System Tables
• Unique Constraints
• Version
• View
• WHEN OTHERS
• WHILE Loop
• --- end ---

ORACLE FUNCTIONS

(scroll to see more)

• ABS
• ACOS
• ADD_MONTHS
• ASCII
• ASCIISTR
• ASIN
• ATAN
• ATAN2
• AVG
• BFILENAME
• BIN_TO_NUM
• BITAND
• CARDINALITY
• CASE
• CAST
• CEIL
• CHARTOROWID
• CHR
• COALESCE
• COMPOSE
• CONCAT
• Concat with ||
• CONVERT
• CORR
• COS
• COSH
• COUNT
• COVAR_POP
• COVAR_SAMP
• CUME_DIST
• CURRENT_DATE
• CURRENT_TIMESTAMP
• DBTIMEZONE
• DECODE
• DECOMPOSE
• DENSE_RANK
• DUMP
• EMPTY_BLOB
• EMPTY_CLOB
• EXP
• EXTRACT
• FLOOR
• FROM_TZ
• GREATEST
• GROUP_ID
• HEXTORAW
• INITCAP
• INSTR
• INSTR2
• INSTR4
• INSTRB
• INSTRC
• LAG
• LAST_DAY
• LEAD
• LEAST
• LENGTH
• LENGTH2
• LENGTH4
• LENGTHB
• LENGTHC
• LISTAGG
• LN
• LNNVL
• LOCALTIMESTAMP
• LOG
• LOWER
• LPAD
• LTRIM
• MAX
• MEDIAN
• MIN
• MOD
• MONTHS_BETWEEN
• NANVL
• NCHR
• NEW_TIME
• NEXT_DAY
• NULLIF
• NUMTODSINTERVAL
• NUMTOYMINTERVAL
• NVL
• NVL2
• POWER
• RANK
• RAWTOHEX
• REGEXP_COUNT
• REGEXP_INSTR
• REGEXP_SUBSTR
• REMAINDER
• REPLACE
• ROUND (dates)
• ROUND (numbers)
• RPAD
• RTRIM
• SESSIONTIMEZONE
• SIGN
• SIN
• SINH
• SOUNDEX
• SQRT
• STDDEV
• SUBSTR
• SUM
• SYSDATE
• SYSTIMESTAMP
• SYS_CONTEXT
• TAN
• TANH
• TO_CHAR
• TO_CLOB
• TO_DATE
• TO_DSINTERVAL
• TO_LOB
• TO_MULTI_BYTE
• TO_NCLOB
• TO_NUMBER
• TO_SINGLE_BYTE
• TO_TIMESTAMP
• TO_TIMESTAMP_TZ
• TO_YMINTERVAL
• TRANSLATE
• TRIM
• TRUNC (dates)
• TRUNC (numbers)
• TZ_OFFSET
• UID
• UPPER
• USER
• USERENV
• VARIANCE
• VAR_POP
• VAR_SAMP
• VSIZE
• --- end ---

Home | About Us | Feedback | Sitemap

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy.

Copyright © 2003-2015 TechOnTheNet.com. All rights reserved.