PLSQL Interview Questions Set 07

121. What do you know about pragma_exception_init in PL/SQL?

The pragma_exception_init command in PL/SQL instructs the compiler to associate an exception name with an Oracle error number. This enables one to refer to any internal exception by name and create a custom handler for it.

122. Explain rowid,rownum ?What are the pseduocolumns we have?

Row id: Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.
Rownum: For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. You can use ROWNUM to limit the number of rows returned by a query, as in this example:
SELECT * FROM employees WHERE ROWNUM < 10;

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.

The lists of pseudocolumns are:

– CURRVAL and NEXTVAL
– LEVEL
– ROWID
– ROWNUM
-TIMESTAMP
-SYSTIME
-USER
-UID

123. How packaged procedures are called from the stored procedure or anonymous block?

PACKAGE NAME.PROCEDURE NAME (parameters);
Variable: = PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE

124. How to disable multiple triggers if a table at a time?

ALTER table table_name DISABLE ALL TRIGGERS

125. How we can create a table through procedure?

You can create table from procedure using Execute immediate command.
CREATE procedure p1 is
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE temp AS SELECT * FROM emp ‘ ;
END;

126. In pl/sql what is the use of out parameter even though we have return statement.

Without parameters you can get the more than one out values in the calling program. It is recommended not to use out parameters in functions. If you need more than one out values then use procedures instead of functions.

127. What are integrity constraints?

Data integrity allows defining certain data quality requirements that the data in the database needs to meet. If a user tries to insert data that doesn’t meet these requirements, Oracle will not allow so.

Constraint types
There are five integrity constraints in Oracle.

Not Null:
A column in a table can be specified not null. It’s impossible to insert a null in such a column. The default is null.

Unique Key:
The unique constraint doesn’t allow duplicate values in a column. If the unique constraint encompasses two or more columns, no two equal combinations are allowed.

Primary Key:
On a technical level, a primary key combines a unique and a not null constraint. Additionally, a table can have at most one primary key. After creating a primary key, it can be referenced by a foreign key.

Foreign key:
A foreign key constraint (also called referential integrity constraint) on a column ensures that the value in that column is found in the primary key of another table. If a table has a foreign key that references a table, that referenced table can be dropped with a drop table. Cascade constraints. It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA-14455: attempt to create referential integrity constraint on temporary table.

Check constraints:
A check constraint allows stating a minimum requirement for the value in a column. If more complicated requirements are desired, an insert trigger must be used.

128. Name the table where characteristic of package, procedure and functions are stored?

user_objects
user_source
user_dependencise

129. State the advantage and disadvantage of cursor?

Cursor is nothing but it’s a memory area of executing sql or oracle statements.

Advantage:
1. We can retrieve more than one record by one query.
2. Its use our RAM so retrieval is fast.
3. By using Collection we can retrieve the bundle of data in cursor.
4. A cursor is an oracle variable.
5. It is similar to a 2D Array.
6. It is a forward only and read only variable type.

Disadvantage:
No such of disadvantage of cursor. When we use cursor one thing is necessary. When I have select only
one record then we should use implicit cursor and when more record then we should use explicit cursor.

130. State the difference between implicit and explicit cursor?

Implicit Cursor:
1. When a query return s Single Row Value then Implicit Cursor is used. It’s return Only One Row. Cursor.
2. Name is assigned implicitly.
3. Implicit Cursor used for all SQL Statements, that, DECLARE, OPEN, FETCH, CLOSE.
4. It’s defined by the Oracle Server where never the Operations Single Row.
5. Implicit Cursors Automatically provides by Oracle which performs DML Statements. Queries return only one row.
6. We are able to handle NO_DATA_FOUND Exception in implicit Cursor.

Explicit Cursor:
1. A subquery returns more than one row Explicit Cursor is Created.
2. The rows returned by the query are called Active Set. Its return multiple rows.
3. Curosr is assigned explicitly. It’s used to process Multirow SELECT Statements.
4. Retrieving multiple rows the Programmer declares cursors explicitly.
5.  Explicit Cursors defined by the User. Queries return more than rows.
6. We are not able to handle NO_DATA_FOUND Exception.

131. What are %type and %row type? What are the advantages of using these over data types.

%TYPE is used to declare a variable with the same type as that of a database table column. TYPE can be used with the column name preceded with table name to decide the datatype and length of the variable at runtime.

ROWTYPE can be used to declare the variable having the same no. of variables inside it (ROWTYPE) as no. of columns there in the table.

In this case columns selected with SELECT statement must match with variables inside the row type variable. If not then induvidually refer these variables inside the ROWTYPE variables.

These two provides data independence and allows you to adopt database changes due to new
business requirements. You need not know datatype and size in advance.

132. What is the starting oracle error number ?

ORA-00000 is the starting oracle error number.

133. What is meant by forward declaration in functions?

•  If you are defining a package body having two procedures, if you want to use second procedure in the definitions of first procedure.
• You have to declare the second package with its arguments (if have) before using in the definition of first procedure it is labeled as forward declaration.

134. Can commit, rollback, savepoint be used in database triggers? If yes then how? If no why? With reasons

• Triggers should not contain transaction control statements like commit, rollback, savepoint or set transaction.
• Because it is fired as part of the execution of the triggering statement.
• When the triggering statement is committed or rolled back, work in the trigger is committed or rolled back as well.

135. Can we declare a column having number data type and its scale is larger than precision ex: column name NUMBER (10,100), column name NUMBER (10,-84)

Yes such declaration is possible .Explanation with example.

1. Number (9, 11) means there are 11 digits after decimal .However as the max precision is 9 so the rest are zero padded .Like 0.00999999999
2. Number (9, -11) means that there are 9 digits whereas the rest is zero padded towards left of the decimal point Like 99999999900000000000.0

136. Explain how procedure and functions are called in a pl/sql block ?

Procedure can be called in the following ways:

a) CALL <procedure name> direct
b) EXCECUTE <procedure name> from calling environment
c) <Procedure name> from other procedures or functions or packages

Functions can be called in the following ways:

a) EXCECUTE <Function name> from calling environment. Always use a variable to get the return value.
b) As part of an SQL/PL SQL Expression.

137. Explain the types of cursor?

Cursor is a sql private work area. It opens an area of memory where the query is parsed and executed.

Types:

Implicit: Implicit cursor are declared by PL/SQL implicitly for all DML and PL/SQL statements, including queries that return only one row.
Explicit: For queries that return more than one row, explicit cursors are declared and named by the programmer and manipulated through specific statements in the block’s executable actions.

138. Explain the usage of WHERE CURRENT OF clause in cursors?

When referencing the current row from an explicit cursor, use the where current of clause. This allows you to apply updates and delete to the row currently being addressed, without the need to explicitly reference the rowid. You must include the for update clause in the cursor query on open.

Syntax: Where current of cursor;

139. Give the structure of the function?

CREATE OR REPLACE FUNCTION function_name(formal parameters list with only IN mode)
RETURN datatype
IS/AS
local variable declarations
BEGIN
executable statments;
RETURN value;
END function name;

140. Give the Structure of the procedure?

CREATE OR REPLACE PROCEDURE procedure_name(Optional Parameters)
IS / AS
[PRAGMA AUTONOMOUS_TRANSACTION;] [local declarations]
BEGIN
Executable statements [EXCEPTION exception handlers]
END ;