TOP 20 PL SQL INTERVIEW QUESTION

TOP 20 PL SQL INTERVIEW QUESTION

1. Tell me about yourself?

This is probably the most asked question in plsql interview. It breaks the ice and gets you to talk about something you should be fairly comfortable with. Have something prepared that doesn't sound rehearsed. It's not about you telling your life story and quite frankly, the interviewer just isn't interested. Unless asked to do so, stick to your education, career and current situation. Work through it chronologically from the furthest back to the present.

2. What’s a PL/SQL table? Its purpose and Advantages?

A PL/SQL table is one dimensional, indexed, unbounded sparsed collection of homogeneous Data. PLSQL tables are used to move data into and out of the database and between client side applications and stored sub-programs. They have attributes such as exits, prior, first, last, delete ,next . These attributes make PLSQL tables easier to use and applications easier to maintain. Advantages: 1 PLSQL tables give you the ability to hold multiple values in a structure in memory so that a PLSQL block does not have to go to the database every time it needs to retrieve one of these values - it can retrieve it directly from the PLSQL table in memory. 2 Global temporary tables act as performance enhancers when compared to standard tables as they greatly reduce the disk IO. 3 They also offer the ease-of-use of standard tables, since standard SQL can be used with them; no special array-processing syntax is required.

3. What Can You Do for Us That Other Candidates Can't? What makes you unique?

This will take an assessment of your experiences, skills and traits. Summarize concisely: "I have a unique combination of strong technical skills, and the ability to build strong customer relationships. This allows me to use my knowledge and break down information to be more user-friendly."

4. What is a Cursor? How many types of Cursor are there?

Cursor is an identifier/name to a work area that we can interact with to access its information. A cursor points to the current row in the result set fetched. There are three types of cursors. They are 1 Implicit cursors – created automatically by PL/SQL for all SQL Dml statements such as Insert Update, delete and Select 2 Explicit cursors – Created explicitly. They create a storage area where the set of rows Returned by a query are placed. 3 Dynamic Cursors – Ref Cursors( used for the runtime modification of the select querry). Declaring the cursor, Opening the cursor, Fetching data , Closing the cursor(Releasing the work area) are the steps involved when using explicit cursors.

5. What are the modes for passing parameters to Oracle?

There are three modes for passing parameters to subprograms 1.IN - An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value. 2. OUT – An out-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself. 3.INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.

6. What is the difference between Truncate and Delete Statement?

1.Truncate – Data truncated by using truncate statement is lost permanently and cannot be retrieved even by rollback. Truncate command does not use rollback segment during its execution, hence it is fast. 2. Delete – Data deleted by using the delete statement can be retrieved back by Rollback. Delete statement does not free up the table object allocated space.

7. What are Exceptions? How many types of Exceptions are there?

Exceptions are conditions that cause the termination of a block. There are two types of exceptions 1.Pre-Defined – Predefined by PL/SQL and are associated with specific error codes. 2.User-Defined – Declared by the users and are rose on deliberate request. (Breaking a condition etc.) Exception handlers are used to handle the exceptions that are raised. They prevent exceptions from propagating out of the block and define actions to be performed when exception is raised.

8. What is a Pragma Exception_Init? Explain its usage?

Pragma Exception_Init is used to handle undefined exceptions. It issues a directive to the compiler asking it to associate an exception to the oracle error. There by displaying a specific error message pertaining to the error occurred. Pragma Exception_Init (exception_name, oracle_error_name).

9. What is the difference between Package, Procedure and Functions?

1.A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms. 2.Procedure is a sub program written to perform a set of actions and can return multiple values. 3.Function is a subprogram written to perform certain computations and return a single value. Unlike subprograms packages cannot be called, passed parameters or nested.

10. What is an Anonymous block?

Anonymous Block is a block of instructions in PL/SQL and SQL which is not saved under a name as an object in database schema It is also not compiled and saved in server storage, so it needs to be parsed and executed each time it is run. However, this simple form of program can use variables, can have flow of control logic, can return query results into variables and can prompt the user for input using the SQL*Plus '&' feature as any stored procedure.

11. What is a Trigger ? How many types of Triggers are there?

Trigger is a procedure that gets implicitly executed when an insert/update/delete statement is issued against an associated table. Triggers can only be defined on tables not on views, how ever triggers on the base table of a view are fired if an insert/update/delete statement is issued against a view. There are two types of triggers, Statement level trigger and Row level trigger. Insert After / For each row Trigger is fired / Update / Before / For Each statement Delete.

12. What is Commit, Rollback and Save point?

Commit – Makes changes to the current transaction permanent. It Erases the savepoints and releases the transaction locks. Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions. Rollback – This statement is used to undo work.

13. What is the difference between DDL, DML and DCL structures?

DDL statements are used for defining data. Ex: Create, Alter, Drop,Truncate,Rename. DML statements are used for manipulating data. Ex: Insert, update, truncate. DCL statements are used for to control the access of data. Ex; Grant, Revoke. TCL statements are used for data saving.Ex; Commit,Rollback,Savepoint.

14. What is the difference between the snapshot and synonym?
    7 A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity. 8 A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.
15. What is the difference between Function and Procedure?

1..Procedure is a sub program written to perform a set of actions and returns multiple valuesUsing out parameters or return no value at all. 2..Function is a subprogram written to perform certain computations and return a single value.

16. How do we Tune the Queries?

Queries can be tuned by Checking the logic (table joins), by creating Indexes on objects in the where clause, by avoiding full table scans. Finally use the trace utility to generate the trace file, use the TK-Prof utility to generate a statistical analysis about the query using which appropriate actions can be taken.

17. What is the difference between data types char and varchar?

Char reserves the number of memory locations mentioned in the variable declarations, even though not used (it can store a maximum of 255 bytes). Where as Varchar does not reserve any memory locations when the variable is declared, it stores the values only after they are assigned (it can store a maximum of 32767 bytes).

18. What is the significance of _all tables?

All tables are multi-org tables which are associated with the company as a whole. Multiple Organizations is enabled in Oracle Applications by partitioning some database tables by the Operating Unit. Other tables are shared across Operating Units (and therefore across set of books). Examples of Applications with partitioned tables are Oracle Payables, Oracle Purchasing, Oracle Receivables, Oracle Projects, Oracle Sales & Marketing etc. The name of each corresponding partitioned table is.

19. What are mutating tables? And what is mutating error?

A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. A mutating error occurs when a trigger which fires when updation/deletion/insertion is done on a table A performs insertion/updation/deletion on the same table A. This error results in an infinite loop which is termed as a mutating error.

20. What is Data cleaning and testing?

Data Cleaning: Transformation of data in its current state to a pre-defined, standardized format using packaged software or program modules. Data Testing: The agreed upon conversion deliverables should be approved by the client representatives who are responsible for the success of the conversion. In addition, three levels of conversion testing have been identified and described in the prepare conversion test plans deliverables. Eg: for Summary Balances in GL we set Test Criteria as Record Counts, Hash Totals, Balances, Journal Debit and Credit. 

Share on Google Plus

About Mindsforest