21. What is Forward Declaration?
In oracle declaring procedures within package body are called forward declaring generally before we are calling private procedures into public procedure first we must implement private procedure within body otherwise use a forward declaration within the package body.
22. What is the difference between FUNCTION, PROCEDURE, AND PACKAGE in PL/SQL?
Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.
Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.
Package: A package is schema object which groups logically related PL/SQL types, items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.
23. Which is the Default Cursor in Oracle PL/SQL?
Implicit Cursors are the Default Cursor in PL/SQL. These cursors are automatically activated when DML statements are encountered such as Insert, Delete or Update.
24. Is there a PL/SQL Engine in SQL*Plus?
Oracle Forms, SQL*Plus does not have an SQL Engine. Thus, all your PL/SQL are sent directly to the database engine for execution. This makes it comparatively more efficient as SQL statements are not uncovered and sent to the database individually.
25. What are the different parts of an Explicit Cursor?
The different parts in the process of making an Explicit Cursor are as follows:
1. Declaring the Cursor
2. Opening the Cursor
3. Fetching the Cursor
4. Closing the Cursor
26. Explain the difference between Truncate and Delete?
Truncate is much faster than Delete Command. It basically resets the Memory Blocks after Execution. Delete is a Database Manipulation Language (DML) Command whereas Truncate is a Data Definition Language (DDL) Command and it is comparatively slower.
27. Explain the difference between Varchar and Char?
Varchar doesn’t set aside memory location during the declaration of a variable. It stores the value only after a variable is defined or assigned a value. Its storage capacity is 32767 Bytes. Char, however, preserves the memory location mentioned in the variable declaration even if it is not used. The maximum storage capacity for a Character variable is 255 Bytes.
28. Explain Union, Union All, Intersect and Minus in PL/SQL?
Union: It returns all the distinct rows selected by either of the Queries.
Union All: It returns all the rows selected by one of the queries which include all the duplicates.
Intersect: It returns all the distinct rows selected by both the queries.
Minus: It returns all the distinct rows selected by the first query and not by the second one.
29. What is a mutating table error and how can you get around it?
Level: Intermediate Expected 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.
30. How can you generate debugging output from PL/SQL?
Expected to Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used 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.
31. 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
32. What is a Dual Table?
The 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.
33. What is the use of Double Ampersand (&&) in SQL Queries? Give an example?
Use “&&” if you want to reuse the variable value without prompting the user each time.
Forex: Select empno, name, &&column_name from employee order by &column_name;
34. 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.
35. 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 the column.
The system crashes or Server gets down.
The session Killed.
Locking takes place. Etc.
36. 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 the column level.
There are 5 types of constraints:
• Not Null Constraint
• Unique Key Constraint
• Primary Key Constraint
• Foreign Key Constraint
• Check Key Constraint.
37. What is the utilization of interfaces?
An interface takes after a class in which none of the strategies have been executed—the system marks are there, yet the body of each methodology is void. To use an interface, another class must realize it by giving a body to most of the procedures contained in the interface. Interfaces can give a layer of reflection to your code. They isolate the specific execution of a system from the declaration for that procedure. In this manner, you can have unmistakable use of a
methodology in perspective of your particular application.
38. 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 : A table can have more than one Unique Key. Unique key column can store NULL values. Uniquely identify each value in a column. Uniquely identify each row in a table. (Oracle plsql Interview Questions)
Primary Key : A table can have only one Primary Key. The primary key column cannot store NULL values.
39. What are the different functionalities of a Trigger?
Trigger is also same as stored procedure & also it will automatically be invoked whenever DML operation performed against table or view.
40. 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 the trigger body. These are inserting, updating, deleting clauses. These clauses are used in the 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;
