Showing posts with label PLSQL. Show all posts
Showing posts with label PLSQL. Show all posts

June 25, 2019

Top 20 Oracle PL/SQL Interview Questions and Answers


The following technical interview questions and answers are as per my experiences while working in Oracle PL/SQL projects. These may be useful for the freshers and experienced developers whenever facing PL/SQL interview.

 

Ques: 1. What is a mutating table error? How can you get this error?

Answer:

This error can come with triggers, whenever a trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables, so the database is selecting from one while updating the other. This is all because of the table is in middle of a transaction and referencing the same table again in the middle of the updating action causes the trigger to mutate.


Oracle Fusion Applications interview Questions and Answers


Ques: 2. What is the key difference between SQL and PL/SQL?

Answer:

SQL and PL/SQL are used to access data within Oracle databases. SQL is a limited language that allows you to directly interact with the database. You can write queries, manipulate objects and data of oracle database with SQL language. SQL doesn't include the programming concepts.

By using PLSQL, you can extract and manipulate the objects and data of oracle database. And can do all the things that normal programming languages can have, such as looping and controlled executions.

 

Oracle Accounts Payables Interview Questions and Answers

 

Ques: 3. What is the difference between Form triggers and Database level triggers?

Answer:

Form level triggers are use in forms and fire on any level like item level, row level or on block level on requirement of application. And database triggers are written in database directly and fire on behalf of any transaction like Insert, Update and delete on table automatically.

The key difference in form level triggers and database trigger is that form level trigger fire on user or application requirement and database triggers will fire automatically.

 

Oracle ADF Interview Questions and Answers                                 

 

Ques: 4. What is Pseudo column?

Answer:

Pseudo columns are database columns which are used for different purposes in oracle database.

ROWNUM, ROWID, SYSDATE, UID, USER, ORA_ROWSCN, SYSTIMESTAMP are pseudo columns in oracle database.

 

Oracle Access Manager Interview Questions and Answers


Ques: 5. What is exception handling in oracle? How can they be handled?

Answer:

When an error occurs, exception is raised normally, and execution is stopped. Control transfers to exception handling part. Exception is an error situation which arises during program execution. Exception handlers are routines written to handle the exception. The exceptions can be internally defined User-defined exception. In oracle, exception can be handled by using these exception statements:
EXCEPTION WHEN
DUP_VAL_ON_INDEX
NOT_LOGGED_ON
TOO_MANY_ROWS
VALUE_ERROR
NO_DATA_FOUND

 

Oracle Fusion HCM Interview Questions and Answers

 

Ques: 6. What is the key difference between OPEN-FETCH-CLOSE and FOR LOOP in CURSOR?

Answer:

A FOR LOOP in cursor implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.

While using the OPEN-FETCH-CLOSE, we have to explicitly open the query and closing the query.

 

Oracle SCM Interview Questions and Answers

 

Ques: 7. What is Dynamic SQL? How can we use it in Oracle?

Answer: 

Dynamic SQL is used by PL/SQL to execute Data Definition Language (DDL) statements, Data Control (DCL) statements, or Transaction Control statements within PL/SQL blocks. These statements can, probably will change from execution to execution means change at runtime. These statements are not stored within the source code but are stored as character variables in the program.

    The SQL statements are created dynamically at runtime by using variables. This is used either using native dynamic SQL or through the DBMS_SQL package. Dynamic SQL supports all SQL data types.

 

Oracle Financials Interview questions and Answers

 

Ques: 8. What is the key difference Between Row Level Trigger and Statement Level Trigger?

Answer:

Row level trigger executes once for each row after or before in the DML event.  It can be defined by using FOR EACH ROW.

Statement Level trigger executes once after or before the DML event, it doesn’t matter many rows are affected by the DML event.  

 

Oracle Cloud Interview Questions and Answers


Ques: 9. What are the various steps included in the compilation process of a PL/SQL block?

Answer:

In the compilation process of a PL/SQL block, the syntax checking, binding, and p-code generation are involved. Syntax checking involves checking PL/SQL code for compilation errors. Syntax errors have been corrected, a storage address is assigned to the variables that are used to hold data for Oracle. This process is called binding. After binding, P-code is generated for the PL/SQL block. P-code is a list of instructions to the PL/SQL engine. For named blocks, p-code is stored in the database, and it is used the next time the program is executed.

 

Oracle SQL Interview Questions and Answers


