Interview Questions.

Q1. What is PL SQL ?

Ans: PL SQL is a procedural language which has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration.

 Q2. Differentiate among % ROWTYPE and TYPE RECORD.

Ans: % ROWTYPE is used when a question returns an entire row of a desk or view.

TYPE RECORD, on the other hand, is used while a question returns column of different tables or views.

Eg.  TYPE r_emp is RECORD (sno smp.Smpnopercentkind,sname smp sname %type)

e_rec smp ROWTYPE

Cursor c1 is pick out smpno,dept from smp;

e_rec c1 %ROWTYPE

 Q3. Explain uses of cursor.

Ans: Cursor is a named private place in SQL from which data may be accessed. They are required to method each row individually for queries which go back multiple rows.

 Q4. Show code of a cursor for loop.

Ans: Cursor publicizes %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the lively set in fields of the document and shuts while all statistics are processed.

Eg.  FOR smp_rec IN C1 LOOP

totalsal=totalsal+smp_recsal;

ENDLOOP;

Q5. Explain the uses of database cause.

Ans: A PL/SQL software unit related to a specific database desk is called a database trigger. It is used for :

1)Audit statistics adjustments.

2)Log occasions transparently.

3)Enforce complicated enterprise guidelines.

Four)Maintain reproduction tables

five)Derive column values

6)Implement Complex safety authorizations

Q6. What are the 2 kinds of exceptions.

Ans: Error coping with a part of PL/SQL block is referred to as Exception. They have  types : user_defined and predefined.

 Q7. Show some predefined exceptions.

Ans: DUP_VAL_ON_INDEX

ZERO_DIVIDE

NO_DATA_FOUND

TOO_MANY_ROWS

CURSOR_ALREADY_OPEN

INVALID_NUMBER

INVALID_CURSOR

PROGRAM_ERROR

TIMEOUT _ON_RESOURCE

STORAGE_ERROR

LOGON_DENIED

VALUE_ERROR

and so forth.

Q8. Explain Raise_application_error.

Ans: It is a procedure of bundle DBMS_STANDARD that lets in issuing of user_defined blunders messages from database trigger or stored sub-program.

Q9. Show how functions and methods are known as in a PL SQL block.

Ans: Function is called as part of an expression.

Total:=calculate_sal(‘b644’)

Procedure is known as  as a assertion in PL/SQL.

Calculate_bonus(‘b644’);

 Q10. Explain  digital tables to be had on the time of database cause execution.

Ans: Table columns are referred as THEN.Column_name and NOW.Column_name.

For INSERT related triggers, NOW.Column_name values are available only.

For DELETE related triggers, THEN.Column_name values are to be had best.

For UPDATE associated triggers, each Table columns are available. 

Q11. What are the guidelines to be carried out to NULLs while doing comparisons?

Ans:  1) NULL is by no means TRUE or FALSE

2) NULL can't be same or unequal to different values

3) If a fee in an expression is NULL, then the expression itself evaluates to NULL besides)

Q12. How is a system of PL SQL compiled?

Ans: Compilation process consists of syntax test, bind and p-code era techniques.

Syntax checking tests the PL SQL codes for compilation mistakes. When all mistakes are corrected, a garage cope with is assigned to the variables that preserve information. It is called Binding. P-code is a list of instructions for the PL SQL engine. P-code is stored in the database for named blocks and is used the subsequent time it's miles performed.

HubSpot Video
 

Q13. Differentiate among Syntax and runtime mistakes.

Ans: A syntax mistakes may be effortlessly detected via a PL/SQL compiler. For eg, incorrect spelling.

A runtime mistakes is handled with the help of exception-dealing with segment in an PL/SQL block. For eg, SELECT INTO announcement, which does no longer go back any rows.

 Q14. Explain Commit, Rollback and Savepoint.

Ans: For a COMMIT declaration, the following is true:

Other users can see the statistics adjustments made by using the transaction.

The locks acquired with the aid of the transaction are launched.

