DATABASE CONCEPTS: August 2018

1. Oracle function can return multiple values? Example/How to return multiple values from a function in Oracle PL/SQL?

A. Generally we write functions for computing some value. Function can return only one value, though it’s definition may contains more than one return statements. Let us see the below defined function func_test. Its definition is having multiple return statements.

CREATE OR REPLACE

FUNCTION func_test

RETURN NUMBER

BEGIN

RETURN 1;

RETURN 2;

END;

SELECT func_test AS value FROM dual;

VALUE

------

But, upon executing this function, it is returning only one value. Now, Let us see how to return more than one value from function. For this, we need to create a collection/record object.
For example, I am creating numbers collection of NUMBER data type.

CREATE OR REPLACE TYPE numbers IS TABLE OF NUMBER;

Below function simply returns 10 numbers values from 1 to 10 in the collection objects of numbers.

CREATE OR REPLACE

FUNCTION func_multi_val

RETURN numbers

l_numbers numbers := numbers();

BEGIN

FOR i IN 1 .. 10

LOOP

l_numbers.EXTEND();

l_numbers(i) := i;

END LOOP;

RETURN l_numbers;

END;

Now we will check whether our function is returning multiple values or not.

SELECT * FROM TABLE(func_multi_val);

COLUMN_VALUE

-------------

SQL Query to get ZERO records when it contains N number of records?

We can get in Two ways as per my knowledge

SELECT * FROM MAK_EMP WHERE 1=2;

SELECT * FROM MAK_EMP

MINUS

SELECT * FROM MAK_EMP;

How many LONG columns are allowind in table?

Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

4. How can we find select statement is indexed or not?

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from USER_INDEXES

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from ALL_INDEXES

How to delete duplicate rows from a table.

We will use the concept of rowed in order to delete the duplicate, max select the, natural key, candidate key , actually logical defined unique record we do group by and identify max row on that particular key and delete the data which is not identified on that particular query.

Example Query:

DELETE FROM DATA_DUPS WHERE ROWID NOT IN(

SELECT MAX(ROWID) FROM DATA_DUPS GROUP BY ID);

Explain about DUAL TABLE?

Special one row one column table present by default in oracle installation
It will be present in the sys schema
It will return always single row
Suitable for use in selecting pseudo column like user or sysdate
It can be used functions, Triggers, procedures etc

Difference between Delete, Truncate and Drop?

DELETE	TRUNCATE	DROP
Can Delete Partial Data	Remove Entire Date	Removes The Object
DML	DDL	DDL
SLOW	FAST	FAST
Recover Using Rollback	Data Lost Forever	Data Can Be Recovered (Flashback Option)
Delete Trigger May Be Fired	No Triggers Fired	No DML Triggers
High Water Mark Is Not Going To Reset	High Water Mark Is Going To Reset	Object Lost
No Impact On Dependent Objects	No Impact On Dependent Objects	Dependent Objects Will Have To Be Recreated

What is DBMS_Profiler?

DBMS_Profiler does the same job for PL/SQL what explain plan performs for sql, it helps in identifying where the PL/SQL code is taking time so that the developer can optimize it accordingly.

Procedure Vs. Function: Key Differences

Procedure		Function
Used mainly to a execute certain process		Used mainly to perform some calculation
Cannot call in SELECT statement		A Function that contains no DML statements can be called in SELECT statement
Use OUT parameter to return the value		Use RETURN to return the value
It is not mandatory to return the value		It is mandatory to return the value
RETURN will simply exit the control from subprogram.	RETURN will exit the control from subprogram and also returns the value
Return data type will not be specified at the time of creation	Return data type is mandatory at the time of creation

What are Parameter Modes in PL/SQL Functions and Procedures?

IN MODE : It is a default mode, it is used to accept the values from the end user, it is for reading purpose.
OUT MODE : This mode is used to send the values to the end user, it is used for writing purpose.
IN/OUT MODE : This mode is used for both reading and writing purpose. It accept the values from the end user and also it returns the values to the end user.

What are Formal and Actual Parameters?

Formal Parameters: Formal parameter is the parameter which you specify when you define the subroutine or function. These parameters define the list of possible variables, their positions along with their datatypes. While specifying the formal parameters in the signature of your subroutines you do not assign any value to them except the default values which make a parameter optional.

Example of Formal Parameters:

CREATE OR REPLACE FUNCTION circle_area (radius NUMBER)
RETURN NUMBER IS

This is the header of the PL/SQL Function which we saw in How to Create Functions in Oracle Database. Here the parameter radius NUMBER is the formal parameter.

public static int Circle_area (int radius) { }

And that is the same function signature in Java where also the parameter int radius is a formal parameter.

Actual parameters: Actual parameters are the parameters which you specify when you call the subroutine such as Functions or Procedures. These are actually the values you provide to the subroutine when you call them. You can call a subroutine without an actual parameter when the formal parameter has a default value.

Example of Actual parameters:

BEGIN
DBMS_OUTPUT.PUT_LINE(circle_area (25));
END;
/

Here in the above PL/SQL anonymous block we call the function circle_area( ) whose signature we saw above by passing the value 25 against the formal parameter radius NUMBER. This Numeric value 25 is the actual parameter.

Always make sure that the datatype of the value you specified as an actual parameter must match with the datatype of the corresponding formal parameter. Also the value specified as an actual parameter of a function will override the default value of the corresponding formal parameter, if it has any.

What is PL/SQL?

A. PL/SQL is a block-structured language. That is, the basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested sub-blocks. Typically, each logical block corresponds to a problem or subproblem to be solved. Thus, PL/SQL supports the divide-and-conquer approach to problem solving called stepwise refinement.

PL/SQL is an extension of Structured Query Language (SQL) that is used in Oracle. Unlike SQL, PL/SQL allows the programmer to write code in a procedural format. Full form of PL/SQL is "Procedural Language extensions to SQL".

It combines the data manipulation power of SQL with the processing power of procedural language to create super powerful SQL queries.

PL/SQL means instructing the compiler 'what to do' through SQL and 'how to do' through its procedural way.

Similar to other database languages, it gives more control to the programmers by the use of loops, conditions and object-oriented concepts.

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

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.

This is the component which has the actual PL/SQL code.
This consists of different sections to divide the code logically (declarative section for declaring purpose, execution section for processing statements, exception handling section for handling errors)
It also contains the SQL instruction that used to interact with the database server.
All the PL/SQL units are treated as PL/SQL blocks, and this is the starting stage of the architecture which serves as the primary input.
Following are the different type of PL/SQL units.

Anonymous Block
Function
Library
Procedure
Package Body
Package Specification
Trigger
Type
Type Body

What are the components of a PL/SQL block?

A set of related declarations and procedural statements is called block.

What are the components of PL/SQL block?

Declarative section, Executable section and Exception section.

What is Cursors in PL/SQL?

Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit

https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/01_oview.htm

%TYPE

The %TYPE attribute provides the datatype of a variable or database column. This is particularly useful when declaring variables that will hold database values. For example, assume there is a column named title in a table named books. To declare a variable named my_title that has the same datatype as column title, use dot notation and the %TYPE attribute, as follows:

my_title books.title%TYPE;

Declaring my_title with %TYPE has two advantages. First, you need not know the exact datatype of title. Second, if you change the database definition of title (make it a longer character string for example), the datatype of my_title changes accordingly at run time.

%ROWTYPE

In PL/SQL, records are used to group data. A record consists of a number of related fields in which data values can be stored. The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable.

Columns in a row and corresponding fields in a record have the same names and datatypes. In the example below, you declare a record named dept_rec. Its fields have the same names and datatypes as the columns in the dept table.

DECLARE

   dept_rec dept%ROWTYPE;  -- declare record variable

You use dot notation to reference fields, as the following example shows:

my_deptno := dept_rec.deptno;

If you declare a cursor that retrieves the last name, salary, hire date, and job title of an employee, you can use %ROWTYPE to declare a record that stores the same information, as follows:

DECLARE

   CURSOR c1 IS

      SELECT ename, sal, hiredate, job FROM emp;

   emp_rec c1%ROWTYPE;  -- declare record variable that represents

                        -- a row fetched from the emp table

When you execute the statement

FETCH c1 INTO emp_rec;

The value in the ename column of the emp table is assigned to the ename field of emp_rec, the value in the sal column is assigned to the sal field, and so on. Figure 1-3 shows how the result might appear.

Figure 1-3 %ROWTYPE Record

Text description of pls81002_rowtype_record.gif follows

Modularity

Modularity lets you break an application down into manageable, well-defined modules. Through successive refinement, you can reduce a complex problem to a set of simple problems that have easy-to-implement solutions. PL/SQL meets this need with program units, which include blocks, subprograms, and packages.

Subprograms

PL/SQL has two types of subprograms called procedures and functions, which can take parameters and be invoked (called). As the following example shows, a subprogram is like a miniature program, beginning with a header followed by an optional declarative part, an executable part, and an optional exception-handling part:

PROCEDURE award_bonus (emp_id NUMBER) IS

   bonus        REAL;

   comm_missing EXCEPTION;

BEGIN  -- executable part starts here

   SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;

   IF bonus IS NULL THEN

      RAISE comm_missing;

   ELSE

      UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;

   END IF;

EXCEPTION  -- exception-handling part starts here

   WHEN comm_missing THEN

...

END award_bonus;

When called, this procedure accepts an employee number. It uses the number to select the employee's commission from a database table and, at the same time, compute a 15% bonus. Then, it checks the bonus amount. If the bonus is null, an exception is raised; otherwise, the employee's payroll record is updated.

Packages

PL/SQL lets you bundle logically related types, variables, cursors, and subprograms into a package. Each package is easy to understand and the interfaces between packages are simple, clear, and well defined. This aids application development.

Packages usually have two parts: a specification and a body. The specification is the interface to your applications; it declares the types, constants, variables, exceptions, cursors, and subprograms available for use. The body defines cursors and subprograms and so implements the specification.

In the following example, you package two employment procedures:

CREATE PACKAGE emp_actions AS  -- package specification

   PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);

   PROCEDURE fire_employee (emp_id NUMBER);

END emp_actions;

CREATE PACKAGE BODY emp_actions AS  -- package body

   PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS

   BEGIN

      INSERT INTO emp VALUES (empno, ename, ...);

   END hire_employee;

   PROCEDURE fire_employee (emp_id NUMBER) IS

   BEGIN

      DELETE FROM emp WHERE empno = emp_id;

   END fire_employee;

END emp_actions;

Only the declarations in the package specification are visible and accessible to applications. Implementation details in the package body are hidden and inaccessible.

Packages can be compiled and stored in an Oracle database, where their contents can be shared by many applications. When you call a packaged subprogram for the first time, the whole package is loaded into memory. So, subsequent calls to related subprograms in the package require no disk I/O. Thus, packages can enhance productivity and improve performance.

Data Abstraction

Data abstraction lets you extract the essential properties of data while ignoring unnecessary details. Once you design a data structure, you can forget the details and focus on designing algorithms that manipulate the data structure.

Collections

The collection types TABLE and VARRAY allow you to declare index-by tables, nested tables, and variable-size arrays (varrays for short). A collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.

To reference an element, use standard subscripting syntax. For example, the following call references the fifth element in the nested table (of type Staff) returned by function new_hires:

DECLARE

   TYPE Staff IS TABLE OF Employee;

   staffer Employee;

   FUNCTION new_hires (hiredate DATE) RETURN Staff IS

   BEGIN ... END;

BEGIN

   staffer := new_hires('10-NOV-98')(5);

...

END;

Collections work like the arrays found in most third-generation programming languages. Also, collections can be passed as parameters. So, you can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.

Records

You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row fetched from a cursor. But, with a user-defined record, you can declare fields of your own.

Records contain uniquely named fields, which can have different datatypes. Suppose you have various data about an employee such as name, salary, and hire date. These items are dissimilar in type but logically related. A record containing a field for each item lets you treat the data as a logical unit.

Consider the following example:

DECLARE

   TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT);

   TYPE MeetingTyp IS RECORD (

      date_held DATE,

      duration  TimeRec,  -- nested record

      location  VARCHAR2(20),

      purpose   VARCHAR2(50));

Notice that you can nest records. That is, a record can be a component of another record.

Error Handling

PL/SQL makes it easy to detect and process predefined and user-defined error conditions called exceptions. When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. To handle raised exceptions, you write separate routines called exception handlers.

Predefined exceptions are raised implicitly by the runtime system. For example, if you try to divide a number by zero, PL/SQL raises the predefined exception ZERO_DIVIDE automatically. You must raise user-defined exceptions explicitly with the RAISEstatement.

You can define exceptions of your own in the declarative part of any PL/SQL block or subprogram. In the executable part, you check for the condition that needs special attention. If you find that the condition exists, you execute a RAISE statement. In the example below, you compute the bonus earned by a salesperson. The bonus is based on salary and commission. So, if the commission is null, you raise the exception comm_missing.

DECLARE

...

   comm_missing EXCEPTION;  -- declare exception

BEGIN

...

   IF commission IS NULL THEN

      RAISE comm_missing;  -- raise exception

   END IF;

   bonus := (salary * 0.10) + (commission * 0.15);

EXCEPTION

   WHEN comm_missing THEN ... -- process the exception

Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:

Support for SQL
Support for object-oriented programming
Better performance
Higher productivity
Full portability
Tight integration with Oracle
Tight security

Support for SQL

SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like commands such as SELECT, INSERT, UPDATE, and DELETE make it easy to manipulate the data stored in a relational database.

SQL is non-procedural, meaning that you can state what you want done without stating how to do it. Oracle determines the best way to carry out your request. There is no necessary connection between consecutive statements because Oracle executes SQL statements one at a time.

PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. So, you can manipulate Oracle data flexibly and safely. Also, PL/SQL fully supports SQL datatypes. That reduces the need to convert data passed between your applications and the database.

PL/SQL also supports dynamic SQL, an advanced programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements "on the fly" at run time.

Support for Object-Oriented Programming

Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.

By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details without affecting client programs.

In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. That helps your programs better reflect the world they are trying to simulate.

Better Performance

Without PL/SQL, Oracle must process SQL statements one at a time. Each SQL statement results in another call to Oracle and higher performance overhead. In a networked environment, the overhead can become significant. Every time a SQL statement is issued, it must be sent over the network, creating more traffic.

However, with PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce communication between your application and Oracle. As Figure 1-5 shows, if your application is database intensive, you can use PL/SQL blocks and subprograms to group SQL statements before sending them to Oracle for execution.

PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Also, stored procedures, which execute in the server, can be invoked over slow network connections with a single call. That reduces network traffic and improves round-trip response times. Executable code is automatically cached and shared among users. That lowers memory requirements and invocation overhead.

Figure 1-5 PL/SQL Boosts Performance

Text description of pls81005_plsql_boosts_performance.gif follows

Text description of the illustration pls81005_plsql_boosts_performance.gif

PL/SQL also improves performance by adding procedural processing power to Oracle tools. Using PL/SQL, a tool can do any computation quickly and efficiently without calling on the Oracle server. This saves time and reduces network traffic.

Higher Productivity

PL/SQL adds functionality to non-procedural tools such as Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use familiar procedural constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger. You need not use multiple trigger steps, macros, or user exits. Thus, PL/SQL increases productivity by putting better tools in your hands.

Also, PL/SQL is the same in all environments. As soon as you master PL/SQL with one Oracle tool, you can transfer your knowledge to other tools, and so multiply the productivity gains. For example, scripts written with one tool can be used by other tools.

Full Portability

Applications written in PL/SQL are portable to any operating system and platform on which Oracle runs. In other words, PL/SQL programs can run anywhere Oracle can run; you need not tailor them to each new environment. That means you can write portable program libraries, which can be reused in different environments.

Tight Integration with SQL

The PL/SQL and SQL languages are tightly integrated. PL/SQL supports all the SQL datatypes and the non-value NULL. That allows you manipulate Oracle data easily and efficiently. It also helps you to write high-performance code.

The %TYPE and %ROWTYPE attributes further integrate PL/SQL with SQL. For example, you can use the %TYPE attribute to declare variables, basing the declarations on the definitions of database columns. If a definition changes, the variable declaration changes accordingly the next time you compile or run your program. The new definition takes effect without any effort on your part. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

Tight Security

PL/SQL stored procedures enable you to partition application logic between the client and server. That way, you can prevent client applications from manipulating sensitive Oracle data. Database triggers written in PL/SQL can disable application updates selectively and do content-based auditing of user inserts.

Furthermore, you can restrict access to Oracle data by allowing users to manipulate it only through stored procedures that execute with their definer's privileges. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself.

15. Explain about cursor attributes?

A. Each cursor and cursor is four attributes.

%FOUND: To check weather cursor has fetched any row, true if rows are fetched.

%ISOPEN: To check whether cursor is open or not