Ques: 10. What packages have Oracle provided to PLSQL developers?

Answer:

Oracle provides the DBMS_ series of packages to PLSQL developers to smooth the programming. The below packages, the developer should be aware of:

DBMS_DDL

UTL_FILE

DBMS_OUTPUT

DBMS_JOB

DBMS_SQL

DBMS_PIPE

DBMS_TRANSACTION

DBMS_LOCK

DBMS_ALERT

DBMS_UTILITY

 

Oracle RDMS Interview Questions and Answers

 

Ques: 11. How can you protect your PL/SQL source code?

Answer:

Oracle provides a binary wrapper utility that can be used to scramble PL/SQL source code. This utility was introduced in Oracle 7.

This utility use human-readable PL/SQL source code as input and writes out portable binary object code. It can larger than the original in size. The binary code can be distributed without fear of exposing your used algorithms and methods. Oracle will still understand and know how to execute the code. Be careful, there is no "decode" command available. So, always keep your source code saved.

 

BI Publisher Interview Questions and Answers

 

Ques: 12. What are SQLCODE and SQLERRM in PLSQL? What is their importance in programming in PLSQL?

Answer:

In PLSQL, SQLCODE returns the value of the error number for the last encountered error. Whereas the SQLERRM returns the actual error message for the last encountered error. They are used in exception handling in PLSQL. These are very useful for the WHEN OTHERS exception.


Oracle 10g Interview Questions and Answers 



Ques: 13. What do you understand by cursors in PLSQL?


Answer:

Cursor is a pointer variable in a memory and use for data manipulation operations in PLSQL. Basically, cursor is a private SQL memory area. It is also used to improve the performance of the PLSQL block.

Two types of cursors are:

a). Implicit cursor: Implicit cursors use oracles to manipulate the data manipulation operations internally and developers have no control on this type of cursor. We use sql%notfound and sql%rowcount cursor attributes in implicit cursor.

b). Explicit cursor: Explicit cursors are created by the developers and can control it by using the Fetch, Open and close keywords.

There are five types of cursors attributes in PLSQL:

1). %isopen: used to verify whether this cursor is open or not.
2). %found: If cursor fetch the data then %found return true.
3). %notfound: If cursor fetches not data then %notfound return true.
4). %rowcount: It return no. of rows that are in cursor.
5). %bulk_rowcount: It is same as %rowcount but it is used in bulk.

 

Ques: 14. What is the Index-By-Tables in PLSQL?

Answer:

Index-By-Tables is also known as Associative Arrays. These are the sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.

Syntax : TYPE tab_type_name IS TABLE OF element type

               INDEX  BY  BINARY_INTEGER;

Ex:  TYPE DeptTabTyp IS TABLE OF Dept%ROWTYPE

         INDEX BY BINARY_INTEGER;

         Dept_tab DeptTabTyp;

 

Ques: 15. What do you understand by bulk binding?

Answer:

The bulk binding technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. A DML operation statement can transfer all the elements of a collection in a single operation.

           For example, If the collection has 100 elements, It lets you to perform the 100 SELECT, INSERT, UPDATE, or DELETE statements using a single operation.              

 

Ques: 16. What do you under by AUTONOMOUS_TRANSACTION?

Answer:

The pragma AUTONOMOUS_TRANSACTION instructs the PL/SQL compiler to mark a PLSQL block as autonomous i.e. independent. Autonomous transactions let the compiler stop the main transaction to do DML operations, commit or roll back those operations, then resume the main transaction.

 

Ques: 17. What are the two components of LOB datatype in PLSQL?

Answer:

The two components of the LOB datatype in PLSQL are:

1). LOB locator: LOB Locator is a locator, which points to the location in the database where the actual value is stored. This value is stored along with the record in the table row and is like a pointer to the actual location of LOB value.

2). LOB value: It is referred to an actual image, file or value of the LOB datatype.

 

Ques: 18. What are cascading of triggers?

Answer:

If we insert data in one table and that table have trigger on it then trigger fire. And in this trigger, there is another table that we are using for inserting the data in it and this table has also trigger on it then this trigger also fire. This is called cascading of triggers.

 

Ques: 19. What do you understand by Table Functions in PLSQL?

Answer:

We can use the table function like the name of the database table. Table functions are functions that produce a collection of rows (either a nested table or a Varray) that can be queried like a physical database table or assigned to PL/SQL collection variable.

 

