PL/SQL

Que:- What is difference between a PROCEDURE & FUNCTION ?

Ans:-

1. A function is always return value using the return statement. A procedure may return one or more values through parameter or may not all.

2. A function can be called from sql statement and queries while procedure can not be called from sql statement.

3. we can not insert image through function but we can inser t the image through procedure.

Que:- State the difference between implicit and explicit cursor's.

Ans:-

1. Implicit cursor are declared and used by the oracle internally. Whereas the explicit cursor are declared and used by the user.

2.Implicitly cursor are not need to declare oracle creates and process and closes automatically. The explicit cursor should be declared and closed by the user.

Que:- Explian rowid, rownum? What are the pseduocolumns we have?

Ans:-

ROWID :- It gives the hexadecimal string representing the address of a row and  It gives the loction in database where row is physically stored.

ROWNUM :  it gives a sequence number in which rows are retrieved from the database. It is used for TOP Anylysis.

Other Pseudo columns are NEXTVAL,CURRVAL ,SYSDATE,LEVEL .    

Que:- Explain the usage of WHERE CURRENT OF clause in cursors ?

Ans:-

"WHERE CURRENT OF" Clause in an UPDATE,DELETE Satatement refers to the latest row fetChed from a cursor.

Que:- State the advantage and disadvantage of Cursor?

Ans:-

Que:- What is Raise_application_error ?

Ans:-

Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an used defined messages from stored sub-program or database trigger.

Syntax : RAISE_APPLICATION_ERROR(errnum,’errmsg’);

-errnum : it indicates the error number that containsany values between -20,000 to -20,999.

-errmsg : Description About Error Message.

Que:- Name the tables where characteristics of Package, procedure and functions are stored ?

Ans:-

User_objects , User_Source, and User_error.

Que:- How many types of database triggers can be specified on a table ? What are they ?

Ans:-

We can 12 type of triggers.

DML : Insert,Update,Delete

Timing  : Before , After

Levels : Row , Statement .

Que:- What is “ref cursor”.

Ans:-

It’s a run time query binding with cursor variable . Normal cursor are static cursor because they  get acquired of query at run time .

Que:- What are the Restrictions on Cursor Variables?

Ans:-

• You cannot declare cursor in package specification.
• Cursors are not allowed when using db links.
• We cannot use comparison operators.
• We cannot assign NULL values.
• Cursor's values cannot be stored in table columns.
• We Cannot be used with associative array, nested tables and varray.
• We cannot be use one where the other is expected.
• We cannot reference a cursor variable in cursor FOR LOOP.
• We cannot directly goto any columns.

Que:- What is difference between % ROWTYPE and TYPE RECORD ? 

Ans:-

% ROWTYPE is to be used whenever query returns a entire row of a table or view. TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.

Que:- What is a database trigger ? Name some usages of database trigger ?

Ans:-

Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables. A database triggers is stored PL/SQL program unit associated with a specific database table or view. The code in the trigger defines the action the database needs to perform whenever some database manipulation (INSERT, UPDATE, DELETE) takes place.

Unlike the stored procedure and functions, which have to be called explicitly, the database triggers are fires (executed) or called implicitly whenever the table is affected by any of the above said DML operations. Till oracle 7.0 only 12 triggers could be associated with a given table, but in higher versions of Oracle there is no such limitation. A database trigger fires with the privileges of owner not that of user

A database trigger has three parts:

• A triggering event
• A trigger constraint (Optional)
• Trigger action

A triggering event can be an insert, update, or delete statement or a instance shutdown or startup etc. The trigger fires automatically when any of these events occur A trigger constraint specifies a Boolean expression that must be true for the trigger to fire. This condition is specified using the WHEN clause. The trigger action is a procedure that contains the code to be executed when the trigger fires.

Que:- What are the modes of parameters that can be passed to a procedure ?

Ans:-

IN,OUT,IN-OUT parameters.

IN: parameter is the default mode which acts as a constant inside calling environment.value passed in this parameter can not be changed.OUT parameter mode is used to pass value from calling environment into main block,here we can change the value.It acts as a variable inside calling environment.

INOUT: parameter mode which pass value into calling environment and will get the value back in main block. IN parameter mode uses call by reference method to pass value from formal parameter to actual parameter.

OUT & INOUT: parameter mode uses call by value method to pass values.

Que:- Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?

Ans:-

It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

Que:- What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?

Ans:-

A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.

A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

Que:- What is Pragma EXECPTION_INIT ? Explain the usage ?

Ans:-

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

Que:- What is a stored procedure ?

Ans:-

A stored procedure is a named pl/sql block which performs an action. Advantages of using stored procedures include easier maintenance, decreased application size, increased execution speed, and greater memory savings, to name a few.

Que:- What are two virtual tables available during database trigger execution ?

Ans:-

The table columns are referred as OLD.column_name and NEW.column_name.

For triggers related to INSERT only NEW.column_name values only available.

For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.

For triggers related to DELETE only OLD.column_name values only available.

Que:- What is PL/SQL table ? 

Ans:-

Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key. TYPE tab IS TABLE OF VARCHAR2(30);

This way we can make declaration of PL/SQL tables. They are also reffed as Nested Table and are pat of PLSQL collections. They are used for bulk data processing.

Que:- What is a cursor for loop ?

Ans:-

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

Example:-

FOR emp_rec IN C1 LOOP

salary_total := salary_total +emp_rec sal;

END LOOP;

Que:- What is PL/SQL ?

Ans:-

PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

Que:- What is TTITLE and BTITLE ?

Ans:-

This Ttitle and Btitle are used on creating SQL*PLUS report. Ttitle is used for toptitle heading where as Btitle is used for Buttomn title heading.

Que:- What are advantages fo Stored Procedures ?

Ans:-

Extensibility, Modularity, Re-usability, Maintainability and one time compilation. Easy maintenance, Improved data security and integrity, Improved performance

Que:- The most important DDL statements in SQL are ?

Ans:-

CREATE TABLE - creates a new database table

ALTER TABLE - alters (changes) a database table

DROP TABLE - deletes a database table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

Que:- What are the return values of functions SQLCODE and SQLERRM ?

Ans:-

SQLCODE: returns the latest code of the error that has occurred.

SQLERRM: returns the relevant error message of the SQLCODE.

Que:- How to stop asking ampersand & value in .sql file or runtime code
And:- SQL> set define off