ORACLE AND MSSQLSERVER

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...

FORALL The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. To test bulk binds using records we first create...

BULK COLLECT Bulk binds can improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. It can be used with all three types of collections: associative arrays, nested...

Bulk Binds Bulk binding decreases context switch between PL/SQL engine and SQL Engine that makes code execution faster. Context switch occurs every time when PL/SQL calls SQL engine to parse, execute or fetch from cursor. Since context switching uses...

Bulk Binds Bulk binding decreases context switch between PL/SQL engine and SQL Engine that makes code execution faster. Context switch occurs every time when PL/SQL calls SQL engine to parse, execute or fetch from cursor. Since context switching uses...

Collection Methods A collection method is a built-in PL/SQL subprogram that returns information about a collection or operates on a collection. Collection methods make collections easier to use, and make your applications easier to maintain. You invoke...

What is a sub query? What are its various types? - Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables. - A sub query is added in the where clause of the main query. There can be two types of subqueries: a.) Correlated...

A trigger is an database object which executes automatically in response to a an on a particular table in a database. the event can be insert , update or delete. Triggers are stored programs, which are automatically executed or fired when some events...

second highest salary in each department SELECT * FROM (SELECT emp.*, DENSE_RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) secondhsal FROM emp )x WHERE x.second hsal=2 ;