PL/SQL - Triggers

Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers could be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

Triggers can be written for the following purposes:

• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions

Creating Triggers

The syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

Where,

• CREATE [OR REPLACE] TRIGGER trigger_name: Creates or replaces an existing trigger with the trigger_name.
• {BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed. The INSTEAD OF clause is used for creating trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.
• [OF col_name]: This specifies the column name that would be updated.
• [ON table_name]: This specifies the name of the table associated with the trigger.
• [REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE.
• [FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.
• WHEN (condition): This provides a condition for rows for which the trigger would fire. This clause is valid only for row level triggers.

Example:

To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters:

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

The following program creates a row level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values:

CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
   sal_diff number;
BEGIN
   sal_diff := :NEW.salary  - :OLD.salary;
   dbms_output.put_line('Old salary: ' || :OLD.salary);
   dbms_output.put_line('New salary: ' || :NEW.salary);
   dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/

When the above code is executed at SQL prompt, it produces the following result:

Trigger created.

Here following two points are important and should be noted carefully:

• OLD and NEW references are not available for table level triggers, rather you can use them for record level triggers.
• If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.
• Above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using DELETE operation on the table.

Triggering a Trigger

Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create a new record in the table:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

When a record is created in CUSTOMERS table, above create trigger display_salary_changes will be fired and it will display the following result:

Old salary:
New salary: 7500
Salary difference:

Because this is a new record so old salary is not available and above result is coming as null. Now, let us perform one more DML operation on the CUSTOMERS table. Here is one UPDATE statement, which will update an existing record in the table:

UPDATE customers
SET salary = salary + 500
WHERE id = 2;

When a record is updated in CUSTOMERS table, above create trigger display_salary_changes will be fired and it will display the following result:

Old salary: 1500
New salary: 2000
Salary difference: 500

Oracle Concepts and Architecture Interview Questions and Answers

Interview questions and answers for Oracle Concepts and Architecture  Interview Questions and Answers

Oracle Concepts and Architecture Interview Questions and Answers includes, Physical database, structure of Oracle Database, components of Logical database, Tablespace, SYSTEM tablespace, relationship among Database, schema, Schema Objects, Table, View, Sequence, Synonym, Private Synonyms, Public Synonyms, Index, Indexes Update, Clusters, cluster Key, Index Cluster. Synonyms, Cluster

What are the components of Physical database structure of Oracle Database?
    ORACLE database is comprised of three types of files. One or more Data files, two are more Redo Log files, and one or more Control files.

2.     What are the components of Logical database structure of ORACLE database?
    Tablespaces and the Database's Schema Objects.

3.     What is a Tablespace?
    A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

4.     What is SYSTEM tablespace and When is it Created?
    Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

5.     Explain the relationship among Database, Tablespace and Data file.
    Each databases logically divided into one or more tablespaces One or more data files are explicitly created for each tablespace.

6.     What is schema?
    A schema is collection of database objects of a User.

7.     What are Schema Objects ?
    Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables,views,sequences,synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

8.     Can objects of the same Schema reside in different tablespaces.?
    Yes.

9.     Can a Tablespace hold objects from different Schemes ?
    Yes.

10.     what is Table ?
      A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

11.     What is a View ?
      A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

12.     Do View contain Data ?
      Views do not contain or store data.

13.     Can a View based on another View ?
    Yes.

14.     What are the advantages of Views ?
      •Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
•Hide data complexity.
•Simplify commands for the user.
•Present the data in a different perpecetive from that of the base table.
•Store complex queries.
15.     What is a Sequence ?
    A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

16.     What is a Synonym ?
    A synonym is an alias for a table, view,sequence or program unit.

17.     What are the type of Synonyms ?
    There are two types of Synonyms Private and Public.

18.     What is a Private Synonyms ?
    A Private Synonyms can be accessed only by the owner.

19.     What is a Public Synonyms ?
      A Public synonyms can be accessed by any user on the database.

20.     What are synonyms used for ?
    Synonyms are used to : Mask the real name and owner of an object.
Provide public access to an object
Provide location transparency for tables,views or program units of a remote database.
Simplify the SQL statements for database users.

21.     What is an Index ?
    An Index is an optional structure associated with a table to have direct access to rows,which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

22.     How are Indexes Update ?
    Indexes are automatically maintained and used by ORACLE. Changes to table data are automatically incorporated into all relevant indexes.

23.     What are Clusters ?
      Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

24.     What is cluster Key ?
      The related columns of the tables in a cluster is called the Cluster Key.

25.     What is Index Cluster ?
    A Cluster with an index on the Cluster Key.

26.     What is Hash Cluster ?
    A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

27.     When can Hash Cluster used ?
    Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

28.     What is Database Link ?
    A database link is a named object that describes a "path" from one database to another.

29.     What are the types of Database Links?
    Private Database Link, Public Database Link & Network Database Link.

30.     What is Private Database Link?
    Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

31.     What is Public Database Link?
    Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

32.     What is Network Database link?
    Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

33.     What is Data Block?
    ORACLE database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

34.     How to define Data Block size?
    A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and cann't be changed latter.

35.     What is Row Chaining?
    In Circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.

36.     What is an Extent?
    An Extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.

37.     What is a Segment?
    A segment is a set of extents allocated for a certain logical structure.

38.     What are the different types of Segments?
    Data Segment, Index Segment, Rollback Segment and Temporary Segment.

39.     What is a Data Segment?
    Each Non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

40.     What is an Index Segment?
    Each Index has an Index segment that stores all of its data.

41.     What is Rollback Segment?
    A Database contains one or more Rollback Segments to temporarily store "undo" information.

42.     What are the uses of Rollback Segment?
    Rollback Segments are used:
To generate read-consistent database information during database recovery to rollback uncommitted transactions for users.

43.     What is a Temporary Segment?
    Temporary segments are created by ORACLE when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

44.     What is a Data File?
    Every ORACLE database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database.

45.     What are the Characteristics of Data Files?
    A data file can be associated with only one database.Once created a data file can't change size.
One or more data files form a logical unit of database storage called a tablespace.

46.     What is a Redo Log?
    The set of Redo Log files for a database is collectively known as the database's redo log.

47.     What is the function of Redo Log?
    The Primary function of the redo log is to record all changes made to data.

48.     What is the use of Redo Log Information?
    The Information in a redo log file is used only to recover the database from a system or media failure the prevents database data from being written to a database's data files.

49.     What does a Control file Contain?
    A Control file records the physical structure of the database. It contains the following information.
•Database Name
•Names and locations of a database's files and redolog files.
•Time stamp of database creation.

50.     What is the use of Control File?
    When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

51.     What is a Data Dictionary?
    The data dictionary of an ORACLE database is a set of tables and views that are used as a read-only reference about the database.
It stores information about both the logical and physical structure of the database, the valid users of an ORACLE database, integrity constraints defined for tables in the database and space allocated for a schema object and how much of it is being used.

52.     What is an Integrity Constrains?
    An integrity constraint is a declarative way to define a business rule for a column of a table.

53.     Can an Integrity Constraint be enforced on a table if some existing table data does not satisfy the constraint?
    No.

54.     Describe the different type of Integrity Constraints supported by ORACLE?
    •NOT NULL Constraint - Disallows NULLs in a table's column.
•UNIQUE Constraint - Disallows duplicate values in a column or set of columns.
•PRIMARY KEY Constraint - Disallows duplicate values and NULLs in a column or set of columns.
•FOREIGN KEY Constrain - Require each value in a column or set of columns match a value in a related table's UNIQUE or PRIMARY KEY.
•CHECK Constraint - Disallows values that do not satisfy the logical expression of the constraint.

55.     What is difference between UNIQUE constraint and PRIMARY KEY constraint?
    A column defined as UNIQUE can contain NULLs while a column defined as PRIMARY KEY can't contain Nulls.

56.     Describe Referential Integrity?
    A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.

57.     What are the Referential actions supported by FOREIGN KEY integrity constraint?
    UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data.

DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.

58.     What is self-referential integrity constraint?
    If a foreign key reference a parent key of the same table is called self-referential integrity constraint.

59.     What are the Limitations of a CHECK Constraint?
    The condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can't contain subqueries, sequence, the SYSDATE,UID,USER or USERENV SQL functions, or the pseudocolumns LEVEL or ROWNUM.

60.     What is the maximum number of CHECK constraints that can be defined on a column?
    No Limit.

Oracle Reports Interview Questions and Answers

Interview questions and answers for Oracle Reports  Interview Questions and Answers

Oracle Reports Interview Questions and Answers includes, different file extensions that are created by oracle reports, designation, lexical reference, bind reference, use of command line parameter cmd file, external pl/sql library executed, default parameter, read level consistency, term, link property sheet, place holder column, hidden column, break group, anchors, matrix object, layout editor of the report writer, term panel, anchoring object, frame & repeating frame.

What are the different file extensions that are created by oracle reports?
    Rep file and Rdf file.

2.     From which designation is it preferred to send the output to the printed?
    Previewer.

3.     Is it possible to disable the parameter from while running the report?
    Yes

4.     What is lexical reference?How can it be created?
    Lexical reference is place_holder for text that can be embedded in a sql statements.A lexical reference can be created using & before the column or parameter name.

5.     What is bind reference and how can it carate?
    Bind reference are used to replace the single value in sql,pl/sql statements a bind reference can be careated using a (:) before a column or a parameter name.

6.     What use of command line parameter cmd file?
    It is a command line argument that allows you to specify a file that contain a set of arguments for r20run.

7.     Where is a procedure return in an external pl/sql library executed at the client or at the server?
    At the client.

8.     Where is the external query executed at the client or the server?
    At the server.

9.     What are the default parameter that appear at run time in the parameter screen?
    Destype and Desname.

10.     Which parameter can be used to set read level consistency across multiple queries?
    Read only.

11.     What is term?
      The term is terminal definition file that describes the terminal form which you are using r20run.

12.     What is use of term?
      The term file which key is correspond to which oracle report functions.

13.     Is it possible to insert comments into sql statements return in the data model editor?
      Yes.

14.     If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated?
      Only for 10 records.

15.     What are the sql clauses supported in the link property sheet?
    Where startwith having.

16.     To execute row from being displayed that still use column in the row which property can be used?
    Format trigger.

17.     Is it possible to set a filter condition in a cross product group in matrix reports?
    No.

18.     If a break order is set on a column would it effect columns which are under the column?
    No.

19.     With which function of summary item is the compute at options required?
    percentage of total functions.

20.     What is the purpose of the product order option in the column property sheet?
    To specify the order of individual group evaluation in a cross products.

21.     Can a formula column be obtained through a select statement?
    Yes.

22.     Can a formula column refered to columns in higher group?
    Yes.

23.     How can a break order be created on a column in an existing group?
      By dragging the column outside the group.

24.     What are the types of calculated columns available?
      Summary, Formula, Placeholder column.

25.     What is the use of place holder column?
    A placeholder column is used to hold a calculated values at a specified place rather than allowing is to appear in the actual row where it has to appeared.

26.     What is the use of hidden column?
    A hidden column is used to when a column has to embedded into boilerplate text.

27.     What is the use of break group?
    A break group is used to display one record for one group ones.While multiple related records in other group can be displayed.

28..     If two groups are not linked in the data model editor, what is the hierarchy between them?
    Two group that is above are the left most rank higher than the group that is to right or below it.

29.     The join defined by the default data link is an outer join yes or no?
    Yes.

30.     How can a text file be attached to a report while creating in the report writer?
    By using the link file property in the layout boiler plate property sheet.

31.     Can a repeating frame be careated without a data group as a base?
    No.

32.     Can a field be used in a report wihtout it appearing in any data group?
    Yes.

33.     For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame?
    Yes.

34.     Is it possible to center an object horizontally in a repeating frame that has a variable horizontal size?
    Yes.

35.     If yes,how?
    By the use anchors.
36.     What are the two repeating frame always associated with matrix object?
    One down repeating frame below one across repeating frame.

37.     Is it possible to split the printpreviewer into more than one region?
    Yes.

38.     Does a grouping done for objects in the layout editor affect the grouping done in the datamodel editor?
    No.

39.     How can a square be drawn in the layout editor of the report writer?
    By using the rectangle tool while pressing the (Constraint) key.

40.     To display the page no. for each page on a report what would be the source & logical page no. or & of physical page no.?
    & physical page no.

41.     What does the term panel refer to with regard to pages?
    A panel is the no. of physical pages needed to print one logical page.

42.     What is an anchoring object & what is its use?
    An anchoring object is a print condition object which used to explicitly or implicitly anchor other objects to itself.

43.     What is a physical page? & what is a logical page?
    A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the Previewer.

44.     What is the frame & repeating frame?
    A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the no. of records that are to displayed is not known before.

PL SQl Interview Questions - Basics to Advanced Interview Questions

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

2.     What is the basic structure of PL/SQL?
    PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

3.     What are the components of a PL/SQL block?
    A set of related declarations and procedural statements is called block.

4.     What are the components of a PL/SQL Block?
    Declarative part, Executable part and Execption part.

5.     What are the datatypes a available in PL/SQL?
    Some scalar data types such as
NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.

6.     What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?
    % TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.

The advantages are: I. need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

7.     What is difference between % ROWTYPE and TYPE RECORD ?
    % 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.

E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type );
e_rec emp% ROWTYPE
Cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.

8.     What is PL/SQL table?
    Objects of type TABLE are called "PL/SQL tables", which are modelled 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.

9.     What is a cursor? Why Cursor is required?
    Cursor is a named private SQL area from where information can be accessed.
Cursors are required to process rows individually for queries returning multiple rows.

10.     Explain the two types of Cursors?
      There are two types of cursors, Implict Cursor and Explicit Cursor.
PL/SQL uses Implict Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.

11.     What are the PL/SQL Statements used in cursor processing?
      DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO <variable list> or Record types, CLOSE cursor name.

12.     What are the cursor attributes used in PL/SQL?
       %ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows featched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are featched.
% NOT FOUND - to check whether cursor has featched any row. True if no rows are featched.
These attributes are proceded with SQL for Implict Cursors and with Cursor name for Explict Cursors.

13.     What is a cursor for loop?
      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.

eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;

14.     What will happen after commit statement ?    
      Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;-----
commit;
end loop;
end;

The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.

The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

15.     Explain the usage of WHERE CURRENT OF clause in cursors ?
    WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor.

16.     What is a database trigger ? Name some usages of database trigger ?
    Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modificateions, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

17.     How many types of database triggers can be specified on a table? What are they?
                     Insert             Update             Delete

Before Row                 o.k.                  o.k.                o.k.

After Row                   o.k.                  o.k.                o.k.

Before Statement        o.k.                  o.k.                o.k.

After Statement           o.k.                  o.k.                o.k.

If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.

If WHEN clause is specified, the trigger fires according to the retruned boolean value.

18.     Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?
    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.

19.     What are two virtual tables available during database trigger execution?
      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.

20.     What happens if a procedure that updates a column of table X is called in a database trigger of the same table?
    Mutation of table occurs.

21.     Write the order of precedence for validation of a column in a table ?
    I. done using Database triggers.
ii. done using Integarity Constraints.

22.     What is an Exception? What are types of Exception?
    Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined execptions are.

CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.

23.     What is Pragma EXECPTION_INIT? Explain the usage?
      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)

24.     What is Raise_application_error?
      Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.

25.     What are the return values of functions SQLCODE and SQLERRM?
    SQLCODE returns the latest code of the error that has occured.
SQLERRM returns the relevant error message of the SQLCODE.

26.     Where the Pre_defined_exceptions are stored?
    In the standard package.
Procedures, Functions & Packages;

27.     What is a stored procedure?
    A stored procedure is a sequence of statements that perform specific function.

30.     What is difference between a PROCEDURE & FUNCTION?
    A FUNCTION is alway returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.

31.     What are advantages of Stored Procedures?
    Extensibility,Modularity, Reusability, Maintainability and one time compilation.

32.     What are the modes of parameters that can be passed to a procedure?
    IN,OUT,IN-OUT parameters.

33.     What are the two parts of a procedure?
    Procedure Specification and Procedure Body.

34.     Give the structure of the procedure?
      PROCEDURE name (parameter list.....)
is
local variable declarations

BEGIN
Executable statements.
Exception.
exception handlers

end;

35.     Give the structure of the function?
      FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;

36.     Explain how procedures and functions are called in a PL/SQL block ?
      Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');

37.     What is Overloading of procedures?
      The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.

e.g. DBMS_OUTPUT put_line

38.     What is a package? What are the advantages of packages?
      Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Applicaton Design, and Information.
Hiding,. Reusability and Better Performance.

39.     What are two parts of package?
      The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.
Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.

40.     What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?
      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.

41.     How packaged procedures and functions are called from the following ?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
      a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.

42.     Name the tables where characteristics of Package, procedure and functions are stored?
    User_objects, User_Source and User_error.
 

PL/SQL Advanced Interview Questions

1. Which of the following statements is true about implicit cursors?
   1. Implicit cursors are used for SQL statements that are not named.
   2. Developers should use implicit cursors with great care.
   3. Implicit cursors are used in cursor for loops to handle data processing.
   4. Implicit cursors are no longer a feature in Oracle.
   
   
2. Which of the following is not a feature of a cursor FOR loop?
   1. Record type declaration.
   2. Opening and parsing of SQL statements.
   3. Fetches records from cursor.
   4. Requires exit condition to be defined.
3. A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?
   1. Use employee.lname%type.
   2. Use employee.lname%rowtype.
   3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
   4. Declare it to be type LONG.
4. Which three of the following are implicit cursor attributes?
   1. %found
   2. %too_many_rows
   3. %notfound
   4. %rowcount
   5. %rowtype
5. If left out, which of the following would cause an infinite loop to occur in a simple loop?
   1. LOOP
   2. END LOOP
   3. IF-THEN
   4. EXIT
6. Which line in the following statement will produce an error?
   1. cursor action_cursor is
   2. select name, rate, action
   3. into action_record
   4. from action_table;
   5. There are no errors in this statement.
7. The command used to open a CURSOR FOR loop is
   1. open
   2. fetch
   3. parse
   4. None, cursor for loops handle cursor opening implicitly.
8. What happens when rows are found using a FETCH statement
   1. It causes the cursor to close
   2. It causes the cursor to open
   3. It loads the current row values into variables
   4. It creates the variables to hold the current row values
9. Read the following code:
   

   CREATE OR REPLACE PROCEDURE find_cpt
   (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
   IS
   BEGIN
     IF v_cost_per_ticket  > 8.5 THEN
   SELECT  cost_per_ticket
   INTO            v_cost_per_ticket
   FROM            gross_receipt
   WHERE   movie_id = v_movie_id;
     END IF;
   END;
   

   Which mode should be used for V_COST_PER_TICKET?
   
   1. IN
   2. OUT
   3. RETURN
   4. IN OUT
10. Read the following code:
    

    CREATE OR REPLACE TRIGGER update_show_gross
          {trigger information}
         BEGIN
          {additional code}
         END;
    

    The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?
    
    1. WHEN (new.cost_per_ticket > 3.75)
    2. WHEN (:new.cost_per_ticket > 3.75
    3. WHERE (new.cost_per_ticket > 3.75)
    4. WHERE (:new.cost_per_ticket > 3.75)
11. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
    1. Only one
    2. All that apply
    3. All referenced
    4. None
12. For which trigger timing can you reference the NEW and OLD qualifiers?
    1. Statement and Row
    2. Statement only
    3. Row only
    4. Oracle Forms trigger
13. Read the following code:
    

    CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
    RETURN number IS
    
    v_yearly_budget NUMBER;
    
    BEGIN
           SELECT  yearly_budget
           INTO            v_yearly_budget
           FROM            studio
           WHERE   id = v_studio_id;
    
           RETURN v_yearly_budget;
    END;
    

    Which set of statements will successfully invoke this function within SQL*Plus?
    
    1. VARIABLE g_yearly_budget NUMBER
       EXECUTE g_yearly_budget := GET_BUDGET(11);
    2. VARIABLE g_yearly_budget NUMBER
       EXECUTE :g_yearly_budget := GET_BUDGET(11);
    3. VARIABLE :g_yearly_budget NUMBER
       EXECUTE :g_yearly_budget := GET_BUDGET(11);
    4. VARIABLE g_yearly_budget NUMBER
       :g_yearly_budget := GET_BUDGET(11);

14. CREATE OR REPLACE PROCEDURE update_theater
    (v_name IN VARCHAR v_theater_id IN NUMBER) IS
    BEGIN
           UPDATE  theater
           SET             name = v_name
           WHERE   id = v_theater_id;
    END update_theater;
    

    When invoking this procedure, you encounter the error:
    

    ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.

    How should you modify the function to handle this error?
    
    1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
    2. Handle the error in EXCEPTION section by referencing the error code directly.
    3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
    4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.
15. Read the following code:
    

    CREATE OR REPLACE PROCEDURE calculate_budget IS
    v_budget        studio.yearly_budget%TYPE;
    BEGIN
           v_budget := get_budget(11);
           IF v_budget < 30000
      THEN
                   set_budget(11,30000000);
           END IF;
    END;
    

    You are about to add an argument to CALCULATE_BUDGET. What effect will this have?
    
    1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
    2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
    3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
    4. All three procedures are marked invalid and must be recompiled.
16. Which procedure can be used to create a customized error message?
    1. RAISE_ERROR
    2. SQLERRM
    3. RAISE_APPLICATION_ERROR
    4. RAISE_SERVER_ERROR
17. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
    1. ALTER TRIGGER check_theater ENABLE;
    2. ENABLE TRIGGER check_theater;
    3. ALTER TABLE check_theater ENABLE check_theater;
    4. ENABLE check_theater;
18. Examine this database trigger
    

    CREATE OR REPLACE TRIGGER prevent_gross_modification
    {additional trigger information}
    BEGIN
           IF TO_CHAR(sysdate, DY) = MON
     THEN
     RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
           END IF;
    END;
    

    This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?
    
    1. BEFORE DELETE ON gross_receipt
    2. AFTER DELETE ON gross_receipt
    3. BEFORE (gross_receipt DELETE)
    4. FOR EACH ROW DELETED FROM gross_receipt
19. Examine this function:
    

    CREATE OR REPLACE FUNCTION set_budget
    (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
    BEGIN
           UPDATE  studio
           SET             yearly_budget = v_new_budget
           WHERE   id = v_studio_id;
    
           IF SQL%FOUND THEN
                   RETURN TRUEl;
           ELSE
                   RETURN FALSE;
           END IF;
    
           COMMIT;
    END;
    

    Which code must be added to successfully compile this function?
    
    1. Add RETURN right before the IS keyword.
    2. Add RETURN number right before the IS keyword.
    3. Add RETURN boolean right after the IS keyword.
    4. Add RETURN boolean right before the IS keyword.
20. Under which circumstance must you recompile the package body after recompiling the package specification?
    1. Altering the argument list of one of the package constructs
    2. Any change made to one of the package constructs
    3. Any SQL statement change made to one of the package constructs
    4. Removing a local variable from the DECLARE section of one of the package constructs
21. Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
    1. When the transaction is committed
    2. During the data manipulation statement
    3. When an Oracle supplied package references the trigger
    4. During a data manipulation statement and when the transaction is committed
22. Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
    1. DBMS_DISPLAY
    2. DBMS_OUTPUT
    3. DBMS_LIST
    4. DBMS_DESCRIBE
23. What occurs if a procedure or function terminates with failure without being handled?
    1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
    2. Any DML statements issued by the construct are committed
    3. Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.
    4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.
24. Examine this code
    

    BEGIN
           theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
    END;
    

    For this code to be successful, what must be true?
    
    1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
    2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
    3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
    4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.
25. A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?
    1. DBMS_DDL
    2. DBMS_DML
    3. DBMS_SYN
    4. DBMS_SQL

Oracle SQL/PLSQL Interview Questions with Answers

 What are joins and Types of join?

Ans. We need retrive data from two or more tables to make our result complete. We need to perform a join.

INNER JOIN
This join returns rows when there is at least one match in both the tables.

OUTER JOIN
There are three different Outer Join methods.
LEFT OUTER JOIN
This join returns all the rows from the left table with the matching rows from the right table. If there are no field matching in the right table then it returns NULL values
RIGHT OUTER JOIN
Right outer join returns all the rows from the right table with the matching rows from the left table. If there are no field matching in the left table then it returns NULL values
FULL OUTER JOIN
Full outer join merge left outer join and right outer join.  this returns row from either table when the conditions are met and returns null value when there is no match
CROSS JOIN
Corss join is  does not necessary any condition to join. The output result contains records that are multiplication of record  from both the tables.
What is DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right)  based on the requirement.
WHAT ARE SET OPERATORS?
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.
What are different datatypes supported by sql in oracle?
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data types for character values, Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values, Date data type for date values, Long, Raw (size),  Long Raw,  Clob, Blob, Nclob, Bfile for large objects.
What is difference between long and lob datatypes?
Ans:LOB
1) The maximum size is 4GB. 2) LOBs (except NCLOB) can be attributes of an object type. 3) LOBs support random access to data. 4) Multiple LOB columns per table or LOB attributes in an object type.
LONG
1) The maximum size is 2GB.  2) LONGs cannot.    3) LONGs support only sequential access. 4) Only one LONG column was allowed in a table
How much memory is allocated for date datatype? What is default date format in oracle?
Ans: For Date data type oracle allocates 7 bytes Memory.   Default Date Format is: DD-MON-YY.
What is range for each datatype of sql?
Ans: Datatype Range Char  Varchar2  Number    Float     LONG, RAW, LONGRAW  Large Objects (LOB’s) 2000 bytes  4000 bytes  Precision
1 to 38 Scale -84 to 127  Precision 38 decimals Or 122 binary precision   2 GB  4GB
What is a constraint? What are its various levels?
Ans: Constraint: Constraints are representators of the column to enforce data entity and consistency.There r two levels
1)Column-level constraints 2)Table-level constraints.
List out all the constraints supported by oracle
Primary Key , Foreign Key or Referential Integrity, Not Null, Unique, Check.

 