%ROWCOUNT: Number of rows fetched/update/deleted

and %NOT FOUND: To check weather cursor has fetched any row, true if not rows are fetched.

When appended to the cursor these attributes returns useful information about execution of data manipulation statement.

16. What are restrictions of using cursor variable?

A. PL/SQL table cannot store Cursor variable and remote subprogram cannot return value of the cursor variable.

17. What are triggering attributes?

A. Triggering Attributes are use to catch event when you won’t to identify are to perform certain actions. There as follows inserting, updating and deleting.

18. What is the difference between GRANT and REVOKE commands?

A. Grant commands is use to allow user to perform certain activity on the DB. Revoke command

Disallow the user perform certain activities.

19. Define Joins and its types?

A. A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables.

Equijoins

An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.

Self Joins

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition.

Inner Joins

An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition

Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

left outer join

right outer join

full outer join

Antijoins

An antijoin returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. It returns rows that fail to match (NOT IN) the subquery on the right side.

Semijoins

A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.

Semijoin and antijoin transformation cannot be done if the subquery is on an OR branch of the WHERE clause.

Equijoins, NonEquijoins and self joins and outer joins.

20. List out advantages of VIEWS?

A. Complex queries that need to execute often can be saved in a view. Views are extremely useful when query uses multiple tables. If tables are changed only the view definition needs to be changed and not the code where view is used. Views are usually virtual and occupied no space. They prevent undesired access by providing security as the data that is not of interest to user can be left out of the view. A view displays different data for different type’s users.

21. Explain the usage of WHERE CURRENT OF CLAUSE in Cursors?

A. Where current of clause in an UPDATE, DELETE statement refers to the latest row fetched from a Cursor.

22. Is it possible to use transaction control statements (TCL) such a ROLLBACK and COMMIT in database Triggers? Why?

A. It is not possible. As Triggers are defined for each table, If you use COMMIT or ROLLBACK in a trigger, it effects logical transaction processing.

We cannot use commit and rollback in triggers without using PRAGMA AUTONOMOUS_TRANSACTION.
Triggers are fired when any DML is performed on a table like insert update or delete, and these all have tendency to lock the rows for their operation. whereas Commit or Rollback when used releases the locks if any acquired by transaction. thus in this state trigger gets confused either to lock the rows or to release the lock and goes to mutating stage.
with use of autonomous_transaction parent transaction is separated from child transaction and child transaction can be committed or rolled back without affecting parent transaction and that is how we can commit or rollback in triggers.

Generally In triggers you can't use TCL commands. But you can use TCL commands in Autonomous Triggers. You can declare a trigger as Autonomous by providing PRAGMA AUTONOMOUS_TRANSACTION in the beginning of the trigger.
At a same time you have to end your trigger with commit/rollback. You can use these types of triggers to maintain log details of a table. Autonomous Transaction is a feature of oracle 8i which maintains the state of its transactions and save it, to affect with the commit or rollback of the surrounding transactions.

Here is the simple example to understand this:-

ora816 SamSQL :> declare
2 Procedure InsertInTest_Table_B
3 is
4 BEGIN
5 INSERT into Test_Table_B(x) values (1);
6 Commit;
7 END ;
8 BEGIN
9 INSERT INTO Test_Table_A(x) values (123);
10 InsertInTest_Table_B;
11 Rollback;
12 END;
13 / PL/SQL procedure successfully completed.
ora816 SamSQL :> Select * from Test_Table_A; X---------- 123
ora816 SamSQL :> Select * from Test_Table_B; X---------- 1

Notice in above pl/sql COMMIT at line no 6 , commits the transaction at line-no 5 and line-no 9. The Rollback at line-no 11 actually did nothing. Commit/ROLLBACK at nested transactions will commit/rollback all other DML transaction before that. PRAGMA AUTONOMOUS_TRANSACTION override this behavior. Let us the see the following example with PRAGMA AUTONOMOUS_TRANSACTION.

ora816 SamSQL :> declare
2 Procedure InsertInTest_Table_B
3 is
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT into Test_Table_B(x) values (1);
7 Commit;
8 END ;
9 BEGIN
10 INSERT INTO Test_Table_A(x) values (123);
11 InsertInTest_Table_B;
12 Rollback;
13 END;
14 /PL/SQL procedure successfully completed.
ora816 SamSQL :> Select * from Test_Table_A;no rows selected
ora816 SamSQL :> Select * from Test_Table_B; X---------- 1

With PRAGMA AUTONOMOUS_TRANSACTION, the transaction state maintained independently. Commit/Rollback of nested transaction will no effect the other transaction. It is advisable to increase the value of TRANSACTIONS parameter in the INIT parameter file to allow for the extra concurrent transaction.

23. What is Mutating error?

This error occurs when you try to select rows from a table and try to update the rows of the same table. Most developers face this problem. To avoid getting this error, create views, select the rows from the views, and then try to update the original table rows.

ORA-04091: table is mutating

Triggers don't mutate; tables do. A mutating table error is raised when a table is modified by the trigger against it. As an example consider a modified EMP table, with an additional column named INCREASE; creating the following update trigger will raise a mutating table error:

Code (SQL):

CREATE OR REPLACE TRIGGER updt_emp_sal
BEFORE UPDATE ON emp
FOR each ROW
BEGIN
IF :NEW.sal <> :OLD.sal THEN
UPDATE emp SET increase = :NEW.sal - :OLD.sal
WHERE empno = :NEW.empno;
END IF;
END;
/

The trigger is modifying the table which is already being modified by the UPDATE statement. Oracle will raise an ORA-04091 error and execution will stop, preventing the data from being modified.

As mentioned before there are no mutating triggers, so the first and last 'events' cannot occur. The middle three are all the same error, an ORA-04091. Using a procedure in an autonomous transaction to update the table and calling that procedure from the trigger can eliminate such errors.

24. Write the order of precedence for validation of a column in a table?

A. So, if it is before insert trigger then the trigger will run first before the constraints in the table. if it is after insert trigger , constraints are checked first and then trigger will run.

25. What is raise application error in oracle?

A. The raise_application_error is actually a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure. This allows the application to raise application errors rather than just Oracle errors.

RAISE_APPLICATION_ERROR is a standard built-in procedure by Oracle that raises an error with number and user understandable error message. This helps user to work on the resolution of error in most exclusive and correct way.

Raise_application_error shows application specific error message. You cannot re-raise it.

For Example,

DECLARE

ln_employee_num NUMBER;

ls_name employees.name%TYPE;

BEGIN

SELECT emp.employee_number, emp.name

INTO ln_employee_num, ls_name

FROM employees emp

WHERE emp.employee_number = 1;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR (-20000,'Data not found

for employee number:'||ln_employee_num);

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR (-20001,'Other error while

fetching data for

employee number:'||ln_employee_num

||'. Error is '||SQLERRM);

END;

26. What is RAISE?

RAISE is also a standard built-in procedure by Oracle but it is mostly used to raise user-defined exception. It is also used to raise standard oracle exception. Without showing an actual message of exception, RAISE will just raise an exception and fails the execution of the code.

The advantage of RAISE is that it can be used to re-raise the same exception again.

For Example,

DECLARE

my_error EXCEPTION;

BEGIN

IF 1 < 2 THEN

RAISE my_error;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE my_error;

WHEN my_error THEN

RAISE_APPLICATION_ERROR (-20000,'Error Raised. Please resolve');

END;

27. What are the return values of functions SQLCODE and SQLERRM ?

A. SQLCODE returns the latest code of the error that has occurred. SQLERRM returns the relevant error message of the SQLCODE.

The function SQLCODE returns the number code of the most recent exception.

SQLCODE returns the number of the last encountered error.

SQLERRM returns the message associated with its error-number argument. The error-number argument is optional: if omitted, SQLERRM returns the message associated with the current value of SQLCODE.

28. Overloading Packages, Functions & Procedures? / What does it mean to "overload" an Oracle stored procedure or function?

A. The process of "overloading" originates in the world of object-oriented coding with the concept of polymorphism. It is the idea that the functionality of a PL/SQL stored procedure of function can be changed based on the input datatype.

Polymorphism was a spin off of the PL/SQL concept called "overloading" Overloading a stored procedure or function refers to the ability of a programming method to perform more than one kind of operation depending upon the context in which the method is used.

For a simple example of overloading, you can write a PL/SQL function that does one thing when a numeric argument is passed to the procedure and another thing when a character string is passed as an argument.

For a simple Oracle example of overloading, consider the wwv_flow_mail.sendprocedure. There are two overloaded versions of the send procedure and Oracle knows which to invoke by the argument data types passed to the send procedure. If you pass a CLOB datatype for p_body and p_body_html, then the second send procedure is invoked.

Polymorphism and overloading in PL/SQL

Polymorphism is the ability of different objects to receive the same message and respond in different ways. Polymorphism simplifies the communication between objects by using a common interface to hide different implementation details.

A very simple example of this is the use of the operator "+", in working with characters it can be used for concatenation and if used with numerical values it would be used for addition. A programming example of overloading would look as follows.

Concatenation example:

First_Name = "John"
Last_Name = "Smith"
Full_Name = First_Name + Last_Name

Numerical example

Kounter = Kounter + 1

Oracle member methods and overloading

Overloading is associated with the object-oriented idea of "polymorphism" whereby a single procedure may be context-sensitive, depending on the input parameters and it is also used in the object-oriented Oracle with the idea of member methods.

An example of this type of method would be an update method that updates a table attribute based on the value that is passed. If a date value is passed, the procedure will do the conversion to character, same with a number.

The technique for overloading a method procedure or function is identical to the overloading of standard procedure or function.

create type
   test3(
   type_passed varchar2(8),
   member procedure input_type(in_char char),
   member procedure input_type(in_varchar varchar2),
   member procedure input_type(in_date date),
   member procedure input_type(in_number number)
);

29. What Is a PL/SQL Package?

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.

Advantages of PL/SQL Packages

Packages have a long history in software engineering, offering important features for reliable, maintainable, reusable code, often in team development efforts for large systems.

Modularity

Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.

Easier Application Design

When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.

Information Hiding

With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.

Added Functionality

Packaged public variables and cursors persist for the duration of a session. They can be shared by all subprograms that execute in the environment. They let you maintain data across transactions without storing it in the database.

Better Performance

When you call a packaged subprogram for the first time, the whole package is loaded into memory. Later calls to related subprograms in the package require no disk I/O.

Packages stop cascading dependencies and avoid unnecessary recompiling. For example, if you change the body of a packaged function, Oracle does not recompile other subprograms that call the function; these subprograms only depend on the parameters and return value that are declared in the spec, so they are only recompiled if the spec changes.

Disadvantages:

More memory may be required on the Oracle database server when using Oracle PL/SQL packages as the whole package is loaded into memory as soon as any object in the package is accessed.

Updating one of the functions/procedures will invalid other objects which use different function/procedures since whole package need to be compiled.

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows

The INTERSECT operator, which returns only those unique rows returned by both queries

The MINUS operator, which returns only unique rows returned by the first query but not by the second

Oracle PL SQL Interview Questions

PL Sql vs Sql
Comparison	SQL	PL/SQL
Execution	Single command at a time	Block of code
Application	Source of data to be displayed	Application created by data aquired by SQL
Structures include	DDL and DML based queries and commands	Includes procedures, functions, etc
Recommended while	Performing CRUD operations on data	Creating applications to display data obtained using sql
Compatibility with each other	SQL can be embedded into PL/SQL	PL/SQL cant be embedded in SQL

Q. What is SQL and also describe types of SQL statements?

SQL stands for Structured Query Language. SQL is a language used to communicate with the server to access, manipulate and control data.

There are 5 different types of SQL statements.

Data Retrieval: SELECT
Data Manipulation Language (DML): INSERT, UPDATE, DELETE, MERGE
Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE.
Transaction Control Statements: COMMIT, ROLLBACK, SAVEPOINT
Data Control Language (DCL): GRANT, REVOKE

Q. What is an alias in SQL statements?

Alias is a user-defined alternative name given to the column or table. By default column alias headings appear in upper case. Enclose the alias in a double quotation marks (“ “) to make it case sensitive. “AS” Keyword before the alias name makes the SELECT clause easier to read.

For ex: Select empname AS name from employee; (Here AS is a keyword and “name” is an alias).

Q. What is a Literal? Give an example where it can be used?

A Literal is a string that can contain a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. Date and character literals must be enclosed within single quotation marks (‘ ‘), number literals need not.

For ex: Select last_name||’is a’||job_id As “emp details” from employee; (Here “is a” is a literal).

Learn Oracle PL SQL Training: Become experts in components of Oracle PL/SQL, Oracle database environment | Take Up Now Course at Mindmajix. Enroll & Become Certified

Q. What is a difference between SQL and iSQL*Plus?

*SQL Vs iSQLPlus**
SQL	*iSQLPlus**
Is a Language	Is an Environment
Character and date columns heading are left-justified and number column headings are right-justified.	Default heading justification is in Centre.
Cannot be Abbreviated (short forms)	Can be Abbreviated
Does not have a continuation character	Has a dash (-) as a continuation character if the command is longer than one line
Use Functions to perform some formatting	Use commands to format data

Q. Define the order of Precedence used in executing SQL statements.

Order of Precedence used in executing SQL statements
Order Evaluated	Operator
1	Arithmetic operators (*, /, +, -)
2	Concatenation operators (\|\|)
3	Comparison conditions
4	Is[NOT] NULL, LIKE, [NOT] IN
5	[NOT] BETWEEN
6	NOT Logical condition
7	AND logical condition
8	OR logical condition

Q. What are SQL functions? Describe in brief different types of SQL functions?

SQL Functions are very powerful feature of SQL. SQL functions can take arguments but always return some value.
There are two distinct types of SQL functions:

1) Single-Row functions: These functions operate on a single row to give one result per row.

Types of Single-Row functions:

Character
Number
Date
Conversion
General

2) Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.

Types of Multiple-Row functions:

AVG
COUNT
MAX
MIN
SUM
STDDEV
VARIANCE

Frequently Asked SQL Interview Questions & Answers

Q. Explain character, number and date function in detail?

Character functions: accept character input and return both character and number values. Types of character function are:

a) Case-Manipulation Functions: LOWER, UPPER, INITCAP

b) Character-Manipulation Functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, REPLACE

Number Functions: accept Numeric input and return numeric values. Number Functions are: ROUND, TRUNC and MOD

Date Functions: operates on values of the Date data type. (All date functions return a value of DATE data type except the MONTHS_BETWEEN Function, which returns a number. Date Functions are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC.

Q. What is a Dual Table?

Dual table is owned by the user SYS and can be accessed by all users. It contains one columnDummy and one row with the value X. The Dual Table is useful when you want to return a value only once. The value can be a constant, pseudocolumn or expression that is not derived from a table with user data.

Checkout Oracle PL SQL Tutorials

Q. Explain Conversion function in detail?

Conversion Functions converts a value from one data type to another. Conversion functions are of two types:

Implicit Data type conversion:

VARCHAR2 or CHAR To NUMBER, DATE
NUMBER To VARCHAR2
DATE To VARCHAR2

Explicit data type conversion:

TO_NUMBER
TO_CHAR
TO_DATE

TO_NUMBER function is used to convert Character string to Number format. TO_NUMBER function use fx modifier. Format: TO_NUMBER ( char[, ‘ format_model’] ). fx modifier specifies the exact matching for the character argument and number format model of TO_NUMBER function.

TO_CHAR function is used to convert NUMBER or DATE data type to CHARACTER format. TO_CHAR Function use fm element to remove padded blanks or suppress leading zeros. TO_CHAR Function formats:TO_CHAR (date, ‘format_model’).Format model must be enclosed in single quotation marks and is case sensitive.

For ex: Select TO_CHAR (hiredate, ‘MM/YY’) from employee.

TO_DATE function is used to convert Character string to date format. TO_DATE function use fx modifier which specifies the exact matching for the character argument and date format model of TO_DATE function. TO_DATE function format: TO_DATE ( char[, ‘ format_model’] ).

For ex: Select TO_DATE (‘may 24 2007’,’mon dd rr’) from dual;

Q. Describe different types of General Function used in SQL?

General functions are of following types:

NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then NVL function return value of exp2.
NVL2: If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)
NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)
COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over NVL function is that the COALESCE function can take multiple alternative values.
Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function.

Q. What is difference between COUNT (*), COUNT (expression), COUNT (distinct expression)? (Where expression is any column name of Table)

COUNT (*): Returns number of rows in a table including duplicates rows and rows containing null values in any of the columns.

COUNT (EXP): Returns the number of non-null values in the column identified by expression.

COUNT (DISTINCT EXP): Returns the number of unique, non-null values in the column identified by expression.

Q. What is a Sub Query? Describe its Types?

A sub query is a SELECT statement that is embedded in a clause of another SELECT statement. Sub query can be placed in WHERE, HAVING and FROM clause.