The work carried out by the transaction will become everlasting.

A ROLLBACK declaration gets issued whilst the transaction ends, and the following is authentic.

The work finished in a transition is undone as though it become by no means issued.

All locks acquired by transaction are released.

It undoes all of the work achieved by using the consumer in a transaction. With SAVEPOINT, only a part of transaction can be undone.

 Q15. Define Implicit and Explicit Cursors.

Ans: A cursor is implicit via default. The user can't manipulate or system the facts on this cursor.

If a question returns a couple of rows of records, this system defines an express cursor. This permits the application to system each row sequentially because the cursor returns it.

 Q16. Explain mutating desk errors.

Ans: It occurs whilst a trigger attempts to update a row that it's miles currently using. It is constant by the use of perspectives or transient tables, so database selects one and updates the alternative.

 Q17. When is a claim announcement required?

Ans: DECLARE assertion is utilized by PL SQL anonymous blocks such as with stand on my own, non-stored processes. If it is used, it need to come first in a stand by myself document.

Q18. How many triggers may be applied to a desk?

Ans: A most of 12 triggers may be implemented to at least one table.

 Q19. What is the importance of SQLCODE and SQLERRM?

Ans: SQLCODE returns the fee of the variety of blunders for the final encountered blunders whereas SQLERRM returns the message for the ultimate errors.

Q20. If a cursor is open, how can we find in a PL SQL Block?

Ans: the %ISOPEN cursor repute variable can be used.

Q21. Show the two PL/SQL cursor exceptions.

Ans: Cursor_Already_Open

Invaid_cursor

 Q22. What operators cope with NULL?

Ans: NVL converts NULL to another certain price.

Var:=NVL(var2,’Hi’);

IS NULL and IS NOT NULL can be used to check specially to look whether or not the fee of a variable is NULL or now not.

Q23. Does SQL*Plus actually have a PL/SQL Engine?

Ans: No, SQL*Plus does now not have a PL/SQL Engine embedded in it. Thus, all PL/SQL code is sent immediately to database engine. It is much greater green as every statement is not personally stripped off.

 Q24. What applications are available to PL SQL builders?

Ans: DBMS_ series of packages, consisting of, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.

Q25. Explain 3 fundamental elements of a trigger.

Ans:

A triggering statement or event.

A restriction

An motion

Q26. What are character functions?

Ans: INITCAP, UPPER, SUBSTR, LOWER and LENGTH are all individual functions. Group features give results primarily based on groups of rows, as opposed to character rows. They are MAX, MIN, AVG, COUNT and SUM.

 Q27. Explain TTITLE and BTITLE.

Ans: TTITLE and BTITLE instructions that control record headers and footers.

 Q28. Show the cursor attributes of PL/SQL.

Ans:  %ISOPEN : Checks if the cursor is open or no longer

%ROWCOUNT : The range of rows which might be up to date, deleted or fetched.

%FOUND : Checks if the cursor has fetched any row. It is proper if rows are fetched

%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows aren't fetched.

Q29. What is an Intersect?

Ans: Intersect is the product of  tables and it lists best matching rows.

 Q30.What are sequences?

Ans: Sequences are used to generate collection numbers without an overhead of locking. Its disadvantage is that the sequence variety is lost if the transaction is rolled returned.

Q31.How could you reference column values BEFORE and AFTER you have got inserted and deleted triggers?

Ans: Using the key-word “new.Column name”, the triggers can reference column values by way of new collection. By the usage of the keyword “old.Column name”, they can reference column vaues via vintage collection.

 Q32. What are the uses of SYSDATE and USER key phrases?

Ans: SYSDATE refers back to the cutting-edge server device date. It is a pseudo column. USER is likewise a pseudo column however refers to contemporary person logged onto the session. They are used to reveal adjustments occurring within the table.

Q33. How does ROWID help in jogging a question faster?

