Cursors retrieve the values one at a time from the database, and process them sequentially.
Collections retrieve all values in one go and You can carry out operations on the group of values as a whole.

What is an autonomous transaction?
-An autonomous transactions is available from Oracle 8i.
-An autonomous transaction is an independent transaction that is initiated by another transaction.
-It must contain at least one Structured Query Language (SQL) statement
-Autonomous transactions allow a single transaction to be subdivided into multiple commit/rollback transactions.
-When an autonomous transaction is called, the original transaction (calling transaction) is temporarily suspended.
-The autonomous transaction must commit or roll back before it returns control to the calling transaction.
-Autonomous transactions can be nested. In theory, there is no limit to the possible number of nesting levels.
-An autonomous transaction is defined in the declaration of a pl/sql block. This can be an anonymous block, function, procedure, object method or trigger.
-This is done by adding the statement 'PRAGMA AUTONOMOUS_TRANSACTION;' anywhere in the declaration block.

Why do you want to join our team?

Trigger may have same name as that of other schema objects, such as tables, views, and procedures. However, to avoid confusion, this is not recommended.

The BEFORE and AFTER options cannot be used for triggers created over views.

Difference between Object Type and Record Type

Record type works only in Pl/Sql.

Object type gets stored in database.
We can use it both in sql and pl/sql.

When an exception is raised inside a cursor for loop, the cursor is closed implicitly before the handler is invoked. So the value of explicit cursor are not available in the handler.

If you want to execute the same sequence of statements for more than one exception then list the exception names separated by the keyword OR. We should not use the others exception in the list separated by OR. It must appear by itself

A goto statement can’t be branch to an exception handler nor can it branch from an exception handler to the current block

Compiler hint nocopy

By default in subprograms
IN parameter is passed by reference.
OUT and IN OUT parameters are passed by value.

When the parameters hold large data structures such as collections, records, and instances of object types, all this copying slows down execution and uses up memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference.


NOCOPY is a hint and Oracle does not guarantee a parameter will be passed by reference when explicitly mentioned

By default, if a subprogram exits with an unhanded exception, the values assigned to its OUT and IN OUT formal parameters are not copied into the corresponding actual parameters, and changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters as well. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back."

Cast Function

cast function converts one datatype to another.

It is available from Oracle 9i version.

Syntax:- cast ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

Ex:- SELECT CAST(SUBSTR('ABZ123',-3,3) AS NUMBER) FROM DUAL

In the above example cast converts last three digits of string to number data type.

How to find number of ‘e’s in a word ?

SELECT length('geekinterview.com') - length (replace ('geekinterview.com', 'e'))
FROM DUAL;

A subprogram can have only one other exception in a program.

Exceptions cannot be propagated across remote procedure calls

ADD_MONTHS returns the date plus integer months.
If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month.Otherwise, the result has the same day component as date.

If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, and REPLACE.

If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

From Session1

From Sesion2

SQL> ALTER TABLE emp ADD (Dateofjoin DATE);
ALTER TABLE emp ADD (Dateofjoin DATE)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Aggregate functions can appear in
- select lists
- ORDER BY
- HAVING clauses

Single Row functions can appear in
- select lists
- WHERE clauses
- START WITH
- CONNECT BY clauses
- HAVING clauses.

The first two function of the parse phase Syntax Check and Semantic Analysis happen for each and every SQL statement within the database irrespective of whether it is hard parsing or soft parsing.

A transaction acquires an exclusive DML lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.