Guidelines for using sub queries:

Enclose sub queries within parenthesis
Place sub queries on the right side of the comparison condition.
Use Single-row operators with single-row sub queries and Multiple-row operators with multiple-row sub queries.

Types of sub queries:

Single-Row Sub query: Queries that return only one row from the Inner select statement. Single-row comparison operators are: =, >, >=, <, <=, <>
Multiple-Row Sub query: Queries that return more than one row from the inner Select statement. There are also multiple-column sub queries that return more than one column from the inner select statement. Operators includes: IN, ANY, ALL.

Q. What is difference between ANY and ALL operators?

ANY Operator compares value to each value returned by the subquery. ANY operator has a synonym SOME operator.

> ANY means more than the minimum.

< ANY means less than the maximum

= ANY is equivalent to IN operator.

ALL Operator compares value to every value returned by the subquery.

> ALL means more than the maximum

< ALL means less than the minimum

<> ALL is equivalent to NOT IN condition.

Q. What is a MERGE statement?

The MERGE statement inserts or updates rows in one table, using data from another table. It is useful in data warehousing applications.

Q. What is a difference between “VERIFY” and “FEEDBACK” command?

VERIFY Command: Use VERIFY Command to confirm the changes in the SQL statement (Old and New values). Defined with SET VERIFY ON/OFF.

Feedback Command: Displays the number of records returned by a query.

Q. What is the use of Double Ampersand (&&) in SQL Queries? Give example?

Use “&&” if you want to reuse the variable value without prompting the user each time.

For ex: Select empno, ename, &&column_name from employee order by &column_name;

Q. What are Joins and how many types of Joins are there?

Joins are used to retrieve data from more than one table.

There are 5 different types of joins.

types of Joins
Oracle 8i and Prior	SQL: 1999 (9i)
Equi Join	Natural/Inner Join
Outer Join	Left Outer/ Right Outer/ Full Outer Join
Self Join	Join ON
Non-Equi Join	Join USING
Cartesian Product	Cross Join

Q. Explain all Joins used in Oracle 8i?

?Cartesian Join: When a Join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. To avoid a Cartesian product, always include a valid join condition in a “where” clause. To Join ‘N’ tables together, you need a minimum of N-1 Join conditions. For ex: to join four tables, a minimum of three joins is required. This rule may not apply if the table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.

Equi Join: This type of Join involves primary and foreign key relation. Equi Join is also called Simple or Inner Joins.

Non-Equi Joins: A Non-Equi Join condition containing something other than an equality operator. The relationship is obtained using an operator other than equal operator (=).The conditions such as <= and >= can be used, but BETWEEN is the simplest to represent Non-Equi Joins.

Outer Joins: Outer Join is used to fetch rows that do not meet the join condition. The outer join operator is the plus sign (+), and it is placed on the side of the join that is deficient in information. The Outer Join operator can appear on only one side of the expression, the side that has information missing. It returns those rows from one table that has no direct match in the other table. A condition involving an Outer Join cannot use IN and OR operator.

Self Join: Joining a table to itself.

Q. Explain all Joins used in Oracle 9i and later release?

Cross Join: Cross Join clause produces the cross-product of two tables. This is same as a Cartesian product between the two tables.

Natural Joins: Is used to join two tables automatically based on the columns which have matching data types and names, using the keyword NATURAL JOIN. It is equal to the Equi-Join. If the columns have the same names but different data types, than the Natural Join syntax causes an error.

Join with the USING clause: If several columns have the same names but the data types do not match, than the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equi Join. Use the USING clause to match only one column when more than one column matches. Do not use a table name or alias in the referenced columns. The NATURAL JOIN clause and USING clause are mutually exclusive.

For ex: Select a.city, b.dept_name from loc a Join dept b USING (loc_id) where loc_id=10;

Joins with the ON clause: Use the ON clause to specify a join condition. The ON clause makes code easy to understand. ON clause is equals to Self Joins. The ON clause can also be used to join columns that have different names.

Left/ Right/ Full Outer Joins: Left Outer Join displays all rows from the table that is Left to the LEFT OUTER JOIN clause, right outer join displays all rows from the table that is right to the RIGHT OUTER JOIN clause, and full outer join displays all rows from both the tables either left or right to the FULL OUTER JOIN clause.

Q. What is a difference between Entity, Attribute and Tuple?

Entity: A significant thing about which some information is required. For ex: EMPLOYEE (table). Attribute: Something that describes the entity. For ex: empno, empname, empaddress (columns). Tuple: A row in a relation is called Tuple.

Q. What is a Transaction? Describe common errors can occur while executing any Transaction?

Transaction consists of a collection of DML statements that forms a logical unit of work.

The common errors that can occur while executing any transaction are:

The violation of constraints.

Data type mismatch.
Value too wide to fit in column.
The system crashes or Server gets down.
The session Killed.
Locking take place. Etc.

Q. What is locking in SQL? Describe its types?

Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback. Types of locking are:

Implicit Locking: Occurs for all SQL statements except SELECT.

Explicit Locking: Can be done by user manually.

Further there are two locking methods:

Exclusive: Locks out other users
Share: Allows other users to access

Q. What is a difference between Commit, Rollback and Savepoint?

· COMMIT: Ends the current transaction by making all pending data changes permanent.

· ROLLBACK: Ends the current transaction by discarding all pending data changes.

· SAVEPOINT: Divides a transaction into smaller parts. You can rollback the transaction till a particular named savepoint.

Q. What are the advantages of COMMIT and ROLLBACK statements?

Advantages of COMMIT and ROLLBACK statements are:

· Ensure data consistency

· Can preview data changes before making changes permanent.

· Group logically related operations.

Q. Describe naming rules for creating a Table?

Naming rules to be consider for creating a table are:

Table name must begin with a letter,
Table name can be 1-30 characters long,
Table name can contain only A-Z, a-z, 0-9,_, $, #.
Table name cannot duplicate the name of another object owned by the same user.
Table name cannot be an oracle server reserved word.

Q. What is a DEFAULT option in a table?

A column can be given a default value by using the DEFAULT option. This option prevents null values from entering the column if a row is inserted without a value for that column. The DEFAULT value can be a literal, an expression, or a SQL function such as SYSDATE and USER but the value cannot be the name of another column or a pseudo column such as NEXTVAL or CURRVAL.

Q. What is a difference between USER TABLES and DATA DICTIONARY?

USER TABLES: Is a collection of tables created and maintained by the user. Contain USER information. DATA DICTIONARY: Is a collection of tables created and maintained by the Oracle Server. It contains database information. All data dictionary tables are owned by the SYS user.

Q. Describe few Data Types used in SQL?

Data Types is a specific storage format used to store column values. Few data types used in SQL are:

VARCHAR2(size): Minimum size is ‘1’ and Maximum size is ‘4000’
CHAR(size): Minimum size is ‘1’and Maximum size is ‘2000’
NUMBER(P,S): " Precision" can range from 1 to 38 and the “Scale” can range from -84 to 127.
DATE
LONG: 2GB
CLOB: 4GB
RAW (size): Maximum size is 2000
LONG RAW: 2GB
BLOB: 4GB
BFILE: 4GB
ROWID: A 64 base number system representing the unique address of a row in the table.

Q. In what scenario you can modify a column in a table?

During modifying a column:

You can increase the width or precision of a numeric column.
You can increase the width of numeric or character columns.
You can decrease the width of a column only if the column contains null values or if the table has no rows.
You can change the data type only if the column contains null values.
You can convert a CHAR column to the VARCHAR2 data type or convert a VARCHAR2 column to the CHAR data type only if the column contains null values or if you do not change the size.

Q. Describe few restrictions on using “LONG” data type?

A LONG column is not copied when a table is created using a sub query. A LONG column cannot be included in a GROUP BY or an ORDER BY clause. Only one LONG column can be used per table. No constraint can be defined on a LONG column.

Q. What is a SET UNUSED option?

SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped, even though their column data remains in the table’s rows. After a column has been marked as unused, you have no access to that column. A select * query will not retrieve data from unused columns. In addition, the names and types of columns marked unused will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column. The SET UNUSED information is stored in the USER_UNUSED_COL_TABS dictionary view.

Q. What is a difference between Truncate and Delete?

The main difference between Truncate and Delete is as below:

SQL Truncate Vs SQL Delete
TRUNCATE	DELETE
Removes all rows from a table and releases storage space used by that table.	Removes all rows from a table but does not release storage space used by that table.
TRUNCATE Command is faster.	DELETE command is slower.
Is a DDL statement and cannot be Rollback.	Is a DDL statement and can be Rollback.
Database Triggers do not fire on TRUNCATE.	Database Triggers fire on DELETE.

Q. What is a main difference between CHAR and VARCHAR2?

CHAR pads blank spaces to a maximum length, whereas VARCHAR2 does not pad blank spaces.

Q. What are Constraints? How many types of constraints are there?

Constraints are used to prevent invalid data entry or deletion if there are dependencies. Constraints enforce rules at the table level. Constraints can be created either at the same time as the table is created or after the table has been created. Constraints can be defined at the column or table level. Constraint defined for a specific table can be viewed by looking at the USER-CONSTRAINTS data dictionary table. You can define any constraint at the table level except NOT NULL which is defined only at column level. There are 5 types of constraints:

Not Null Constraint
Unique Key Constraint
Primary Key Constraint
Foreign Key Constraint
Check Key Constraint.

Q. Describe types of Constraints in brief?

NOT NULL: NOT NULL Constraint ensures that the column contains no null values.

UNIQUE KEY: UNIQUE Key Constraint ensures that every value in a column or set of columns must be unique, that is, no two rows of a table can have duplicate values in a specified column or set of columns. If the UNIQUE constraint comprises more than one column, that group of columns is called a Composite Unique Key. There can be more than one Unique key on a table. Unique Key Constraint allows the input of Null values. Unique Key automatically creates index on the column it is created.

PRIMARY KEY: Uniquely identifies each row in the Table. Only one PRIMARY KEY can be created for each table but can have several UNIQUE constraints. PRIMARY KEY ensures that no column can contain a NULL value. A Unique Index is automatically created for a PRIMARY KEY column. PRIMARY KEY is called a Parent key.

FOREIGN KEY: Is also called Referential Integrity Constraint. FOREIGN KEY is one in which a column or set of columns take references of the Primary/Unique key of same or another table. FOREIGN KEY is called a child key. A FOREIGN KEY value must match an existing value in the parent table or be null.

CHECK KEY: Defines a condition that each row must satisfy. A single column can have multiple CHECK Constraints. During CHECK constraint following expressions is not allowed:

1) References to CURRVAL, NEXTVAL, LEVEL and ROWNUM Pseudo columns.

2) Calls to SYSDATE, UID, USER and USERENV Functions

Q. What is the main difference between Unique Key and Primary Key?

The main difference between Unique Key and Primary Key are:

Unique Vs Primary Key
Unique Key	Primary Key
A table can have more than one Unique Key.	A table can have only one Primary Key.
Unique key column can store NULL values.	Primary key column cannot store NULL values.
Uniquely identify each value in a column.	Uniquely identify each row in a table.

Q. What is a difference between ON DELETE CASCADE and ON DELETE SET NULL?

ON DELETE CASCADE Indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted. ON DELETE SET NULL Coverts foreign key values to null when the parent value is removed. Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.

Explore Oracle PL SQL Sample Resumes! Download & Edit for Free!Download Now!

Q. What is a Candidate Key?

The columns in a table that can act as a Primary Key are called Candidate Key.

Q. What are Views and why they are used?

A View logically represents subsets of data from one or more table. A View is a logical table based on a table or another view. A View contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.

Views are used:

· To restrict data access

· To make complex queries easy

· To provide data Independence

· Views provide groups of user to access data according to their requirement.

Q. What is a difference between Simple and Complex Views?

The main differences between two views are:

Simple Views Vs Complex Views
Simple View	Complex View
Derives data from only one table.	Derives data from many tables.
Contains no functions or group of data	Contain functions or groups of data.
Can perform DML operations through the view.	Does not always allow DML operations through the view.

Q. What are the restrictions of DML operations on Views?

Few restrictions of DML operations on Views are:

You cannot DELETE a row if the View contains the following:

Group Functions
A Group By clause
The Distinct Keyword
The Pseudo column ROWNUM Keyword.

You cannot MODIFY data in a View if it contains the following:

Group Functions
A Group By clause
The Distinct Keyword
The Pseudo column ROWNUM Keyword.
Columns defined by expressions (Ex; Salary * 12)

You cannot INSERT data through a view if it contains the following:

Q. What is PL/SQL?

PL/SQL is a procedural language extension with SQL Language.
Oracle 6.0 introduced PL/SQL
It is a combination of SQL and Procedural Statements and used for creating applications.
Basically PL/SQL is a block structure programming language whenever we are submitting PL/SQL
Blocks then all SQL statements are executing separately by using sql engine and also all procedure statements are executed separately.

Q. What are the different functionalities of a Trigger ?

Trigger is also same as stored procedure & also it will automatically invoked whenever DML operation performed against table or view.

There are two types of triggers supported by PL/SQL

Statement Level Trigger.
Row Level Trigger

Statement Level Trigger: In statement level trigger, trigger body is executed only once for DML statement.

Row Level Trigger: In row level trigger, trigger body is executed for each row DML statements. It is the reason, we are employing each row clause and internally stored DML transaction in trigger specification, these qualifiers :old, :new, are also called as records type variables.
These qualifiers are used in trigger specification & trigger body.

Synatx:

:old.column_name
Synatx:
:new column_name

When we are use this qualifiers in trigger specification then we are not allowed to use “:” in forms of the qualifiers names.

Q. Write a PL/SQL Program which raise a user defined exception on thursday?

declare

a exception

begin

If to_char(sysdate, ‘DY)=’THU’

then

raise a;

end if;

exception

when a then

dbms_output.put_line(‘my exception raised on thursday’);

end;

Output: my exception raised on thursday

Q.Write a PL/SQL program to retrieve emp table and then display the salary?

declare

v_sal number(10);

begin

select max(sal)intr v_sal;

from emp;

dbms_output.put_line(v.sal);

end;

(or)

declare
ANumber(10);
Bnumber(10);
Cnumber(10);
begin
a:=70;
b:=30;
c:=greatest+(a,b);
dbms_output.put_line(c);
end;
/

Output:70

Q. Write a PL/SQL cursor program which is used to calculate total salary from emp table without using sum() function?

Declare

cursor c1 is select sal from emp;

v_sal number(10);

n.number(10):=0;

begin

open c1;

loop

fetch c1 into v_sal;

exit when c1%not found;

n:=n+v_sal;

end loop;

dbms_output.put_line(‘tool salary is’||’ ‘ ||n);

close c1;

end;

Output: total salary is: 36975

Q. Write a PL/SQL cursor program to display all employee names and their salary from emp table by using % not found attributes?

Declare

Cursor c1 is select ename, sal from emp;

v_ename varchar2(10);

v_sal number(10);

begin

open c1;

loop

fetch c1 into v_ename, v_sal;

exist when c1 % notfound;

dbms_output.put_line(v_name ||’ ‘||v_sal);

end loop;

close c1;

end;

Q. What is Mutating Trigger?

· Into a row level trigger based on a table trigger body cannot read data from same table and also we cannot perform DML operation on same table.

· If we are trying this oracle server returns mutating error oracle-4091: table is mutating.

· This error is called mutating error, and this trigger is called mutating trigger, and table is called mutating table.

· Mutating errors are not occured in statement level trigger because through these statement level trigger when we are performing DML operations automatically data committed into the database, whereas in row level trigger when we are performing transaction data is not committed and also again we are reading this data from the same table then only mutating errors is occured.

Q. What is Triggering Events (or) Trigger Predicate Clauses?

If we want to perform multiple operations in different tables then we must use triggering events within trigger body. These are inserting, updating, deleting clauses. These clauses are used in statement, row-level trigger. These triggers are also called as trigger predicate clauses.
Syntax:

If inserting then

stmts;

else if updating then

stmts;

else if deleting then

stmts;

end if;

Q. What is Discard File?

· This file extension is .dsc

· Discard file we must specify within control file by using discard file clause.

· Discard file also stores reflected record based on when clause condition within control file. This condition must be satisfied into table tablename clause.

Q. What is REF CURSOR (or) CURSOR VARIABLE (or) DYNAMIC CURSOR ?

Oracle 7.2 introduced ref cursor, This is an user defined type which is used to process multiple records and also this is a record by record process.

In static cursor database servers executes only one select statement at a time for a single active set area where in ref cursor database servers executes number of select statement dynamically for a single active set area that's why those cursor are also called as dynamically cursor.

