Explore topic-wise InterviewSolutions in .

True.

False. The %NOTFOUND attribute returns true when the last row of the cursor is processed and no other row is available.

Oracle implicitly declares a cursor to all the DDL and DML commands that return only one row. For queries returning multiple rows, an explicit cursor is created.

The DROP PACKAGE command.

True.

The types, objects, and subprograms declared within a package are referred to using the dot notation as −

package_name.type_name

package_name.object_name

package_name.subprogram_name

CREATE PACKAGE command is used for creating the specification part. CREATE PACKAGE BODY command is used for creating the body part.

PL/SQL packages have two parts −

• Specification part − where the interface to the application are defined.

• Body part − where the implementation of the specification are defined.

The steps that need to be performed on explicit cursor are −

• DECLARE − assigns a name to the cursor and defines the structure of query within it.

• OPEN − executes the query, whereby the rows returned by the query are available for fetching.

• FETCH − assigns values from the current row (cursor position) into specified variables.

• CLOSE − releases the memory space.

Explicit cursors are defined explicitly using the CURSOR statement, with a general syntax −

CURSOR cursor_name [(parameters)] IS query_expression;

It allows processing queries that return multiple rows.

User defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION. Syntax −

<exception_name> EXCEPTION;

A function returns a value and a stored procedure doesn’t return a value.

DROP PROCEDURE command.

DROP TRIGGER command.

ALTER TRIGGER update_marks DISABLE;

False. INSTEAD OF is a valid option only for views. INSTEAD OF trigger cannot be specified in a table.

The optional argument [OR REPLACE] in a CREATE TRIGGER command re-creates an existing trigger. Using this option allows changing the definition of an existing trigger without having to delete it first.

True.

A WHEN clause specifies the condition that must be true for the trigger to be triggered.

A trigger cannot execute the COMMIT, ROLLBACK, or SAVEPOINT commands.

True.

False. When a trigger is associated to a view, the base table triggers are normally enabled.

True.

Basically triggers are used to create consistencies, access restriction and implement securities to the database. Triggers are also used for −

• Creating validation mechanisms involving searches in multiple tables

• Creating logs to register the use of a table

• Update other tables as a result of inclusion or changes in the current table.

A trigger is automatically executed without any action required by the user, whereas, a stored procedure needs to be explicitly invoked.

LOOP command, FOR.. LOOP command, WHILE command.

It returns the Boolean value TRUE if no rows were processed.

It returns the Boolean value TRUE if at least one row was processed.

It returns the number of rows that are processed by a SQL statement.

False. PL/SQL doesn’t support the data definition commands like CREATE.

True.

False. The BEGIN declaration starts the execution section.

True.

Oracle uses workspaces to execute the SQL commands. In other words, when Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. A cursor is an identifier for this area. It allows programmers to name this area and access it’s information.

A trigger is a PL/SQL program that is stored in the database and executed immediately before or after the INSERT, UPDATE, and DELETE commands.

A package is a file that groups functions, cursors, stored procedures, and variables in one place.

It declares a composed variable that is equivalent to the row of a table. After the variable is created, the fields of the table can be accessed, using the name of this variable.

For example

emptype := emp%rowtype;

name := emptype.empname;

It assigns a variable the same data type used by the column, for which the variable is created. For example,

dcode := dept.detpno%type;

The variable dcode is created with the same data type as that of the deptno column of the dept table.

The colon (: )sign implies that the variable :deficit is an external variable.

OR

NOT

greeting := ‘Hello’ || ‘World’;

Use of wrong assignment operator. The correct syntax is: balance := balance + 2000;

• Declaration section
• Execution section
• Exception section

• Variables and constants
• Embedded SQL support
• Flow control
• Cursor management
• Exception handling
• Stored procedures and packages
• Triggers

• Stored procedures and functions
• Packages
• Triggers
• Cursors

• Block-structured language.

• Stored procedures help better sharing of application.

• Portable to all environments that support Oracle.

• Integration with the Oracle data dictionary.

False. PL/SQL has all features of a structured programming language including data types, variables, subroutines, modules and procedural constructs.

True.

PL/SQL is an extension of SQL. SQL is non-procedural. PL/SQL is a procedural language designed by oracle to overcome the limitations that exist in SQL.