Ans: ROWID is the logical deal with of a row, it isn't a physical column. It composes of facts block quantity, report wide variety and row quantity in the statistics block. Thus, I/O time gets minimized retrieving the row, and effects in a quicker question.

Q34. What are database hyperlinks used for?

Ans: Database links are created that allows you to shape verbal exchange among diverse databases, or different environments like test, development and manufacturing. The database links are study-only to access different data as nicely.

Q35. What does fetching a cursor do?

Ans: Fetching a cursor reads Result Set row by way of row.

Q36. What does closing a cursor do?

Ans: Closing a cursor clears the non-public SQL region in addition to de-allocates reminiscence

Q37. Explain the uses of Control File.

Ans: It is a binary file. It records the shape of the database. It consists of places of numerous log documents, names and timestamps. They may be stored in exceptional places to assist in retrieval of statistics if one report gets corrupted.

 Q38. Explain Consistency

Ans: Consistency suggests that records will no longer be meditated to different users until the records is devote, in order that consistency is maintained.

Q39. Differ between Anonymous blocks and sub-programs.

Ans: Anonymous blocks are unnamed blocks that are not saved anywhere whilst sub-applications are compiled and stored in database. They are compiled at runtime.

 Q40. Differ between DECODE and CASE.

Ans: DECODE and CASE statements are very comparable, however CASE is extended version of DECODE. DECODE does not permit Decision making statements in its area.

Select decode(totalsal=12000,’high’,ten thousand,’medium’) as decode_tesr from smp where smpno in (10,12,14,sixteen);

This statement returns an error.

CASE is at once utilized in PL SQL, but DECODE is utilized in PL SQL through SQL best.

Q41. Explain self reliant transaction.

Ans: An autonomous transaction is an unbiased transaction of the principle or figure transaction. It isn't always nested if it's miles began through every other transaction.

There are several conditions to use self sustaining transactions like occasion logging and auditing.

Q42. Differentiate among SGA and PGA.

Ans: SGA stands for System Global Area while PGA stands for Program or Process Global Area. PGA is only allocated 10% RAM size, however SGA is given forty% RAM length.

 Q43. What is the area of Pre_defined_functions.

Ans: They are stored within the preferred bundle known as “Functions, Procedures and Packages”

 Q44. Explain polymorphism in PL SQL.

Ans: Polymorphism is a feature of OOP. It is the capability to create a variable, an object or function with more than one forms. PL/SQL supports Polymorphism inside the shape of application unit overloading inner a member feature or package..Unambiguous logic have to be averted even as overloading is being achieved.

Q45. What are the makes use of of MERGE?

Ans: MERGE is used to mix multiple DML statements into one.

Syntax : merge into tablename

the use of(question)

on(be part of condition)

when now not matched then

[insert/update/delete] command

whilst matched then

[insert/update/delete] command

Q46. Can 2 queries be achieved simultaneously in a Distributed Database System?

Ans: Yes, they can be achieved simultaneously. One query is constantly independent of the second one question in a distributed database gadget based totally on the 2 section commit.

 Q47. Explain Raise_application_error.

Ans: It is a method of the package DBMS_STANDARD that permit issuing a user_defined blunders messages from the database cause or saved sub-software.

Q48. What is out parameter used for eventhough go back statement also can be utilized in pl/sq.?

Ans: Out parameters allows multiple value inside the calling application. Out parameter isn't advocated in features. Procedures may be used in place of features if more than one values are required. Thus, these procedures are used to execute Out parameters.

Q49. How would you convert date into Julian date format?

Ans: We can use the J format string :

SQL > select to_char(to_date(‘29-Mar-2013’,’dd-mon-yyyy’),’J’) as julian from twin;

Q50. Explain SPOOL

Ans: Spool command can print the output of sq. Statements in a document.

Spool/tmp/sql_outtxt

pick smp_name, smp_id from smp wherein dept=’money owed’;

spool off;

Q51. Mention what PL/SQL bundle includes?

Ans: A PL/SQL package includes