Generally we are not allowed to pass static cursor as parameters to use subprograms where as we can also pass ref cursor as parameter to the subprograms because basically refcursor is an user defined type in oracle we can also pass all user defined type as parameter to the subprograms.
Generally static cursor does not return multiple record into client application where as ref cursor are allowed to return multiple records into client application (Java, .Net, php, VB, C++).

This is an user defined type so we are creating it in 2 steps process i.e first we are creating type then only we are creating variable from that type that’s why this is also called as cursor variable.

Q. What are The Types of Ref Cursors?

In all databases having 2 ref cursors.

Strong ref cursor
Weak ref cursor

Strong ref cursor is a ref cursor which have return type, whereas weak ref cursor has no return type.
Syntax:

Type typename is ref cursor return record type data type;

Variable Name typename

Syntax

Type typename is ref cursor

Variable Name typename;

In Weak ref cursor we must specify select statement by using open for clause this clause is used in executable section of the PL/SQL block.
Syntax:

Open ref cursor varname for SELECT * FROM tablename condition;

Q. What is Difference Between trim, delete collection method?

SQL> declare

type t1 is table of number(10);

v_t t1;=t1(10,20,30,40,50,60);

beign

v_t.trim(2);

dbms_output.put_line(‘after deleting last two elements’);

for i in v_t.first.. V_t.last

loop

dbms_output.put_line(v_t(i));

End loop;

vt.delete(2);

dbms_output.put_line(‘after deleting second element;);

for i in v_t.first..v_t.last

loop

If v_t.exists(i) then

dbms_output.put_line(v_t(i));

end if;

end loop;

end;

Q. What is Overloading Procedures?

Overload is refers to same name can be used for different purpose, in oracle we can also implement overloading procedure through package. Overloading procedure having same name with different type or different number of parameters.

Q. What is Global Variables?

In oracle we are declaring global variables in Package Specification only.

Q. What is Forward Declaration?

In oracle declaring procedures within package body is called forward declaring generally before we are calling private procedures into public procedure first we must implements private into public procedure first we must implements private procedure within body otherwise use a forward declaration within package body.

Q. What is Invalid_number, Value_Error?

In oracle when we try to convert “string type to number type” or” data string into data type” then oracle server returns two types of errors.

1. Invalid.number

2. Value_error (or) numeric_error

a) Invalid_number:

When PL/SQL block have a SQL statements and also those SQL statements try to convert string type to number type or data string into data type then oracle server returns an error: ora-1722-Invalid Number

For handling this error oracle provides number exception Invalid_number exceptionname.

Example:

begin

Insert into

emp(empno, ename, sal) values(1,’gokul’, ‘abc’)

exception

when invalid_number then

dbms_output.put_line(‘insert proper data only’);

end;

b)value_error
Whenever PL/SQL block having procedural statements and also those statements find to convert string type to number type then oracle servers returns an error: ora-6502:numeric or value error: character to number conversion error
For handling this error oracle provided exception value_error exception name
Example:

begin

declare

z number(10);

begin

z:= ‘&x’ + ‘&y’;

dbms_output.put_line(z);

exception

when value_error then

dbms_output.put_line(‘enter numeric data value for x & y only’);

end;

Output:

Enter value for x:3

Enter value for y:2

z:=5

Enter value for x:a

Enter value for y:b

Error:enter numeric data value for x & y only.

Q. What is Flashback Query?

· Flashback query are handle by Database Administrator only flashback queries along allows content of the table to be retrieved with reference to specific point of time by using as of clause that is flashback queries retrieves clause that is flashback queries retrieves accidental data after committing the transaction also.

· Flashback queries generally uses undo file that is flashback queries retrieve old data before committing the transaction oracle provide two method for flashback queries

Method1: using timestamp

Method2: using scn number

Question #1) Differentiate PL/SQL and SQL?

Answer: Difference between SQL and PL/SQL can be categorized as follows

SQL PL/SQL

SQL is a natural language which is very useful for interactive processing. PL/SQL is a procedural extension of Oracle - SQL.

No procedural capabilities like condition testing, looping is offered by SQL. PL/SQL supports procedural capabilities as well as high language features such as conditional statements, looping statements, etc.

All SQL statements are executed by the database server one at a time, thus it is a time-consuming process. PL/SQL statements send the entire block of statements to the database server at the same time, thus network traffic is reduced considerably.

No error handling procedures are there in SQL. PL/SQL supports customized error handling.

Question #2) Enlist the characteristics of PL/SQL?

Answer: Characteristics of PL/SQL are as follows

PL/SQL allows access and sharing of the same sub programs by multiple applications.

PL/SQL is known for portability of code as code can be executed on any operating system provided Oracle is loaded on it.

With PL/SQL user can write their own customized error handling routines.

Improved transaction performance with integration to Oracle data dictionary.

Question #3) What are the data types available in PL/SQL?

Answer: Data types define the ways to identify the type of data and their associated operations. There are 4 types of predefined data types explained as follows

Scalar Data Types: A scalar data type is an atomic data type that does not have any internal components.

For example

CHAR (fixed length character value between 1 and 32,767 characters)

VARCHAR2 (variable length character value between 1 and 32,767 characters)

NUMBER ( fixed-decimal, floating-decimal or integer values)

BOOLEAN ( logical data type for TRUE FALSE or NULL values)

DATE (stores date and time information)

LONG (character data of variable length)

Composite Data Types: A composite data type is made up of other data types and internal components that can be easily used and manipulated. For example RECORD, TABLE, and VARRAY.

Reference Data Types: A reference data types holds values, called pointers that designate to other program items or data items. For example REF CURSOR.

Large Object Data Types: A Large Object datatype holds values, called locators, that defines the location of large objects( such as video clips, graphic image, etc) stored out of line.

For example

BFILE (Binary file)

BLOB (Binary large object)

CLOB ( Character large object)

NCLOB( NCHAR type large object)

Question #4) Explain the purpose of %TYPE and %ROWTYPE data types with the example?

Answer: PL/SQL uses %TYPE declaration attribute for anchoring. This attribute provides the datatype of a variable, constant or column. %TYPE attribute is useful while declaring a variable that has the same datatype as a table column.

For example, the variable m_empno has the same data type and size as the column empno in table emp.

m_empno emp.empno%TYPE;

%ROWTYPE attribute is used to declare a variable to be a record having the same structure as a row in a table. The row is defined as a record and its fields have the same names and data types as the columns in the table or view.

For example: dept_rec dept%ROWTYPE;

This declares a record that can store an entire row for DEPT table.

Question #5) What do you understand by PL/SQL packages?

Answer: PL/SQL packages are schema objects that groups functions, stored procedures, cursors and variables at one place. Packages have 2 mandatory parts

Package Specifications

Package body

Question #6) What do you understand by PL/SQL cursors?

Answer: PL/SQL requires a special capability to retrieve and process more than one row and that resource is known as Cursors. A cursor is a pointer to the context area, which is an area of memory containing SQL statements and information for processing the statements.

PL/SQL Cursor is basically a mechanism under which multiple rows of the data from the database are selected and then each row is individually processed inside a PL/SQL program.

Question #7) Explain cursor types?

Answer: There are two types of cursors. They are explained as follows

1) Explicit Cursors: For queries that return more than one row, an explicit cursor is declared and named by a programmer. In order to use explicit cursor in PL/SQL, 4 steps are followed

Declare the cursor

Syntax: CURSOR <cursor_name> is

SELECT statement;

Where <cursor_name> is the name assigned to the cursor and SELECT statement is the query that returns rows to the cursor active set.

Open the cursor

Syntax: OPEN <cursor_nam>;

Where, <cursor_name> is the name of the previously defined cursor.

Fetch rows from the cursor

Syntax: FETCH <cursor_name> INTO <record_list>;

Where <cursor_name> refers to the name of the previously defined cursor from which rows are being fetched.

<record_list> represents the list of variables that will receive the data being fetched.

Closing the cursor

Syntax: CLOSE <cursor_name>;

Where <cursor_name> is the name of the cursor being closed.

2) Implicit cursors: When any SQL statement is executed, PL/SQL automatically creates a cursor without defining such cursors are known as implicit cursors.

For following statements, PL/SQL employs implicit cursors

INSERT

UPDATE

DELETE

SELECT ( queries that return exactly one row)

Question #8) When do we use triggers?

Answer: The word ‘Trigger’ means to activate. In PL/SQL, the trigger is a stored procedure that defines an action taken by the database when database related event is performed. Triggers are mainly required for the following purposes

To maintain complex integrity constraints

Auditing table information by recording the changes

Signaling other program actions when changes are made to table

Enforcing complex business rules

Preventing invalid transactions

Question #9) Explain the difference in execution of triggers and stored procedures?

Answer: A stored procedure is executed explicitly by issuing procedure call statement from another block via a procedure call with arguments.

The trigger is executed implicitly whenever any triggering event like the occurrence of DML statements happens.

Question #10) Explain the difference between Triggers and Constraints?

Answer: Triggers are different from constraints in the following ways

Triggers Constraints

Only affect those rows added after the trigger is enabled. Affect all rows of the table including that already exist when the constraint is enabled.

Triggers are used to implement complex business rules which cannot be implemented using integrity constraints. Constraints maintain the integrity of the database.

Question #11) What is a PL/SQL block?

Answer: In PL/SQL, statements are grouped into units called Blocks. PL/SQL blocks can include constants, variables, SQL statements, loops, conditional statements, exception handling. Blocks can also build a procedure, a function or a package.

Broadly, PL/SQL blocks are two types

1) Anonymous blocks: PL/SQL blocks without header are known as anonymous blocks. These blocks do not form the body of a procedure, function or triggers.

Example:

DECLARE

num NUMBER(2);

sq NUMBER(3);

BEGIN

num:= &Number1;

sq := num*num;

DBMS_OUTPUT.PUT_LINE(‘Square:’ ||sq);

END;

2) Named blocks: PL/SQL blocks having header or labels are known as Named blocks. Named blocks can either be subprograms (procedures, functions, packages) or Triggers.

Example:

FUNCTION sqr (num IN NUMBER)

RETURN NUMBER is sq NUMBER(2);

BEGIN

sq:= num*num;

RETURN sq;

END;

Question #12) Differentiate between syntax and runtime errors?

Answer: Syntax errors are the one which can be easily identified by a PL/SQL compiler. These errors can be the spelling mistake, etc.

Runtime errors are those errors in PL/SQL block for which exception handling section is to be included for handling the errors. These errors can be SELECT INTO statement which does not return any rows.

Question #13) What are COMMIT, ROLLBACK, and SAVEPOINT?

Answer: COMMIT, SAVEPOINT, and ROLLBACK are three transaction specifications available in PL/SQL.

COMMIT statement: When DML operation is performed, it only manipulates data in database buffer and the database remains unaffected by these changes. To save/store these transaction changes to the database, we need to COMMIT the transaction. COMMIT transaction saves all outstanding changes since the last COMMIT and the following process happens

Affected rows locks are released

Transaction marked as complete

Transaction detail is stored in the data dictionary.

Syntax: COMMIT;

ROLLBACK statement: When we want to undo or erase all the changes that have occurred in the current transaction so far, we require rolling back of the transaction. In other words, ROLLBACK erases all outstanding changes since the last COMMIT or ROLLBACK.

Syntax to rollback a transaction fully

ROLLBACK;

SAVEPOINT statement: The SAVEPOINT statement gives a name and marks a point in the processing of the current transaction. The changes and locks that have occurred before the SAVEPOINT in the transaction are preserved while those that occur after the SAVEPOINT are released.

Syntax:

SAVEPOINT <savepoint_name>;

Question #14) What is the mutating table and constraining table?

Answer: A table which is currently being modified by a DML statement like defining triggers in a table is known as a Mutating table.

A table that might need to be read from for a referential integrity constraint is known as constraining table.

Question #15) What are actual parameters and formal parameters?

Answer: The variables or an expression referred to as parameters that appear in the procedure call statement is known as Actual parameters.

For example: raise_sal(emp_num, merit+ amount);

Here in the above example, emp_num and amount are the two actual parameters.

The variables that are declared in the procedure header and are referenced in the procedure body are called as Formal parameters.

For example:

PROCEDURE raise_sal( emp_id INTEGER) IS

curr_sal REAL:

………..

BEGIN

SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;

…….

END raise_sal;

Here in the above example, emp_id acts as a formal parameter.

Question #16) What is the difference between ROLLBACK and ROLLBACK TO statements?

Answer: The transaction is completely ended after ROLLBACK statement i.e. ROLLBACK command completely undoes a transaction and release all locks.

On the other hand, a transaction is still active and running after ROLLBACK TO command as it undoes a part of the transaction up till the given SAVEPOINT.

Question #17) Write a PL/SQL script to display the following series of numbers: 99,96,93……9,6,3?

Answer

SET SERVER OUTPUT ON

DECLARE

BEGIN

FOR i IN REVERSE 1..99

LOOP

IF Mod(i,3) = 0 THEN

DBMS_OUTPUT.PUT_LINE(i);

END IF;

END LOOP;

END;

Question #18) What are the 3 modes of parameter?

Answer: 3 modes of the parameter are IN, OUT, IN OUT. These can be explained as follows

IN parameters: IN parameters allow you to pass values to the procedure being called and can be initialized to default values. IN parameters acts like a constant and cannot be assigned any value.

OUT parameters: OUT parameters return value to the caller and they must be specified. OUT parameters act like an uninitialized variable and cannot be used in an expression.

IN OUT parameters: IN OUT parameters passes initial values to a procedure and returns updated values to the caller. IN OUT parameters act like an initialized variable and should be assigned a value.

Question #19) Why is %ISOPEN always false for an implicit cursor?

Answer: An implicit cursor, SQL%ISOPEN attribute is always false because the implicit cursor is opened for a DML statement and is closed immediately after the execution of DML statement.

Question #20) When a DML statement is executed, in which cursor attributes, the outcome of the statement is saved?

Answer: The outcome of the statement is saved in 4 cursor attributes. These are

SQL%FOUND

SQL%NOTFOUND

SQL%ROWCOUNT

SQL%ISOPEN

Question #21) What are the ways on commenting in a PL/SQL code?

Answer: Comments are the text which is included with the code to enhance readability and for the understanding of the reader. These codes are never executed. There are two ways to comment in PL/SQL

1) Single line comment: This comment starts with double –.

Example:

DECLARE

num NUMBER(2); — it is a local variable.

BEGIN

2) Multi-line comment: This comment starts with /* and ends with */.

Example:

BEGIN

num := &p_num; /* This is a host variable used in program body */

……….

END

Question #22) What do you understand by Exception handling in PL/SQL?

Answer: When an error occurs in PL/SQL, the exception is raised. In other words, to handle undesired situations where PL/SQL scripts terminated unexpectedly, an error handling code is included in the program. In PL/SQL, all exception handling code is placed in EXCEPTION section.

There are 3 types of EXCEPTION:

Predefined Exceptions: Common errors with predefined names.

Undefined Exceptions: Less common errors with no predefined names.

User-defined Exceptions: Do not cause runtime error but violate business rules.

Question #23) Enlist some predefined exceptions?

Answer: Some of the predefined exceptions are

NO_DATA_FOUND: Single row SELECT statement where no data is returned.

TOO_MANY_ROWS: Single row SELECT statement where more than one rows are returned.

INVALID_CURSOR: Illegal cursor operation occurred.

ZERO_DIVIDE: Attempted to divide by zero.

Question #24) What are PL/SQL cursor exceptions?

Answer: The exceptions related to PL/SQL cursors are

CURSOR_ALREADY_OPEN

INVALID_CURSOR

Question #25) Explain the difference between cursor declared in procedures and cursors declared in the package specification?

Answer: The cursor declared in the procedure is treated as local and thus cannot be accessed by other procedures.

The cursor declared in the package specification is treated as global and thus can be accessed by other procedures.

Question #26) What are INSTEAD of triggers?

Answer: The INSTEAD OF triggers are the triggers written especially for modifying views, which cannot be directly modified through SQL DML statements.

Question #27) What are expressions?

Answer: Expressions are represented by a sequence of literals and variables that are separated by operators. In PL/SQL, operations are used to manipulate, compare and calculate some data. An expression is a composition of ‘Operators’ and ‘Operands’.

Operands: These are an argument to the operators. Operands can be a variable, function call or constant.

Operators: These specify the actions to be performed on operators. E.g. ‘+’, ‘*’, etc.

Question #28) List different type of expressions with the example.

Answer: Expressions can be as mentioned below

Numeric or Arithmetic expressions : e.g. 20* 10+ 15

Boolean expressions: e.g. ‘spot’ LIKE ‘sp%t’

String expressions: e.g. LENGTH (‘NEW YORK’|| ‘NY’)

Date expressions: e.g. SYSDATE>TO_DATE(’15-NOV-16’, “dd-mm-yy”)

Question #29) Write a program that shows the usage of WHILE loop to calculate the average of user entered numbers and entry of more numbers are stopped by entering number 0?