Select 3 product which having highest  sale price

 SELECT * FROM ( SELECT * FROM product ORDER BY sales_price DESC) WHERE rownum <= 3

 

Delete the records from product which having null description

delete from product where product_name is null

 

Display detail of product which having maximum sale 

 SELECT * FROM
( SELECT product_id, count(product_id)as cnt FROM sales group by product_id order by cnt desc) WHERE rownum = 1  

Select customer details and produtct details of cutomer imran

Select p.product_name, c.cutomer_name, p.sale_price from product p, cutomer c, sale s

where p.product_id=s.product_id and c.cutomer_id=s.ccustomer_id and customer_name=’imran’

 

Difference between DELETE & TRUNCATE statement
Ans. Delete is a DML command. Truncate is a DDL command.
In Delete statement we can use where clause But we can’t use where clause in truncate statement.
Delete activates trigger. Truncate does not activate trigger.
We can rollback delete command. We can not rollback truncate command. Delete does not reset identity of table. Truncate resets identity of table.

 Difference between Primary key and Unique Key

Ans. Primary key and Unique key enforce uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, where as unique key creates a non-clustered index by default. Another major difference is that primary key does not allow NULL value, but unique key allows one NULL value only.

 

What are  oracle number, character, date, conversion, other
functions.

Ans.
Oracle Number Functions –
Round (m, [n]),
Trunc (m, [n]),
Power (m, n),
Sqrt,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
 