PL/SQL table and record TYPE statements

Procedures and Functions

Cursors

Variables ( tables, scalars, statistics, and many others.) and constants

Exception names and pragmas for relating an mistakes wide variety with an exception

Cursors

Q52. Mention what are the advantages of PL/SQL programs? 

Ans: It presents numerous benefits like

Enforced Information Hiding:It offers the freedom to select whether or not to hold statistics non-public or public

Top-down design:You can layout the interface to the code hidden within the package before you surely applied the modules themselves

Object staying power:Objects declared in a package deal specification behaves like a international facts for all PL/SQL gadgets in the utility. You can regulate the bundle in a single module after which reference those changes to any other module

Object oriented design: The package gives builders robust holdover how the modules and records systems within the package deal may be used

Guaranteeing transaction integrity: It offers a stage of transaction integrity

Performance development: The RDBMS mechanically tracks the validity of all program items stored within the database and beautify the performance of applications.

Q53. Mention what are special techniques to hint the PL/SQL code? 

Ans: Tracing code is a vital approach to degree the code performance at some stage in the runtime. Different methods for tracing includes

DBMS_APPLICATION_INFO

DBMS_TRACE

DBMS_SESSION and DBMS_MONITOR

trcsess and tkproof utilities

Q54. Mention what does the hierarchical profiler does? 

Ans: The hierarchical profiler should profile the calls made in PL/SQL, aside from filling the gap among the loopholes and the expectations of overall performance tracing. The efficiencies of the hierarchical profiler includes

Distinct reporting for SQL and PL/SQL time consumption

Reports matter of awesome sub-packages calls made in the PL/SQL, and the time spent with each subprogram call

Multiple interactive analytics reviews in HTML layout by means of using the command line application

More powerful than conventional profiler and other tracing utilities

Q55. Mention what does PLV msg permits you to do?

Ans: The PLV msg permits you to

Assign individual text message to unique row in the PL/SQL desk

It retrieves the message text by using quantity

It substitutes mechanically your personal messages for preferred Oracle errors messages with limit toggle

Batch load message numbers and text from a database desk immediately PLV msg PL/SQL desk

Q56. Mention what's the PLV (PL/Vision) package deal offers? 

Ans:

Null substitution fee

Set of assertion exercises

Miscellaneous utilities

Set of constants used throughout PL imaginative and prescient

Pre-defined datatypes

 Q57. Mention what's the use of PLVprs and PLVprsps?

Ans:

PLVprs:It is an extension for string parsing for PL/SQL, and it's miles the bottom level of string parsing functionality

PLVprsps:It is the highest degree package deal to parse PL/SQL source code into separate atomics. It is predicated on other parsing programs to get work achieved.

Q58. Explain how you may reproduction a record to document content material and document to PL/SQL table earlier PL/SQL?

Ans: With a unmarried application call – “fcopy method”, you could copy the entire contents of one record into some other report.  While to replicate the contents of a report directly right into a PL/SQL table, you may use this system “file2pstab”.

Q59. Explain how exception handling is carried out in advance PL/SQL?

Ans: For exception coping with PL/SQl presents an effective plugin PLVexc.  PLVexc supports 4 exclusive exception coping with movements.

Continue processing

Record and then preserve

Halt processing

Record and then halt processing

For those exceptions that re-occurs you could use the RAISE statement.

Q60. Mention what hassle one might face while writing log data to a records-base desk in PL/SQL?

Ans: While writing log facts to a database table, the problem you face is that the records is handiest available most effective as soon as the brand new rows are devoted to the database.  This is probably a problem as such PLVlog is usually deployed to song mistakes and in lots of such instances the modern transaction might fail or otherwise needed a rollback.

Q61. Mention what is the characteristic this is used to transfer a PL/SQL table log to a database table?

Ans: To switch a PL/SQL table log a database log table characteristic “PROCEDURE ps2db” is used.

Q62. When you need to use a default “rollback to” savepoint of PLVlog?

