ORACLE SQL, PL - SQL - Handling BULK EXCEPTION
ORACLE SQL, PL - SQL - Handling BULK EXCEPTION
ORACLE SQL, PL - SQL - Handling BULK EXCEPTION
More
ORACLE Topics
Handling BULK EXCEPTION COLLECTIONS and TYPES of COLLECTI
How BULK COLLECT is used with FORALL SAVE EXCEPTIONS? (Handling Exception with BULK Collect) Which Collection type should be used?
PL/SQL provides the different ways of handling exceptions. The Bulk Exceptions are used to save the exception information and continue Examples of COLLECTIONS and
processing. COLLECTION Methods
All exceptions raised during execution are saved in %BULK_EXCEPTION attribute. It also stores a collection of records similar to BULK COLLECT. DATABASE Normalization Techniques
PACKAGE Overloading
• %BULK_EXCEPTIONS(i).ERROR_CODE holds the corresponding Oracle error code.
Creating PACKAGES and Call it's Metho
• %BULK_EXCEPTIONS(i).ERROR_INDEX holds the iteration number of the FORALL statement.
ORACLE 11g Features
• %BULK_EXCEPTIONS.COUNT holds total number of exceptions encountered.
CONTINUE and CONTINUE WHEN
Statement
In order to bulk collect exceptions, we have to use FORALL clause with SAVE EXCEPTIONS keyword.
Passing parameters in
Functions/Procedures
• Connect to SQL*Plus with proper credentials and run following query to create the table and populate it with some data.
CREATE TABLE TEST Stored Procedure Vs. Functions
( SQL Query Order Execution
Test_ID NUMBER(9) NOT NULL PRIMARY KEY, DWH(OLAP) Vs. Operational DB(OLTP)
Test_Desc VARCHAR(15), Data Migration Steps and SCD Changes
New_Desc VARCHAR(15) ROLLBACK behaviour when FOR ALL is
) used
Handling BULK Exception using SAVE
EXCEPTION
> DESC TEST;
Name Null? Type BULK Collect with NATIVE Dynamic SQ
----------------------------------------- -------- ---------------------------- BULK Collect and Collection of Record
TEST_ID NOT NULL NUMBER(9)
Using BULK Collect and BULK Binds
TEST_DESC VARCHAR2(15)
NEW_DESC VARCHAR2(15) ORACLE Table Locking
How to kill ORACLE Session?
SQL> SELECT * FROM TEST;
Handling PL/SQL Errors(Exception
TEST_ID TEST_DESC NEW_DESC Handling)
---------- --------------- --------------- RAISE_APPLICATION_ERROR Built-IN
101 Selecting Procedure
102 Inserting Data
103 Delete Records Exception Trapping Functions
104 Trigger Records WHERE and HAVING clause Alternative
105 Create View
TRIGGER and Types of TRIGGERS
Identify Columns having all NULLS
Run following PL/SQL block to populate the table and later on update it to see the exception behaviour. TABLE Vs. MATERIALIZED View
VIEWS in ORACLE
SYNONYMS in ORACLE
DECLARE How INDEXES stored in DB
Local and Global Indexes
TYPE Test_ID IS TABLE OF TEST.TEST_Id%TYPE;
CLUSTERED and NON-CLUSTERED Inde
TYPE Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE; INDEXES in ORACLE
Opening Parameterized Cursor in
v_Test_ID Test_ID; Different ways
Sub-Queries-And-Types-of-Sub-Querie
v_Test_Desc Test_Desc;
COMMIT inside Trigger
v_Err_count NUMBER; Difference between Primary and Uniq
Key
BEGIN Difference between %TYPE Vs.
%ROWTYPE
SELECT Test_ID, Test_Desc WITH Clause in ORACLE
DECODE Vs. CASE
BULK COLLECT INTO v_Test_ID, v_Test_Desc
ROWNUM Vs. ROW_NUMBER()
FROM Test; ROWNUM Vs. ROWID
INSERT and DELETE Execution Plan
FORALL i IN v_test_ID.FIRST..v_test_ID.LAST SAVE EXCEPTIONS
Different types of JOINs in ORACLE
UPDATE TEST NOT IN Vs. NOT EXISTS Operator
https://tipsfororacle.blogspot.com/2016/10/handling-bulk-exception.html 1/3
3/10/2021 ORACLE SQL, PL/SQL: Handling BULK EXCEPTION
SET NEW_DESC = TO_CHAR(TEST_ID)||TEST_DESC IN Vs. EXISTS Operator
How Count Function behaves with
WHERE TEST_ID = v_test_ID(i); different operators
DELETE Vs. TRUNCATE Vs. DROP
Commit;
Find Highest/Minimum Salary and
EXCEPTION
Employee Information
Identify and Remove DUPLICATE Recor
WHEN OTHERS THEN MUTATING Table Error and How to Avo
It.
v_Err_Count := SQL%BULK_EXCEPTIONS.COUNT; GLOBAL TEMPORARY Tables in ORACLE
CHAR-NCHAR-VARCHAR-VARCHAR2-
DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' || v_Err_Count);
NVARCHAR
Popular Posts
It's RUDE to Read and Run!
Analytic Functions Vs. Aggregate
Get involved and leave your Comments in the Box Below. The more people get involved, the more we all bene it. So, l eave your thoughts
Functions
before you l eave the page.
https://tipsfororacle.blogspot.com/2016/10/handling-bulk-exception.html 2/3
3/10/2021 ORACLE SQL, PL/SQL: Handling BULK EXCEPTION
Publish Preview
https://tipsfororacle.blogspot.com/2016/10/handling-bulk-exception.html 3/3