Answer

DECLARE

n NUMBER;

avg NUMBER :=0 ;

sum NUMBER :=0 ;

count NUMBER :=0 ;

BEGIN

n := &enter_a_number;

WHILE(n<>0)

LOOP

count := count+1;

sum := sum+n;

n := &enter_a_number;

END LOOP;

avg := sum/count;

DBMS_OUTPUT.PUT_LINE(‘the average is’||avg);

END;

Question #30) What do you understand by PL/SQL Records?

Answer: A PL/SQL records can be referred as a collection of values or say, a group of multiple pieces of information, each of which is of simpler types and can be related to one another as fields.

There are three types of records supported in PL/SQL

Table based records

Programmer based records

Cursor based records

1.What is cursor ?

Answer :

Cursor is a buffer area which is used to process multiple records and also record by record tabs.

There are 2 types of cursors :

1.Implicit cursor

2.Explicit cursor

Implicit cursor : Implicit cursor is a buffer area which has been defined and controlled by oracle internally. Implicit cursor will process single record at a time.

example :

declare

v_Ename varchar2(100);

begin

select ename into V_Ename from Employee where empno=101;

dbms_output.put_line(V_Ename );

end;

The above cursor is implicit cursor where all the operations are defined by oracle engine internally like declaring the cursor,fetching values from the cursor and close cursor.

Explicit Cursor : Explicit cursor is a cursor which is defined by user to process set of records.For multiple records user needs to use explicit cursor.Explicit cursor operations are done by the user.

There are following 4 operations needs to be done by user :

1.Declare cursor

2.Open cursor

3.Fetch all records from the cursor

4.Close cursor.

2.What are different cursor attributes?

Answer :

There are following cursor attributes :

1.%Found

2.%Not Found

3.%Isopen

4.%Rowcount

3.What is ref cursor?Why it is used?

Answer :

As the name suggested ref cursor is a variable which will point to the address or reference of the cursor.Ref cursor is variable not cursor but that variable points to cursor.

There are 2 type of ref cursors :

1.Strong Ref cursor

2.Weak Ref cursor

4.What is %ROWTYPE ? Explain this with example.

Answer :

%ROWTYPE is cursor attribute which is used to define the record of the field.Each field assumes it own datatype and %ROWTYPE is used to define the specific record type.

example :

CREATE OR REPLACE PROCEDURE P_Employee_Information

CURSOR Emp_Cur IS SELECT Employee_name, Employee_Number FROM emp;

variable1 Emp_Cur %ROWTYPE; ---This is cursor variable name

BEGIN

OPEN Emp_Cur ;

LOOP

FETCH Emp_Cur INTO variable1;

EXIT WHEN Emp_Cur %Notfound; ---When cursor

DBMS_OUTPUT.PUT_LINE( variable1.Employee_name || ' works in department '

|| myvar.Employee_Number);

END LOOP;

CLOSE Emp_Cur ;

END;

5.How to write Cursor with for loop?

Answer:

Cursor declares %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.Means while using the for loop user dont need to Open the cursor and fetch the values from cursor or close cursor explicitly.

In For loop all cursor operations done implicitly..

Real Example:

FOR Sample_cursor IN C1 LOOP

Total_Salary=Total_Salary + Appraisals;

END LOOP;

6.What is Database Trigger?What is real use of trigger?

Answer :

PL SQL procedure which is used to trigger specific event on specific condition is known as database triggers. Triggers are database objects with specific conditions.

Examples of Trigger :

1)Audit data modifications.

2)Log events transparently.

3)Enforce complex business rules.

4)Maintain replica tables

5)Derive column values

6)Implement Complex security authorizations.

7.What is Raise_Application_Error?

Answer :

When user wants to insert Error message then user needs to use the Raise_Application_Error procedure. Raise_Application_Error is the system defined procedure of package named DBMS_STANDARD.

Syntax :

Raise_Application_Error(Error_Code,Error_Message);

Example :

Raise_Application_Error (-20343, ‘The balance is too low.’);

8.What is commit?RollBack?Savepoint?

Answer :

Commit :

When user commits the data after transaction that changes are permanent changes.

1.Other users can see the data changes made by the transaction.

2.The locks acquired by the transaction are released.

3.The work done by the transaction becomes permanent.

Rollback :

When transaction become wrong user can rollback the data.

1.The work done in a transition is undone as if it was never issued.

2.All locks acquired by transaction are released.

Savepoint :

It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.

9.What is mutating error?(90% asked PL SQL Interview Questions)

Answer :

It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.

10.What is mean by Unique Indexes?(90% asked PL SQL Interview Questions)

Answer:

1.To create unique index you must have CREATE ANY INDEX privilege.Here the concept is bit different.User needs to check the values of the table to create unique index.If table contains uniquely identified values in specified column then you should use unique index.

2.Especially while creating the table if we specify the primary key then unique index is automatically created on that column.

3.But for Unique key constaint columns you separately need to do indexing.Kindly make sure that Unique key indexes created on the columns which has unique values only.

4.The unique indexes are also called as clustered indexes when primary key is defined on the column.

Example:

Create Unique index Index_name on Table_name(Unique column name);

Example:

CREATE UNIQUE INDEX UI1_EMP on EMP(EMP_ID);

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

11.How many triggers can be applied on one table?

Answer :

There are maximum 12 triggers can be applied on one table.

12.What is Bit-map index?Explain with Example.(80 % Asked in PL SQL Interview Questions)

Answer:

1.If Table contains the distinct values which are not more than 20 distinct values then user should go for Bit map indexes.

2.User should avoid the indexing on each and every row and do the indexing only on distinct records of the table column.You should able to check drastic change in query cost after changing the normal index to Bit map index.

3.The bit map indexes are very much useful in dataware housing where there are low level of concurrent transactions.Bit map index stores row_id as associated key value with bitmap and did the indexing only distinct values.

4.Means If in 1 million records only 20 distinct values are there so Bitmap index only stores 20 values as bitmap and fetches the records from that 20 values only.

Syntax:

Create bitmap index Index_name on Table_name(Columns which have distinct values);

Example:

CREATE BITMAP index BM_DEPT_NAME on DEPT(Department_name);

12. What are different cursor attributes?

Answer :

%ISOPEN :

Checks if the cursor is open or not

%ROWCOUNT :

The number of rows that are updated, deleted or fetched.

%FOUND :

Checks if the cursor has fetched any row. It is true if rows are fetched

%NOT FOUND :

Checks if the cursor has fetched any row. It is True if rows are not fetched.

13.What is sequences in PL SQL?

Answer :

Sequences are used to generate sequence numbers without an overhead of locking. Its drawback is that the sequence number is lost if the transaction is rolled back.

14.What is database link used for?

Answer :

Database links are used to communicate between 2 databases.Database links are created in order to form communication between various databases, or different environments like test, development and production.When user wants to insert or update data from one environment to another environment then database links are used.

15.What is difference between Anonymous block and subprogram?

Answer :

Anonymous block :

Anonymous blocks are programs or unnamed block which is used to check some functionality and which are not stored in database.

Subprograms :

Subprograms are stored blocks which are stored in to database. Subprograms are compiled at runtime.

16.What are types of exceptions in PL SQL?

Answer :

There are 2 types of exceptions in PL SQL :

1.Predefined Exceptions :

These exceptions are system defined exceptions.

2.User defined Exceptions:

User defined exceptions are exceptions which are defined by the user in the program.

17.Give us example of Predefined Exceptions?

Answer :

DUP_VAL_ON_INDEX

ZERO_DIVIDE

NO_DATA_FOUND

TOO_MANY_ROWS

CURSOR_ALREADY_OPEN

INVALID_NUMBER

INVALID_CURSOR

PROGRAM_ERROR

TIMEOUT _ON_RESOURCE

STORAGE_ERROR

LOGON_DENIED

VALUE_ERROR

18.What operators deals with null?

Answer :

NVL converts NULL to another specified value.

var:=NVL(var2,’Hi’);

IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.

19.What is cost based optimizer?

Answer:

Cost Based Optimizer (CBO) uses the artificial intelligence to execute the Query. The Optimizer itself decides the execution plan based on the cost of query. The cost based method means the database must decide which query execution plan to choose. It decides the query execution plan by deciding the statistical information based on the database objects.(tables, indexes and clusters).The Oracle cost based optimizer is designed to determine the most effective way to carry out the SQL statement.

“Cost based optimizer considers the statistical information of the table for query execution”

20.What is difference between Decode and Case?

Answer :

DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.

select decode(Total_salary=50000,’high’,40000,’medium’) as “Decode Test” from Employee where Employee_number in (1,2,3,4,5,6);

This statement will return an error.

CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.

What is PL SQL?

PL SQL is a Block Structured programming language created by Oracle in the 1990s in hoping to provide additional procedural programming solutions to SQL.

What are the key differences between SQL and PL SQL?

SQL is a Structured Query Language as opposed to P/L SQL which is a full procedural language. SQL code is processed one statement block at a time, while P/L SQL code is executed as a single program at one time. SQL can be within P/L SQL, but P/L SQL cannot be within SQL.

What is the basic structure of PL/SQL?

PL SQL, as much as any other procedural language, contains blocks. These blocks which are the basic unit of sensible code are primarily categorized by two types: anonymous blocks and named blocks.

[DECLARE]

Declaration statements;

BEGIN

Execution statements;

[EXCEPTION]

Exception handling statements;

END;

They are called anonymous because they have no names and are not saved in an Oracle Database.

The figure below shows a detailed PL SQL block structure:

PL SQL Program Structure

Header Section is for naming/labeling named blocks. This may or may not be used.

Declaration Section is for declaring variables, cursors, or sub-blocks that will be used in the Execution Section and Exception Section. This may or may not be used.

Execution Section block is where runtime code is placed. Statements in this section are required to exist for the structure to run.

Exception Section contains the exception and error handling of the code.

Essential keywords such as IS, BEGIN, EXCEPTION, and END are vital in the program for the runtime engine to distinguish each block sections.

What are triggers and its uses?

Triggers are blocks of code which are run whenever the criteria for a specific event is satisfied. They are hardcoded within the PL SQL program and listens to events such as: DML(database manipulation), DDL(database definition), and database operation. They can be coded within a view, table, database, or scheme for which the mentioned event belongs.

There are many uses of triggers. They can be used to generate column values upon activating. For event logging within the table activities, auditing, and creating table duplicates. For security, they can implement security authorization, and handle invalid transactions.

General Structure of creating a Trigger:

CREATE [OR REPLACE ] TRIGGER triggerName

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF colName]

ON tableName

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

DECLARE

Declaration-statements

BEGIN

Executable-statements

EXCEPTION

Exception-handling-statements

END;

How is a PL/SQL code compiled?

Firstly, PL/SQL code is transferred to the server and is compiled to byte code. This process takes place prior to program execution. To increase the performance of the procedural code, the procedures are converted to native code shared libraries which are conveniently linked to the kernel. Note that increase in performance still greatly depends on the code structure. This is independent to database calls, and affects performance only on loops, calculations, etc.

What are few of the schema objects that are created using PL/SQL?

A schema is a user-owned set of schema objects, or logical data structures. These schema objects types are as follows:

Clusters

Database links

Database triggers

Dimensions

External procedure libraries

Indexes and index types

Java classes, Java resources, and Java sources

Materialized views and materialized view logs

Object tables, object types, and object views

Operators

Sequences

Stored functions, procedures, and packages

Synonyms

Tables and index-organized tables

Views

Among other objects which are not contained in a schema are:

Contexts

Directories

Profiles

Roles

Tablespaces

Users

Rollback segments

Define Commit, Rollback and Savepoint.

The COMMIT Statement finalizes to end your transaction and sets all changes to permanent. A transaction in SQL is any of statements that the Oracle Database treats as a single block. This also enables users to see the updates and changes made by the transaction. Finally, the COMMIT statement deletes all the savepoints prior to the transaction and releases transaction locks.

The ROLLBACK statement undoes the changes that the transaction has made. This is practically the opposite of the COMMIT Statement. Also, any locks made due to the transaction are released.

In conjunction, the SAVEPOINT statement is also used to set a restoration point when the ROLLBACK Statement is used. This limits the bounds of the ROLLBACK Statement by only reverting to the SAVEPOINT set point.

What are the different datatypes available in PL/SQL?

PL SQL data types can be broadly divided into following categories. There are many data types available in PL SQL but mostly you will be using some of the popular ones.

Numbers – INT, INTEGER, FLOAT, NUMBER, SMALLINT, REAL etc.

Character or String – CHAR, CHARACTER, RAW, VARCHAR, VARCHAR2, NCHAR, NVARCHAR2 etc.

Boolean – BOOLEAN

Date Time – DATE, TIMESTAMP etc.

Refer them in detail at oracle Database Documentation.

What are %TYPE and %ROWTYPE for?

The %ROWTYPE allows the coder to indirectly represent a full or partial row of a database table or view, whereas the %TYPE allows for the coder to indirectly represent the data type from a previously declared variable or column. Basically, %ROWTYPE works on a full object whereas %TYPE works on a single column. The advantage to using either of these enables the coder to maintain data type declarations without ever having to know or change the data type for the items that use these. Below is an example of how the %TYPE allows for a layer of abstraction between names; allowing the coder to just change the first occurrence of the data type.

DECLARE

name VARCHAR(50);

firstName name%TYPE;

lastName name%TYPE;

province name%TYPE;

nationality name%TYPE;

emp employees_table%ROWTYPE;

BEGIN

Execution section;

END;

What is an exception in PL/SQL? What are the two types of exceptions?

Exceptions are manageable errors in a program. This means that errors handled by exceptions are within the bounds of the programmer to repair and PL/SQL provides catch features to encapsulate these errors to enable debugging and preventing the program to stop working.

There are two main types of exceptions – System Exceptions and User-Defined Exceptions. System Exceptions are such as no_data_found or too_many_rows and already defined by PL SQL. User-Defined Exceptions are exceptions defined by the user to handle particular errors.

How are functions and procedures called in PL/SQL?

For Procedures:

CALL <procedure name> to call it directly

EXECUTE <procedure name> from calling environment

<Procedure name> from other procedures or functions or packages

Functions are called directly from other programs or procedures, no additional keyword is required.

Cite the differences between execution of triggers and stored procedures?

Triggers are activated automatically if the criteria for activation is met. This requires virtually no input or action from the user. On the other hand, a stored procedure requires to be explicitly called for it to be activated.

What is the cause of mutating table error and how can we solve it?

This error occurs when an activated trigger causes a change or update in currently used table row. This is fixed using views or temp tables instead of the actual table being used so that the database can provide use for the table row, while updating/changing the other.

What are the different types of Constraints?

Some of the popular constraints are:

Check

Not NULL

Primary key

Unique

Foreign Key

Why does %ISOPEN return false for an implicit cursor?

Implicit cursors: SQL%ISOPEN always returns FALSE, indicating that the implicit cursor has been closed.

What are the different parts of a package?

(1) Package Specification – it holds the global package declaration

(2) Package Body – holds the functions, procedures (along with its local declarations), and cursor declarations.

What are the differences between Implicit and Explicit Cursors?

Explicit cursors are cursors created by the programmer to improve the manipulation of the context area. It is declared within the Declaration Section of the PL/SQL Block using the SELECT Statement. For example:

CURSOR cursorName [ parameter ] [ RETURN returnType ]

IS SELECT STATEMENT;

On the other hand, Implicit Cursors are automatically generated by Oracle if there is no existing explicit cursor for the statement. This happens at the instant the SQL statement is ran. The developer has no control over the content and data within implicit cursors.

Why is there a need for SQLCODE and SQLERRM variables?

SQLCODE AND SQLERRM are globally-defined variables that handles the error description whenever a statement invokes an exception block. They are important due to their capability to trace exceptions that are highlighted by the OTHERS handler. The difference between the two is that SQLEERM returns the exact error message of the recently raised error, while SQLCODE returns the error code number of the last encountered error.

What does the argument [OR REPLACE] do?

The [OR REPLACE] argument grants the developer to reinvent an existing trigger. This means that with [OR REPLACE] it is possible to edit the specifics of a trigger without removing it.

What is error ORA-12154: TNS:could not resolve the connect identifier specified?

This error normally occurs when trying to establish a connection with your database. This is due to typographical error in database naming, rending the Oracle unable to know what database you wish to connect to. This is a very popular error as this is one of the headscratchers faced by developers when trying to connect to a database. This maybe also an issue with the connection string within your tnsnames.ora file. Be sure to check that the tnsnames.ora is accessible and has the service name that you are currently using. But most commonly, syntax and typographical errors are the reason behind this error.

Define Overloaded Procedure

An overloaded procedure is a feature in PL/SQL that enables the developer to re-use and existing procedure with the exact name by varying the parameter structure such as the data type and parameter number. This is synonymous to the overloaded methods/functions in fundamental programming. This promotes uniformity and versatility of a PL/SQL block by giving a specific name procedure multiple ways to be called and multiple circumstances to operate.

