Question:- Tell me a few important views used in Oracle you have learned?
Answer:- These are the few commands that can be used to view in oracle • V$Parameter • V$Database • V$Instance • V$Datafiles • V$controlfiles • V$logfiles
Question:- Compare SQL and PL/SQL.
Answer:- SQL • A single query or command execution • A data source for reports, web pages, etc. • Declarative in nature • Manipulating data PL/SQL • A full programming language • An application language to build, format, and display reports, web pages, etc. • Procedural in nature • Creating applications
Question:- What is PL/SQL?
Answer:- Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration and conditional branching.
Question:- What is the Basic Structure of PL/SQL?
Answer:- PL/SQL uses a block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
Question:- What is a Trigger and what are its uses?
Answer:- A trigger is a database object that automatically executes in response to some events on the tables or views. It is used to apply the integrity constraint to database objects. A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for: • Audit data modifications • Log events transparently • Enforce complex business rules • Maintain replica tables • Derive column values • Implement complex security authorizations Any constant, variable, or parameter has a data type depending on which the storage constraints, format, and range of values and operations are determined.
Question:- What Data Types are Present in PL/SQL?
Answer:- There are various kinds of data types present in PL/SQL. They are: 1. Scalar: The scalar data type is a one-dimensional data type with no internal components. CHAR, DATE, LONG, VARCHAR2, NUMBER, and BOOLEAN are some examples of the scalar data type. 2. Composite: The composite data type is made up of different data types that are easy to update and have internal components that can be utilized and modified together. For instance, RECORD, TABLE, VARRAY, and so on. 3. Reference: The reference data type stores pointers, which are values that relate to other programs or data elements. REF CURSOR is an example of the reference data type. 4. Large Object: The large object data type stores locators, which define the location of large items stored out of line such as video clips, graphic images, and so on. BLOB, BFILE, CLOB, and NCLOB are examples of the large object data type.
Question:- What are the Basic Parts of a Trigger?
Answer:- There are three basic parts of a trigger. They are: • A triggering statement or event • A restriction • An action
Question:- How is the Process of PL/SQL Compiled?
Answer:- Syntax checking, binding, and P-code generation are all part of the compilation process. Syntax checking looks for compilation issues in PL/SQL code. After all mistakes have been fixed, the data holding variables are given a storage address. This process is referred to as binding. The PL/SQL engine’s P-code is a set of instructions. For named blocks, P-code is saved in the database and used the next time it is run.
Question:- What is a Join?
Answer:- A join is a query that combines rows from two or more tables, views, or materialized views. A join is performed by the Oracle Database whenever there are multiple tables in the FROM clause of the query. Most of these queries contain at least one join condition, either in the FROM or WHERE clause.
Question:- What is a View?
Answer:- A view is created by joining one or more tables. It is a virtual table that is based on the result set of an SQL statement; it contains rows and columns, just like a real table. A view can be created with the CREATE VIEW statement.
Question:- What Does a PL/SQL Package Consist Of?
Answer:- A PL/SQL package consists of: • PL/SQL table and record type statements • Procedures and functions • Cursors • Variables, such as tables, scalars, records, etc., and constants • Exception names and pragmas for relating an error number with an exception • Cursors
Question:- What do you know about the Commands COMMIT, ROLLBACK, and SAVEPOINT?
Answer:- COMMIT: The COMMIT command saves changes to a database permanently during the current transaction. ROLLBACK: The ROLLBACK command is used at the end of a transaction to undo any modifications made since the start of the transaction. SAVEPOINT: The SAVEPOINT command saves the current point with a unique name during the processing of a transaction.
Question:- What are the Benefits of PL/SQL Packages?
Answer:- PL/SQL packages provide several benefits. Some of them are as follows: • Enforced Information Hiding: It offers the liberty to choose whether to keep the data private or public. • Top-down Design: We can design the interface to the code hidden in the package before we actually implement the modules. • Object Persistence: Objects declared in a package specification behave like global data for all PL/SQL objects in the application. We can modify the package in one module and then reference those changes in another • Object-oriented Design: The package gives developers a stronghold over how the modules and data structures inside the package can be used. • Guaranteeing Transaction Integrity: It provides a certain level of transaction integrity. • Performance Improvement: The RDBMS automatically tracks the validity of all program objects stored in the database. It also enhances the performance of packages.
Question:- What is Exception Handling?
Answer:- Exception handling is a mechanism that is implemented to deal with runtime errors. It can be adjusted in PL/SQL. PL/SQL provides the exception block that raises the exception, thus helping the programmer to find the fault and resolve it. When an error occurs, the program’s error handling code is included. There are two different types of exceptions defined in PL/SQL: • User-defined exception • System-defined exception