Oracle Character Functions-
Chr (x)
Concert (string1, string2)
Lower (string)
Upper (string)
Substr (string, from_str, to_str)
ASCII (string)
Length (string)
Initcap (string).
 
Oracle Date Functions-
sysdate
Months between (d1, d2)
To_char (d, format)
Last day (d)
Next_day (d, day).

Oracle Conversion Functions-
To_char
To_date
To_number
What is syntax of PL/SQL BLOCK
Ans. DECLARE

 BEGIN
   
 EXCEPTION
   
 END;

 

What are different types of oracle PL/SQL BLOCKS?

Ans:

Oracle PL/SQL DECLARE BLOCK – In DECLARE BLOCK all the declarations of the variable used in the program is made. If no variables are used this block will become optional.
Oracle PL/SQL BEGIN BLOCK -  In BEGIN BLOCK all the executable statements are placed. This block is Mandatory.
Oracle EXCEPTION BLOCK – In EXCEPTION BLOCK all the exceptions are handled. this block is optional.

 what is a Oracle PL/SQL cursor? and how to create cursor syntax?
Ans: Cursor is Private SQL area in PL/SQL.
     Declare the Cursor,
     Open the Cursor,
     Fetch values from SQL into the local Variables,
     Close the Cursor.
Type of cursors are supported by oracle pl/sql?
Ans.  There are two types of cursors namely Implicit Cursor, Explicit Cursor.
What is a cursor for loop?
Ans: Cursor For Loop is shortcut process for Explicit Cursors because the Cursor is Open, Rows are fetched once for each iteration and the cursor is closed automatically when all the rows have been processed.
What are cursor attributes?
Ans:  %Found, %NotFound,   %IsOpen, %RowCount are the cursor attributes.
 Use of cursor with “for update of” clause?