Ans: The default “rollback to” savepoint of PLVlog is used while the users has turned on the rollback activity and has not supplied an alternative savepoint within the call to put_line.  The default savepoint is initialized to the c none consistent.

Q63. Why PLVtab is taken into consideration as the easiest manner to access the PL/SQL desk?

Ans: The PL/SQL table are the nearest to arrays in PL/SQL, and a good way to get admission to this desk you have to first claim a desk kind, and then you need to declare PL/SQL desk itself. But through using PLVtab, you could avoid defining your own PL/SQL desk type and make PL/SQL facts-desk get admission to easy.

Q64. Mention what does PLVtab enables you to do when you showthe contents of PL/SQL tables?

Ans: PLVtab enables you to do following things whilst you show the contents of PL/SQL tables

Display or suppress a header for the desk

Display or suppress the row numbers for the desk values

Show a prefix earlier than each row of the desk

Q65. Explain how are you going to save or area your msg in a desk?

Ans: To shop msg in a table, you could do it in two methods

Load character messages with calls to the add_textprocedure

Load sets of messages from a database desk with the load_from_dbmsprocedure

Q66. Mention what's the usage of feature “module process” in PL/SQL?

Ans: The “module system” permits to convert all the strains of code in a particular application unit with one method name.  There are three arguments for modules

module_in

cor_in

Last_module_in

Q67. Mention what PLVcmt and PLVrb does in PL/SQL?

Ans: PL/Vision gives  applications that assist you manipulate transaction processing in PL/SQL application. It is PLVcmt and PLVrb.

PLVcmt:PLVcmt package deal wraps logic and complexity for coping with commit processing

PLVrb:It offers a programmatic interface to roll-returned hobby in PL/SQL

Q68. Compare SQL & PL/SQL

Ans:

Criteria    SQL    PL/SQL

What it's miles    Single question or command execution    Full programming language

What it contains    Data source for reviews, internet pages    Application language to build, format and show document, net pages

Characteristic    Declarative in nature    Procedural in nature

Used for    Manipulating facts    Creating packages
 

Q69. What is the simple structure of PL/SQL?

Ans: PL/SQL makes use of block shape as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

Master PL/SQL, in this PL/SQL certification training.

Q70. How is a process of PL/SQL compiled?

Ans: Compilation technique includes syntax test, bind and p-code generation tactics.Syntax checking assessments the PL/SQL codes for compilation errors. When all errors are corrected, a garage deal with is assigned to the variables that keep statistics. It is known as Binding. P-code is a list of commands for the PL/SQL engine. P-code is stored inside the database for named blocks and is used the subsequent time it is completed.

Q71. What is the distinction among FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?

Ans: Function: The primary cause of a PL/SQL feature is commonly to compute and return a single value. A characteristic has a go back type in its specification and should return a price specified in that kind.

Procedure: A procedure does not have a go back kind and must not return any cost however it may have a return declaration that in reality stops its execution and returns to the caller. A technique is used to return multiple values in any other case it's miles typically much like a feature.

Package: A bundle is schema object which groups logically associated PL/SQL kinds , gadgets and subprograms. You also can say that it's miles a group of capabilities, method, variables and record kind statement. It offers modularity, due to this facility it aids utility development. It is used to hide statistics from unauthorized customers.

Q72. What is saved Procedure?

Ans: A stored technique is a sequence of assertion or a named PL/SQL block which performs one or greater unique capabilities. It is just like a system in other programming languages. It is stored inside the database and can be repeatedly done. It is saved as schema object. It can be nested, invoked and parameterized.

Q73. What is cursor and why it's miles required?

Ans: A cursor is a brief work place created in a device reminiscence while an SQL assertion is accomplished.A cursor contains statistics on a choose statement and the row of information accessed via it. This temporary paintings vicinity shops the records retrieved from the database and manage this information. A cursor can maintain multiple row, however can process handiest one row at a time. Cursor are required to method rows in my opinion for queries.