An overloaded procedure is nothing more than a mechanism that allows the coder to reuse the same procedure name for different subprograms inside a PL/SQL block by varying the number of parameters or the parameter data type. Below is an example of where the same subprogram (callit) is reused but the data type for the input parameter is changed from INTEGER to VARCHAR2; Oracle is smart enough to know the input parameter type and call the proper subprogram.

SQL>

DECLARE

PROCEDURE ovlprocedure (num1 INTEGER) IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Parameter is an INTEGER');

END ovlprocedure;

PROCEDURE ovlprocedure (character VARCHAR2) IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Parameter is a VARCHAR2');

END ovlprocedure;

BEGIN

ovlprocedure (99);

ovlprocedure ('Hello');

* END;

SQL> /

Parameter is an INTEGER

Parameter is a VARCHAR2

PL/SQL procedure successfully completed.

What are Integrity Rules?

The Entity Integrity Rule(EIR) states that the Primary key must have value always or not Null.

The Foreign Key Integrity Rule(FKIR) states that if there exists a database relationship between a foreign key and a primary key, the Master Table cannot be deleted WHEN THERE IS DATA EXISTING IN THE CHILD TABLE.

The Business Integrity Rules encompasses the underlying processing not included in FKIR and EIR.

How can we make an IF Statement within a SELECT Statement?

We make use of the DECODE keyword. For example,

e.g. select DECODE (EMP_CAT,’3?,’Third’,’4?,’Fourth’Null);

What is Normalization?

Normalization is a neat feature where redundant tables and duplicate attributes are purposely removed to optimize the logical layout of the structure makes it easier to manage. This also improves data retrieval and nowadays, more and more servers implement normalized databases because of improvisations.

a) 1 Normal Form : This states that the database doesn’t have duplicate attributes.

b) 2 Normal Form: This states that all the candidate keys are linked on the primary key. Problems related to 2 Normal Form occurs when a multiple columned primary key exists.

c) 3rd Normal Form : This states that if transitive dependency doesn’t occur in a table, it is of 3rd Normal Form

What is error ORA-01000: maximum open cursors exceeded

There are two common scenarios in which this error might occur:

(1) When the value for OPEN_CURSORS is very low compared to the demand of cursors in your program. It is possible to increase the value of OPEN_CURSORS but discouraged, as it doesn’t identify the problem in your program and only allocates more resources than it should, which is very inefficient and wasteful for a developer.

(2) A more recommended approach is considering your program and search for problems that are concerning cursors. Take note of the implicit and explicit cursors as they also take up the available cursors available. Common issues of developers having this problem is they failed to close their cursor that is inside a loop, which in turn creates the error.

What is error ORA-03113: end-of-file on communication channel?

This is a common error which developers tend to overthink. A rather no-brainer cause for this is that the physical connection between the client’s end and the database server might have been severed. Check your connection and make sure the cable properly connected to its intended port. Also make sure to check the status of the server if it is still functioning, some developers report an issue in their server that causes this error.

Most of the time, the problem lies on the physical layer and nothing more. But if you think the problem lies on the server itself, try accessing the alert log and take note of the catalog.

What is an example of translating a date into Julian Format?

We make use of the String Format: ‘J’

For example, SQL > select to_char(to_date(‘5-Dec-2017’,’dd-mon-yyyy’),’J’)

What is consistency?

Consistency simply means that each user sees the consistent view of the data.

Consider an example: there are two users A and B. A transfers money to B’s account. Here the changes are updated in A’s account (debit) but until it will be updated to B’s account (credit), till then other users can’t see the debit of A’s account. After the debit of A and credit of B, one can see the updates. That’s consistency.

What are the various restrictions imposed on View in terms of DML?

These are you are NOT ALLOWED to delete rows of Views containing: The Distinct keyword, Group Functions, The Pseudocolumn ROWNUM keyword, or a Group by Clause.

The other restriction is that you are NOT ALLOWED to change contents in a View that contains: The Distinct keyword, Group Functions, The Pseudocolumn ROWNUM keyword, a Group by Clause, or Expression-defined Columns (e.g. number_of_days * 7)

Few restrictions of DML operations on Views are:

You cannot DELETE a row if the View contains the following:

1) Group Functions

2) A Group By clause

3) The Distinct Keyword

4) The Pseudo column ROWNUM Keyword.

You cannot MODIFY data in a View if it contains the following:

1) Group Functions

2) A Group By clause

3) The Distinct Keyword

4) The Pseudo column ROWNUM Keyword.

5) Columns defined by expressions (Ex; Salary * 12)

What is PL/SQL Records?

PS/SQL Records is type of data structure that contain a set of data(can be of various types), or distinct information values that can be referenced with each other as fields. They are useful for classifying and retrieving data with common attributes or properties. With this, it is much easier to identify similar data by tracing the attributes.

PL/SQL can manage three types of records:

• Table based records

• Programmer based records

• Cursor based records

What is scope and visibility in PL/SQL?

The scope of a variable pertains to range within PL/SQL that it can be referenced. To expound, it is the block that contains the linked blocks along with the declared block.

The definition of scope and visibility for a variable is quite close with the only difference being if you have to qualify the variable. The scope of a variable refers to the region (breadth) of code where the variable can be referenced. The visibility refers to the region of code you can reference the variable without qualifying it. So, hopefully you can see, visibility is a subset of the scope and requires the variable to be qualified (told where it comes from) in order to use. An example is clearly the best option here to help explain. Consider the PL/SQL code:

SQL>

CREATE OR REPLACE PROCEDURE proceduretest IS

var1 VARCHAR2(1); -- scope of proceduretest.var1 begins

PROCEDURE prodOne

var1 VARCHAR2(1); -- scope of prodOne.var1 begins

BEGIN -- visible prodOne.var1

var1:= 'prodOne';

DBMS_OUTPUT.PUT_LINE('In procedure prodOne, var1 = ' || var1);

-- even though proceduretest.var1 is not visible it can still be qualified/referenced

DBMS_OUTPUT.PUT_LINE('In procedure prodOne, proceduretest.var1 = ' || proceduretest.var1);

END; -- scope of prodOne.var1 ends

PROCEDURE prodTwo

BEGIN -- visible proceduretest.var1

DBMS_OUTPUT.PUT_LINE('In procedure prodTwo, var1(proceduretest) = ' || var1);

DBMS_OUTPUT.PUT_LINE('In procedure prodOne, proceduretest.var1 = ' || proceduretest.var1);

END;

BEGIN -- visible proceduretest.var1

var1:='0';

DBMS_OUTPUT.PUT_LINE('In proceduretest, var1= ' || var1);

prodOne;

prodTwo;

* END; -- scope of proceduretest.var1 ends

SQL> exec proceduretest

In proceduretest, var1 = 0

In procedure prodOne, var1 = prodOne

In procedure prodOne, proceduretest.var1 = 0

In procedure prodTwo, var1 (proceduretest) = 0

In procedure prodOne, proceduretest.var1 = 0

PL/SQL procedure successfully completed.

In the sample code above, we can observe the referenced variable, var1 is identical on the procedures. We can also see that var1 is not locally declared within procedure prodTwo. In this instance, the var1 used is the one declared from proceduretest which is visible on the perspective prodTwo. You can still be able to declare it locally like prodOne as well. This code tests the scope and visibility of variable var1 within different procedures. Oracle also provides debugging log to determine the scope of your variables.

Is it possible to read/write files to-and-from PL/SQL?

By making use of the UTL_FILE package, which was introduced in Oracle 7.3, we can make our PL/SQL code write and read files to and from our computer. However, you still need to receive an access grant by a DBA user to do such an activity. This promotes security and prevent intrusive coding.

For example:

CONNECT / AS SYSDBA

CREATE OR REPLACE DIRECTORY dir AS ‘/temp’;

GRANT read, write ON DIRECTORY dir to test;

To grant the user to access to UTL_FILE:

GRANT EXECUTE ON UTL_FILE to test;

To write to a File:

DECLARE

handlerOne UTL_FILE.FILE_TYPE;

BEGIN

handlerOne := UTL_FILE.FOPEN(‘DIR’, ‘tofile’, ‘w’);

UTL_FILE.PUTF(handlerOne, ‘Write To File. \n’);

UTL_FILE.FCLOSE(handlerOne);

EXCEPTION

WHEN utl_file.invalid_path THEN

raise_application_error(-20000, ‘Path is Invalid. Check UTL_FILE_DIR’);

END;

To read from a File:

DECLARE

handlerOne UTL_FILE.FILE_TYPE;

bufferOne varchar2(4000);

BEGIN

handlerOne := UTL_FILE.FOPEN(‘DIR’, ‘tofile’, ‘r’);

UTL_FILE.GET_LINE(handlerOne, bufferOne);

Dbms_output.put_line(‘The Contents from the File are: ’ || bufferOne);

UTL_FILE.FCLOSE(handlerOne);

EXCEPTION

WHEN utl_file.invalid_path THEN

raise_application_error(-20000, ‘Path is Invalid. Check UTL_FILE_DIR’);

END;

How to Declare Fixed Length String Value In PL SQL

We can use CHAR to declare fixed length string value. Note that if the assigned string value is smaller then white spaces will be padded to end of it to create the value.

DECLARE

v1 VARCHAR2 (10) := 'PANKAJ';

v2 CHAR (10) := 'PANKAJ';

v3 CHAR (6) := 'PANKAJ';

BEGIN

IF v1 = v2

THEN

DBMS_OUTPUT.put_line ('v1 = v2');

ELSE

DBMS_OUTPUT.put_line ('v1 != v2');

END IF;

IF v1 = v3

THEN

DBMS_OUTPUT.put_line ('v1 = v3');

ELSE

DBMS_OUTPUT.put_line ('v1 != v3');

END IF;

END;

PL SQL Fixed Length String, PL SQL CHAR data type

How can we implement Rollback or Commit statement in a Trigger?

We cannot. It is not logical to put a Rollback or Commit within a Trigger because these statements impose a savepoint which affects the logical transaction processing.

How to Fix Oracle Error ORA-00942: table or view does not exist

There are two ways to check and fix this error.

Check for typo error in your program, also check if the table and views are created or not and you are connecting to correct database.

Check if your user has required permissions or not, this is also one of the reason where you can’t see the table or view.

How can we debug in PL/SQL?

We can make use of the DBMS_OUTPUT for printing breakpoint activities. We can also use DBMS_DEBUG.

Is it possible to pass an object or table to a procedure as an argument?

Using database links, we can pass an object type of a certain database into another database. By this, we can pass these as arguments and be used by another procedure that contains the appropriate parameter argument type to receive this object. For example

-- Database One: receives the table as an argument and stored to TableTest

CREATE OR REPLACE PROCEDURE receiveTable(TableTest DBMS_SQL.VARCHAR2S) IS

BEGIN

null;

END;

-- Database Two: passes the table as an argument and is hoped to be received by ‘receiveTable’

CREATE OR REPLACE PROCEDURE sendTable IS

TableTest DBMS_SQL.VARCHAR2S@DBLINK2;

BEGIN

receiveTable @DBLINK2(TableTest);

END;

How many times can we COMMIT in a loop?

It is advised to commit as least as possible especially within loops or iterative statements. This is to minimize resource over usage on instances that the loops were poorly constructed or no end keyword, resulting in a leak and infinite calls to the COMMIT block. This scenario results in a ORA-1555 error.

In declaring undo or rollback statements, having the least number COMMITS lessens the stress and code execution of these state, resulting in a faster and much more responsive rollbacks.

Also it is of good programming habit to allocate the least amount of memory resource as much as possible while keeping the same functionality of the code. This is done by minimizing the overuse of COMMIT within the code.

How do we accept inputs from user during runtime?

The ACCEPT keyword is used to receive inputs from the user. It also allows the received data to be buffered to a variable for storage.

For example:

> accept y number prompt 'Enter your desired number: '

>declare

z number;

begin

z := &y;

end;

How can we connect an Android App to an Oracle database and use the PL/SQL procedural code?

Before getting into the PL/SQL, we must create a REST API using JSON for establishing connection. You can import the Retrofit libraries along with dependencies to establish communication with your Android App. Then prepare the functions and procedures using PL/SQL, and then once your oracle database is connected you are good to go.

2. What is difference between TRUNCATE & DELETE?

1. Truncate is a DDL command

2. We can remove bulk amount of records at a time

3. We can't rollback the records

4. Release the space in database

5. Truncate reset the high water mark

6. Truncate explicitly commit

1. Delete is a DML command

2. We can delete record by record

3. We can rollback the records

4. Can’t release the memory in database

5. Delete can’t reset the water mark

6. Delete implicitly commit

(OR)

Ans: Differences:

TRUNCATE commits after deleting entire table i.e., cannot be rolled back.

Database triggers do not fire on TRUNCATE DELETE allows the filtered deletion.

Deleted records can be rolled back or committed.Database triggers fire on DELETE.

3. Difference between view and materialized view

Difference

View is a logical table

View can hold the query

We can’t create indexes on view

View will create security purpose

Mv is a physical table

Mv can hold the query with refresh data

We can create indexes on mv

Mv will create performance issues

4. Difference between procedure and function?

Procedure:

Procedure allow the DML statements without any restrictions

We can’t call procedure in sql language

We can store images in stored procedure

Function:

Function not allow the DML statements (If you need to use we can use pragma)

We can call Function in sql language

Function can’t store images

5. What is cursor?

Cursor is private sql area which is used to execute sql statements and store processing information

6. What is explicit and implicit cursor and examples?

The implicit cursor is automatically declared by oracle every time an sql statement is executed whenever you issue a sql statement, the oracle server opens an area of memory in which the command is parsed and executed. Every implicit cursor attribute start with sql%.

An explicit cursor is created and managed by the user. And used for multi row select statement.

7.What do u understand by database and what is objects in oracle

Ans: A database is defined as a collection of meaningful data. Objects in oracle means Table, Views, Procedures, Triggers, Synonym etc

8.What is a table, view, snapshot?

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.a

Views: 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.)

Snapshot: A Snapshot is a recent copy of a table from database or in some cases ,a subset of rows/columns of a table. It is also known as Materialized view.

9.Do a view contain data?

Ans: Views do not contain or store data

What are the advantages of views?

Ans: 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 perspective from that of the base table.

- Store complex queries.

10.What is an Oracle sequence?

Ans: A Sequence generates a serial list of unique numbers for numerical columns of a database's tables.

11.What is a synonym?

Ans: A synonym is an alias for a table, view, sequence or program unit.

12.What are the types of synonyms?

Ans: There are two types of synonyms private and public.

13.What is a private synonym?

Ans: Only its owner can access a private synonym.

14.What is a public synonym?

Ans: Any database user can access a public synonym

15.What is an Oracle index?

Ans: 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. Index may also be considered as a ordered list of content of a column.

16.What is a schema?

Ans: The set of objects owned by user account is called the schema.

17.What is a join? Explain the different types of joins?

Ans: Join is a query, which retrieves related columns or rows from multiple tables.

Self Join - Joining the table with itself.

Equi Join - Joining two tables by equating two common columns.

Non-Equi Join - Joining two tables by not equating two common columns.

Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

18.Difference between SUBSTR and INSTR?

