Oracle/PLSQL: Insert multiple rows with a single INSERT statement

---

Question: How can I insert multiple rows of explicit data in one SQL command in Oracle?
Answer: You can insert multiple rows using the following syntax:

INSERT ALL
   INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
   INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
   INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
SELECT * FROM dual;

Example #1

If you wanted to insert 3 rows into the suppliers table, you could run the following SQL statement:

INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

Example #2

You can also insert multiple rows into multiple tables. For example, if you wanted to insert into both the suppliers and customers table, you could run the following SQL statement:

INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;

This example will insert 2 rows into the suppliers table and 1 row into the customers table.

Oracle/PLSQL Topics: Oracle Error Messages

---

The following is a listing of Oracle Error Messages:
00001-00899

ORA-00001	ORA-00051	ORA-00068	ORA-00257
ORA-00018	ORA-00054	ORA-00071	ORA-00301
ORA-00020	ORA-00057	ORA-00078	ORA-00304
ORA-00023	ORA-00058	ORA-00100	ORA-00361
ORA-00028	ORA-00060	ORA-00107	ORA-00401
ORA-00034	ORA-00063	ORA-00201

00900-00999

ORA-00900	ORA-00917	ORA-00933	ORA-00957
ORA-00902	ORA-00918	ORA-00934	ORA-00960
ORA-00903	ORA-00919	ORA-00935	ORA-00962
ORA-00904	ORA-00920	ORA-00936	ORA-00971
ORA-00905	ORA-00923	ORA-00937	ORA-00972
ORA-00906	ORA-00924	ORA-00938	ORA-00975
ORA-00907	ORA-00925	ORA-00939	ORA-00979
ORA-00908	ORA-00926	ORA-00942	ORA-00980
ORA-00909	ORA-00927	ORA-00946	ORA-00984
ORA-00910	ORA-00928	ORA-00947	ORA-00985
ORA-00911	ORA-00931	ORA-00948	ORA-00995
ORA-00913	ORA-00932	ORA-00955

01000-01399

ORA-01000	ORA-01013	ORA-01039	ORA-01114
ORA-01001	ORA-01014	ORA-01040	ORA-01133
ORA-01002	ORA-01017	ORA-01042	ORA-01200
ORA-01004	ORA-01023	ORA-01045	ORA-01234
ORA-01005	ORA-01031	ORA-01052
ORA-01006	ORA-01033	ORA-01074
ORA-01007	ORA-01034	ORA-01089
ORA-01008	ORA-01035	ORA-01109
ORA-01012	ORA-01037	ORA-01113

01400-01499

ORA-01400	ORA-01422	ORA-01435	ORA-01452
ORA-01401	ORA-01423	ORA-01436	ORA-01453
ORA-01402	ORA-01424	ORA-01437	ORA-01454
ORA-01403	ORA-01425	ORA-01438	ORA-01461
ORA-01404	ORA-01426	ORA-01439	ORA-01465
ORA-01405	ORA-01427	ORA-01440	ORA-01468
ORA-01406	ORA-01428	ORA-01441	ORA-01471
ORA-01407	ORA-01429	ORA-01442	ORA-01476
ORA-01408	ORA-01430	ORA-01446
ORA-01416	ORA-01432	ORA-01448
ORA-01417	ORA-01433	ORA-01449
ORA-01418	ORA-01434	ORA-01451

01500-01999

ORA-01504	ORA-01719	ORA-01785	ORA-01846
ORA-01506	ORA-01722	ORA-01789	ORA-01847
ORA-01555	ORA-01723	ORA-01790	ORA-01858
ORA-01632	ORA-01724	ORA-01810	ORA-01861
ORA-01652	ORA-01727	ORA-01818	ORA-01916
ORA-01653	ORA-01728	ORA-01820	ORA-01918
ORA-01700	ORA-01747	ORA-01821	ORA-01950
ORA-01704	ORA-01756	ORA-01830
ORA-01710	ORA-01775	ORA-01839
ORA-01711	ORA-01779	ORA-01843

02000-06499

ORA-02003	ORA-02268	ORA-02437	ORA-04091
ORA-02011	ORA-02270	ORA-02444	ORA-04092
ORA-02024	ORA-02290	ORA-02449	ORA-04098
ORA-02069	ORA-02291	ORA-03113
ORA-02086	ORA-02292	ORA-04000
ORA-02256	ORA-02293	ORA-04031
ORA-02260	ORA-02298	ORA-04088

06500-09999

ORA-06500	ORA-06508	ORA-06514	ORA-06572
ORA-06501	ORA-06510	ORA-06515	ORA-06575
ORA-06502	ORA-06511	ORA-06550	ORA-08002
ORA-06503	ORA-06512	ORA-06564	ORA-08103