Ans: This Clause stop accessing of other users on the particular columns used by the cursor until the COMMIT is issued.
How Exception is different from error?
Ans: Whenever an error occurs Exception raises. Error is a bug whereas the Exception is a warning or error condition.

PL SQL Interview Questions - New Oracle Interview question

Oracle PL / SQL Interview Question

0
inShare

• What’s a PL/SQL table? Its purpose and Advantages?
  A PL/SQL table is one dimensional, indexed, unbounded sparsed collection of homogeneous
  Data.
  PLSQL tables are used to move data into and out of the database and between client side applications and stored sub-programs. They have attributes such as exits, prior, first, last, delete ,next . These attributes make PLSQL tables easier to use and applications easier to maintain.
  Advantages:
  1 PL\SQL tables give you the ability to hold multiple values in a structure in memory so that a PL\SQL block does not have to go to the database every time it needs to retrieve one of these values - it can retrieve it directly from the PL\SQL table in memory.
  2 Global temporary tables act as performance enhancers when compared to standard tables as they greatly reduce the disk IO.
  3 They also offer the ease-of-use of standard tables, since standard SQL can be used with them; no special array-processing syntax is required.

• What is a Cursor? How many types of Cursor are there?
  A) Cursor is an identifier/name to a work area that we can interact with to access its information. A cursor points to the current row in the result set fetched. There are three types of cursors. They are
  1 Implicit cursors – created automatically by PL/SQL for all SQL Dml statements such as
  Insert Update, delete and Select
  2 Explicit cursors – Created explicitly. They create a storage area where the set of rows
  Returned by a query are placed.
  3 Dynamic Cursors – Ref Cursors( used for the runtime modification of the select querry).
  Declaring the cursor, Opening the cursor, Fetching data , Closing the cursor(Releasing the work area) are the steps involved when using explicit cursors.