Ans: INSTR (String1, String2 (n, (m)), INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.

SUBSTR (String1 n, m) SUBSTR returns a character string of size m in string1, starting from n-th position of string1.

19.What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?

Ans: CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR the maximum length is 255 and 2000 for VARCHAR2

20.How to access the current value and next value from a sequence?

Ans: Current Value : Sequence name.CURRVAL

Next Value sequence name.NEXTVAL.

21.What are the components of physical database structure of Oracle database?

Ans: Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

22.Query to delete duplicate row from a table

Ans: Delete from emp where rowid not in (Select min(rowid) from emp Groupby emp_dept)

23.What is a cursor its attribute and types?

Ans: The Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL operation and is called Cursor.

Types of Cursor:

Implicit Cursor: If the Oracle engine opened a cursor for its internal processing then it is know as implicit cursor. It is invoked implicitly.

Explicit Cursor: A cursor which is opened for processing data through a PL/SQL block is know as Explicit Cursor.

Attributes Of a Implicit Cursor:

%ISOPEN —returns TRUE if cursor is open else FALSE.

Syntax is SQL%ISOPEN

%ROWCOUNT--- returns number of records processed from cursor syntax is SQL %ROWCOUNT %FOUND---- returns TRUE if record is fetched successfully else FALSE, syntax is SQL%FOUND %NOTFOUND-- returns TRUE if record is not fetched successfully else FALSE syntax is SQL%NOTFOUND Attributes Of a Explicit Cursor %ISOPEN—returns TRUE if cursor is open else FALSE. Syntax is cursorname%ISOPEN %ROWCOUNT--- returns number of records processed from cursor syntax is cursorname %ROWCOUNT %FOUND---- returns TRUE if record is fetched successfully else FALSE, syntax is cursorname %FOUND %NOTFOUND-- returns TRUE if record is not fetched successfully else FALSE syntax is cursorname %NOTFOUND

24.What are inline views?

Ans: Inline view is Sub-query(queries written in a where clause of SQL statements.). It is a query whose return values are used in filtering conditions of the main query.

25.How can we refresh a snapshot?

Ans: Refreshing Snapshots: A snapshot can be refreshed automatically or manually. If a snapshot has to be automatically refreshed then refresh clause must be specified in the CREATE SNAPSHOT. The FAST, COMPLETE or FORCE specifies the type of REFRESH used for automatic refresh. For automatic refresh we can specify the START WITH and NEXT parameter to decide the time interval for the next update.

COMPLETE refresh: In complete refresh the snapshot query is executed and places the result in the snapshot.

FAST refresh : In this only the changes made to the master table will be updated to the snapshot. The corresponding log file is used to update. Fast refresh will be done only if * The snapshot is a simple snapshot. * The snapshot's master table has a snapshot log \ * The snapshot log was created before the snapshot was last refreshed or created.

FORCE refresh : In this ORACLE decides how to refresh the snapshot at the scheduled refresh time. If a fast refresh is possible it performs a fast refresh else it does a complete refresh.

26.What is a tablespace?

Ans: A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

27.Is sequence cyclic?

Ans: Yes

28.Select nth highest value from a list of values ?

Ans: SELECT a.emp_name,a.sal FROM emp a WHERE &n - 1= (SELECT COUNT(DISTINCT sal) FROM emp b WHERE b.sal > a.sal )

29.What are triggers and its types?

Ans: A trigger is a piece of code attached to a table that is executed after specified DML statements executed on that table. There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, STATEMENT, TABLE, INSERT, UPDATE, DELETE and ALL key words: For eg: BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT

30.What is the maximum number of triggers, can apply to a single table?

Ans: 12 triggers(Oracle).

31.Difference between rowid and rownum?

Ans: ROWID is pseudo column in every table. The physical address of the rows is use to for the ROWID.IN HEXADECIMAL representation, ROWID is shown as 18 character string of the following format BBBBBBBBB.RRRR.FFFF (block, row, file) FFFF is the fileid of the datafile that contains the row. BBBBBBBBB is the address of the datablock within the datafile that contains the row. RRRR is the ROW NUMBER with the data block that contains the row. They are unique identifiers for the any row in a table. They are internally used in the construction of indexes.

Rownum is the sequential number of rows in the result set object.

32.What is the fastest query method for a table?

Ans: By rowid

33.What is the difference of a LEFT JOIN and an INNER JOIN statement?

Ans: A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables

34.How can I avoid a divide by zero error?

Ans: Use the DECODE function. This function is absolutely brilliant and functions like a CASE statement, and can be used to return different columns based on the values of others.

35.Is view updatable?

Ans: Only if the view is a simple horizontal slice through a single table.

36.What is Dual ?

Ans: The DUAL table is a table with a single row and a single column used where a table is syntactically required.

37.What is the difference between CHAR and VARCHAR ?

Ans: CHAR is fixed length character type at storage level, and that VARCHAR will be variable length.

38.Do we use commit in triggers.

Ans: No

39.How will the fetch the last inserted record in any table ?

Ans: select column 1, column 2.... From where rowid = (select max(rowid) from table);

40.What are constraints and its types?

Integrity Constraint : An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true.

Types of integrity constraints : The following integrity constraints are supported by ORACLE:

1. NOT NULL : disallows nulls (empty entries) in a table's column

2. UNIQUE : disallows duplicate values in a column or set of columns

3. PRIMARY KEY : disallows duplicate values and nulls in a column or set of columns

4. FOREIGN KEY : requires each value in a column or set of columns match a value in a related table's UNIQUE or PRIMARY KEY.

5. CHECK : disallows values that do not satisfy the logical expression of the constrain

41.What is Referential Integrity and Referential integrity constraint ?

Ans: Referential Integrity : Referential integrity defines the relationships among different columns and tables in a relational database. It’s called referential integrity because the values in one column or set of columns refer to or must match the values in a related column or set of columns.

A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.

42.What is groups by and having clause? Explain with example

Ans: Group by clause tells oracle to group rows based on distinct values that exists for specified columns. The group by clause creates a data set , containing several sets of records grouped together based on condition.

Having Clause: Having clause can be used with GROUP BY clause. Having imposes a condition on the group by clause which further filters the group created by the GROUP BY clause. Select ename,empno From Empl Group by empno having empno > 10;

43.What are LOCKS? What are types of different types of Lock?

Ans: Locks are mechanisms intended to prevent destructive interaction between users accessing ORACLE data. ORACLE uses locks to control concurrent access to data. Locks are used to achieve two important database goals : Consistency : Ensures that the data a user is viewing or changing is not changed (by other users) until the user is finished with the data. Integrity : Ensures that the database's data and structures reflect all changes made to them in the correct sequence.

Types of Locks :

1. Data Locks (DML)

2. Dictionary Locks (DDL)

3. Internal Locks and Latches

4. Distributed Locks

5. Parallel Cache Management Locks

Data Locks : Row Level and Table Level Row Level : Exclusive Locks Table Level

1. Row Share Table Locks (RS)

2. Row Exclusive Table Locks (RX)

3. Share Table Locks (S)

4. Share Row Exclusive Table Locks (SRX)

5. Exclusive Table Locks (X)

Dictionary Locks :

1. Exclusive DDL Locks

2. Share DDL Locks

3. Breakable Parse Locks Restrictiveness of Locks : In general, two levels of locking can be used in a multi-user database: • Exclusive Locks : An exclusive lock prohibits the sharing of the associated resource. The first transaction to exclusively lock a resource is the only transaction that can alter the resource until the exclusive lock is released. • Share Locks : A share lock allows the associated resource to be shared, depending on the operations involved (e.g., several users can read the same data at the same time). Several transactions can acquire share locks on the same resource. Share locks allow a higher degree of data concurrency than exclusive locks.

44.Difference between unique key,primary key and foreign key ?

Ans: Foreign key: A foreign key is one or more columns whose values are based on the primary or candidate key values from another table. Unique key can be null; Primary key cannot be null.

45.What are Advantages of TRUNCATE Command over DELETE/DROP TABLE Command ?

Ans: The TRUNCATE command provides a fast, efficient method for deleting all rows from a table or cluster.

1. A TRUNCATE statement does not generate any rollback information and it commits immediately; it is a DDL statement and cannot be rolled back.

2. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations (grants).

3. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.

4. As a TRUNCATE statement deletes rows from a table (or clustered table), triggers associated with the table are not fired.

5. Also, a TRUNCATE statement does not generate any audit information corresponding to DELETE statements if auditing is enabled. Instead, a single audit record is generated for the TRUNCATE statement being issued.

46.What are steps involved in Execution of SQL statements?

Ans: STEPS IN EXECUTION OF SQL STATEMENTS :

1. Create a cursor

2. Parse the statement

3. Describe Results

4. Defining outputs

5. Bind any variables

6. Execute the statement

7. Fetch rows of a query result

47.What do you mean by Parsing?

Ans: Parsing : Parsing is the process of: 1. Translating a SQL statement, verifying it to be a valid statement 2. Performing data dictionary lookups to check table and column definitions 3. Acquiring parse locks on required objects so that their definitions do not change during the statement's parsing 4. Checking privileges to access referenced schema objects 5. Determining the execution plan to be used when executing the statement 6. Loading it into a shared SQL area 7. For distributed statements, routing all or part of the statement to remote nodes that contain referenced data

48.What is a HINT and what are types HINT?

Ans: Hints are suggestions that you give the optimizer for optimizing a SQL statement. Hints allow you to make decisions usually made by the optimizer.

TYPES OF HINTS :

ALL_ROWS : The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput.

FIRST_ROWS : The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time.

FULL : The FULL hint explicitly chooses a full table scan for the specified table.

ROWID : The ROWID hint explicitly chooses a table scan by ROWID for the specified table.

CLUSTER : The CLUSTER hint explicitly chooses a cluster scan to access the specified table.

HASH : The HASH hint explicitly chooses a hash scan to access the specified table.

INDEX : The INDEX hint explicitly chooses an index scan for the specified table.

AND_EQUAL: The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. (You can specify multiple indexes through this hint) INDEX_ASC: The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in ascending order of their indexed values.

INDEX_DESC: The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in descending order of their indexed values.

ORDERED : The ORDERED hint causes ORACLE to join tables in the order in which they appear in the FROM clause.

USE_NL : The USE_NL hint causes ORACLE to join each specified table to another row source with a nested loops join using the specified table as the inner table.

USE_MERGE : The USE_MERGE hint causes ORACLE to join each specified table with another row source with a sort-merge join.

49.What do u mean by EXCEPTION_INIT Pragma ?

Ans: EXCEPTION_INIT Pragma : To handle unnamed internal exceptions, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A "pragma" is a compiler directive, which can be thought of as a parenthetical remark to the compiler. Pragmas (also called "pseudoinstructions") are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler. The predefined pragma EXCEPTION_INIT tells the PL/SQL compiler to associate an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it. You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package

using the syntax PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number); where "exception_name" is the name of a previously declared exception. For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is "no data found," in which case SQLCODE returns +100. SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code. For user-defined exceptions, SQLCODE returns +1 and SQLERRM returns the message “User-Defined Exception” unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message. The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names.

What do u mean by JSP query?

Ans: JSP Query : The JSP Query is a standard query for number to words conversion, used especially for converting amount in number into equivalent amount in words. The query is as follows : Select to_char ( to_date ( ‘&no’, ‘J’ ), ‘JSP’ ) words from dual; For eg : Select to_char ( to_date ( '23949','j' ), 'JSP' ) "words" from dual; The value that can pass to &no cannot exceed 7 digits.

50.Describe Oracle database’s physical and logical structure ?

Ans: Physical: Data files, Redo Log files, Control file. Logical : Tables, Views, Tablespaces, etc.

51.What is “Check Constraints” and “with check options” and “Default Specification”?

Ans: CHECK Integrity Constraints: A CHECK integrity constraint on a column or a set of columns requires that a specified condition be true or unknown (ie. Not false) for every row of the table. If a DML statement is issued so that the condition of the CHECK constraint evaluates to false, the statement is rolled back. With check Option: With Check option restricts inserts and updates performed through the view to prevent them from creating rows that the view cannot itself select .based on where clause of the create view statement. For eg: Create or replace view Women As select name from Employee Where Sex= ‘Female’ With Check Option; Default Specification It supplies a default value if column value is not specified on INSERT It can contain literals (constants) and SQL functions, USER, SYSDATE, sequence It cannot include references to any columns.

52.What is the maximum no. Of columns a table can have ?

Ans: 254(Oracle)

53.Can a trigger written for a view ?

Ans: No

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 ?

Ans: 3

54.Can you create index on view ?

Ans: No

55.What is the difference between alias and synonym ?

Ans: Alias is temporary and used with one query. Synonym is permanent and not used as alias.

What’s the length of SQL integer ?

Ans: 32 bit length

56.What is tkprof and how is it used?

Ans: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool . This can also be used to generate explain plan output.

57.What is explain plan and how is it used?

Ans: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.

58.What is The Dynamic Performance Tables?

Ans: Throughout its operation, ORACLE maintains a set of "virtual" tables that record current database activity. These tables are called Dynamic performance tables. Because dynamic performance tables are not true tables, they should not be accessed by most users. However, database administrators can query these tables and can create views on the tables and grant access to those views to other users. The dynamic performance tables are owned by SYS and their names all begin with V_$. Views are created on these tables, and then synonyms are created for the views. The synonym names begin with V$.

59.What is Savepoint ?

Ans: Savepoints are intermediate markers that can be declared in long transactions that contain many SQL statements. By using savepoints, you can arbitrarily mark your work at any point within a long transaction. This allows you the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.

60.What is Deadlocks?

Ans: A deadlock is a situation that can occur in multi-user systems that causes some number of transactions to be unable to continue work. A deadlock can occur when two or more users are waiting for data locked by each other. It typically happens when each of two or more users are waiting to access a resource that another user has already locked. This creates a deadlock situation because each user is waiting for resources held by the other user. Eg Transaction 1 Time Point Transaction 2 UPDATE emp 1 UPDATE emp SET sal = sal*1.1 SET sal = 1342 WHERE empno = 1000; WHERE empno = 2000; UPDATE emp 2 UPDATE emp SET sal = sal*1.1 SET sal = 1342 WHERE empno = 2000; WHERE empno = 1000; ORA-00060 3 deadlock detected while waiting for resource

61.What is Privilege ?

Ans: A privilege is a right to execute a particular type of SQL statement or to access another user's object. Types of privileges : • system privileges • object privileges System Privileges : System privileges allow users to perform a particular systemwide action, or to perform a particular action on a particular type of object. E.g. Create Tablespace, Delete the row of any table, etc. Object Privileges : Object privileges allow users to perform a particular action on a specific object. E.g. Delete row of specific table, etc. Roles : Roles are named groups of related privileges that are granted to users or other roles. Advantages of Roles : 1. Reduced granting of privileges 2. Dynamic privilege management (Changing of privileges) 3. Selective availability of privileges (Enalbling/Disabling roles) 4. Application awareness (Enalbling/Disabling of roles by application)

62.What is Two Phase Commit ?

Ans: Two Phase Commit is a mechanism wherein ORACLE automatically controls and monitors the commit or rollback of a distributed transaction and maintains the integrity of the global database. The Phases of the Two-Phase Commit Mechanism :

• Prepare phase : The global co-ordinator (initiating node) asks participants to prepare (to promise to commit or rollback the transaction, even if there is a failure).

• Commit phase : If all participants respond to the co-ordinator that they are prepared, the co-ordinator asks all nodes to commit the transaction; if all participants cannot prepare, the co-ordinator asks all nodes to roll back the transaction.

63.Explain about snapshots in detail?