Ques: 20. What is the use of NOCOPY in PLSQL?

Answer:

In PLSQL programming, the NOCOPY is Compiler Hint. When the Parameters hold large data structures such as collections and records, all this time copying slows down the execution. To prevent this, we can specify NOCPY. This allows the PL/SQL Compiler to pass OUT and INOUT parameters by reference.


May 25, 2019

Top 20 Oracle SQL Interview Questions & Answers



Ques: 1. What are the differences between the DELETE, TRUNCATE and DROP commands? 

Ans: The differences between DELETE, TRUNCATE and drop commands are as:
  • DELETE is a DML command, but TRUNCATE and DROP are DDL commands.
  • DELETE removes the records based on an optional WHERE clause, but TRUNCATE command cannot have option to filter the records to be removed
  • While using DELETE, it is possible to roll back a transaction, but with TRUNCATE and DROP transaction, roll back is not possible.
  • DELETE does not reset the identity of the table. TRUNCATE resets the identity of table i.e. the auto-increment keys are reset to 1.

  • Triggers will get fired in DELETE, but no triggers will fired in TRUNCATE and DROP statements.

  • Can use DELETE commands, whenever you want to delete specific records. Use TRUNCATE when you want table to be cleaned again and use DROP when you don’t need that table anymore.
  • TRUNCATE is faster than DELETE and uses a lesser amount of system.
  • TRUNCATE cannot be used on a table referenced by a FOREIGN KEY constraint.

  • DROP command not only removes the table from the database. Its structures, indexes, privileges, and constraints also get removed.
Syntax for DELETE, TRUNCATE and DROP commands:

DELETE FROM table_name WHERE column_name = column_value;
DELETE FROM table_name;

TRUNCATE TABLE table_name;

DROP TABLE table_name;




Ques: 2. What are the different types of statements in SQL?

Ans: Below are the different types of statements in SQL.

a). Data Definition Language (DDL):

DDL are used to define and alter/modify the structure of table or database or schema. These commands manage the design and storage of DB objects. 

CREATE:  To create DB objects in the database.
ALTER: To alter the existing structure of DB objects.
DROP: To delete existing DB objects from database.
TRUNCATE: To remove all records from a table. It frees the space allocated to those records.
RENAME: To rename database objects

b). Data Manipulation Language (DML):

These statements are used to manipulate or manage the table’s records. The basic operations carried out on the tabular data like selection, insertion new records, delete, and update the existing records:

SELECT: To select specific data from a database.
INSERT: To insert new records into a table.
UPDATE: To update existing records.
DELETE: To delete all records from a table.
MERGE: Conditionally insert or update records in table.

c). Data Control Language (DCL):

DCL commands control the level of access that users have to the database objects. DCL statements are used to create roles, grant permission and control access to the database objects.
GRANT: To provide user access for the database.
REVOKE: To revoke/withdraw the access given to user.

D). Transaction Control Language (TCL):

TCL allows you to control and manage transactions to maintain the integrity of data in oracle.
COMMIT: To save the transaction in database.
SAVEPOINT: To identify a point in a transaction to which you can rollback at a later point in time whenever required.
ROLLBACK: It restores the database to original since the last COMMIT.


Ques: 3. What are the differences between Primary Key, Unique Key, and Foreign Key?

Ans: The main differences between Primary Key, Unique Key, and Foreign Key are as:
  • The primary key cannot have a NULL value. Unique key can have one NULL values and Foreign key can have multiple NULL values. But the Foreign key shouldn’t have a null value. Else, the system will consider it as an orphan record.
  • A DB table can have only one primary key. But a table can have more than one Unique key and Foreign keys.
  • A Foreign key does not automatically create an index, clustered or non-clustered. You must manually create an index on the foreign key.
  • By default, Primary key supports clustered index. The data in the database table are physically organised in the sequence of clustered index. By default, Unique key is a unique non-clustered index.
  • Primary key can be related to another table as a Foreign Key. Unique key is not related to another table as a Foreign Key.
  • There are advantages of having a foreign key supported with a clustered index, but you get only one per table. The advantage using a clustered index is that, on selecting the parent plus all child records, it can bring all child records next to each other.


Oracle ADF Interview Questions and Answers                                 


Ques: 4. What is the main purpose of a Subquery?

