41. Explain the difference between a data block, an extent, and a segment?
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
42. What is NULL value in Oracle?
NULL value represents unknown or missing data. It is used as a place holder or represented as a default entry indicating that no actual data is present.
43. What does coalescing a tablespace do?
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.
44. Explain materialized views and how they are used?
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
45. Compare and contrast TRUNCATE and DELETE for a table?
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
46. What is the difference between Varchar2 and Varchar?
Varchar2 Datatype is memory efficient as it variable memory storage datatype whereas a Varchar Datatype variable is not memory efficient as it has fixed memory storage. Varchar occupies space for NULL values whereas Varchar2 variable does not. Varchar can store up to 2KB whereas a Varchar2 Datatype can store up to 4KB.
47. What is the basic difference between a Procedure and a Function?
You can use ROWID to fetch Row from the table. The use of ROW ID is the fastest query method for fetching data from the table.
48. Enlist various loops in PL/SQL Database?
The various loops used in PL/SQL are as follows:
1. Simple Loop
2. For Loop
3. Nested Loop
4. While Loop
49. Describe the disadvantage of Database Trigger over Stored Procedures?
We cannot control the execution of a Trigger whereas a Stored Procedure Execution can be controlled by the programmer
50. What is the basic difference between a Procedure and a Function?
Both Procedures and Functions have the capability of accessing parameters but Functions return a value to the PL/SQL Block whereas Procedures do not return any value.
51. What is PL/SQL, Why do we need PL/SQL instead of SQL?
• PL/SQL is a procedural language extension with SQL Language.
• Oracle introduced PL/SQL
• It is a combination of SQL and Procedural Statements and used for creating applications.
• Basically, PL/SQL is a block-structured 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.
• Explain your current and previous projects along with your roles and responsibilities, mention some of the challenging difficulties you’ve faced in your project while working with PL/SQL.
52) What are the structure of PL/SQL block?
Declare
declarations
Begin
executable statements
Exception
exception handlers
End;
53) Write a PL/SQL Program that raises 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
;
54) What is a Mutating Trigger?
• Into a row-level trigger based on a table, the trigger body cannot read data from the same table and also we cannot perform DML operation on the same table.
• If we are trying this oracle server returns mutating error oracle-4091: table is mutating.
• This error is called a mutating error, and this trigger is called a mutating trigger, and the table is called a mutating table.
• Mutating errors are not occurred in statement-level trigger because through this statement-level trigger when we are performing DML operations automatically data committed into the database, whereas in the 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 occurred.
55) What are The Types of Ref Cursors?
In all databases having 2 ref cursors.
1. Strong ref cursor
2. Weak ref cursor
A strong ref cursor is a ref cursor that has a return type, whereas a 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 the Weak ref cursor, we must specify a select statement by using open for clause this clause is used in the executable section of the PL/SQL block.
Syntax:
Open ref cursor varname for SELECT * FROM table_name condition;
56) What are Overloading Procedures?
Overload refers to the same name that can be used for a different purpose, in oracle we can also implement an overloading procedure through the package. Overloading procedure having the same name with different types or different numbers of parameters.
57) What are the Global Variables?
In oracle, we are declaring global variables in Package Specification only.
58) What is Forward Declaration?
In oracle declaring procedures within the 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.
59) What are the benefits of PL/SQL Packages?
The benefits of PL/SQL Packages are.
• A package is used for storing the functions and procedures in a single unit.
• Packages provide security to grant privileges.
• A package consisting of the functions and procedures shares a common variable among them.
• Packages support even if the functions are overloaded.
• Packages enhance the performance even when the multiple objects are loaded into memory.
60) What are the characteristics of PL/SQL?
• Multiple applications in PL/SQL allow accessibility and sharing the same subprograms.
• It is portable as the code can be executed on any operating system provided that Oracle is loaded on it.
• PL/SQL allows the users to write their own customized error handling routines.
• Improved transaction performance with integration to Oracle data dictionary.
