PL/SQL Interview Questions Part 2
1.) Explain Raise_application_error?
It is a physical care for of bundle DBMS_STANDARD that allows issuing of user_defined fault messages from database trigger or stored sub-program.
2.) Explain 2 virtual tables available at the fourth dimension of database trigger execution?
Table columns are referred equally THEN.column_name in addition to NOW.column_name.
• For INSERT related triggers, NOW.column_name values are available only.
• For DELETE related triggers, THEN.column_name values are available only.
• For UPDATE related triggers, both Table columns are available.
3.) What are the rules to locomote applied to NULLs whilst doing comparisons?
• NULL is never TRUE or FALSE
• NULL cannot locomote equal or unequal to other values
• If a value inward an aspect is NULL, in addition to then the aspect itself evaluates to NULL except for concatenation operator (||)
4.) Does SQL*Plus too bring a PL/SQL Engine?
No, SQL*Plus does non bring a PL/SQL Engine embedded inward it. Thus, all PL/SQL code is sent remove to database engine. It is much to a greater extent than efficient equally each contestation is non individually stripped off.
5.) Explain three basic parts of a trigger.
• H5N1 triggering contestation or event.
• H5N1 restriction
• An action
6.) What are graphic symbol functions?
INITCAP, UPPER, SUBSTR, LOWER in addition to LENGTH are all graphic symbol functions. Group functions give results based on groups of rows, equally opposed to private rows. They are MAX, MIN, AVG, COUNT in addition to SUM.
7.) Explain TTITLE in addition to BTITLE.
TTITLE in addition to BTITLE commands that command study headers in addition to footers.
8.) What is an Intersect?
Intersect is the production of 2 tables in addition to it lists alone matching rows.
9.) What are sequences?
Sequences are used to generate sequence numbers without an overhead of locking. Its drawback is that the sequence number is lost if the transaction is rolled back.
10.) What is a cursor for loop?
Cursor for loop implicitly declares %ROWTYPE equally loop index, opens a cursor, fetches rows of values from active ready into fields inward the tape in addition to closes when all the records bring been processed.
11.) Explain the usage of WHERE CURRENT OF clause inward cursors?
WHERE CURRENT OF clause inward an UPDATE, DELETE contestation refers to the latest row fetched from a cursor.
12.) Is it possible to exercise Transaction command Statements such a ROLLBACK or COMMIT inward Database Trigger? Why?
It is non possible. As triggers are defined for each table, if you lot exercise COMMIT of ROLLBACK inward a trigger, it affects logical transaction processing.
13.) What is Raise_application_error?
Raise_application_error is a physical care for of bundle DBMS_STANDARD which allows to number a user_defined fault messages from stored sub-program or database trigger.
14.) What is the divergence betwixt PROCEDURE & FUNCTION?
• H5N1 FUNCTION is e'er returns a value using the render statement.
• H5N1 PROCEDURE may render 1 or to a greater extent than values through parameters or may non render at all.
15.) Explain how procedures in addition to functions are called inward a PL/SQL block?
• Function is called equally share of an expression.
• Procedure is called equally a PL/SQL statement
16.) What is Overloading of procedures?
The Same physical care for advert is repeated alongside parameters of dissimilar information types in addition to parameters inward dissimilar positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line
17.) What is a package? What are the advantages of packages?
Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Application Design, in addition to Information. Hiding, Reusability in addition to Better Performance.
18.) What are 2 parts of package?
The 2 parts of bundle are PACKAGE SPECIFICATION & PACKAGE BODY.
• Package Specification contains declarations that are global to the packages in addition to local to the schema.
• Package Body contains actual procedures in addition to local proclamation of the procedures in addition to cursor declarations.
19.) What is divergence betwixt a Cursor declared inward a physical care for in addition to Cursor declared inward a bundle specification?
• H5N1 cursor declared inward a bundle specification is global in addition to tin locomote accessed past times other procedures or procedures inward a package.
• H5N1 cursor declared inward a physical care for is local to the physical care for that cannot locomote accessed past times other procedures.
20.) How would you lot reference column values BEFORE in addition to AFTER you lot bring inserted in addition to deleted triggers?
Using the keyword “new.column name”, the triggers tin reference column values past times novel collection. By using the keyword “old.column name”, they tin reference column values past times erstwhile collection.
21.) What are the uses of SYSDATE in addition to USER keywords?
SYSDATE refers to the electrical flow server organization date. It is a pseudo column. USER is too a pseudo column but refers to electrical flow user logged onto the session. They are used to monitor changes happening inward the table.
22.) How does ROWID attention inward running a question faster?
ROWID is the logical address of a row; it is non a physical column. It composes of information block number; file number in addition to row number inward the information block. Thus, I/O fourth dimension gets minimized retrieving the row, in addition to results inward a faster query.
23.) What is SPOOL?
Spool command tin impress the output of Sql statements inward a file.
24.) What does fetching a cursor do?
Fetching a cursor reads Result Set row past times row.
25.) What does closing a cursor do?
Closing a cursor clears the private SQL expanse equally good equally de-allocates memory.
26.) Explain autonomous transaction.
An autonomous transaction is an independent transaction of the primary or bring upwards transaction. It is non nested if it is started past times or thence other transaction.
There are several situations to exercise autonomous transactions similar lawsuit logging in addition to auditing.
27.) What is out parameter used for fifty-fifty though render contestation tin too locomote used inward pl/Sql?
Out parameters allows to a greater extent than than 1 value inward the calling program. Out parameter is non recommended inward functions. Procedures tin locomote used instead of functions if multiple values are required. Thus, these procedures are used to execute out parameters.
28.) Mention what does the hierarchical profiler does?
The hierarchical profiler could profile the calls made inward PL/SQL, apart from filling the gap betwixt the loopholes in addition to the expectations of functioning tracing. The efficiencies of the hierarchical profiler includes
• Distinct reporting for SQL in addition to PL/SQL fourth dimension consumption
• Reports count of distinct sub-programs calls made inward the PL/SQL, in addition to the fourth dimension spent alongside each subprogram call
• Multiple interactive analytics reports inward HTML format past times using the command line of piece of job utility
• More effective than conventional profiler in addition to other tracing utilities
29.) Mention what does PLV msg allows you lot to do?
The PLV msg enables you lot to
• Assign private text message to specified row inward the PL/SQL table
• It retrieves the message text past times number
• It substitutes automatically your ain messages for criterion Oracle fault messages alongside limit toggle
• Batch charge message numbers in addition to text from a database tabular array remove PLV msg PL/SQL table
30.) Mention what is the PLV (PL/Vision) bundle offers?
• Null exchange value
• Set of assertion routines
• Miscellaneous utilities
• Set of constants used throughout PL vision
• Pre-defined information types
31.) Mention what is the exercise of PLVprs in addition to PLVprsps?
PLVprs: It is an extension for string parsing for PL/SQL, in addition to it is the lowest degree of string parsing functionality
PLVprsps: It is the highest degree bundle to parse PL/SQL root code into course of teaching atomics. It relies on other parsing packages to become piece of job done.
32.) Explain how you lot tin re-create a file to file content in addition to file to PL/SQL tabular array inward advance PL/SQL?
With a unmarried programme telephone telephone – “fcopy procedure”, you lot tin re-create the consummate contents of 1 file into or thence other file. While to re-create the contents of a file remove into a PL/SQL table, you lot tin exercise the programme “file2pstab”.
33.) Mention what PLVcmt in addition to PLVrb does inward PL/SQL?
PL/Vision offers 2 packages that attention you lot handle transaction processing inward PL/SQL application. It is PLVcmt in addition to PLVrb.
• PLVcmt: PLVcmt bundle wraps logic in addition to complexity for dealing alongside commit processing
• PLVrb: It provides a programmatic interface to roll-back activity inward PL/SQL
Sumber http://www.gcreddy.com/
1.) Explain Raise_application_error?
It is a physical care for of bundle DBMS_STANDARD that allows issuing of user_defined fault messages from database trigger or stored sub-program.
2.) Explain 2 virtual tables available at the fourth dimension of database trigger execution?
Table columns are referred equally THEN.column_name in addition to NOW.column_name.
• For INSERT related triggers, NOW.column_name values are available only.
• For DELETE related triggers, THEN.column_name values are available only.
• For UPDATE related triggers, both Table columns are available.
3.) What are the rules to locomote applied to NULLs whilst doing comparisons?
• NULL is never TRUE or FALSE
• NULL cannot locomote equal or unequal to other values
• If a value inward an aspect is NULL, in addition to then the aspect itself evaluates to NULL except for concatenation operator (||)
4.) Does SQL*Plus too bring a PL/SQL Engine?
No, SQL*Plus does non bring a PL/SQL Engine embedded inward it. Thus, all PL/SQL code is sent remove to database engine. It is much to a greater extent than efficient equally each contestation is non individually stripped off.
5.) Explain three basic parts of a trigger.
• H5N1 triggering contestation or event.
• H5N1 restriction
• An action
6.) What are graphic symbol functions?
INITCAP, UPPER, SUBSTR, LOWER in addition to LENGTH are all graphic symbol functions. Group functions give results based on groups of rows, equally opposed to private rows. They are MAX, MIN, AVG, COUNT in addition to SUM.
7.) Explain TTITLE in addition to BTITLE.
TTITLE in addition to BTITLE commands that command study headers in addition to footers.
8.) What is an Intersect?
Intersect is the production of 2 tables in addition to it lists alone matching rows.
9.) What are sequences?
Sequences are used to generate sequence numbers without an overhead of locking. Its drawback is that the sequence number is lost if the transaction is rolled back.
10.) What is a cursor for loop?
Cursor for loop implicitly declares %ROWTYPE equally loop index, opens a cursor, fetches rows of values from active ready into fields inward the tape in addition to closes when all the records bring been processed.
11.) Explain the usage of WHERE CURRENT OF clause inward cursors?
WHERE CURRENT OF clause inward an UPDATE, DELETE contestation refers to the latest row fetched from a cursor.
12.) Is it possible to exercise Transaction command Statements such a ROLLBACK or COMMIT inward Database Trigger? Why?
It is non possible. As triggers are defined for each table, if you lot exercise COMMIT of ROLLBACK inward a trigger, it affects logical transaction processing.
13.) What is Raise_application_error?
Raise_application_error is a physical care for of bundle DBMS_STANDARD which allows to number a user_defined fault messages from stored sub-program or database trigger.
14.) What is the divergence betwixt PROCEDURE & FUNCTION?
• H5N1 FUNCTION is e'er returns a value using the render statement.
• H5N1 PROCEDURE may render 1 or to a greater extent than values through parameters or may non render at all.
15.) Explain how procedures in addition to functions are called inward a PL/SQL block?
• Function is called equally share of an expression.
• Procedure is called equally a PL/SQL statement
16.) What is Overloading of procedures?
The Same physical care for advert is repeated alongside parameters of dissimilar information types in addition to parameters inward dissimilar positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line
17.) What is a package? What are the advantages of packages?
Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Application Design, in addition to Information. Hiding, Reusability in addition to Better Performance.
18.) What are 2 parts of package?
The 2 parts of bundle are PACKAGE SPECIFICATION & PACKAGE BODY.
• Package Specification contains declarations that are global to the packages in addition to local to the schema.
• Package Body contains actual procedures in addition to local proclamation of the procedures in addition to cursor declarations.
19.) What is divergence betwixt a Cursor declared inward a physical care for in addition to Cursor declared inward a bundle specification?
• H5N1 cursor declared inward a bundle specification is global in addition to tin locomote accessed past times other procedures or procedures inward a package.
• H5N1 cursor declared inward a physical care for is local to the physical care for that cannot locomote accessed past times other procedures.
20.) How would you lot reference column values BEFORE in addition to AFTER you lot bring inserted in addition to deleted triggers?
Using the keyword “new.column name”, the triggers tin reference column values past times novel collection. By using the keyword “old.column name”, they tin reference column values past times erstwhile collection.
21.) What are the uses of SYSDATE in addition to USER keywords?
SYSDATE refers to the electrical flow server organization date. It is a pseudo column. USER is too a pseudo column but refers to electrical flow user logged onto the session. They are used to monitor changes happening inward the table.
22.) How does ROWID attention inward running a question faster?
ROWID is the logical address of a row; it is non a physical column. It composes of information block number; file number in addition to row number inward the information block. Thus, I/O fourth dimension gets minimized retrieving the row, in addition to results inward a faster query.
23.) What is SPOOL?
Spool command tin impress the output of Sql statements inward a file.
24.) What does fetching a cursor do?
Fetching a cursor reads Result Set row past times row.
25.) What does closing a cursor do?
Closing a cursor clears the private SQL expanse equally good equally de-allocates memory.
26.) Explain autonomous transaction.
An autonomous transaction is an independent transaction of the primary or bring upwards transaction. It is non nested if it is started past times or thence other transaction.
There are several situations to exercise autonomous transactions similar lawsuit logging in addition to auditing.
27.) What is out parameter used for fifty-fifty though render contestation tin too locomote used inward pl/Sql?
Out parameters allows to a greater extent than than 1 value inward the calling program. Out parameter is non recommended inward functions. Procedures tin locomote used instead of functions if multiple values are required. Thus, these procedures are used to execute out parameters.
28.) Mention what does the hierarchical profiler does?
The hierarchical profiler could profile the calls made inward PL/SQL, apart from filling the gap betwixt the loopholes in addition to the expectations of functioning tracing. The efficiencies of the hierarchical profiler includes
• Distinct reporting for SQL in addition to PL/SQL fourth dimension consumption
• Reports count of distinct sub-programs calls made inward the PL/SQL, in addition to the fourth dimension spent alongside each subprogram call
• Multiple interactive analytics reports inward HTML format past times using the command line of piece of job utility
• More effective than conventional profiler in addition to other tracing utilities
29.) Mention what does PLV msg allows you lot to do?
The PLV msg enables you lot to
• Assign private text message to specified row inward the PL/SQL table
• It retrieves the message text past times number
• It substitutes automatically your ain messages for criterion Oracle fault messages alongside limit toggle
• Batch charge message numbers in addition to text from a database tabular array remove PLV msg PL/SQL table
30.) Mention what is the PLV (PL/Vision) bundle offers?
• Null exchange value
• Set of assertion routines
• Miscellaneous utilities
• Set of constants used throughout PL vision
• Pre-defined information types
31.) Mention what is the exercise of PLVprs in addition to PLVprsps?
PLVprs: It is an extension for string parsing for PL/SQL, in addition to it is the lowest degree of string parsing functionality
PLVprsps: It is the highest degree bundle to parse PL/SQL root code into course of teaching atomics. It relies on other parsing packages to become piece of job done.
32.) Explain how you lot tin re-create a file to file content in addition to file to PL/SQL tabular array inward advance PL/SQL?
With a unmarried programme telephone telephone – “fcopy procedure”, you lot tin re-create the consummate contents of 1 file into or thence other file. While to re-create the contents of a file remove into a PL/SQL table, you lot tin exercise the programme “file2pstab”.
33.) Mention what PLVcmt in addition to PLVrb does inward PL/SQL?
PL/Vision offers 2 packages that attention you lot handle transaction processing inward PL/SQL application. It is PLVcmt in addition to PLVrb.
• PLVcmt: PLVcmt bundle wraps logic in addition to complexity for dealing alongside commit processing
• PLVrb: It provides a programmatic interface to roll-back activity inward PL/SQL