Oracle SQL/PLSQL Interview Questions with Answers

What are joins and Types of join?

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

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

OUTER JOIN
There are three different Outer Join methods.
LEFT OUTER JOIN
This join returns all the rows from the left table with the matching rows from the right table. If there are no field matching in the right table then it returns NULL values
RIGHT OUTER JOIN
Right outer join returns all the rows from the right table with the matching rows from the left table. If there are no field matching in the left table then it returns NULL values
FULL OUTER JOIN
Full outer join merge left outer join and right outer join.  this returns row from either table when the conditions are met and returns null value when there is no match
CROSS JOIN
Corss join is  does not necessary any condition to join. The output result contains records that are multiplication of record  from both the tables.

What is DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right)  based on the requirement.

WHAT ARE SET OPERATORS?
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.

What are different datatypes supported by sql in oracle?
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data types for character values, Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values, Date data type for date values, Long, Raw (size),  Long Raw,  Clob, Blob, Nclob, Bfile for large objects.

What is difference between long and lob datatypes?
Ans:LOB
1) The maximum size is 4GB. 2) LOBs (except NCLOB) can be attributes of an object type. 3) LOBs support random access to data. 4) Multiple LOB columns per table or LOB attributes in an object type.
LONG
1) The maximum size is 2GB.  2) LONGs cannot.    3) LONGs support only sequential access. 4) Only one LONG column was allowed in a table

How much memory is allocated for date datatype? What is default date format in oracle?
Ans: For Date data type oracle allocates 7 bytes Memory.   Default Date Format is: DD-MON-YY.

What is range for each datatype of sql?
Ans: Datatype Range Char  Varchar2  Number    Float     LONG, RAW, LONGRAW  Large Objects (LOB’s) 2000 bytes  4000 bytes  Precision
1 to 38 Scale -84 to 127  Precision 38 decimals Or 122 binary precision   2 GB  4GB

What is a constraint? What are its various levels?
Ans: Constraint: Constraints are representators of the column to enforce data entity and consistency.There r two levels
1)Column-level constraints 2)Table-level constraints.

List out all the constraints supported by oracle
Primary Key , Foreign Key or Referential Integrity, Not Null, Unique, Check.

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

 Difference between Primary key and Unique Key

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

 

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

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

Oracle Conversion Functions-
To_char
To_date
To_number

What is syntax of PL/SQL BLOCK
Ans. DECLARE
 <declarations>
 BEGIN
    <Exececutable Statements>
 EXCEPTION
    <Exception Handler(s)>
 END;

 

What are different types of oracle PL/SQL BLOCKS?

Ans:

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

 

 what is a Oracle PL/SQL cursor? and how to create cursor syntax?
Ans: Cursor is Private SQL area in PL/SQL.
     Declare the Cursor,
     Open the Cursor,
     Fetch values from SQL into the local Variables,
     Close the Cursor.
Type of cursors are supported by oracle pl/sql?
Ans.  There are two types of cursors namely Implicit Cursor, Explicit Cursor.

What is a cursor for loop?
Ans: Cursor For Loop is shortcut process for Explicit Cursors because the Cursor is Open, Rows are fetched once for each iteration and the cursor is closed automatically when all the rows have been processed.

What are cursor attributes?
Ans:  %Found, %NotFound,   %IsOpen, %RowCount are the cursor attributes.

 Use of cursor with “for update of” clause?
Ans: This Clause stop accessing of other users on the particular columns used by the cursor until the COMMIT is issued.

How Exception is different from error?
Ans: Whenever an error occurs Exception raises. Error is a bug whereas the Exception is a warning or error condition.

PL/SQL Interview Questions - New Questions Set 1

Q1. I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:

• Grant direct access on the tables to your user. Do not use roles!

GRANT select ON scott.emp TO my_user;

• Define your procedures with invoker rights (Oracle 8i and higher);

create or replace procedure proc1
authid current_user is
begin
...

• Move all the tables to one user/schema.

Q2. What is a mutating and constraining table?
“Mutating” means “changing”. A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered “mutating” and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:

• A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger).
• A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.

Q3. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.


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


Q5. What is the difference between %TYPE and %ROWTYPE?
Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database without having to make any code changes.
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

• %TYPE is used to declare a field with the same type as that of a specified table’s column.
• %ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor.

Q6. What Oracle Packages that are commonly available for PL/SQL developers?
Oracle provides many DBMS_ series of packages, such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.


Q7. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
SQLCODE – returns the value of the error number for the last error encountered.
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.


Q8. What is PL/SQL table?
PL/SQL tables are PL/SQL’s way of providing arrays. Arrays are like temporary tables in memory and thus are processed very quickly. It is important for you to realize that they are not database tables, and DML statements cannot be issued against them. This type of table is indexed by a binary integer counter (it cannot be indexed by another type of number) whose value can be referenced using the number of the index.
Remember that PL/SQL tables exist in memory only, and therefore don’t exist in any persistent way, disappearing after the session ends.