• What is the difference between Function and Procedure?
  1..Procedure is a sub program written to perform a set of actions and returns multiple valuesUsing out parameters or return no value at all.
  2..Function is a subprogram written to perform certain computations and return a single value.

• What are the modes for passing parameters to Oracle?
  There are three modes for passing parameters to subprograms
  1.IN - An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value.
  2. OUT – An out-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself.
  3.INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.

• What is the difference between Truncate and Delete Statement?
  1.Truncate – Data truncated by using truncate statement is lost permanently and cannot be retrieved even by rollback. Truncate command does not use rollback segment during its execution, hence it is fast.
  2. Delete – Data deleted by using the delete statement can be retrieved back by Rollback. Delete statement does not free up the table object allocated space.

• What are Exceptions? How many types of Exceptions are there?
  Exceptions are conditions that cause the termination of a block. There are two types of exceptions
  1.Pre-Defined – Predefined by PL/SQL and are associated with specific error codes.
  2.User-Defined – Declared by the users and are rose on deliberate request. (Breaking a condition etc.)
  Exception handlers are used to handle the exceptions that are raised. They prevent exceptions from propagating out of the block and define actions to be performed when exception is raised.

• What is a Pragma Exception_Init? Explain its usage?
  Pragma Exception_Init is used to handle undefined exceptions. It issues a directive to the compiler asking it to associate an exception to the oracle error. There by displaying a specific error message pertaining to the error occurred. Pragma Exception_Init (exception_name, oracle_error_name).