Ans: Snapshots are read-only copies of a master table (or multiple 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. A snapshot is a full copy of a table or a subset of a table that reflects a recent state of the master table. A snapshot is defined by a distributed query that references one or more master tables, view, or other snapshots. Simple vs. Complex Snapshots : Each row in a simple snapshot is based on a single row in a single remote table. Therefore, a simple snapshot's defining query has no GROUP BY or CONNECT BY clauses, or subqueries, joins, or set operations. If a snapshot's defining query contains any of these clauses or operations, it is referred to as a complex snapshot. Internals of Snapshot Creation: When a snapshot is created, several operations are performed internally by ORACLE: • ORACLE (at the snapshot node) creates a table to store the rows retrieved by the snapshot's defining query; this is the snapshot's base table. • ORACLE creates a read-only view on the SNAP$ table (base table) for queries issued against the snapshot. • ORACLE creates a second local view on the remote master table. It uses this view when it refreshes the snapshot. • Additionally, if the snapshot is a simple snapshot, ORACLE creates an index on the SNAP$ table. All of these internal objects are created in the schema of the snapshot. Do not alter, change data in, or delete these objects manually.

64.What is Ref Cursor?

Ans: A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

65.What is row chaining, how does it happen?

Ans: Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won’t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.

66.Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning?

Ans: The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. An instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.

67.What is a Cartesian product?

Ans: A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.

68.What is a mutating table error and how can you get around it?

Ans: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

69.What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

Ans: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

70.What are Transactional Triggers ? Give the uses of Transational Trigger ?

Ans: Transactional Triggers fire in response to transaction processing events. These events represent points during application processing at which Oracle Forms needs to interact with the data source. Examples of such events include updating records, rolling back to savepoints, and committing transactions. By default, Oracle Forms assumes that the data source is an ORACLE database, and issues the appropriate SQL statements to optimize transaction processing accordingly. However, by defining

transactional triggers and user exits, you can build a form to interact with virtually any data source, including even non-relational databases and flat files. Calling User Exits When you define transactional triggers to interact with a non-ORACLE data source, you will usually include a call to a user exit in the appropriate triggers. The code in your user exit interacts with the non-ORACLE data source. Once the user exit has performed the appropriate function (as indicated by the trigger from which it was called), it returns control to Oracle Forms for subsequent processing. For example, a user exit called from an On-Fetch trigger might be responsible for retrieving the appropriate number of records from the non-ORACLE data source. Once the records are retrieved, Oracle Forms takes over the display and management of those records in the form interface, just as it would if the records had been fetched from an ORACLE database. Uses for Transactional Triggers • Transactional triggers, except for the commit triggers, are primarily intended to access certain data sources other than Oracle. • The logon and logoff transactional triggers can also be used with Oracle databases to change connections at run time.

71.What is Autonomous transaction ? Where do we use it?

Ans: In Oracle's database products, an autonomous transaction is an independent transaction that is initiated by another transaction. It must contain at least one Structured Query Language (SQL) statement. Autonomous transactions allow a single transaction to be subdivided into multiple commit/rollback transactions, each of which will be tracked for auditing purposes. When an autonomous transaction is called, the original transaction (calling transaction) is temporarily suspended. The autonomous transaction must commit or roll back before it returns control to the calling transaction. Once changes have been made by an autonomous transaction, those changes are visible to other transactions in the database. Autonomous transactions can be nested. That is, an autonomous transaction can operate as a calling transaction, initializing other autonomous transactions within itself.

72.What is a package, procedure and function?

Ans: Package : A package is a group of related program objects stored together as a unit in the database. A package is an encapsulated collection of related program objects stored together in the database. Program objects are: procedures, functions, variables, constants, cursors, exceptions. Procedure/Function : A procedure or function is a set of SQL and PL/SQL statements grouped together as an executable unit to perform a specific task. The main difference between a procedure and function is 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.

73.What do u mean by overloading?

Ans: Function Overloading : Packages allow you to overload procedures or functions. Overloading a procedure means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.

74.What are the constructs of a procedure, function or a package ?

Ans: The constructs of a procedure, function or a package are : • variables and constants • cursors • exceptions

75.What are cascading triggers? What is the maximum no of cascading triggers at a time?

Ans: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32

76.What is the significance of the & and && operators in PL/SQL ?

Ans: 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.

77.If all the values from a cursor have been fetched and another fetch is issued, the output will be?

Ans: Last Record

78.What is a forward declaration ? What is its use ?

Ans: 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.

79.Any three PL/SQL Exceptions?

Ans: Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

80.Describe the use of %ROWTYPE and %TYPE in PL/SQL

Ans: %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.

81.How can you call a PL/SQL procedure from SQL?

Ans: By use of the EXECUTE (short form EXEC) command.

82.What are the various types of Exceptions ?

Ans: User defined and Predefined Exceptions.

83.What is RAISE_APPLICATION_ERROR ?

Ans: DBMS_STANDARD provides a procedure named raise_application_error, which lets you issue user-defined error messages. That way, you can report errors to an application and avoid returning unhandled exceptions. The calling syntax is : raise_application_error(error_number, error_message); where error_number is a negative integer in the range -20000...-20999 and error_message is a character string up to 2048 bytes in length. An application can call raise_application_error only from an executing stored subprogram. When called, raise_application_error ends the subprogram, rolls back any database changes it made, and returns a user-defined error number and message to the application. The error number and message can be trapped like any ORACLE error. The calling application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. • The statement Raise_Application_Error can be called either from a procedure body or from an exception handler. • Irrespective of whether an error occurred or not, a raise_application_error command always raises an exception in the calling program (eg a forms trigger). If an exception handler is not written in that forms trigger, then a forms error occurs.

What is SQL?

SQL is a domain-specific programming language that allows you to query and manipulate data within database management systems arranged in an optimized manner and categorization. This is possible through implementing commands in SQL that allows you to read, write, select and delete entries or even columns of the same attribute or tables. SQL also provides a very efficient way of creating a dynamic accessway between your programs, websites, or mobile apps to a database. For example, by inputting your login details on a user website, these log information is passed on to a database by SQL for verification and user restriction.

What is the difference between a Database and a Relational Database?

Database or Database Management System(DBMS) and Relational Database Management System(DBMS) are both used by SQL to store data and structures, however each types of Database Management System are preferred with respect to different uses.

The main difference between the two is that DBMS saves your information as files whereas RDMS saves your information in tabular form. Also, as the keyword Relational implies, RDMS allows different tables to have relationships with one another using Primary Keys, Foreign Keys etc. This creates a dynamic chain of hierarchy between tables which also offers helpful restriction on the tables.

DBMS sorts out its tables through a hierarchal manner or navigational manner. This is useful when it comes to storing data in tables that are independent from one another and you don’t wish to change other tables while a table is being filled or edited.

What is the Basic Structure of an SQL?

SQL is framed upon the structure of relational operations. It is based on certain modifications and enhancements.

A very basic SQL query form is:

select A1, A2, ..., An

from R1, R2, ..., Rm

where P

Here Ax are attributes, Rx are the relations within the database and P is the predicate or filter.

What are different categories of SQL commands?

SQL command falls into following four categories:

DML (Data Manipulation Language) which provides data manipulation features

DDL (Data Definition Language) which is used to manipulate database structures

TCL (Transaction Control Language) that takes in charge data transaction verification and error handling

DCL (Data Control Language) are security statements that feature user restrictions and data access permissions to promote security of your data.

What is SQL used for?

SQL is used and is popular for server-side programmers for its ability to process large number of entries in a database in a very fast and easy manner. This opens up to large improvements in data retrieval and manipulation. To expound on this, SQL provides the ability to execute, retrieve, insert, update, delete entries to and from a database. It also allows to create structures such as tables, views, and databases provided a unique name is given.

Define SELECT, INSERT, CREATE, DELETE, UPDATE, DROP keywords

SELECT keyword is used to highlight and get entries in rows from tables or views. It can also be accompanied by AS keyword to provide alias. To filter the SELECT statement, WHERE clause may be included to provide filter conditions and select only the wished entries that satisfy the condition.

INSERT allows to add or insert a row or multiple rows in a database table. Accompanied by VALUES keyword lets you add a row with specific values. INSERT may also be accompanied with SELECT to insert the preselected row.

CREATE is a keyword used to create elements in SQL. It is usually accompanied with the keyword to be created such as CREATE DATABASE, CREATE TABLE, CREATE VIEW, etc.

DELETE keyword is used to deletes record(s) in a database. You should always use it carefully to avoid unwanted data loss. You may delete records you didn’t want to delete. Use WHERE clause to specify the range of the records you with to delete.

UPDATE keyword updates or changes the existing data within an existing record. Be sure to note that the record must be existent.

DROP keyword drops or deletes a table within the database.

What are the key differences between SQL and P/L SQL?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early 90’s. It adds procedural features of programming languages in SQL.

What is data definition language?

DDL or Data Definition Language pertains to the SQL commands directly affecting the database structure. DDL is therefore a category of SQL command classifications that also include DML (Data Manipulation Language), Transactions, and Security. A particular attribute of DDL commands are statements that can manipulate indexes, objects, tables, views, triggers, etc. Three popular DDL keywords in SQL are:

CREATE – which is used to create a table

CREATE TABLE tableName (name data_type);

ALTER – used to modify entries or existing columns within a table.

ALTER TABLE tableName [additional syntax such as ADD, DROP, MODIFY]

DROP – used to Delete or Drop an existing table along with its entries, constraints, triggers, indexes and permissions. Essentially deletes the table.

DROP TABLE tableName;

What is Data Manipulation Language?

DML or Data Manipulation Language is a set of commands that are classified pertaining to its capability to give users the permission to change entries within database. This maybe through Inserting, Retrieving, Deleting or Updating data within tables. Popular DML statements arise from these core functionalities and are listed below:

SELECT – used to highlight a row within a table and retrieve it.

SELECT [columnName] FROM [tableName]

UPDATE – used to update entries from existing tables.

UPDATE [tableName] SET [value]

INSERT – used to insert entries into an existing table.

INSERT INTO [tableName]

DELETE – used to delete entries from an existing table

DELETE FROM [tableName]

What is Transaction Control Language (TCL)?

TCL is a category of SQL commands which primarily deals with the database transaction and save points. These keywords implement the SQL functions and logic defined by the developer into the database structure and behavior. Examples of these TCL commands are:

COMMIT – used to commit a transaction

ROLLBACK – in any advent of errors, transaction rollback is invoked by this keyword.

SAVEPOINT – keyword representing the reverting point of rollback

SET TRANSACTION – sets the specifics of the transaction

What is Data Control Language (DCL)?

Data Control Language or DCL oversees the issuance of access and restrictions to users, including the rights and permissions required within the SQL statements.

Example DCL keywords are:

GRANT – DCL keyword that provides access of certain databases to users.

REVOKE – opposite of the GRANT keyword. Revokes or withdraws the privileges given to the user.

Define tables and fields in a database

In terms of databases, a table is referred to an arrangement of organized entries. It is further divided into cells which contains different fields of the table row.

A field pertains to a data structure that represents a single piece of entry. They are then further organized to records. They practically hold a single piece of data. They are the basic unit of memory allocation for data and is accessible.

What are different types of keys in SQL?

Keys are a vital feature in RDMS, they are essentially fields that link one table to another and promote fast data retrieval and logging through managing column indexes.

Different types of keys are:

Primary Key – a unique key that identifies records in database tables. By unique it means that it must not be Null and must be unique in label.

Candidate Key – a unique field which identifies for column or group of columns independently, without any required reference to other fields.

Alternate Key – can be substituted in use for Primary Keys but are considered as a secondary. The difference is that Alternate Keys can have a Null value, provided that the columns has data within them. A type of Candidate Key which is also required to be unique.

Unique Key – Keys that offer restriction to prevent duplicate data within rows except null entries.

The other keys available are: Foreign Keys, Super Keys, and Composite Keys.

Name the different types of indexes in SQL and define them.

Unique Index: Prevents duplicate entries within uniquely indexed columns. They are automatically generated if a Primary Key is available.

Clustered Index: Used to organize or edit the arrangement within the table, with respect to the key value. Each table is only allowed to have a single clustered index only.

NonClustered Index: Conversely, NonClustered Index only manages the order of logic within entries. It does not manage the arrangement and tables can have multiple NonClustered Indexes.

What is the difference between SQL and MySQL?

SQL which stands for Standard Query Language is a server programming language that provides interaction to database fields and columns. While MySQL is a type of Database Management System, not an actual programming language, more specifically a RDMS or Relational Database Management System. However, MySQL also implements the SQL syntax.

What is UNION and UNION ALL keyword in SQL and what are their differences?

The UNION operator in SQL combines multiple sets highlighted in the SELECT statements. The restrictions of the set are: (1) column number must be identical, (2) Data Types in the set must be the same, and (3) the order of the column highlighted in the SELECT statement must be the same. It automatically eliminates duplicate rows within the results highlighted in the SELECT statement.

UNION ALL does the same function as the UNION, but it includes all, including the duplicate rows.

SELECT C1, C2 FROM T1

UNION

SELECT Cx, Cy FROM T2;

What are the different types of joins in SQL?

The join keyword queries entries from multiple tables. It is used with different keys to find these entries and is conscious on the link between fields.

Inner Join: Returns rows which are common between the tables

Right Join: Returns rows of the right-hand side table, including the common rows.

Left Join: Returns rows of the left-hand side table, including the common rows.

Full Join: Returns all rows, regardless if common or not.

What is Normalization and Denormalization?

Normalization arranges the existing tables and its fields within the database, resulting in minimum duplication. It is used to simplify a table as much as possible while retaining the unique fields.

Denormalization allows the retrieval of fields from all normal forms within a database. With respect to normalization, it does the opposite and puts redundancies into the table.

When can we use the WHERE clause and the HAVING clause?

Both clauses accept conditions that are used for basis on retrieving fields. The difference is that WHERE clause is only used for static non-aggregated columns while the HAVING clause is used for aggregated columns only.

select order_id, SUM(sale_amount) as TotalSale

from SalesData

where quantity>1

group by order_id

having SUM(sale_amount) > 100;

What is the difference among UNION, MINUS and INTERSECT?

The UNION keyword is used in SQL for combining multiple tables but deletes duplicates from the result set.

The INTERSECT keyword is only used for retrieving common rows between multiple tables.

The MINUS keyword essentially subtracts between two tables. Where any common rows which also exist on the latter table is deleted from the former table.

How to select 10 records from a table?

MySQL: Using limit clause, example select * from Employee limit 10;

Oracle: Using ROWNUM clause, example SELECT * FROM Employee WHERE ROWNUM < 10;

SQL Server: Using TOP clause, example SELECT TOP 3 * FROM Employee;

How can you maintain the integrity of your database on instances where deleting an element in a table results in the deletion of element(s) within another table?

This is possible by invoking an SQL trigger which listens for any elements that are deleted in Table A and deletes the corresponding linked elements on the Table B.

What is the process of copying data from Table A to Table B?

INSERT INTO TableB (columnOne, columnTwo, columnThree, ...)

SELECT columnOne, columnTwo, columnThree, ...

FROM TableA

WHERE added_condtion;

What are the differences between IN and EXISTS clause?

The apparent difference between the two is that the EXISTS keyword is relatively faster at execution compared to IN keyword. This is because the IN keyword must search all existing records while EXISTS keywords automatically stops when a matching record has been found.

Also, IN Statement operates within the ResultSet while EXISTS keyword operates on virtual tables. In this context, the IN Statement also does not operate on queries that associates with Virtual tables while the EXISTS keyword is used on linked queries.

What does the acronym ACID stand for in Database Management?

The ACID Acronym stands for Atomicity, Consistency, Isolation, and Durability. This property primarily takes charge of the process integrity of the database system. This means that whatever the user issues as a data transaction to the database must be done completely, accurately, and has withstanding property.

What is a trigger in SQL?

Database trigger is a programs that automatically executes in response to some event on a table or view such as insert/update/delete of a record. Mainly, database trigger helps us to maintain the integrity of the database.

What is Auto Increment feature in SQL?

Auto increment allows the user to create a unique number to be generated whenever a new record is inserted in the table. AUTO INCREMENT is the keyword for Oracle, AUTO_INCREMENT in MySQL and IDENTITY keyword can be used in SQL SERVER for auto incrementing. Mostly this keyword is used to create primary key for the table.

What is collation?

Collation is basically a set of rules on how to compare and sort characters, extended to strings. Collation in MSSQL an MySQL works pretty much the same way, except on certain collation options such as UTF-8. Besides the normal character-wise comparison, collation can also sort and compare strings on an ASCII representation perspective.

What is a recursive stored procedure?

A stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps programmers to use the same set of code any number of times.

Which query operators in SQL is used for pattern matching?

The answer is the LIKE operator.

LIKE operator is used for pattern matching, and it can be used as -.

% – Matches zero or more characters.

_(Underscore) – Matching exactly one character.

What is Hibernate and its relation to SQL?

Hibernate is Object Relational Mapping tool in Java. Hibernate let's us write object oriented code and internally converts them to native sql queries to execute against a relational database.

Hibernate uses its own language like SQL which is called Hibernate Query Language(HQL). The difference is that HQL boasts on being able to query Hibernate’s entity objects.

It also has an object-oriented query language in Hibernate which is called Criteria Query. It proves very beneficial and helpful to developers who primarily uses objects in their front-end applications and Criteria Query can cater those objects in even add SQL-like features such as security and restriction-access.

How can we solve SQL Error: ORA-00904: invalid identifier?

This error usually appears due to syntax errors on calling a column name in Oracle database, notice the ORA identifier in the error code. Make sure you typed in the correct column name. Also, take special note on the aliases as they are the one being referenced in the error as the invalid identifier.

What is a SQL Profiler?

The SQL Profiler is a Graphical User Interface that allows database developers to monitor and track their database engine activities. It features activity logging for every event occurring and provides analysis for malfunctions and discrepancies.

It basically is a diagnostic feature in SQL that debugs performance issues and provides a more versatile way of seeing which part in your trace file is causing a clog in your SQL transactions.

How can we link a SQL database to an existing Android App?

It will require a JDBC (Java Database Connectivity) driver to link these two. Also, you must add the corresponding dependencies to your build.gradle file along with the permissions and grants.

Thursday, August 23, 2018

Wednesday, August 22, 2018

Tuesday, August 21, 2018

Wednesday, August 8, 2018

ctrl-enter : executes the current statement(s)

F5 : executes the current code as a script (think SQL*Plus)

ctrl-space : invokes code insight on demand

CODE EDITOR – COMPLETION INSIGHT – ENABLE COMPLETION AUTO-POPUP (KEYWORD BEING AUTO)

ctrl-Up/Dn : replaces worksheet with previous/next SQL from SQL History

ctrl-shift+Up/Dn : same as above but appends instead of replaces

shift+F4 : opens a Describe window for current object at cursor

ctrl+F7 : format SQL

ctrl+/ : toggles line commenting

ctrl+e : incremental search

Tuesday, August 7, 2018

%TYPE

%ROWTYPE

Figure 1-3 %ROWTYPE Record

Modularity

Subprograms

Packages

Data Abstraction

Collections

Records

Error Handling

Advantages of PL/SQL

Support for SQL

Support for Object-Oriented Programming

Better Performance

Figure 1-5 PL/SQL Boosts Performance

Higher Productivity

Full Portability

Tight Integration with SQL

Tight Security

Equijoins

Self Joins

Inner Joins

Outer Joins

Antijoins

Semijoins