10000 - 12999

ORA-12154

Oracle/PLSQL: SQLERRM Function

---

What does the SQLERRM Function do?

The SQLERRM function returns the error message associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:

EXCEPTION
    WHEN exception_name1 THEN
        [statements]
    WHEN exception_name2 THEN
        [statements]
    WHEN exception_name_n THEN
        [statements]
    WHEN OTHERS THEN
        [statements]
END [procedure_name];

You could use the SQLERRM function to raise an error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);
      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;

Oracle/PLSQL: SQLCODE Function

---

What does the SQLCODE Function do?

The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:

EXCEPTION
    WHEN exception_name1 THEN
        [statements]
    WHEN exception_name2 THEN
        [statements]
    WHEN exception_name_n THEN
        [statements]
    WHEN OTHERS THEN
        [statements]
END [procedure_name];

You could use the SQLCODE function to raise an error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);
      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;

Oracle/PLSQL: WHEN OTHERS Clause

---

What is a WHEN OTHERS clause?

The WHEN OTHERS clause is used to trap all remaining exceptions that have not been handled by your Named System Exceptions and Named Programmer-Defined Exceptions.
The syntax for the WHEN OTHERS clause in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
EXCEPTION
    WHEN exception_name1 THEN
        [statements]
    WHEN exception_name2 THEN
        [statements]
    WHEN exception_name_n THEN
        [statements]
    WHEN OTHERS THEN
        [statements]
END [procedure_name];

The syntax for the WHEN OTHERS clause in a function is:

CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]
    RETURN return_datatype
IS | AS
    [declaration_section]
BEGIN
    executable_section
EXCEPTION
    WHEN exception_name1 THEN
        [statements]
    WHEN exception_name2 THEN
        [statements]
    WHEN exception_name_n THEN
        [statements]
    WHEN OTHERS THEN
        [statements]
END [function_name];

Here is an example of a procedure that uses a WHEN OTHERS clause:

CREATE OR REPLACE PROCEDURE add_new_order
    (order_id_in IN NUMBER, sales_in IN NUMBER)
IS
    no_sales EXCEPTION;
BEGIN
    IF sales_in = 0 THEN
        RAISE no_sales;
ELSE
    INSERT INTO orders (order_id, total_sales )
    VALUES ( order_id_in, sales_in );
END IF;
EXCEPTION
     WHEN DUP_VAL_ON_INDEX THEN
        raise_application_error (-20001,'You have tried to insert a duplicate order_id.');
    WHEN no_sales THEN
        raise_application_error (-20001,'You must have sales in order to submit the order.');
    WHEN OTHERS THEN
        raise_application_error (-20002,'An error has occurred inserting an order.');
END;

In this example, if an exception is encountered that is not a DUP_VAL_ON_INDEX or a no_sales, it will be trapped by the WHEN OTHERS clause.

Frequently Asked Questions

---

Question: Is there any way to get the ORA error number (and/or description) for the errors that will fall into OTHERS?
Something like:

WHEN OTHERS THEN
'Error number ' & Err.Number& ' has happen.'

Answer: Yes, you can use SQLCODE function to retrieve the error number and SQLERRM function to retrieve the error message.
For example, you could raise the error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);
      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;

Oracle/PLSQL: Named Programmer-Defined Exceptions

---

What is a named programmer-defined exception?

Sometimes, it is necessary for programmers to name and trap their own exceptions - ones that aren't defined already by PL/SQL. These are called Named Programmer-Defined Exceptions.
The syntax for the Named Programmer-Defined Exception in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
    exception_name EXCEPTION;
BEGIN
    executable_section
    RAISE exception_name ;
EXCEPTION
    WHEN exception_name THEN
        [statements]
    WHEN OTHERS THEN
        [statements]
END [procedure_name];

The syntax for the Named Programmer-Defined Exception in a function is:

CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]
    RETURN return_datatype
IS | AS
    [declaration_section]
    exception_name EXCEPTION;
BEGIN
    executable_section
    RAISE exception_name ;
EXCEPTION
    WHEN exception_name THEN
        [statements]
    WHEN OTHERS THEN
        [statements]
END [function_name];

Here is an example of a procedure that uses a Named Programmer-Defined Exception:

CREATE OR REPLACE PROCEDURE add_new_order
    (order_id_in IN NUMBER, sales_in IN NUMBER)
IS
    no_sales EXCEPTION;
BEGIN
    IF sales_in = 0 THEN
        RAISE no_sales;
ELSE
    INSERT INTO orders (order_id, total_sales )
    VALUES ( order_id_in, sales_in );
END IF;
EXCEPTION
     WHEN no_sales THEN
        raise_application_error (-20001,'You must have sales in order to submit the order.');
    WHEN OTHERS THEN
        raise_application_error (-20002,'An error has occurred inserting an order.');
