Difference between Primary Key & Foreign Key Primary Key Primary key uniquely identify a record in the table. Primary Key can't accept null values. By default, Primary key is clustered index and data in the database table is physically organized in the...
Advantages REFCURSOR 1. Ref Cursor it self is a data type and easy to declare 2. More flexible because it is not tied to a specific query 3. Easily pass as arguments from subroutine to subroutine. 4. Very handy in transferring data between multi-language...
REF CURSOR A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value. A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. One...
Deleting Duplicate Rows in Oracle CREATE TABLE tbl_test( SER_NO NUMBER, FST_NM VARCHAR2(30), DEPTID NUMBER, CMNT VARCHAR2(30)); INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx'); INSERT INTO tbl_test VALUES(2, 'bbbbb', 2005, 'yyy'); INSERT INTO tbl_test...
Compound Triggers in Oracle 11g In Oracle 11g, the concept of compound trigger was introduced. A compound trigger is a single trigger on a table that enables you to specify actions for each of four timing points: Before the firing statement Before each...
diff between triggers & integrity constraints? Constraint Vs Trigger Constraints will check for existing rows also Triggers will not check for existing rows Constraints provide standard error messages Triggers provide user friendly error messages Constraints...
Bulk Binds and Triggers For bulk updates and deletes the timing points remain unchanged. Each row in the collection triggers a before statement, before row, after row and after statement timing point. For bulk inserts, the statement level triggers only...
SAVE EXCEPTIONS and SQL%BULK_EXCEPTION We saw how the FORALL syntax allows us to perform bulk DML operations, but what happens if one of those individual operations results in an exception? If there is no exception handler, all the work done by the current...
SQL%BULK_ROWCOUNT The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in the SQL%BULK_ROWCOUNT cursor attribu...
