PL SQL Mostly Asked Interview Questions

Top PL/SQL Interview Questions

• What is PL/SQL?
• What is the basic structure of PL/SQL?
• What are the components of a PL/SQL Block?
• What are the data types a available in PL/SQL?
• Is there a limit on the size of a PL/SQL block?
• What is the difference between Anonymous blocks and sub programs?
• Can SQL group functions be used within the procedural statements?
• How is global variable declared within a nested block?
• What is the maximum length of an identifier that can be used?
• What are the different data types that can be defined in a PL/SQL block?
• What is the difference between SQL and PL/SQL?
• What are the advantages of PL/SQL?.
• What is an anonymous block and how is it executed?
• What are the mandatory keywords in a PL/SQL program block?
• What are Transaction Control statements? Can they be used within the PL/SQL block?
• How can you display data from a PL/SQL block?
• What PL/SQL supports?
• Different types of PL/SQL blocks?
• What is the structure of PL/SQL block?
• What are %TYPE and % ROWTYPE?
• What are the advantages of using %TYPE & %ROWTYPE data types?
• Difference between %TYPE and %ROWTYPE?
• What is Bind variable?
• What is Cursor?
• Explain Types of cursors?
• Explain How CURSOR FOR LOOP works?
• What are the PL/SQL Statements used in cursor processing?
• What are the cursor attributes used in PL/SQL?
• What is WHERE CURRENT OF clause?
• Can we pass parameter to a cursor what are the advantages?
• How can you find a cursor is opened or not in PL/SQL block?
• What is a cursor for loop?
• Can you pass a parameter to a cursor?
• Can you use %Rowcount as a parameter to a cursor?
• What is implicit cursor in Oracle?
• What are the Implicit Cursor Attributes?
• How is it possible to fetch a current row from a cursor without using ROWID?
• How can you check if an UPDATE statement in PL/SQL is successfully executed?
• What is the value of SQL%FOUND attribute after & before an INSERT statement executed?
• What are the different ways to determine the status of a cursor?
• What will be the value of %N0TF0UND attribute after a cursor is opened?
• Why does SQL%ISOPEN always returns the FALSE value?
• How can explicit cursors be used to fetch multiple rows?
• What is FOR UPDATE clause?
• What is Ref Cursor?
• Difference between Cursor and Ref Cursor?
• Difference between Strong and Weak Ref cursor?
• Can cursor variables be stored in PL/SQL tables? If yes how. If not why?
• What is Exception in Oracle?
• Explain the types of Exceptions?
• What are the predefined oracle exceptions?
• Explain user defined exceptions in oracle. ?
• How User defined Exceptions Are Raised?
• Explain the guidelines for Avoiding and Handling PL/SQL Errors and Exceptions?
• What is Pragma Init Exception in oracle?
• What is Raise_application_error?
• What are the return values of functions SQLCODE and SQLERRM?
• Can we define exceptions twice in same block?
• What are PL/SQL Cursor Exceptions?
• Can the PL/SQL block process more than one exception at a time?
• Is it possible to have more than one OTHERS clause?
• What causes the INVALID_CURSOR exception?
• Where will the control be transferred if an exception is raised with in block?
• What is the advantage of OTHERS exception handler?
• When is the CURSOR_ALREADY_OPEN exception raised?
• What is the advantage of having a separate exception section within a PL/SQL code?
• What happen when there is no handler for a raised exception?
• What is the difference between user-defined exceptions and Oracle-defined exceptions?
• What is the difference between RAISE and RAISE_APPLICATION_ERROR?
• What is Collection?
• Explain collection types. i.e. Index-by tables, Nested tables, Varrays?
• What is the difference between the cursor and pl/sql table?
• How we can delete the specific record and all records from the pl/sql table.
• What is the Nested table?
• What is bulk collect? Explain?
• What is the Difference between PL/SQL Table & Nested Table?
• How Bulk Binding improve performance?
• How does Bulk Binding Work with SELECT or FETCH Statement?
• Why sometime it is necessary to use LIMIT clause in Bulk Binding?
• Is there a limit on the size of a PL/SQL block?
• What is a stored procedure?
• What are advantages of Stored Procedures?
• What are the modes of parameters that can be passed to a procedure?
• What is Overloading of procedures?
• What are actual and formal parameters?
• What are the types of Notation?
• How can you use pseudo-columns in procedural statements?
• When does a subprogram need recompilation?
• Which data types can be used for parameters within a procedure?
• Can stand-alone programs be overloaded?
• Is it Procedure Return values or Not ?
• How can a parameter be initialized in a procedure?
• Can default values be assigned to IN OUT parameters in a procedure?
• Can DDL commands be used within a procedure? If so, then how?
• What is the scope of the procedure defined within a PL/SQL block?
• Which privileges are required to execute a subprogram owned by another user?
• What happens if an exception is unhandled within a procedure?
• What is the significance of AUTHID property while creating a procedure?
• How can a compiled object code be viewed?
• Can an IN parameter be assigned a value within a procedure?.
• What are default parameter passing methods?
• What is NOCOPY hint?
• What is difference between a PROCEDURE & FUNCTION?
• Explain how procedures and functions are called in a PL/SQL block?
• Can you have two functions with the same name in a PL/SQL block?
• Can you have two stored functions with the same name?
• Can you call a stored function in the constraint of a table?
• What is Over Loading and what are its restrictions?
• Can functions be over loaded?
• Can 2 functions have same name & input parameters but differ only by return data type?
• Can there be multiple RETURN statements within a function?
• Can BOOLEAN data type be used in functions that are called from SQL statements?
• When should a procedure and a function be used?
• How can the text of a procedure or function specification be viewed?
• What are the different parameter modes, which can be used in the function specification?
• Can you invoke a stored function or procedure from the Oracle Forms in the database?
• Can a function be defined without a RETURN statement?
• How can the parameter list for a function or procedure be displayed?
• What is the meaning of ORA-06575: Package or function func_test is in an invalid state?
• What are the restrictions on functions that are called within SQL statements?
• How can you debug a procedure or function for errors?
• Explain PRAGMA AUTONOMOUS_TRANSACTION?
• What is Package?
• What are the advantages of creating packages?
• List some Oracle supplied Packages?
• What are the Disadvantages of packages?
• How PL/SQL supports Polymorphism?
• What are the rules of writing package?
• Mention what PL/SQL package consists of?
• Explain the difference between a FUNCTION, PROCEDURE and PACKAGE?
• How do you make a Function and Procedure as a Private?
• How can you generate debugging output from PL/SQL?
• Can you pass parameters in packages? How?
• What is a package spec and package body? Why the separation?
• What is UTL_File package?
• How many types of functions in UTL_FILE package?
• Can we load the data from table to table through utl_file package?
• Difference b/w sql*loader and utl_file package?
• What function you are using to load the data in UTL_FILE package?
• What are the exceptions are there in utl_file?
• What is trigger in oracle?
• What are the types of triggers?
• What are triggering attributes?
• Disadvantages of trigger in oracle?
• Difference between trigger and stored procedure
• Explain Row level and statement level triggers?
• What are cascading triggers?
• What is compound triggers ?
• How many types of database triggers can be specified on a table? What are they?
• Is it possible to use TCL Statements such a ROLLBACK or COMMIT in Database Trigger?
• Write the order of precedence for validation of a column in a table?
• What are the values of :new and :old in Insert/Delete/Update Triggers ?
• What are mutating triggers?
• What are constraining triggers?
• What are schema-level triggers?
• Does a USER_OBJECTS view have an entry for a trigger?
• What are the system privileges that are required by a schema to create a trigger on a table?
• What is a database event trigger?
• How can you view the errors encountered in a trigger?
• In what condition is it good to disable a trigger?
• Does USER_TRIGGERS have entry for triggers with compilation errors?
• How can triggers be used for the table auditing
• What are INSTEAD OF triggers?
• Is it possible to pass parameters to triggers?
• Can triggers stop a DML statement from executing on a table
• Can a SELECT statement fire a trigger?
• How can the performance of a trigger be improved?
• Can a view be mutating? If yes, then how?
• What are the events on which a database trigger can be based?
• What is a mutating table?
• Do triggers have restrictions on the usage of large data types, such as LONG & LONG RAW?
• Which data dictionary views have the information on the triggers that are available in db?