Technical Interview Questions and Answers :: Oracle PLSQL
1 / 31
bulk insert or bulk delete or bulk fetch of data is called bulk sql
2 / 31
Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine. When rows are retrieved using Oracle bulk collect, they are retrieved with only two context switches.
3 / 31
There are mainly two types of exceptions: checked and unchecked where error is considered as unchecked exception.
4 / 31
Ref cursor is a varible, type of cursor. It points to or references a cursor result. One of the advantages ref cursor has over a plain cursor is that it can be passed to other procedures as a variable.
5 / 31
No Discussion on this question yet!
6 / 31
No Discussion on this question yet!
7 / 31
Cursors help us to do an operation on a set of data that we retrieve by commands such as Select columns from a table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retrieval.
There are mainly 2 types of cursors .
1) Implicit Cursor.
2) Explicit Cursor.
Implicit Cursor: Oracle will implicitly creates an area for the DML operations. Programmer will not have control on implicit cursors. The only useful attribute on this implicit cursor is SQL%ROWCOUNT , it will give the number of rows affected by the recent DML operation.
Explicit Cursor: created by the programmer and programmer have control on it Programmer can
1) Open 2) Close 3) Fetch
one by one and remove rows which have duplicate values.
Explicit Cursors are classified into
1) Normal cursor
2) Parameterized cursor
3) Cursor For Loops and
4) REF cursors
8 / 31
A stored procedure is a set of per-compiled SQL commands (query statements), which are stored in the server. It is faster then the loose SQL statements processed on client, as it is per-compiled. It can execute more then one SQL commands once as they are bundled in a single entity. We can use control statements within the stored procedure, which will allow us to repeat some SQL command. It can send return values depending upon the result. Stored procedures are used to reduce network traffic.
9 / 31
Triggers are a special type of stored procedure, which gets invoked upon a certain event. They can be performed upon an INSERT, UPDATE and DELETE.
10 / 31
: An entity that maps over a result set and establishes a position on a single row within the result set. After the cursor is positioned on a row, operations can be performed on that row, or on a block of rows starting at that position. The most common operation is to fetch (retrieve) the current row or block of rows.