PL SQL Interview Questions

You are right place, If you are looking for oracle interview questions and answers, get more confidence to crack interview by reading this questions and answers we will update more and more latest questions for you…

1. What is Oracle?

Oracle is a relational database management system, which Establishes data in the form of tables. Oracle makes skillful use of all system resources, on all hardware architecture, to deliver Incomparable performance, price performance, and scalability.

Related Article - What is Oracle?

2. What is Oracle table?

A table is the vital unit of data storage in an Oracle database. The tables of a database hold all of the user Available data. Table data is stored in rows and columns. Tables are the Entire unit of data storage in an Oracle Database. Data is stored in rows and columns. To determine a table with a table name, such as employees, and a set of columns. A row is a collection of column information Related to a single record.

3. What is Oracle View? To Write the Syntax

In Oracle, the 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.) It is stored in the Oracle data dictionary and does not store any data. It can be executed when called. A view is generated by a query joining one or Major tables.

Syntax:

CREATE VIEW view name AS  

SELECT columns

FROM tables  

WHERE conditions;

4. How many memory layers are in the Oracle shared pool?

Oracles shared pool Contains of two layers.They are

1. Library cache
2. Data dictionary cache.

Library cache: This layer has information about SQL statements that were parsed, information about cursors and any plan data.

Data Dictionary cache: this Layer has information about the accounts of the users, their Advantage and segments information

5. What is PL/SQL?

PL/SQL Determines for Procedural Language extension of Structured Query Language (SQL). It is a block-structured language having true/false blocks which are made up of 3 sub-blocks i.e. a declarative Segment, an executable Segment, and an exception building Segment. PL/SQL is combined with Oracle and the functionalities of PL/SQL extend after each release of Oracle database.

It combines procedural language elements like conditions and loops and allows declaration of constants and variables, procedures and functions. It also helps the users to develop difficult database applications using control structures, procedures, modules, etc. PL/SQL is not finite by any case-sensitive letters so you are free to use lower case letters or upper case letters.

6. What is The Structure of PL /SQL?

The vital structure of PL/SQL is the BLOCK structure. Each program of PL/SQL Consist Of both the SQL statement and the PL/SQL statement, which eventually forms the PL/SQL block. Every PL/SQL block has 3  defined sections out of which two sections are optional i.e. the declaration section and the exception handling section and one more section is mandatory i.e. the execution section.

Related Article - Oracle SQL and PL SQL way to success

7. Explain Difference Between The Triggers and Constraints?

Constraints will do memory location to table Contrast whereas triggers will do table to table comparison. For this triggers will use magic tables(inserted, deleted).In the order of procession first Constraints next Triggers, But performance wise triggers will give the best performance because the table to table comparison is faster than memory location to table comparison.

8. Explain Difference Between Truncate and Delete?

TRUNCATE:

TRUNCATE SQL query deletes all rows from a table, without logging the individual row deletions. It is a DDL command is executed using a table lock and the entire table is locked to remove all records. We cannot use WHERE clause with TRUNCATE. It deletes all rows from a table. To use Truncate on a table you need at least ALTER acceptance on the table.

TRUNCATE TABLE Customers;

DELETE:

To execute a DELETE queue, delete acceptance is required on the target table. If you need to use a WHERE clause in a DELETE, select permissions are required as well. It is a DML command. It is completed using a row lock, each row in the table is locked for deletion. We can use where clause with DELETE to filter & delete particular records. The DELETE command is used to delete rows from a table based on WHERE condition. It maintains the log, so it slower than truncates. The DELETE statement deletes rows one at a time and records an entry in the transaction log for each deleted row. Identification of column keep DELETE retains the identity. To use Delete you need DELETE acceptance on the table

DELETE FROM Customers;

9. What is the difference between DBMS and RDBMS?

A database management system (DBMS) consists of a collection of interrelated data and a set of programs to access that data. A Relational Database Management System (RDBMS) is a DBMS that is relational in the name. This means that the Inner l workings access data in a relational manner. Oracle is an RDBMS.

Get the best pl sql training from onlineitguru and complete certification also.

10. Define Schema? What is schema objects?

A schema is a Group of objects associated with the database.

Schema objects are abstractions or logical structures that Assign to database objects or structures. Schema objects are found of such things as clusters, indexes Hold Data, packages, sequences, stored procedures, synonyms, tables, views, and so on...

Note:  It is no relationship between a table space and a schema.

11. What is SQL*Plus?

SQL*Plus is the ad-hoc user coherence tool for the Oracle RDBMS. With SQL*Plus, you can Attach into the RDBMS and run SQL commands and PL SQL programs. This is the Main no application interface into the Oracle RDBMS. SQL*Plus is actually an interactive query tool, with some Programming capabilities. It is a non-GUI, character-based tool that has been Entire since the dawn of the Oracle age. Using SQL*Plus, you can Get in an SQL statement, such as a SELECT query, and view the results. You can also execute Data Definition Language (DDL) commands that allow you to Manage and modify your database. You can even enter and execute PL/SQL code.