END;

In this example, we have declared a Named Programmer-Defined Exception called no_sales in our declaration statement with the following code:

no_sales EXCEPTION;

We've then raised the exception in the executable section of the code:

 IF sales_in = 0 THEN
        RAISE no_sales;

Now if the sales_in variable contains a zero, our code will jump directly to the Named Programmer-Defined Exception called no_sales.

Finally, we tell our procedure what to do when the no_sales exception is encountered by including code in the WHEN clause:

WHEN no_sales THEN
        raise_application_error (-20001,'You must have sales in order to submit the order.');

We are also using the WHEN OTHERS clause to trap all remaining exceptions:

 WHEN OTHERS THEN
        raise_application_error (-20002,'An error has occurred inserting an order.');

Oracle/PLSQL: Named System Exceptions

---

What is a named system exception?

Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.
Oracle has a standard set of exceptions already named as follows:

Oracle Exception Name	Oracle Error	Explanation
DUP_VAL_ON_INDEX	ORA-00001	You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCE	ORA-00051	You were waiting for a resource and you timed out.
TRANSACTION_BACKED_OUT	ORA-00061	The remote portion of a transaction has rolled back.
INVALID_CURSOR	ORA-01001	You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ON	ORA-01012	You tried to execute a call to Oracle before logging in.
LOGIN_DENIED	ORA-01017	You tried to log into Oracle with an invalid username/password combination.
NO_DATA_FOUND	ORA-01403	You tried one of the following: You executed a SELECT INTO statement and no rows were returned. You referenced an uninitialized row in a table. You read past the end of file with the UTL_FILE package.
TOO_MANY_ROWS	ORA-01422	You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE	ORA-01476	You tried to divide a number by zero.
INVALID_NUMBER	ORA-01722	You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
STORAGE_ERROR	ORA-06500	You ran out of memory or memory was corrupted.
PROGRAM_ERROR	ORA-06501	This is a generic "Contact Oracle support" message because an internal problem was encountered.
VALUE_ERROR	ORA-06502	You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
CURSOR_ALREADY_OPEN	ORA-06511	You tried to open a cursor that is already open.

The syntax for the Named System Exception in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
EXCEPTION
    WHEN exception_name1 THEN
        [statements]
    WHEN exception_name2 THEN
        [statements]
    WHEN exception_name_n THEN
        [statements]
    WHEN OTHERS THEN
        [statements]
END [procedure_name];

The syntax for the Named System Exception in a function is:

CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]
    RETURN return_datatype
IS | AS
    [declaration_section]
BEGIN
    executable_section
EXCEPTION
    WHEN exception_name1 THEN
        [statements]
    WHEN exception_name2 THEN
        [statements]
    WHEN exception_name_n THEN
        [statements]
    WHEN OTHERS THEN
        [statements]
END [function_name];

Here is an example of a procedure that uses a Named System Exception:

CREATE OR REPLACE PROCEDURE add_new_supplier
    (supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2)
IS
BEGIN
    INSERT INTO suppliers (supplier_id, supplier_name )
    VALUES ( supplier_id_in, supplier_name_in );
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        raise_application_error (-20001,'You have tried to insert a duplicate supplier_id.');
    WHEN OTHERS THEN
        raise_application_error (-20002,'An error has occurred inserting a supplier.');
END;

In this example, we are trapping the Named System Exception called DUP_VAL_ON_INDEX. We are also using the WHEN OTHERS clause to trap all remaining exceptions.

Retrieve third lowest value from a table

---

Question: How can I retrieve the third lowest salary amount from a salary table?

Answer: To retrieve the third lowest salary from a salary table, you could run the following query: (please note that the subquery is sorted in ascending order)

SELECT salary_amount
FROM	(select salary2.*, rownum rnum from
		(select * from salary ORDER BY salary_amount) salary2
	where rownum <= 3 )
WHERE rnum >= 3;

If you wanted to retrieve all fields from the salary table for the third lowest salary, you could run the following query: (please note that the subquery is sorted in ascending order)

SELECT *
FROM	(select salary2.*, rownum rnum from
		(select * from salary ORDER BY salary_amount) salary2
	where rownum <= 3 )
WHERE rnum >= 3;

Retrieve second lowest value from a table

---

Question: How can I retrieve the second lowest salary amount from a salary table?

Answer: To retrieve the second lowest salary from a salary table, you could run the following query: (please note that the subquery is sorted in ascending order)

<

SELECT salary_amount
FROM	(select salary2.*, rownum rnum from
		(select * from salary ORDER BY salary_amount) salary2
	where rownum <= 2 )
WHERE rnum >= 2;