Ans: To provide the result of a secondary query to main query for processing the desired records, This is the main purpose of subquery. A Subquery is always executed first and passes its result to the main query. This data acts as a filter condition in the main query to further restrict the data to be retrieved. A Subquery also called as Nested query is a query within another SQL query and embedded within the WHERE clause. Subqueries work with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, and BETWEEN.
The important properties of a Subquery can be:
  • It can contain more than one column in SELECT clause only if the main query has multiple columns.
  • We cannot use ORDER BY in a Subquery.
  • Use GROUP BY which performs the same function as ORDER BY.
  • Always write a Subquery within a parenthesis.
  • We cannot use BETWEEN operator with a subquery but, can use within a Subquery.
  • You can nest Subqueries up to 32 levels.
Ques: 5. What are the different types of subquery?

Ans: In SQL, there are two types of subquery:

1. Correlated subquery: In a SQL database query, a correlated subquery is a subquery that uses values from the outer query in order to complete. Because a correlated subquery requires the outer query to be executed first, the correlated subquery must run once for every row in the outer query.

2. Non-Correlated Subquery: A Non-correlated subquery is a subquery in which both outer query and inner query are independent to each other.


Ques: 6. What is the difference between Union and Union ALL?

Ans: UNION and UNION ALL merges the contents of two structurally-compatible tables into a single combined table. 
  • The difference between UNION and UNION ALL set operators, is that UNION will remove duplicate records whereas UNION ALL will include duplicate records of the table.
  • The performance of UNION ALL is better than UNION as UNION requires the server to do additional work of removing duplicates. 
For performance reasons, it is recommended to use UNION ALL is the scenarios when it is certain that there will be no duplicates or cases where having duplicates is not a problem.

Ques: 7. In SQL, what is the main difference between CHAR and VARCHAR2 datatype?

Ans: Char and Varchar2, both are used for characters datatype but varchar2 is used for character strings of variable length whereas Char is used for strings of fixed length.

For example, char (20) can only store 20 characters and will not be able to store a string of any other length whereas varchar2(20) can store any data string of length up to 20, i.e. 18,15,8,2.

Ques: 8. Can you List some of the popular built-in functions, available in SQL?

Ans: A lot of built-in functions are available in SQL. Some of the popular and important built-in functions are as:
  • AVG(): Returns the average value.
  • COUNT(): Returns the number of rows.
  • REPLACE(): Replaces the given value in specified string.
  • MONTHS_BETWEEN (): Number of months between the two dates.
  • MAX(): It gives the largest value as output.
  • MIN(): It gives the smallest value as output.
  • SUM(): Outputs the Sum.
  • UPPER(): Converts a value to upper case.
  • LOWER(): Converts a value to lower case.
  • SUBSTR(): Returns a substring from the specified position and number of characters; default returns to end of string.
  • LENGTH(): Returns the length of a text field.
  • ROUND(): Round Off a numeric field to the number of decimals specified.
  • INSTR(): Returns the position of the nth occurrence of string2 in given string.
  • ADD_MONTHS (d, n): Add the number of months in given date.
  • TO_CHAR (): Return string of number converted to character string as specified by the format. 
Ques: 9. Are NULL values same as that of zero or a blank space? 

Ans: A field with a NULL value is a field with no value. NULL value represents a value which is unknown, unavailable, not assigned or not applicable. A NULL value is not at all same as that of zero or a blank space, whereas a zero is a number and blank space is a character. . A field with a NULL value is one that has been left blank during record creation.



Ques: 10. What is the key difference between ‘HAVING’ CLAUSE and ‘WHERE’ CLAUSE?

Ans: WHERE clause can be used only with SELECT statement after table name. HAVING clause is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause. WHERE clause is optional in SELECT statement, but if it used it will be applied to each row to fetch data from table. Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query.


Oracle RDMS Interview Questions and Answers


Ques: 11. What is Self-Join and Cross-Join?


Ans: A self-join is a join in which a table is joined with itself, especially when the table has a Foreign Key which references its own Primary Key. A Cross join produces a result set which is the number of rows in the first table multiplied by a number of rows in the second table if no WHERE clause is used along with Cross join. This kind of result is known as Cartesian Product. If suppose, where clause is used in cross join then the query will work like an Inner join.



Ques: 12. What is Collation? And what are the types of collation sensitivity?