• What is a Raise and Raise Application Error?
  1.Raise statement is used to raise a user defined exception.
  2. A raise application error is a procedure belonging to dbms_standard package. It allows to display a user defined error message from a stored subprogram.

• What is the difference between Package, Procedure and Functions?
  1.A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.
  2.Procedure is a sub program written to perform a set of actions and can return multiple values.
  3.Function is a subprogram written to perform certain computations and return a single value. Unlike subprograms packages cannot be called, passed parameters or nested.

• How do you make a Function and Procedure as a Private?
  Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body.

• How do you kick a Concurrent program from PL/SQL?
  Using FND_REQUEST.SUBMIT_REQUEST.

• What is an Anonymous block?
  Anonymous Block is a block of instructions in PL/SQL and SQL which is not saved under a name as an object in database schema It is also not compiled and saved in server storage, so it needs to be parsed and executed each time it is run. However, this simple form of program can use variables, can have flow of control logic, can return query results into variables and can prompt the user for input using the SQL*Plus '&' feature as any stored procedure.

• What are the two basic parameters that we have to pass while registering PL/SQL procedure?
  Error code and Error Buffer.

• How to display messages in Log file and Output file?
  Using FND_FILE.PUT_LINE

• What is a Trigger ? How many types of Triggers are there?
  Trigger is a procedure that gets implicitly executed when an insert/update/delete statement is issued against an associated table. Triggers can only be defined on tables not on views, how ever triggers on the base table of a view are fired if an insert/update/delete statement is issued against a view.
  There are two types of triggers, Statement level trigger and Row level trigger.
  Insert
  After / For each row
  Trigger is fired / Update /
  Before / For Each statement
  Delete

• Can we use Commit in a Database Trigger, if ‘No’ then why?
  No. Committing in a trigger will violate the integrity of the transaction.

• What is Commit, Rollback and Save point?
  Commit – Makes changes to the current transaction permanent. It Erases the savepoints and releases the transaction locks.
  Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.
  Rollback – This statement is used to undo work.

• What is the difference between DDL, DML and DCL structures?
  DDL statements are used for defining data. Ex: Create, Alter, Drop,Truncate,Rename.
  DML statements are used for manipulating data. Ex: Insert, update, truncate.
  DCL statements are used for to control the access of data. Ex; Grant, Revoke.
  TCL statements are used for data saving.Ex; Commit,Rollback,Savepoint.

• How can u create a table in PL/SQL procedure?
  By using execute immediate statement we can create a table in PLSQL.
  Begin
  Execute immediate ‘create table amit as select * from emp’;
  End;
  All DDL,DML,DCL commands can be performed by using this command.

• How do we Tune the Queries?
  Queries can be tuned by Checking the logic (table joins), by creating Indexes on objects in the where clause, by avoiding full table scans. Finally use the trace utility to generate the trace file, use the TK-Prof utility to generate a statistical analysis about the query using which appropriate actions can be taken.

• What is Explain Plan? How do u use Explain Plan in TOAD?
  It is a utility provided by toad that gives the statistics about the performance of the query. It gives information such as number of full table scans occurred, cost, and usage of indexes

• What is a TK-PROF and its usage?
  Tk-Prof is a utility that reads the trace files and generates more readable data that gives the statistics about the performance of the query on a line to line basis.

• What is Optimization? How many types of Optimization are there?
  Rule based Optimization and Cost Based Optimization.

• What is the default optimization chosen by Oracle?
  Cost based Optimization.

• What is the difference between the snapshot and synonym?
  7 A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.
  8 A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.

• What is the difference between data types char and varchar?
  Char reserves the number of memory locations mentioned in the variable declarations, even though not used (it can store a maximum of 255 bytes). Where as Varchar does not reserve any memory locations when the variable is declared, it stores the values only after they are assigned (it can store a maximum of 32767 bytes).