If you wanted to retrieve all fields from the salary table for the second lowest salary, you could run the following query: (please note that the subquery is sorted in ascending order)

SELECT *
FROM	(select salary2.*, rownum rnum from
		(select * from salary ORDER BY salary_amount) salary2
	where rownum <= 2 )
WHERE rnum >= 2;

Retrieve third highest value from a table

---

Question: How can I retrieve the third highest salary amount from a salary table?

Answer: To retrieve the third highest salary from a salary table, you could run the following query: (please note that the subquery is sorted in descending order)

SELECT salary_amount
FROM	(select salary2.*, rownum rnum from
		(select * from salary ORDER BY salary_amount DESC) salary2
	where rownum <= 3 )
WHERE rnum >= 3;

If you wanted to retrieve all fields from the salary table for the third highest salary, you could run the following query: (please note that the subquery is sorted in descending order)

SELECT *
FROM	(select salary2.*, rownum rnum from
		(select * from salary ORDER BY salary_amount DESC) salary2
	where rownum <= 3 )
WHERE rnum >= 3;

Retrieve second highest value from a table

---

Question: How can I retrieve the second highest salary amount from a salary table?

Answer: To retrieve the second highest salary from a salary table, you could run the following query: (please note that the subquery is sorted in descending order)

SELECT salary_amount
FROM	(select salary2.*, rownum rnum from
		(select * from salary ORDER BY salary_amount DESC) salary2
	where rownum <= 2 )
WHERE rnum >= 2;

If you wanted to retrieve all fields from the salary table for the second highest salary, you could run the following query: (please note that the subquery is sorted in descending order)

SELECT *
FROM	(select salary2.*, rownum rnum from
		(select * from salary ORDER BY salary_amount DESC) salary2
	where rownum <= 2 )
WHERE rnum >= 2;

Retrieve Middle N records from a query

---

Question: How can I retrieve the Middle N records from a query?
For example, what if I wanted to retrieve records 3 to 5 from my query results. How can I do this?

Answer: To retrieve the Middle N records from a query, you can use the following syntax:

SELECT *
FROM	(select alias_name.*, rownum rnum from
		(-- your ordered query --) alias_name
	where rownum <= UPPER_BOUND )
WHERE rnum >= LOWER_BOUND;

For example, if you wanted to retrieve records 3 through 5 from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:

SELECT *
FROM	(select suppliers2.*, rownum rnum from
		(select * from suppliers ORDER BY supplier_name) suppliers2
	where rownum <= 5 )
WHERE rnum >= 3;

If you wanted to retrieve records 3 through 5 from the suppliers table, sorted by supplier_name in descending order, you would run the following query:

SELECT *
FROM	(select suppliers2.*, rownum rnum from
		(select * from suppliers ORDER BY supplier_name DESC) suppliers2
	where rownum <= 5 )
WHERE rnum >= 3;

If you wanted to retrieve records 2 through 4 from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:

SELECT *
FROM	(select suppliers2.*, rownum rnum from
		(select * from suppliers ORDER BY supplier_id) suppliers2
	where rownum <= 4 )
WHERE rnum >= 2;

If you wanted to retrieve records 2 through 4 from the suppliers table, sorted by supplier_id in descending order, you would run the following query:

SELECT *
FROM	(select suppliers2.*, rownum rnum from
		(select * from suppliers ORDER BY supplier_id DESC) suppliers2
	where rownum <= 4 )
WHERE rnum >= 2;

Retrieve Bottom N records from a query

---

Question: How can I retrieve the Bottom N records from a query?
For example, what if I wanted to retrieve the last 3 records from my query results. How can I do this?

Answer: To retrieve the Bottom N records from a query, you can use the following syntax:

SELECT *
FROM (your query ordered in reverse) alias_name
WHERE rownum <= Rows_to_return
ORDER BY rownum DESC;

If you want to retrieve the bottom records from a query, you need to order your results in reverse in the "your query ordered in reverse" section of the solution above.

For example, if you wanted to retrieve the last 3 records from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_name DESC) suppliers2
WHERE rownum <= 3
ORDER BY rownum DESC;

Notice that although you want the last 3 records sorted by supplier_name in ascending order, you actually sort the supplier_name in descending order in this solution.

If you wanted to retrieve the last 3 records from the suppliers table, sorted by supplier_name in descending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_name) suppliers2
WHERE rownum <= 3
ORDER BY rownum DESC;

If you wanted to retrieve the last 5 records from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_id DESC) suppliers2
WHERE rownum <= 5
ORDER BY rownum DESC;

If you wanted to retrieve the last 5 records from the suppliers table, sorted by supplier_id in descending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_id) suppliers2
WHERE rownum <= 5
ORDER BY rownum DESC;