Ans: Character data is sorted using rules that define the correct character sequence along with options for specifying case-sensitivity, character width, accent marks and Kana character types. Collation is defined as a set of rules that determine how character data can be sorted as well as compared. Different types of collation sensitivity are as follows:
Case Sensitivity: A and a and B and b.
Kana Sensitivity: Japanese Kana characters.
Width Sensitivity: Single byte character and double byte character.
Accent Sensitivity.





Ques: 13. What is difference between unique and distinct?

Ans: Functionality wise, there is no difference between Unique and distinct keywords. But the only difference is that unique is applied before insertion and retrieval. It consists of non-duplicate values. If unique constraint is given it does not take duplicate values. Distinct is used in retrieval it gives the suppressed row (for example, if two rows are same it will show single row and non-duplicate row) therefore distinct is the combination of suppressed duplicate and non-duplicate rows.

So, specify DISTINCT or UNIQUE if you want Oracle to return only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list.


Ques: 14. What will be the output of following Query?

Select case when NULL=NULL then ‘Joan’ Else ‘Emily’ from dual;

Ans: In SQL null value cannot not be equal to itself. So, NULL=NULL is false and the output of above query is ‘Emily’.


Ques: 15. What do you understand by “REFRESH” options of Materialized view?

Ans:
REFRESH ON COMMIT:
This option commits the data in materialized views immediately after data insertion and commits in table.  This option is known as incremental refresh option. Materialized View is not fully refreshed with this option.

REFRESH ON DEMAND:
Using this option, you can add the condition for refreshing data in materialized views. You can refresh the data using fast (incremental approach), Complete and Force options.


Ques: 16. What is the main difference between 'BETWEEN' and 'IN' condition operators?

Ans: In SQL, BETWEEN operator is used to display rows based on a range of values in a row whereas the IN operator is used to check for values contained in a specific set of values.
Example of BETWEEN:
SELECT * FROM Employees where EMPLOYEE_ID BETWEEN 100 AND 150;

Example of IN: 

SELECT * FROM Employees where EMPLOYEE_ID IN (100,110,150);


Ques: 17. What is the difference Between Substr And Instr ?

Ans: INSTR (String1,String2(n,(m)),INSTR returns the position of the mth occurrence of the string 2 instring1. The search begins from nth position of string1.SUBSTR (String1 n,m)SUBSTR returns a character string of size m in string1, starting from nth position of string1.

For example:
SELECT INSTR('Oracle SQL Developer','e',1,2) FROM DUAL;
Output: 13

SELECT SUBSTR('Oracle SQL Developer',8,3) FROM DUAL;
Output: SQL


Ques: 18. What is a View? What are its advantages and disadvantages?

Ans: View is a pre-complied SQL query which is used to select data from one or more tables. A view is like a table, but it doesn't physically store data. View is a good way to present data in a format if you use that query quite often. View can also be used to restrict users from accessing the tables directly. A View is a virtual table which contains data from one or more tables. It selects only required values thus restricting the access to table data. And it also makes complex queries a bit easier.

Advantages of using Views:
  • It enables viewing data without storing the data in an object.
  • Restrict the view of a table by hiding some of its columns.
  • Join two or more tables and display it as a single object.
  • Restrict the access of a table so that nobody can insert rows in the table without permission.
Disadvantages of Views:
  • Cannot apply DML statements on it.
  • A View becomes inactive if a table that is a part of View gets dropped.
  • It is an object and hence, it consumes memory.

Ques: 19. What is the main difference between SQL and PL/SQL?

Ans: SQL (Structured Query Language) is a non-procedural language that interacts with the database and is used for database manipulation using the Data Definition Language (DDL) and Data Manipulation Language (DML) statements. Control statements cannot be used in SQL, which is compiled and executed statement by statement at the run time (late-binding).

PL/SQL is a programming language that allows the usage of Cursor Control statements and Transaction Control statements, such as if...then...else. It integrates with SQL functions and statements to interact and manipulate with the database. Unlike SQL, PL/SQL statements are compiled and processed as a block of code into the machine-readable code, which is executed at run time (early binding); and therefore, improves the performance.


Ques: 20. What is a coalesce function?

Ans: Coalesce function is used to return the first not NULL value out of the multiple values or expressions passed to the coalesce function as parameters.

Example-
COALESCE(NULL, NULL, 5, 'Technical') will return the value 5.

COALESCE(NULL, NULL, NULL) will return NULL value as no not NULL value is encountered in the parameters list.