• Items are imported from the legacy system using the item import interface using the SRS. How are items imported using the UNIX /PLSQL commands with out using SRS?
  1.From the operating system, use CONCSUB to submit a concurrent program. It's an easiest way to test a concurrent program.
  Normally, CONCSUB submits a concurrent request and returns control to the OS prompt/shell script without waiting for the request to complete. The CONCSUB WAIT parameter can be used to make CONCSUB wait until the request has completed before returning control to the OS prompt/shell script
  By using the WAIT token, the utility checks the request status every 60 seconds and returns to the operating system prompt upon completion of the request. concurrent manager does not abort, shut down, or start up until the concurrent request completes. If your concurrent program is compatible with itself, we can check it for data integrity and deadlocks by submitting it many times so that it runs concurrently with itself.
  Syntax: CONCSUB [WAIT= [START=] [REPEAT_DAYS=] [REPEAT_END=]
  To pass null parameters to CONCSUB, use '""' without spaces for each null parameter.
  In words: single quote double quote double quote single quote
  Following is an example of CONCSUB syntax with null parameters:
  CONCSUB oe/oe OE 'Order Entry Super User' JWALSH CONCURRENT XOE XOEPACK 4 3 '""' 3
  2. To Invoke a Concurrent Program using PL/SQL:
  i) Just insert a row in FND_CONCURRENT_REQUESTS with the apropriate parameters and commit.
  ii) Invoke the SUBMIT_REQUEST procedure in FND_REQUEST package.
  FND_REQUEST.SUBMIT_REQUEST( 'AR', 'RAXMTR', '', '', FALSE, 'Autoinvoice Master Program', sc_time, FALSE, 1, 1020, 'VRP', '01-JAN-00', chr(0)

• How can the duplicate records be deleted from the table?
  delete from t1 a where rowid not in (select max(rowid) from t1 b where a.no=b.no)

• What is the significance of _all tables?
  All tables are multi-org tables which are associated with the company as a whole. Multiple Organizations is enabled in Oracle
  Applications by partitioning some database tables by the Operating Unit. Other tables are shared across Operating Units (and therefore across set of books). Examples of Applications with partitioned tables are Oracle Payables, Oracle Purchasing, Oracle Receivables, Oracle Projects, Oracle Sales & Marketing etc. The name of each corresponding partitioned table is the view name appended by '_ALL'

• What are mutating tables? And what is mutating error?
  A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint.
  A mutating error occurs when a trigger which fires when updation/deletion/insertion is done on a table A performs insertion/updation/deletion on the same table A. This error results in an infinite loop which is termed as a mutating error.

• What is difference between oracle 7 andoracle 8i?
  A) Oracle 7 is a simple RDBMS, where as Oracle 8i is ORDBMS i.e., RDBMS with Object Support.
  The main add-ons in version 8 are…
  Abstract Data types
  Varrays
  PL/SQL Tables
  Nested Tables
  Partitioned Tables

• What is Data cleaning and testing.
  Data Cleaning: Transformation of data in its current state to a pre-defined, standardized format using packaged software or program modules.
  Data Testing: The agreed upon conversion deliverables should be approved by the client representatives who are responsible for the success of the conversion. In addition, three levels of conversion testing have been identified and described in the prepare conversion test plans deliverables.
  Eg: for Summary Balances in GL we set Test Criteria as Record Counts, Hash Totals, Balances, Journal Debit and Credit.
  
  
• While registering a report and a pl/sql block we pass some parameters, for any pl/sql block we pass 2 additional parameters. Can u list them?
  It requires two IN parameters for a PL/SQL procedure that's registered as a concurrent program in Apps. They are
  1. Errcode IN VARCHAR2
  2. Errbuff IN VARCHAR2
  
  
• what is a trace file?
  when ever an internal error is detected by a process in oracle it dumps the information about the error into a trace file.
  Alter session set sql_trace=TRUE

• When do you use Ref Cursors?
  We base a query on a ref cursor when you want to:
  1.More easily administer SQL
  2. Avoid the use of lexical parameters in your reports
  3. Share data sources with other applications, such as Form Builder
  4. Increase control and securityv) Encapsulate logic within a subprogram

Complete Oracle Interview Questions and Answers - part 10

What are the triggers available in the reports?
Before report, Before form, After form , Between page, After report.

Why is a Where clause faster than a group filter or a format trigger?
Because, in a where clause the condition is applied during data retrievalthan after retrieving the data.

Can one selectively load only the records that one need? (for DBA)
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)

Can one skip certain columns while loading data? (for DBA)
One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example: -- One cannot use POSTION(x:y) as it is stream data, there are no positional fields-the next field begins after some delimiter, not in column X. -->
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)

How does one load multi-line records? (for DBA)
One can create one logical record from multiple physical records using one of the following two clauses:
. CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
. CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

How can get SQL*Loader to COMMIT only at the end of the load file? (for DBA)
One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.

Can one improve the performance of SQL*Loader? (for DBA)
A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual.
Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads. Run multiple load jobs concurrently.

How does one use SQL*Loader to load images, sound clips and documents? (for DBA)
SQL*Loader can load data from a "primary data file", SDF (Secondary Data file - for loading nested tables and VARRAYs) or LOGFILE. The LOBFILE method provides and easy way to load documents, images and audio clips into BLOB and CLOB columns. Look at this example:
Given the following table:
CREATE TABLE image_table (
image_id NUMBER(5),
file_name VARCHAR2(30),
image_data BLOB);
Control File:
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
image_id INTEGER(5),
file_name CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg

What is the difference between the conventional and direct path loader? (for DBA)
The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Utilities Users Guide.

GENERAL INTERVIEW QUESTIONS
What are the various types of Exceptions ?
User defined and Predefined Exceptions.

Can we define exceptions twice in same block ?
No.

What is the difference between a procedure and a function ?
Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.

Can you have two functions with the same name in a PL/SQL block ?
Yes.

Can you have two stored functions with the same name ?
Yes.

Can you call a stored function in the constraint of a table ?
No.

What are the various types of parameter modes in a procedure ?
IN, OUT AND INOUT.

What is Over Loading and what are its restrictions ?
OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.

Can functions be overloaded ?
Yes.

Can 2 functions have same name & input parameters but differ only by return datatype ?
No.

What are the constructs of a procedure, function or a package ?
The constructs of a procedure, function or a package are :
variables and constants
cursors
exceptions

Why Create or Replace and not Drop and recreate procedures ?
So that Grants are not dropped.

Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions in a package.

What are the parts of a database trigger ?
The parts of a trigger are:
A triggering event or statement
A trigger restriction
A trigger action

What are the various types of database triggers ?
There are 12 types of triggers, they are combination of :
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.
(3*2*2=12)

What is the advantage of a stored procedure over a database trigger ?
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

What is the maximum no. of statements that can be specified in a trigger statement ?
One.

Can views be specified in a trigger statement ?
No

What are the values of :new and :old in Insert/Delete/Update Triggers ?
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value

What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.

What are mutating triggers ?
A trigger giving a SELECT on the table on which the trigger is written.

What are constraining triggers ?
A trigger giving an Insert/Update on a table having referential integrity constraint on the triggering table.

Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.