Q9. What is a Cursor?
Cursors are work areas used for internal processing in order to execute an SQL statement. In other word, it is a point to context area which has information for Oracle to process SQL statements. It allows fetching rows returned by a select statement.
There are two types of Cursors.

• Implicit Cursor which occurs on behalf of every SQL statement and it is declared automatically.
• Explicit Cursor is a Named Cursor used in PL SQL which is defined by programmer.

Q10. What is the difference between an explicit cursor and select into statement?
When a cursor name is explicitly assigned to a SELECT statement through CURSOR statement it is called an explicit cursor. Explicit cursors are generally used to work with more than one row within PL/SQL. It has got the following attributes:
1. Defining the Cursor
2. Opening the Cursor
3. Fetching rows(values) from Cursor (one at a time)
4. Closing the Cursor
SELECT into statement allows you to retrives the query result in user defined variables. But the limitation here is that the query must return a single row. Its a best practice to handle NO_DATA_FOUND and TOO_MANY_ROWS exception when dealing with “SELECT INTO” statement.

Advantages of PL/SQL

These are the advantages of PL/SQL.

• Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
•  Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
•  Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
• Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

Advantages of PL/SQL

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

• Tight Integration with SQL
  
• Better Performance
  
• Higher Productivity
  
• Full Portability
  
• Tight Security
  
• Access to Pre-defined Packages
  
• Support for Object-Oriented Programming
  
• Support for Developing Web Applications and Pages
  

Tight Integration with 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.

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. This extensive SQL support lets you manipulate Oracle data flexibly and safely.

Also, PL/SQL fully supports SQL datatypes, reducing the need to convert data passed between your applications and the database.
The PL/SQL language is tightly integrated with SQL. You do not have to translate between SQL and PL/SQL datatypes; a NUMBER or VARCHAR2 column in the database is stored in a NUMBER or VARCHAR2 variable in PL/SQL. This integration saves you both learning time and processing time. Special PL/SQL language features let you work with table columns and rows without specifying the datatypes, saving on maintenance work when the table definitions change.
Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages. 

Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation. Many database features, such as triggers and object types, make use of PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.
PL/SQL supports both static and dynamic SQL. The syntax of static SQL statements is known at precompile time and the preparation of the static SQL occurs before runtime, where as the syntax of dynamic SQL statements is not known until runtime.  

Dynamic SQL is a 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 at run time, without knowing details such as table names and WHERE clauses in advance. For information on the use of static SQL with PL/SQL, see Chapter 6, "Performing SQL Operations from PL/SQL". For information on the use of dynamic SQL, see Chapter 7, "Performing SQL Operations with Native Dynamic SQL". For additional information about dynamic SQL, see Oracle Database Application Developer's Guide - Fundamentals.

Better Performance

Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead.

With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application. As Figure 1-1 shows, you can use PL/SQL blocks and subprograms to group SQL statements before sending them to the database for execution. PL/SQL also has language features to further speed up SQL statements that are issued inside a loop.

PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient. Because stored procedures execute in the database server, a single call over the network can start a large job. This division of work reduces network traffic and improves response times. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.

Figure 1-1 PL/SQL Boosts Performance

Description of the illustration lnpls005.gif

Higher Productivity

PL/SQL lets you write very compact code for manipulating data. In the same way that scripting languages such as Perl can read, transform, and write data from files, PL/SQL can query, transform, and update data in a database. PL/SQL saves time on design and debugging by offering a full range of software-engineering features, such as exception handling, encapsulation, data hiding, and object-oriented datatypes.

PL/SQL extends tools such as Oracle Forms. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits. PL/SQL is the same in all environments. After you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.

Full Portability

Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.

Tight Security

PL/SQL stored procedures move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself or to the text of the UPDATE statement. Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.
For information on wrapping, or hiding, the source of a PL/SQL unit, see Appendix A, "Obfuscating PL/SQL Source Code".

Access to Pre-defined Packages

Oracle provides product-specific packages that define APIs you can call from PL/SQL to perform many useful tasks. These packages include DBMS_ALERT for using database triggers, DBMS_FILE for reading and writing operating system (OS) text files, DBMS_HTTP for making hypertext transfer protocol (HTTP) callouts, DBMS_OUTPUT for display output from PL/SQL blocks and subprograms, and DBMS_PIPE for communicating over named pipes. For additional information on these packages, see "Overview of Product-Specific Packages".
For complete information on the packages supplied by Oracle, see Oracle Database PL/SQL Packages and Types Reference.

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. See Chapter 12, "Using PL/SQL With Object Types".

In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. This direct mapping helps your programs better reflect the world they are trying to simulate. For information on object types, see Oracle Database Application Developer's Guide - Object-Relational Features.