[PL/SQL] Oracle interview questions

What is the difference between … and … ?

binh12A3

10 min readSep 2, 2021

1. What is SQL, What are the types of SQL statements ?

2. What is the difference between DELETE and TRUNCATE ?

3. How to get unique records without using DISTINCT in ORACLE ?

We’ll firgue out 9 different ways

4. What is the difference between DML and DDL ?

5. What is the difference between DECODE and CASE ?

We can’t use DECODE as a single row function
DECODE can have multiple datatypes while CASE…WHEN…THEN… doesn’t (we can resolve by using CASE WHEN…THEN…)
CASE can standalone and can have more conditions

6. What is the difference between REPLACE and TRANSLATE function ?

7. Set operators : UNION, UNIONALL, INTERSECT, MINUS

8. SQL loader vs EXTERNAL TABLE (Oracle 18c)

SQL Loader is a utility provided by Oracle to load data from external files into a target table in database by using command line.

With EXTERNAL TABLE, we can create an external table, and inline possibility to read/view data from an external flat file in realtime.

9. What is SUBQUERY and its types ?

10. NULL and Order by Clause ?

For grouping function, it’ll ignore the NULL value, except COUNT(*)

11. What is difference between RANK and DENSE RANK as Aggregate Function and Analytical Function ?

DENSE_RANK() will be continous while RANK() doesn’t

You can use RANK() and DENSE_RANKS() with both Aggregate (grouping) or Analytical function

And you could compute ranks for each departments by using “PARTITION BY”

12. Query was running fine yesterday but its very slow today, how would you start your analysis ?

13. How to delete duplicated records ?

For example, here we need to remove 1 record 1002, and 2 records 1004

We have several methods :

Using MAX(rowid) then delete records not in this list

Using subquery

Using ROW_NUMBER()

Using temporary table + drop/rename (not recommended)

Added a temp column

14. What is pragma autonomous transaction ?

Let’s make some scenarios

Pragma autonomous will create a child and independent transaction which means the commit in child transaction won’t affect to the main transaction. The flow will be : main transaction → child transaction, when child transaction complete → it’ll back to the main transaction.

15. Realtime usecase for pragma autonomous transaction ?

15.1 ERROR Logging

For example, when there is a raised error we’ll insert into log table then rollback the currect transaction. Then the error log will be rollbacked as well.

In this case, to log the error log, we’ll create an autonomous_transaction procedure

15.2 COMMIT inside trigger

In the trigger, it’s not possible to do a COMMIT directly. You could do it by using autonomous_transaction

15.3 Calling a function having “DML statement” in SELECT statement

It’s not recommended in a real usage, but it’s good to know that you could do it by using autonomous_transaction.

16. What are the advantages of packages ?

17. Oracle constraint with DEFERRABLE NOVALIDATE option ?

If you have a table with duplidated rows, then you create a primary key, then it’ll throw an error. To resolve it, you could use “DEFERRABLE NOVALIDATE”, Oracle will skip checking existing rows in the table. The constraint will check for subsequent/next insert only.

18. Does “creating a primary key constraint” always create an index automatically ?

Yes, but not always. Oracle will create a “UNIQUE” index only when there is no index on that column yet.

For example, here we create a index “t1” on column “empno” then Oracle doesn’t create a “UNIQUE” index anymore.

19. Will “Dropping constraint” will drop underlying index ?

Yes, but not always. The associated indexes will be dropped only when they were created automatically together when the constrain was created. Else if the index was created manually, then it won’t be dropped.

20. Will “Dropping table” will drop underlying index ?

Yes, when we drop the table, all the associated indexes, constraints, records will be automatically dropped.

21. Can a “primary key” create NONUNIQUE index ?

Yes, in some scenarios, primary key can create non-unique index by using “DEFERRABLE NOVALIDATE” keyword when create a primary key.

22. We use “UNIQUE INDEX” or “UNIQUE CONSTRAINT” to enforce uniqueness of a column. What’s the difference? And when to use what ?

23. How to exclude duplicate records while insertion?

There are 2 ways :

Method 1 : using “DML ERROR Logging Tables”

Here we can do an INSERT with “LOG ERRORS INTO” keyword, then there is no raised error anymore.

And errors during the insert are captured in to log table which we created above.

Method 2 : using hint “IGNORE_ROW_ON_DUPKEY_INDEX (table_name(primary_column_name))”

The drawback of this method is it just simply ignore the errors when inserting, so we can’t capture which error occured.

NOTE :

These above 2 ways works on the constrain. Suppose the table doesn’t have unique constraint, primary key or it’s disable. Then the above 2 ways won’t work, so we have to use below methods.

Using MERGE and NOT MATCHED (not present)

2. Using subquery

24. Oracle FOR vs FORALL | Oracle FORALL advantages and limitations

For example, we want to read from a collection then insert into table.

FOR : element by element, the loop will try 5 times, and the insert statement will get executed for 5 times.

FORALL : we’ll bind all information in collection into INSERT statement. The excecute this insert statement one.

25. Oracle MERGE statement

MERGE statement is a combination of INSERT/UPDATE/DELETE.

For example, here we want to make “emp_tgt” table in sync with “emp_src” table.

Normally, to do that we need 2 statements, 1 UPDATE and 1 INSERT. Then instead of doing 2 statements, we can combine it into 1 statement MERGE function.

To use DELETE in MERGE, for example when the flag “RESIGNED” = “Y” then we’ll make a DELETE

Oracle syntax:

26. Oracle MERGE statement Error Handling

When there is an error i.e in INSERT, then all data in all branches (UPDATE, INSERT) of MERGE will be rollbacked.

To handle the exception, we’ll log it into DML Error Logging table

27. Oracle TABLE function

You can use a collection as a data source by using TABLE() function

In Oracle 19c, we don’t need to write down TABLE keyword

You can use a function returns collection as a data source

28. Oracle PIPELINED TABLE function

TABLE() will load all data into collection before returning, so it’ll take more time. While PIPELINED will return each rows immediately when the value is ready. For example, we have a lot of logic in a loop, approx 1 seconds for each rows. And you only want to get 5 rows, then the TABLE() will wait for 100s. While the PIPELINE just wait for 5s only.