Can you increase the size of a tablespace ? How ?
Yes, by adding datafiles to it.

What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc.

What is the use of Data Dictionary ?
Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc

What are the advantages of clusters ?
Access time reduced for joins.

What are the disadvantages of clusters ?
The time for Insert increases.

Can Long/Long RAW be clustered ?
No.

Can null keys be entered in cluster index, normal index ?
Yes.

Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.

What are the min. extents allocated to a rollback extent ?
Two

What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
The various states of a rollback segment are :
ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.

What is the difference between unique key and primary key ?
Unique key can be null; Primary key cannot be null.

An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
No.

an you define multiple savepoints ?
Yes.

Can you Rollback to any savepoint ?
Yes.

What is the maximum no. of columns a table can have ?
254.

What is the significance of the & and && operators in PL SQL ?
The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable. If a transaction is very large, and the rollback segment is not able to hold the rollback information, then will the transaction span across different rollback segments or will it terminate ? It will terminate (Please check ).

Can you pass a parameter to a cursor ?
Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear. CURSOR c1 (median IN NUMBER) IS SELECT job, ename FROM emp WHERE sal > median;

What are the various types of RollBack Segments ?
Public Available to all instances
Private Available to specific instance

Can you use %RowCount as a parameter to a cursor ?
Yes

Is the query below allowed :
Select sal, ename Into x From emp Where ename = 'KING'
(Where x is a record of Number(4) and Char(15))
Yes

Is the assignment given below allowed :
ABC = PQR (Where ABC and PQR are records)
Yes

Is this for loop allowed :
For x in &Start..&End Loop
Yes

How many rows will the following SQL return :
Select * from emp Where rownum < 10;
9 rows

How many rows will the following SQL return :
Select * from emp Where rownum = 10;
No rows

Which symbol preceeds the path to the table in the remote database ?
@

Are views automatically updated when base tables are updated ?
Yes

Can a trigger written for a view ?
No

If all the values from a cursor have been fetched and another fetch is issued, the output will be : error, last record or first record ?
Last Record

A table has the following data : [[5, Null, 10]]. What will the average function return ?
7.5

Is Sysdate a system variable or a system function?
System Function

Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue an rollback and again issue a nextval. What will the output be ?
3

Definition of relational DataBase by Dr. Codd (IBM)?
A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.

What is Multi Threaded Server (MTA) ?
In a Single Threaded Architecture (or a dedicated server configuration) the database manager creates a separate process for each database user. But in MTA the database manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the databases memory requirement and resources.

Which are initial RDBMS, Hierarchical & N/w database ?
RDBMS - R system
Hierarchical - IMS
N/W - DBTG

What is Functional Dependency
Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R

What is Auditing ?
The database has the ability to audit all actions that take place within it.
a) Login attempts, b) Object Accesss, c) Database Action Result of Greatest(1,NULL) or Least(1,NULL) NULL

While designing in client/server what are the 2 imp. things to be considered ?
Network Overhead (traffic), Speed and Load of client server

When to create indexes ?
To be created when table is queried for less than 2% or 4% to 25% of the table rows.

How can you avoid indexes ?
TO make index access path unavailable - Use FULL hint to optimizer for full table scan - Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another. - Use an expression in the Where Clause of the SQL.

What is the result of the following SQL :
Select 1 from dual
UNION
Select 'A' from dual;
Error

Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
Yes, database trigger would fire.

Can you alter synonym of view or view ?
No

Can you create index on view ?
No

What is the difference between a view and a synonym ?
Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.

What is the difference between alias and synonym ?
Alias is temporary and used with one query. Synonym is permanent and not used as alias.

What is the effect of synonym and table name used in same Select statement ?
Valid

What's the length of SQL integer ?
32 bit length

What is the difference between foreign key and reference key ?
Foreign key is the key i.e. attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.

Can dual table be deleted, dropped or altered or updated or inserted ?
Yes

If content of dual is updated to some value computation takes place or not ?
Yes

If any other table same as dual is created would it act similar to dual?
Yes

For which relational operators in where clause, index is not used ?
<> , like '% ...' is NOT functions, field +constant, field || ''

Assume that there are multiple databases running on one machine. How can you switch from one to another ?
Changing the ORACLE_SID

What are the advantages of Oracle ?
Portability : Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols.
Market Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue. This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available.
Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerence to disk failure. You can also do point-in-time recovery.
Performance : Speed of a 'tuned' Oracle Database and application is quite good, even with large databases. Oracle can manage > 100GB databases.
Multiple database support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.

What is a forward declaration ? What is its use ?
PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. This declaration at the start of a subprogram is called forward declaration. A forward declaration consists of a subprogram specification terminated by a semicolon.

What are actual and formal parameters ?
Actual Parameters : Subprograms pass information using parameters. The variables or expressions referenced in the parameter list of a subprogram call are actual parameters. For example, the following procedure call lists two actual parameters named emp_num and amount:
Eg. raise_salary(emp_num, amount);
Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters. For example, the following procedure declares two formal parameters named emp_id and increase: Eg. PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;

What are the types of Notation ?
Position, Named, Mixed and Restrictions.

What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size ?
In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 & 300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of database creation}.
The initial SGA was around 4MB when the server RAM was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB.

If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ?
Yes

What are various types of joins ?
Equijoins, Non-equijoins, self join, outer join

What is a package cursor ?
A package cursor is a cursor which you declare in the package specification without an SQL statement. The SQL statement for the cursor is attached dynamically at runtime from calling procedures.

If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted ?
Yes. Because Create table is a DDL which commits automatically as soon as it is executed. The DDL commits the transaction even if the create statement fails internally (eg table already exists error) and not syntactically.

What are the various types of queries ??
Normal Queries
Sub Queries
Co-related queries
Nested queries
Compound queries

What is a transaction ?
A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

What is implicit cursor and how is it used by Oracle ?
An implicit cursor is a cursor which is internally created by Oracle. It is created by Oracle for each individual SQL.

Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
Public synonyms

What is PL/SQL?
PL/SQL is Oracle's Procedural Language extension to SQL. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.

Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

Is there a limit on the size of a PL/SQL block?
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package or procedure.
SQL> select * from dba_object_size where name = 'procedure_name'

Can one read/write files from PL/SQL?
Included in Oracle 7.3 is a UTL_FILE package that can read and write files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;

How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code. This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.yyy

Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL
AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;