PL/SQL Interview Questions Part 1
1.) What is PL/SQL?
PL/SQL stands for procedural linguistic communication extension to SQL. It supports procedural features of programming linguistic communication as well as SQL both. It was developed past times Oracle Corporation inward early on of 90's to parent the capabilities of SQL.
2.) What is PL/SQL table? Why it is used?
Objects of type tables are called PL/SQL tables that are modeled equally database table. We tin mail away likewise say that PL/SQL tables are a way to providing arrays. Arrays are similar temporary tables inward retentiveness that are processed really quickly. PL/SQL tables are used to movement volume data. They simplifies moving collections of data.
3.) Mention what PL/SQL bundle consists of?
A PL/SQL bundle consists of
• PL/SQL tabular array as well as tape TYPE statements
• Procedures as well as Functions
• Cursors
• Variables ( tables, scalars, records, etc.) as well as constants
• Exception names as well as pragmas for relating an fault number amongst an exception
• Cursors
4.) Mention what are the benefits of PL/SQL packages?
It provides several benefits like
• Enforced Information Hiding: It OFFERS the freedom to select whether to proceed information private or public
• Top-down design: You tin mail away pattern the interface to the code hidden inward the bundle earlier you lot truly implemented the modules themselves
• Object persistence: Objects declared inward a bundle specification behaves similar a global information for all PL/SQL objects in the application. You tin mail away modify the bundle inward i module as well as and hence reference those changes to to a greater extent than or less other module
• Object oriented design: The bundle gives developers potent concur over how the modules as well as information structures within the bundle tin mail away endure used
• Guaranteeing transaction integrity: It provides a flat of transaction integrity
• Performance improvement: The RDBMS automatically tracks the validity of all programme objects stored inward the database and parent the performance of packages.
5.) Explain uses of cursor.
Cursor is a named private surface area inward SQL from which information tin mail away endure accessed. They are required to physical care for each row individually for queries which render multiple rows.
6.) Explain the uses of database trigger.
A PL/SQL programme unit of measurement associated amongst a detail database tabular array is called a database trigger. It is used for:
• Audit information modifications.
• Log events transparently.
• Enforce complex trouble organisation rules.
• Maintain replica tables
• Derive column values
• Implement Complex safety authorizations
7.) What are the information types available inward PL/SQL?
There are 2 types of information types inward PL/SQL:
• Scalar information types Example are NUMBER, VARCHAR2, DATE, CHAR, as well as LONG, BOOLEAN etc.
• Composite information types Example are RECORD, TABLE etc.
8.) What is the basic construction of PL/SQL?
PL/SQL uses BLOCK construction equally its basic structure. Each PL/SQL programme consists of SQL as well as PL/SQL tilt which flat a PL/SQL block.
PL/SQL block contains three sections.
• The Declaration Section (optional)
• The Execution Section (mandatory)
• The Exception treatment Section (optional)
9.) What is the divergence betwixt FUNCTION, PROCEDURE AND PACKAGE inward PL/SQL?
• Function: The top dog purpose of a PL/SQL role is to a greater extent than oft than non to compute as well as render a unmarried value. Influenza A virus subtype H5N1 role has a render type inward its specification as well as must render a value specified inward that type.
• Procedure: Influenza A virus subtype H5N1 physical care for does non accept a render type as well as should non render whatever value but it tin mail away accept a render statement that merely stops its execution as well as returns to the caller. Influenza A virus subtype H5N1 physical care for is used to render multiple values otherwise it is to a greater extent than oft than non similar to a function.
• Package: Influenza A virus subtype H5N1 bundle is schema object which groups logically related PL/SQL types, items as well as subprograms. You tin mail away also say that it is a grouping of functions, procedure, variables as well as tape type statement. It provides modularity, due to this facility it aids application development. It is used to shroud information from unauthorized users.
10.) What is exception? What are the types of exceptions?
Error treatment component of PL/SQL block is called Exception. They accept 2 types: user_defined as well as predefined.
11.) How exception is dissimilar from error?
Whenever an Error occurs Exception arises. Error is a põrnikas whereas exception is a alert or fault condition.
12.) What is the top dog argue behind using an index?
Faster access of information blocks inward the table.
13.) What is the maximum number of triggers, you lot tin mail away apply on a unmarried table?
A maximum of 12 triggers tin mail away endure applied to i table.
14.) How many types of triggers be inward PL/SQL?
There are 12 types of triggers inward PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, as well as INSERT, UPDATE, DELETE as well as ALL keywords.
• BEFORE ALL ROW INSERT
• AFTER ALL ROW INSERT
• BEFORE INSERT
• AFTER INSERT etc.
15.) How is a physical care for of PL/SQL compiled?
Compilation physical care for includes syntax check, bind as well as p-code generation processes.
Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that concur data. It is called Binding. P-code is a listing of instructions for the PL/SQL engine. P-code is stored inward the database for named blocks as well as is used the adjacent fourth dimension it is executed.
16.) Differentiate betwixt Syntax as well as runtime errors?
A syntax fault tin mail away endure easily detected past times a PL/SQL compiler. For e.g., wrong spelling.
A runtime fault is handled amongst the assist of exception-handling department inward an PL/SQL block. For e.g., SELECT INTO statement, which does non render whatever rows.
17.) Explain Commit, Rollback as well as Save point?
For a COMMIT statement, the next is true:
• Other users tin mail away consider the information changes made past times the transaction.
• The locks acquired past times the transaction are released.
• The piece of employment done past times the transaction becomes permanent.
A ROLLBACK tilt gets issued when the transaction ends, as well as the next is true.
• The piece of employment done inward a transition is undone equally if it was never issued.
• All locks acquired past times transaction are released.
It undoes all the piece of employment done past times the user inward a transaction.With SAVEPOINT, only component of transaction tin mail away endure undone.
18.) Differentiate betwixt % ROWTYPE as well as TYPE RECORD?
• % ROWTYPE is used when a inquiry returns an entire row of a tabular array or view.
• TYPE RECORD, on the other hand, is used when a inquiry returns column of dissimilar tables or views.
19.) Define Implicit as well as Explicit Cursors?
A cursor is implicit past times default. The user cannot command or physical care for the information inward this cursor.
If a inquiry returns multiple rows of data, the programme defines an explicit cursor. This allows the application to physical care for each row sequentially equally the cursor returns it.
20.) Explain mutating tabular array error?
It occurs when a trigger tries to update a row that it is currently using. It is fixed past times using views or temporary tables, hence database selects i as well as updates the other.
21.) When is a declare tilt required?
DECLARE tilt is used past times PL/SQL anonymous blocks such equally amongst stand-alone, non-stored procedures. If it is used, it must come upward kickoff inward a stand-alone file.
22.) What is stored Procedure?
A stored physical care for is a sequence of tilt or a named PL/SQL block which performs i or to a greater extent than specific functions. It is similar to a physical care for inward other programming languages. It is stored inward the database as well as tin mail away endure repeatedly executed. It is stored equally schema object. It tin mail away endure nested, invoked as well as parameterized.
23.) What are the advantages of stored procedure?
Modularity, extensibility, reusability, Maintainability as well as i fourth dimension compilation.
24.) What are the cursor attributes used inward PL/SQL?
• %ISOPEN: it checks whether the cursor is opened upward or not.
• %ROWCOUNT: returns the number of rows affected past times DML operations: INSERT, DELETE, UPDATE, SELECT.
• %FOUND: it checks whether cursor has fetched whatever row. If aye - TRUE.
• %NOTFOUND: it checks whether cursor has fetched whatever row. If no - TRUE.
25.) What is consistency?
Consistency merely agency that each user sees the consistent persuasion of the data.
Consider an example: at that spot are 2 users Influenza A virus subtype H5N1 as well as B. Influenza A virus subtype H5N1 transfers MONEY to B's account. Here the changes are updated inward A's trouble organisation human relationship (debit) but until it volition endure updated to B's trouble organisation human relationship (credit), till as well as hence other users can't consider the debit of A's account. After the debit of Influenza A virus subtype H5N1 as well as credit of B, i tin mail away consider the updates. That’s consistency.
26.) What is the importance of SQLCODE as well as SQLERRM?
SQLCODE returns the value of the number of fault for the terminal encountered fault whereas SQLERRM returns the message for the terminal error.
27.) If a cursor is open, how tin mail away nosotros expose inward a PL/SQL Block?
The %ISOPEN cursor status variable tin mail away endure used.
28.) What packages are available to PL/SQL developers?
DBMS_ serial of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.
29.) Explain polymorphism inward PL/SQL?
Polymorphism is a characteristic of OOP. It is the mightiness to practise a variable, an object or role amongst multiple forms. PL/SQL supports Polymorphism inward the flat of programme unit of measurement overloading within a fellow member role or package. Unambiguous logic must endure avoided whilst overloading is existence done.
30.) Mention what are dissimilar methods to describe the PL/SQL code?
Tracing code is a crucial technique to mensurate the code performance during the runtime. Different methods for tracing includes
• DBMS_APPLICATION_INFO
• DBMS_TRACE
• DBMS_SESSION as well as DBMS_MONITOR
• trcsess as well as tkproof utilities
--------------------------------
1.) What is PL/SQL?
PL/SQL stands for procedural linguistic communication extension to SQL. It supports procedural features of programming linguistic communication as well as SQL both. It was developed past times Oracle Corporation inward early on of 90's to parent the capabilities of SQL.
2.) What is PL/SQL table? Why it is used?
Objects of type tables are called PL/SQL tables that are modeled equally database table. We tin mail away likewise say that PL/SQL tables are a way to providing arrays. Arrays are similar temporary tables inward retentiveness that are processed really quickly. PL/SQL tables are used to movement volume data. They simplifies moving collections of data.
3.) Mention what PL/SQL bundle consists of?
A PL/SQL bundle consists of
• PL/SQL tabular array as well as tape TYPE statements
• Procedures as well as Functions
• Cursors
• Variables ( tables, scalars, records, etc.) as well as constants
• Exception names as well as pragmas for relating an fault number amongst an exception
• Cursors
4.) Mention what are the benefits of PL/SQL packages?
It provides several benefits like
• Enforced Information Hiding: It OFFERS the freedom to select whether to proceed information private or public
• Top-down design: You tin mail away pattern the interface to the code hidden inward the bundle earlier you lot truly implemented the modules themselves
• Object persistence: Objects declared inward a bundle specification behaves similar a global information for all PL/SQL objects in the application. You tin mail away modify the bundle inward i module as well as and hence reference those changes to to a greater extent than or less other module
• Object oriented design: The bundle gives developers potent concur over how the modules as well as information structures within the bundle tin mail away endure used
• Guaranteeing transaction integrity: It provides a flat of transaction integrity
• Performance improvement: The RDBMS automatically tracks the validity of all programme objects stored inward the database and parent the performance of packages.
5.) Explain uses of cursor.
Cursor is a named private surface area inward SQL from which information tin mail away endure accessed. They are required to physical care for each row individually for queries which render multiple rows.
6.) Explain the uses of database trigger.
A PL/SQL programme unit of measurement associated amongst a detail database tabular array is called a database trigger. It is used for:
• Audit information modifications.
• Log events transparently.
• Enforce complex trouble organisation rules.
• Maintain replica tables
• Derive column values
• Implement Complex safety authorizations
7.) What are the information types available inward PL/SQL?
There are 2 types of information types inward PL/SQL:
• Scalar information types Example are NUMBER, VARCHAR2, DATE, CHAR, as well as LONG, BOOLEAN etc.
• Composite information types Example are RECORD, TABLE etc.
8.) What is the basic construction of PL/SQL?
PL/SQL uses BLOCK construction equally its basic structure. Each PL/SQL programme consists of SQL as well as PL/SQL tilt which flat a PL/SQL block.
PL/SQL block contains three sections.
• The Declaration Section (optional)
• The Execution Section (mandatory)
• The Exception treatment Section (optional)
9.) What is the divergence betwixt FUNCTION, PROCEDURE AND PACKAGE inward PL/SQL?
• Function: The top dog purpose of a PL/SQL role is to a greater extent than oft than non to compute as well as render a unmarried value. Influenza A virus subtype H5N1 role has a render type inward its specification as well as must render a value specified inward that type.
• Procedure: Influenza A virus subtype H5N1 physical care for does non accept a render type as well as should non render whatever value but it tin mail away accept a render statement that merely stops its execution as well as returns to the caller. Influenza A virus subtype H5N1 physical care for is used to render multiple values otherwise it is to a greater extent than oft than non similar to a function.
• Package: Influenza A virus subtype H5N1 bundle is schema object which groups logically related PL/SQL types, items as well as subprograms. You tin mail away also say that it is a grouping of functions, procedure, variables as well as tape type statement. It provides modularity, due to this facility it aids application development. It is used to shroud information from unauthorized users.
10.) What is exception? What are the types of exceptions?
Error treatment component of PL/SQL block is called Exception. They accept 2 types: user_defined as well as predefined.
11.) How exception is dissimilar from error?
Whenever an Error occurs Exception arises. Error is a põrnikas whereas exception is a alert or fault condition.
12.) What is the top dog argue behind using an index?
Faster access of information blocks inward the table.
13.) What is the maximum number of triggers, you lot tin mail away apply on a unmarried table?
A maximum of 12 triggers tin mail away endure applied to i table.
14.) How many types of triggers be inward PL/SQL?
There are 12 types of triggers inward PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, as well as INSERT, UPDATE, DELETE as well as ALL keywords.
• BEFORE ALL ROW INSERT
• AFTER ALL ROW INSERT
• BEFORE INSERT
• AFTER INSERT etc.
15.) How is a physical care for of PL/SQL compiled?
Compilation physical care for includes syntax check, bind as well as p-code generation processes.
Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that concur data. It is called Binding. P-code is a listing of instructions for the PL/SQL engine. P-code is stored inward the database for named blocks as well as is used the adjacent fourth dimension it is executed.
16.) Differentiate betwixt Syntax as well as runtime errors?
A syntax fault tin mail away endure easily detected past times a PL/SQL compiler. For e.g., wrong spelling.
A runtime fault is handled amongst the assist of exception-handling department inward an PL/SQL block. For e.g., SELECT INTO statement, which does non render whatever rows.
17.) Explain Commit, Rollback as well as Save point?
For a COMMIT statement, the next is true:
• Other users tin mail away consider the information changes made past times the transaction.
• The locks acquired past times the transaction are released.
• The piece of employment done past times the transaction becomes permanent.
A ROLLBACK tilt gets issued when the transaction ends, as well as the next is true.
• The piece of employment done inward a transition is undone equally if it was never issued.
• All locks acquired past times transaction are released.
It undoes all the piece of employment done past times the user inward a transaction.With SAVEPOINT, only component of transaction tin mail away endure undone.
18.) Differentiate betwixt % ROWTYPE as well as TYPE RECORD?
• % ROWTYPE is used when a inquiry returns an entire row of a tabular array or view.
• TYPE RECORD, on the other hand, is used when a inquiry returns column of dissimilar tables or views.
19.) Define Implicit as well as Explicit Cursors?
A cursor is implicit past times default. The user cannot command or physical care for the information inward this cursor.
If a inquiry returns multiple rows of data, the programme defines an explicit cursor. This allows the application to physical care for each row sequentially equally the cursor returns it.
20.) Explain mutating tabular array error?
It occurs when a trigger tries to update a row that it is currently using. It is fixed past times using views or temporary tables, hence database selects i as well as updates the other.
21.) When is a declare tilt required?
DECLARE tilt is used past times PL/SQL anonymous blocks such equally amongst stand-alone, non-stored procedures. If it is used, it must come upward kickoff inward a stand-alone file.
22.) What is stored Procedure?
A stored physical care for is a sequence of tilt or a named PL/SQL block which performs i or to a greater extent than specific functions. It is similar to a physical care for inward other programming languages. It is stored inward the database as well as tin mail away endure repeatedly executed. It is stored equally schema object. It tin mail away endure nested, invoked as well as parameterized.
23.) What are the advantages of stored procedure?
Modularity, extensibility, reusability, Maintainability as well as i fourth dimension compilation.
24.) What are the cursor attributes used inward PL/SQL?
• %ISOPEN: it checks whether the cursor is opened upward or not.
• %ROWCOUNT: returns the number of rows affected past times DML operations: INSERT, DELETE, UPDATE, SELECT.
• %FOUND: it checks whether cursor has fetched whatever row. If aye - TRUE.
• %NOTFOUND: it checks whether cursor has fetched whatever row. If no - TRUE.
25.) What is consistency?
Consistency merely agency that each user sees the consistent persuasion of the data.
Consider an example: at that spot are 2 users Influenza A virus subtype H5N1 as well as B. Influenza A virus subtype H5N1 transfers MONEY to B's account. Here the changes are updated inward A's trouble organisation human relationship (debit) but until it volition endure updated to B's trouble organisation human relationship (credit), till as well as hence other users can't consider the debit of A's account. After the debit of Influenza A virus subtype H5N1 as well as credit of B, i tin mail away consider the updates. That’s consistency.
26.) What is the importance of SQLCODE as well as SQLERRM?
SQLCODE returns the value of the number of fault for the terminal encountered fault whereas SQLERRM returns the message for the terminal error.
27.) If a cursor is open, how tin mail away nosotros expose inward a PL/SQL Block?
The %ISOPEN cursor status variable tin mail away endure used.
28.) What packages are available to PL/SQL developers?
DBMS_ serial of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.
29.) Explain polymorphism inward PL/SQL?
Polymorphism is a characteristic of OOP. It is the mightiness to practise a variable, an object or role amongst multiple forms. PL/SQL supports Polymorphism inward the flat of programme unit of measurement overloading within a fellow member role or package. Unambiguous logic must endure avoided whilst overloading is existence done.
30.) Mention what are dissimilar methods to describe the PL/SQL code?
Tracing code is a crucial technique to mensurate the code performance during the runtime. Different methods for tracing includes
• DBMS_APPLICATION_INFO
• DBMS_TRACE
• DBMS_SESSION as well as DBMS_MONITOR
• trcsess as well as tkproof utilities
--------------------------------
Prepared by: V. Raga Malika