Here are PL/SQL interview questions and answers for fresher as well as experienced candidates to get their dream job.
1) What is PL SQL?
Oracle PL/SQL is an extension of SQL language that combines the data manipulation power of SQL with the processing power of procedural language to create super powerful SQL queries. PL/SQL ensures seamless processing of SQL statements by enhancing the security, portability, and robustness of the Database.
PL/SQL stands for “Procedural Language extensions to the Structured Query Language.”
2) Differentiate between % ROWTYPE and TYPE RECORD.
% ROWTYPE is used when a query returns an entire row of a table or view.
TYPE RECORD on the other hand, is used when a query returns column of different tables or views.
Eg. TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)
e_rec smp %ROWTYPE
Cursor c1 is select smpno,dept from smp;
e_rec c1 %ROWTYPE
3) Explain uses of cursor.
Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows.
4) Show code of a cursor for loop.
Cursor declares %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.
FOR smp_rec IN C1 LOOPtotalsal=totalsal+smp_recsal;ENDLOOP;
5) Explain the uses of database trigger.
A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :
1) Audit data modifications.
2) Log events transparently.
3) Enforce complex business rules.
4) Maintain replica tables
5) Derive column values
6) Implement Complex security authorizations
6) What are the two types of exceptions.
Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.
7) Show some predefined exceptions.
8) Explain Raise_application_error.
It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.
9) Show how functions and procedures are called in a PL SQL block.
Function is called as a part of an expression.
Procedure is called as a statement in PL/SQL.
10) Explain two virtual tables available at the time of database trigger execution.
Table columns are referred as OLD.column_name and NEW.column_name.
For INSERT related triggers, NEW.column_name values are available only.
For DELETE related triggers, OLD.column_name values are available only.
For UPDATE related triggers, both Table columns are available.
11) What are the rules to be applied to NULLs whilst doing comparisons?
1) NULL is never TRUE or FALSE
2) NULL cannot be equal or unequal to other values
3) If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)
12) How is a process of PL SQL compiled?
Compilation process includes syntax check, bind and 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 hold data. 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 next time it is executed.
13) Differentiate between Syntax and runtime errors.
A syntax error can be easily detected by a PL/SQL compiler. For eg, incorrect spelling.
A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.
14) Explain Commit, Rollback and Savepoint.
For a COMMIT statement, the following is true:
- Other users can see the data changes made by the transaction.
- The locks acquired by the transaction are released.
- The work done by the transaction becomes permanent.
A ROLLBACK statement gets issued when the transaction ends, and the following is true.
- The work done in a transition is undone as if it was never issued.
- All locks acquired by transaction are released.
It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.
15) Define Implicit and Explicit Cursors.
A cursor is implicit by default. The user cannot control or process the information in this cursor.
If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.
16) Explain mutating table error.
It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.
17) When is a declare statement required?
DECLARE statement is used by PL SQL anonymous blocks such as with stand alone, non-stored procedures. If it is used, it must come first in a stand alone file.
18) How many triggers can be applied to a table?
A maximum of 12 triggers can be applied to one table.
19) What is the importance of SQLCODE and SQLERRM?
SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.
20) If a cursor is open, how can we find in a PL SQL Block?
the %ISOPEN cursor status variable can be used.
21) Show the two PL/SQL cursor exceptions.
22) What operators deal with NULL?
NVL converts NULL to another specified value.
IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.
23) Does SQL*Plus also have a PL/SQL Engine?
No, SQL*Plus does not have a PL/SQL Engine embedded in it. Thus, all PL/SQL code is sent directly to database engine. It is much more efficient as each statement is not individually stripped off.
24) What packages are available to PL SQL developers?
DBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.
25) Explain 3 basic parts of a trigger.
- A triggering statement or event.
- A restriction
- An action
26) What are character functions?
INITCAP, UPPER, SUBSTR, LOWER and LENGTH are all character functions. Group functions give results based on groups of rows, as opposed to individual rows. They are MAX, MIN, AVG, COUNT and SUM.
27) Explain TTITLE and BTITLE.
TTITLE and BTITLE commands that control report headers and footers.
28) Show the cursor attributes of PL/SQL.
%ISOPEN: Checks if the cursor is open or not
%ROWCOUNT: The number of rows that are updated, deleted or fetched.
%FOUND: Checks if the cursor has fetched any row. It is true if rows are fetched
%NOT FOUND: Checks if the cursor has fetched any row. It is True if rows are not fetched.
29) What is an Intersect?
Intersect is the product of two tables and it lists only matching rows.
30) 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.
31) How would you reference column values BEFORE and AFTER you have inserted and deleted triggers?
Using the keyword “new.column name”, the triggers can reference column values by new collection. By using the keyword “old.column name”, they can reference column vaues by old collection.
32) What are the uses of SYSDATE and USER keywords?
SYSDATE refers to the current server system date. It is a pseudo column. USER is also a pseudo column but refers to current user logged onto the session. They are used to monitor changes happening in the table.
33) How does ROWID help in running a query faster?
ROWID is the logical address of a row, it is not a physical column. It composes of data block number, file number and row number in the data block. Thus, I/O time gets minimized retrieving the row, and results in a faster query.
34) What are database links used for?
Database links are created in order to form communication between various databases, or different environments like test, development and production. The database links are read-only to access other information as well.
35) What does fetching a cursor do?
Fetching a cursor reads Result Set row by row.
36) What does closing a cursor do?
Closing a cursor clears the private SQL area as well as de-allocates memory
37) Explain the uses of Control File.
It is a binary file. It records the structure of the database. It includes locations of several log files, names and timestamps. They can be stored in different locations to help in retrieval of information if one file gets corrupted.
38) Explain Consistency
Consistency shows that data will not be reflected to other users until the data is commit, so that consistency is maintained.
39) Differ between Anonymous blocks and sub-programs.
Anonymous blocks are unnamed blocks that are not stored anywhere whilst sub-programs are compiled and stored in database. They are compiled at runtime.
40) Differ between DECODE and CASE.
DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.
select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from smp where smpno in (10,12,14,16);
This statement returns an error.
CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.
41) Explain autonomous transaction.
An autonomous transaction is an independent transaction of the main or parent transaction. It is not nested if it is started by another transaction.
There are several situations to use autonomous transactions like event logging and auditing.
42) Differentiate between SGA and PGA.
SGA stands for System Global Area whereas PGA stands for Program or Process Global Area. PGA is only allocated 10% RAM size, but SGA is given 40% RAM size.
43) What is the location of Pre_defined_functions.
They are stored in the standard package called “Functions, Procedures and Packages”
44) Explain polymorphism in PL SQL.
Polymorphism is a feature of OOP. It is the ability to create a variable, an object or function with multiple forms. PL/SQL supports Polymorphism in the form of program unit overloading inside a member function or package..Unambiguous logic must be avoided whilst overloading is being done.
45) What are the uses of MERGE?
MERGE is used to combine multiple DML statements into one.
Syntax : merge into tablename
when not matched then
when matched then
46) Can 2 queries be executed simultaneously in a Distributed Database System?
Yes, they can be executed simultaneously. One query is always independent of the second query in a distributed database system based on the 2 phase commit.
47) Explain Raise_application_error.
It is a procedure of the package DBMS_STANDARD that allow issuing a user_defined error messages from the database trigger or stored sub-program.
48) What is out parameter used for eventhough return statement can also be used in pl/sql?
Out parameters allows more than one value in the calling program. Out parameter is not recommended in functions. Procedures can be used instead of functions if multiple values are required. Thus, these procedures are used to execute Out parameters.
49) How would you convert date into Julian date format?
We can use the J format string :
SQL > select to_char(to_date(’29-Mar-2013′,’dd-mon-yyyy’),’J’) as julian from dual;
50) Explain SPOOL
Spool command can print the output of sql statements in a file.
select smp_name, smp_id from smp where dept=’accounts’;
51) Mention what PL/SQL package consists of?
A PL/SQL package consists of
- PL/SQL table and record TYPE statements
- Procedures and Functions
- Variables ( tables, scalars, records, etc.) and constants
- Exception names and pragmas for relating an error number with an exception
52) Mention what are the benefits of PL/SQL packages?
It provides several benefits like
- Enforced Information Hiding: It offers the liberty to choose whether to keep data private or public
- Top-down design: You can design the interface to the code hidden in the package before you actually implemented the modules themselves
- Object persistence: Objects declared in a package specification behaves like a global data for all PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another module
- Object oriented design: The package gives developers strong hold over how the modules and data structures inside the package can be used
- Guaranteeing transaction integrity: It provides a level of transaction integrity
- Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.
53) Mention what are different methods to trace the PL/SQL code?
Tracing code is a crucial technique to measure the code performance during the runtime. Different methods for tracing includes
- DBMS_SESSION and DBMS_MONITOR
- trcsess and tkproof utilities
54) Mention what does the hierarchical profiler does?
The hierarchical profiler could profile the calls made in PL/SQL, apart from filling the gap between the loopholes and the expectations of performance tracing. The efficiencies of the hierarchical profiler includes
- Distinct reporting for SQL and PL/SQL time consumption
- Reports count of distinct sub-programs calls made in the PL/SQL, and the time spent with each subprogram call
- Multiple interactive analytics reports in HTML format by using the command line utility
- More effective than conventional profiler and other tracing utilities
55) Mention what does PLV msg allows you to do?
The PLV msg enables you to
- Assign individual text message to specified row in the PL/SQL table
- It retrieves the message text by number
- It substitutes automatically your own messages for standard Oracle error messages with restrict toggle
- Batch load message numbers and text from a database table directly PLV msg PL/SQL table
56) Mention what is the PLV (PL/Vision) package offers?
- Null substitution value
- Set of assertion routines
- Miscellaneous utilities
- Set of constants used throughout PL vision
- Pre-defined datatypes
57) Mention what is the use of PLVprs and PLVprsps?
- PLVprs: It is an extension for string parsing for PL/SQL, and it is the lowest level of string parsing functionality
- PLVprsps: It is the highest level package to parse PL/SQL source code into separate atomics. It relies on other parsing packages to get work done.
58) Explain how you can copy a file to file content and file to PL/SQL table in advance PL/SQL?
With a single program call – “fcopy procedure”, you can copy the complete contents of one file into another file. While to copy the contents of a file directly into a PL/SQL table, you can use the program “file2pstab”.
59) Explain how exception handling is done in advance PL/SQL?
For exception handling PL/SQl provides an effective plugin PLVexc. PLVexc supports four different exception handling actions.
- Continue processing
- Record and then continue
- Halt processing
- Record and then halt processing
For those exceptions that re-occurs you can use the RAISE statement.
60) Mention what problem one might face while writing log information to a data-base table in PL/SQL?
While writing log information to a database table, the problem you face is that the information is only available only once the new rows are committed to the database. This might be a problem as such PLVlog is usually deployed to track errors and in many such instances the current transaction would fail or otherwise needed a rollback.
61) Mention what is the function that is used to transfer a PL/SQL table log to a database table?
To transfer a PL/SQL table log a database log table function “PROCEDURE ps2db” is used.
62) When you have to use a default “rollback to” savepoint of PLVlog?
The default “rollback to” savepoint of PLVlog is used when the users has turned on the rollback activity and has not provided an alternative savepoint in the call to put_line. The default savepoint is initialized to the c none constant.
63) Why PLVtab is considered as the easiest way to access the PL/SQL table?
The PL/SQL table are the closest to arrays in PL/SQL, and in order to access this table you have to first declare a table type, and then you have to declare PL/SQL table itself. But by using PLVtab, you can avoid defining your own PL/SQL table type and make PL/SQL data-table access easy.
64) Mention what does PLVtab enables you to do when you showthe contents of PL/SQL tables?
PLVtab enables you to do following things when you show the contents of PL/SQL tables
- Display or suppress a header for the table
- Display or suppress the row numbers for the table values
- Show a prefix before each row of the table
65) Explain how can you save or place your msg in a table?
To save msg in a table, you can do it in two ways
- Load individual messages with calls to the
- Load sets of messages from a database table with the
66) Mention what is the use of function “module procedure” in PL/SQL?
The “module procedure” enables to convert all the lines of code in a definite program unit with one procedure call. There are three arguments for modules
67) Mention what PLVcmt and PLVrb does in PL/SQL?
PL/Vision offers two packages that help you manage transaction processing in PL/SQL application. It is PLVcmt and PLVrb.
- PLVcmt: PLVcmt package wraps logic and complexity for dealing with commit processing
- PLVrb: It provides a programmatic interface to roll-back activity in PL/SQL
These interview questions will also help in your viva(orals)
PL/SQL Interview Questions and Answers.
|No error handling procedures are there in SQL.||PL/SQL supports customized error handling.|
- What is Database? A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. ...
- What is DBMS? ...
- What is RDBMS? ...
- What is SQL? ...
- What is the difference between SQL and MySQL? ...
- What are Tables and Fields? ...
- What are Constraints in SQL?
Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them.
Advantages of PL/SQL
- Tight Integration with SQL.
- High Performance.
- High Productivity.
- Support for Object-Oriented Programming.
- Support for Developing Web Applications.
PL/SQL enables users to send a block of statements to the database, which significantly reduces traffic between the app and the database. It is also possible to run PL/SQL on any operating system (OS) or platform where Oracle Database runs.
The PRAGMA keyword is used to signify that the remainder of the PL/SQL statement is a pragma, or directive, to the compiler. Also called apseudoinstruction, a pragma simply passes information to the compiler rather than getting transformed into a particular execution.
Don't miss to read out even a single PL SQL interview questions. We curated this post with thirty most important questions for DBA/QA engineers.
It supports executing from the external programming tools to operate on databases.. of data types, programming constructs, functions, and procedures.. PL SQL Interview Questions.. C. It defines all variables, cursors, subprograms, and other elements to be used in the program.. Use employee.lname%rowtype C. Search data type of LNAME column in EMPLOYEE table to use it.. D. Declare it to be of type LONG.. END LOOP C. IF-THEN D. EXIT. C. The latest implicit cursor is called the SQL cursor and has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.. A VARRAY type is created with the CREATE VARRAY statement, at the schema level.. Create a bind variable.. A. BFILE is used to store large binary objects in operating system files outside the database.. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package.. That’s why we came up with this blog post on the top thirty PL SQL interview questions for fresher DBA/QA engineers.
This blog contains the Top 64 frequently asked PHP Interview Questions and answers in 2022 for freshers & experienced which will help in cracking your PHP interview.
Prepare Better with these Best Oracle PL/SQL Questions ,& walk away with top Interview Tips ✔️Test your knowledge ✔️Market Trend Topics ✔️100% Success | Brush UP!
Difference View is a logical table View can hold the query We can’t create indexes on view View will create security purpose Mv is a physical table Mv can hold the query with refresh data We can create indexes on mv Mv will create performance issues. %ROWCOUNT— returns number of records processed from cursor syntax is SQL %ROWCOUNT %FOUND—- returns TRUE if record is fetched successfully else FALSE, syntax is SQL%FOUND %NOTFOUND– returns TRUE if record is not fetched successfully else FALSE syntax is SQL%NOTFOUND Attributes Of a Explicit Cursor %ISOPEN—returns TRUE if cursor is open else FALSE.. There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, STATEMENT, TABLE, INSERT, UPDATE, DELETE and ALL key words: For eg: BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT. The DUAL table is a table with a single row and a single column used where a table is syntactically required.. Data Locks : Row Level and Table Level Row Level : Exclusive Locks Table Level. As a TRUNCATE statement deletes rows from a table (or clustered table), triggers associated with the table are not fired.. USE_NL : The USE_NL hint causes ORACLE to join each specified table to another row source with a nested loop join using the specified table as the inner table.. For user-defined exceptions, SQLCODE returns +1 and SQLERRM returns the message “User-Defined Exception” unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message.. However, database administrators can query these tables and can create views on the tables and grant access to those views to other users.. Internals of Snapshot Creation: When a snapshot is created, several operations are performed internally by ORACLE: • ORACLE (at the snapshot node) creates a table to store the rows retrieved by the snapshot’s defining query; this is the snapshot’s base table.. • ORACLE creates a read-only view on the SNAP$ table (base table) for queries issued against the snapshot.. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
Looking for GCP (Google Cloud Platform) interview questions? Here is the list of Top Google Cloud Interview Questions and Answers in 2022 for freshers and experienced.
Cloud computing is the computational power, which is on the cloud at all times.. It offers access to its cloud systems and computing services.. Google Compute Engine Google Cloud Container Engine Google Cloud Storage Google Cloud App Engine Google Cloud Dataflow Google Cloud Machine Learning Engine Google BigQuery Service Google Cloud Job Discovery Google Cloud Endpoints Google Cloud Test Lab. Google Cloud Engine is the basic component of the Google Cloud Platform.. It integrates with other GCP technologies (Google Cloud Storage, Google App Engine, Google BigQuery, etc.). There are different service accounts offered by Google but mainly, users prefer to use Google Cloud Platform Console and Google Compute Engine service accounts.. Accessing Google Cloud APIs Cloud APIs can be easily accessed with the client libraries from the server applications.
60 Oracle Exadata Interview Questions and Answers ➔ Real-time Case Study Questions ✔️Frequently Asked ✔️Curated by Experts ✔️Download Sample Resumes
The flash cache is a hardware component configured in the Exadata storage cell server which delivers high performance in reading and writes operations.. A cell or Exadata Storage server cell is a combination of Disk Drives put together to store user data.. Each Cell Disk corresponds to a LUN (Logical Unit) which has been formatted by the Exadata Storage Server Software.. A total of 14 Storage server cells communicate with the storage and push the requested data from the storage to the compute nodes.. It has 96GB of memory per database server node with a total of 7 Storage server cells. Hardware level Storage Server Cells High-Speed Infiniband Switch Software level Smart Scan, Flash Cache Hybrid Columnar Compression IORM (I/O Resource Manager)
1) What is PHP? PHP is a web language based on scripts that allow developers to dynamically create generated web pages. 2) What do the initials of PHP stand for? PHP means PHP: Hypertext Preprocessor.
Oracle Apps Technical Interview Questions and Answers ➔ Real-time Case Study Questions ✔️Frequently Asked ✔️Curated by Experts ✔️Download Sample Resumes.
Data groups: is a group of modules to be made accessible by the user through Responsibility System admin. FND_STANDARD.FORM_INFO 6 Provides information about the form.. A handler is a group of packaged procedures that are used by Oracle Applications to organize.. A data group is a group of Oracle applications and the Oracle ID's of each application Oracle ID grants access privileges to tables in an Oracle Database Data group determines which Oracle Database accounts a responsibilities forms, concurrent programs and reports connect to.. Responsibility defines Applications Privileges Responsibility is a level of authority in Oracle Applications that lets users only those Oracle Applications functions and data appropriate to their roles in an organization.. Each user has a list of one or more responsibilities and several users can share the same responsibility 22 A specific application or a set of applications.. Responsibility: A responsibility is a set of authority in Oracle Apps that lets users access only that functionality of the application appropriate to their roles.. Request Group : it is a collection of reports or concurrent programs.. Ans: An environment variable that denotes the installation directory for Oracle Application Object Library and your other Oracle applications.. User Profile: Provide Oracle Apps with standard information that describes a user, Application, Responsibility, and site.. Ans: Yes.. You can also use value sets that you use in your flex fields to validate Your report parameters.. Ans: Operating Unit: An Organization that uses Oracle Cash Management, Order Management, and Shipping Execution, Oracle Payables, Oracle Purchasing, and Oracle Receivables.. Ans: It is data security functionality in Oracle 10.6 and above.. The benefit is it enables multiple operating units to use a single installation of various modules while keeping transaction data separate and secure by operating unit.