12. What is the Oracle Enterprise Manager?

The Oracle Enterprise Manager is the Advance graphical administration tool designed to help the DBA manage one or more Oracle systems. , In case their data centers or in the Oracle Cloud. Through Inner integration with Oracle’s product stack, Enterprise Manager Gives market-leading and maintenance management and automation support for Oracle applications, databases, middle ware, hardware, and engineered systems.

13. Difference between the Syntax and Run Time Errors?

The errors such as spelling mistakes come below syntax errors, which can easily be found using a PL/SQL compiler. As far as Run time error is concerned, they are found in the PL/SQL block. You need to add an exception handling section to handle those errors. Such errors come under SELECT INTO statements that rebound no rows.

14. What do you understand by Redo Log file mirroring?

Mirroring is a procedure of having a copy of Redo log files. It is done by Build group of log files together. This Establish that LGWR automatically writes them to all the members of the current online redo log group. If the group suddenly fails, the database automatically switches over to the next group. It diminishes the performance.

15. Define constraints? Different Types of Constraints?

The constraint is a rule or restriction concerning a piece of data that is enforced at the data level rather than the object or application level. A constraint clause can constraint a single column or collection of columns in a table.

 There are five types of constraints namely are:

• Null / Not Null
• Primary Key
• Unique
• Check or Validation
• Foreign key or References key

16. Define foreign key?

A foreign key is a Collection of columns with value is based on the primary key values from another table. A foreign key constraint, is known as referential integrity constraint. Foreign key identifies the column collection of columns in the child table that makes up of the foreign key.

17. What is system global area (SGA)?

The SGA is a shared memory region that Oracle uses to Reserve data and control information for one Oracle instance. The SGA is allocated when the Oracle instance begins: it is deal located when the Oracle instance shuts down.  Each Oracle instance that begins lias its own SGA. The information in the SGA is made up of the database buffers, the redo log buffer, and the shared pool: each has a fixed size and is created at instance started

18. What is a Table space?

A database is Split into Logical Storage Unit called table space. A table space is used to grouped related logical structures stable

19. What Are the Pl /SQL packages?

PL/SQL packages are schema objects that groups logically relevant functions stored procedures, cursors, and variables at one place. The package is compiled and stored in a database and its contents can be shared. Packages have 2 parts: a specification and a body.

20. The Compare between the ROLLBACK and ROLLBACK TO Statements?

There is a considerable Compare between the ROLLBACK and ROLLBACK TO statement. When you apply the ROLLBACK command a transaction is ending undone where all the blocks are released. As far as ROLLBACK TO command is concerned, the transaction is undone but till a SAVEPOINT. Thus, the transaction remains real and active even after the command is implemented.

21. What are The Some Predefined Exceptions in PL/SQL?

    Here is a list of some predefined exceptions that can be used in PL/SQL:

• DUP_VAL_ON_INDEX
• NO_DATA_FOUND
• CURSOR_ALREADY_OPEN
• INVALID_NUMBER
• INVALID_CURSOR
• TIMEOUT _ON_RESOURCE
• LOGON_DENIED
• ZERO_DIVIDE
• TOO_MANY_ROWS
• PROGRAM_ERROR
• STORAGE_ERROR
• VALUE_ERROR

22. Why Trigger Is Used?

Triggers are used to define an action when database related events are performed. It is used for preventing invalid transactions, enforcing complex business rules, etc. Triggers mean activating an action.

23. What is Mutating Table and Constraining Table?

A mutating table is the one, which is modified using a DML statement or a table with defined triggers. A constraining table is the one, which is being read for a referential integrity constraint.

24. What are the various types of queries?

The types of queries are:

• Normal Queries
• Sub Queries
• Co-related queries
• Nested queries
• Compound
• queries

25. What is Join?

Join Bringing to gather columns and data from two or more tables. Join is a query in which data is retrieved from 2 or more table. A join matches data from 2 or more tables, based on the values of one or more columns in Exclusive table.

26. What is a stored procedure?

A stored procedure is a Set of SQL statements that are pre-parsed and stored in the database. When the stored procedure is Appeal, only the input and output data is passed; the SQL statements are not transferred or parsed.

27. What Is a Save point Command?

A The SAVE POINT command is utilized to set a point within a transaction to which you may rollback. This command helps in cancelling the portions of the current transaction. Using ROLLBACK with the SAVE POINT TO clause, a transaction can be undone in parts rather than rolling back the entire transaction. What Is a Save point Command?

28. How Can You Generate Debugging Output from Pl/SQL?

Use the DBMS_OUTPUT package. One More possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be utilized to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used

[contact-form-7 id="5350" title="Post insertion"]

29. What Is The Use Of Cascade Constraints?

When this Section is used with the DROP command, a parent table can be dropped even when a child table exists

30. Explain An Ora-01555?

You get this error when you get a Print too old within rollback. It can usually be solved by expanding the undo retention or increasing the size of rollbacks. You should also look at the logic Tangled in the application getting the error message.

------Best of Luck-------