PL/SQL Interview Questions

9 rows will be retreive

Cross join. Total 9 records will come

OCI means::oracle call interface.is a interface api used to access oracle database from C programs. Data Guard stands for it is multiple DB servers environment configured for Physical as well as Logic...

row type is used to hold the total row values

%rowtype:- %rowtype means it is will fetch entire row values.

Type record: Type record means we can fetch record from more than one table.

You can copy the data from one table to another with the help of below Query:

Suppose dummy is the main table and we need to copy the data from dummy_120413 table.

Create tabel dummy_120413 as Select * from dummy;

use following dbms package to get error line no in oracle 11g:

dbms_utility.format_error_backtrace

Using dbms_ouptput.put_line and logging different parts of the procedure.

An implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code, but suffers from inefficiency (the ANSI standard specifies that it must fetch twic...

Implicit Cursor:- It is Oracle pre-defined type.. when we fetch only one record then we need to use implicit cursor.

Explicit cursor:- It is user defined cursor.. when we need to fetch more the one records we need to use explicit cursor..

Using pragma autonomous_transaction we can use the commit and rollback.

Although you cant give commit or rollback in trigger directly, still there are ways to give them
You can use the autonomus transactions to do that

Implicit cursor is created automatically,opened automatically and closed automatically.it holds exactly one record. in case none or more than 1 record are retrieved, an exception is raised and that re...

A function is a store procedure indeed, and you can use autonomous transactions to do some DML

Null is not a value and not a string. It is unknown it is maximum in descending order and minimum value in ascending order.

char(0)

What is the difference between system procedure and system function?.

Also Procedure can be called from triggers but Function cannot be called from trigger. As function returns value. And no value can be returned to trigger.

Bulk binding reduces the context switches between SQL and pl/SQL engines. It enhances the performance but thr memory consumption would be high.

truncate is a ddl...delete is dml
cannot rollback data in truncate...rollback data in delete
truncate -delete entire data not structure...it delete specific datas

By using delete we have to delete particular records only..
If we use truncate we cannot retrieve the data permanently...

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without ...

BY USING AUTONOMOUS TRANSACTION
WE GET THE FOLLOWING ADVANTAGES
WE PROCEDURE HAVING TRANSACTIONS STATEMENTS WE CALL PROCEDURE AUTOMATICALLY EFFECT THE ABOVE TRANSACTIONS ALSO TO OVERCOME THAT PROBLEM WE HAVE TO USE AUTONOMOUS TRANSACTION..

Varrays,nested tables,index by tables are oracle collection.Index by tables also known as associative array similar to hashtables in java. Varrays are like arrays in java static in nature need to d...

In procedure,Function there are three types of parameter is there. eg-IN, OUT, INOUT. IN parameter is call by reference and OUT & INOUT are call by value. Always call by reference is faster than call ...

Pass by reference: "IN" Case
Pass by value : "Out" or "IN OUT" Case which have the overhead of copying the value to new procedure parameter.
In order to make to Pass by reference we will use NOCOPY
Pass by reference: "OUT NOCOPY" or "IN OUT NOCOPY" which deals which the actual reference value.

Implicit cursors are automatically generated,when a sql statement is processed where as

Explicit cursors are user defined.

Implicit cursors returns only single record..where as explicit cursor multiple records. But it process record by record.

When you drop a type body, the object type specification still exists, and you can re-create the type body. Prior to re-creating the body, you can still use the object type, although you cannot call the member functions.

v$lock contains details of locks

Cursor for loop is the one by using this we need not to perform open, close, fetch operations of a cursor..

If we use explicit cursor we need to open the cursor and fetching the data and close the cursor.

If we use cursor for loop cursor will open the cursor and fetching data and close the cursor automatically.

We can use SQL Loader too

Depending on the situation one can use
external tables
or
UTL_FILE package