Oracle Interview Question and Answers Part – 1

Oracle Interview Question and Answers Part – 1

Q. What will happen after commit statement ?

Ans -

1. All changed data will be saved in permanent database

2. Locks on the database tables are leased

3. While processing the cursors

The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.

The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

Q. How many indexes can be created on a table?

Ans - For all possible combination of columns in a table we can create indexes.

Q. What is force view?

Ans - Oracle create a force view regardless of whether the base tables of the view or the referenced object types exist or the owner of the schema containing the view has privileges on them, means we can create a view on a table x even we have not defined the table x.

CREATE FORCE VIEW AS ...;

Q. How to find out Information About Columns in Which Deletes, Inserts, and Updates allowed for a view ?

Ans - SELECT table_name, column_name, updatable, insertable, deletable

FROM user_updatable_columns

ORDER BY 1, 2;

Q. What is the use of with check option in creation of a view ?

Ans. When we create simple updatable view with out the with check option u can insert any record irrespective of the where condition used in the view definition.

Like CREATE OR REPLACE VIEW TMP_V AS

SELECT * FROM RB_ISSUE WHERE PROBLEM_NUMBER = 6;

then u can perform the updation or deletion on the records in the view only where problem_number = 6 but u can insert record with any problem_number. Here RB_ISSUE issue is having a record for problem_number 11.

DELETE FROM tmp_v Where PROBLEM_NUMBER = 11;

0 rows deleted.

insert into tmp_v (SYS_CODE,PROBLEM_NUMBER,SLA_MISSED,IS_SCF) values('DEM_PROJ',111,'n','N')

1 rows inserted.

But if the view has been created with “with check option “ then u are allowed to insert the new records only for the given where criteria.

Like -

CREATE OR REPLACE VIEW TMP_V AS

SELECT * FROM RB_ISSUE WHERE PROBLEM_NUMBER = 6

WITH CHECK OPTION CONSTRAINT MYVIEW;

insert into tmp_v (SYS_CODE,PROBLEM_NUMBER,SLA_MISSED,IS_SCF) values('DEM_PROJ',111,'n','N')

Error starting at line 10 in command:

insert into tmp_v (SYS_CODE,PROBLEM_NUMBER,SLA_MISSED,IS_SCF) values('DEM_PROJ',111,'n','N')

Error report:

SQL Error: ORA-01402: view WITH CHECK OPTION where-clause violation

01402. 00000 - "view WITH CHECK OPTION where-clause violation"

*Cause:

*Action:

Now only the records for problem_number = 6 is allowed to insert.

Q. What object in database can have same name?

Can I give same name to a table and trigger?

Ans -Within a name space, no two objects can have the same name.

The following schema objects share one name space:
• Tables
• Views
• Sequences
• Private synonyms
• Stand-alone procedures
• Stand-alone stored functions
• Packages
• Materialized views
• User-defined types
Each of the following schema objects has its own name space:
• Indexes
• Constraints
• Clusters
• Database triggers
• Private database links
• Dimensions

Note : Names must be from 1 to 30 bytes long with these exceptions:

• Names of databases are limited to 8 bytes.
• Names of database links can be as long as 128 bytes.

Q. How to pass parameters by reference in oracle stored program?

OR

What is NOCOPY in Oracle?

Ans - By default variable passing in pl/sql is by val.

The NOCOPY is a hint that tells the PL/SQL compiler to pass OUT and IN OUT parameters by reference, rather than by value.

When actual parameters are passed by value, the contents of the OUT and IN OUT actual parameters are copied to formal parameter variables, which are then used by the subprogram being called. On successful completion of the subprogram the values are copied back to the actual parameters, but unhandled exceptions result in the actual parameter values being left unchanged. Copying large parameters, such as records, collections, and objects requires both time and additional space – that is costly.

With the NOCOPY hint the actual parameters are passed by reference to the formal parameters and on successful completion the outcome is the same with out the copy to and copy back operation, but unhandled exceptions may leave the parameters in an altered state, so programs must handle errors or cope with the suspect values. In following example we are passing nested table type variable to in_out procedure:

DECLARE

TYPE t_tab IS TABLE OF VARCHAR2(32767);

PROCEDURE in_out_nocopy (p_tab IN OUT NOCOPY t_tab) IS

BEGIN

NULL;

END;

BEGIN

NULL;

END;

/

Q. Why should we rebuild indexes?

Ans – Indexes in oracle, updated automatically while table update or delete. Following are some reasons, we require to rebuild indexes:

1. Number of deleted leaf rows - when table rows are deleted the index nodes are not physically deleted in oracle, instead of this Oracle "logically" deletes the index entry and leaves "dead" nodes in the index tree, that may be re-used if another adjacent entry is required. This is done to speed up SQL deletes, since Oracle does not have to allocate resources to re-balance the index tree when rows are deleted. So if the no of dead node is very high in index – we should rebuild it to balance the tree.

2. Index height - When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows. Oracle index may have four levels, but only in those areas of the index tree where the massive inserts have occurred. Oracle indexes can support many millions of entries in three levels, and any Oracle index that has four or more levels would benefit from rebuilding.

3. If the indexes are fragmented then fetching data require more logical reads. So require Re-build the index.

But finally it’s very costly to rebuild indexes.

Q. What is the statement to re-build indexes?

Ans – Alter index index_name rebuild;

Q. How the Database Does Implicit Rollbacks?

Ans - Before executing an INSERT, UPDATE, or DELETE statement, the database marks an implicit savepoint (unavailable to you). If the statement fails, the database rolls back to the savepoint.

Q. How to debug the procedure ? ---IMP

Ans – We are using oracle sql developer for PL\SQL development.

1. For debugging a stored program we first need to compile it for debug, this step adds in the compiler directives required for debugging. Once we have completed the debug, we should compile the procedure again and remove the extra directives.

2. We can place breakpoints where required in code. When code is run in debug mode, execution will stop at the break point. From this point we can now control the flow of execution, by step over, step into, and step out the code. Also we can modify values of variables and perform other debugging functions

3. In data window we can see limited list of variables, can modify the values by right clicking on it and choosing modify value option.

4. User can perform run to cursor option for execution of code up to the cursor, if cursor exists in code.

5. To assist with debugging, line numbers can be added to the Code window. Right-click on the margin and select Toggle Line Numbers.

Note: for debugging a stored program you need privileges DEBUG CONNECT SESSION and DEBUG ANY

Q. What is SQLCODE and SQLERRM ?

Ans – Both are function for error handling in oracle PL\SQL.

SQLCODE function returns the error number associated with the most recently raised error exception.

SQLERRM function returns the error message associated with the most recently raised error exception.

These functions should only be used within the Exception Handling section of your code:

Q. What are different type of exceptions you found in PL/SQL?

Ans -

CURSOR_ALREADY_OPEN ORA-06511 Exactly what it seems to be. Tried to open a cursor that was already open

NO_DATA_FOUND ORA-01403 The SELECT statement returned no rows or referenced a deleted element in a nested table or referenced an initialized element in an Index-By table.

TOO_MANY_ROWS ORA-01422 The SQL INTO statement brought back more than one value or row (only one is allowed).

ZERO_DIVIDE ORA-01476 A program attempted to divide a number by zero.

ROWTYPE_MISMATCH ORA-06504 Host cursor variable and PL/SQL cursor variable involved in an assignment statement have incompatible return types.

CASE_NOT_FOUND ORA-06592 None of the choices in the WHEN clauses of a CASE statement were selected and there is no ELSE clause.

Q. Can we place the When Others before the other exception handling section?

Ans – No, When others should always be at the last in EXCEPTION section other wise oracle will raise error :

PLS-00370: OTHERS handler must be last among the exception handlers of a block.

Q. We have a before trigger and in trigger body we are inserting some data in a tmp table. What will happened if the tmp table is locked by some other transaction and taking a log of time?

Ans – In this case outer query statement will wait for table until the locked is released or up to the threshold. We will get the operation time out error and the transaction will be roll backed if threshold other wise it will be completed successfully.

Q. What is the difference between clustered and non clustered index?

Ans – Clustered index not taking separate memory since the data in table always store in the order of index, where as non clustered index consume space and the order of physical data may be different than the index order. Clustered index can be created only on clustered tables and one cluster can have only one cluster index where as non clustered index can be created on clustered table as well non clustered tables and we can create more than one non clustered index on a table or cluster.

Q. How you will tell your query to use or Not to use indexes?
Ans - By giving Hints:
/*Index (col name)*/
/*Full (tablename) */

Q. What is an UTL_FILE? What are different procedures and functions associated with it?

Ans - UTL_FILE is oracle supplied package that adds the ability to read and write to operating system files. Methods associated with it are FCLOSE, FCLOSE_ALL and to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE.

Functions associated with it are FOPEN, ISOPEN.

Q. What is dual in oracle? What is the datatype of col in dual?

Ans – Dual is a dummy table of one col and one row and the data type of col in Varchar2. We can get one record from dual with many columns. The owner of dual is SYS. Dual table is used in Oracle when you need to run SQL that does not logically have a table name like -

select sysdate from dual;

select sysdate, sysdate + 1 from dual;

Q. Can we move on the first record from the mid in cursor?

Ans – No, in that case we need to re-open the cursor and it will bring new records also if present in table.

Q. Write an SQL to get all the employee where the salary of the employee is greater than the avg salary of their department?

Ans – Select * from emp a where sal > ( select avg(sal) from emp b where b.dept_no = a.dept_no);

Q. What is the difference between Varchar and Varchar2?

Ans – As per oracle documentation, The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.

Note : NCHAR and NVARCHAR2 are Unicode datatypes that store Unicode character data. The character set of NCHAR and NVARCHAR2 datatypes can only be either AL16UTF16 or UTF8 and is specified at database creation time as the national character set.

As for now, they are synonyms.

VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future, as ANSI standard prescribes.

Q. What is the difference between RAISE_APPLICATION_ERROR & RAISE statements?

Ans - You use RAISE statement to raise a programmer-defined exception declared in declaration section of the pl/sql block like :

RAISE your_exception;

You also can use unqualified RAISE statement in an exception handler to re-raise the same exception:

RAISE;

When you need to raise application-specific error from within the server (from database trigger, for example) and

pass this error back to the client application process, you use RAISE_APPLICATION_ERROR.

RAISE_APPLICATION_ERROR(Error_number, 'text', Keep_error_stack)

Q. What is the use of Raise_application_Error and PRAGMA EXCEPTION_INIT?

Ans - Notice that in exception handling section the WHEN clause requires an exception name. If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR, then one can be assigned using PRAGMA_EXCEPTION_INIT, as shown in the following example:

DECLARE

...

Null_salary EXCEPTION;

PRAGMA EXCEPTION_INIT(Null_salary, -20101);

BEGIN

...

RAISE_APPLICATION_ERROR(-20101, 'salary is missing');

...

EXCEPTION

WHEN Null_salary THEN

......

Q. How many Datatypes available in Oracle PL/SQL ?

Ans - We have four major category of data types in Oracle PL/SQL :

Scalar : Which have atomic values and don't have internal components, such as NUMBER, VARCHAR2, DATE, LONG, BOOLEAN.

Composite : Which have internal components and can be accessed individually, such as RECORD & TABLE.

Reference : Which have the reference of other data items like cursor variables in case of ref-cursors.

Large Object : Which contains the pointers to large objects stored seperately like vedio, test etc.

Q. What is the difference between %TYPE and %ROWTYPE in Oracle?

Or

What is %TYPE and %ROWTYPE in Oracle?

Ans - Both are Oracle key words, used to inherit the data types of variables defined earlier in either in PL/SQL program, or can be used to acquire the datatype of a column of table or view.

% TYPE (used to define scalar datatypes) provides the data type of a variable or a database column to that variable.

Sal employee.salary%TYPE;

Here we have defined a variable sal which acquired data type from column salary of table employee.

% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.

rowtype_attribute := {cursor_name | cursor_variable_name | table_name}%ROWTYPE

The major advantage using this approach to define variables in PL/SQL programs is in case any modification in definition of a column in a table, the data type of a variable changes accordingly.

Q. What is the execution order of various clauses in SELECT statement in oracle?

Ans - A SELECT statement have below clauses in it's full format ;

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

When we perform a select on a table with all these clauses, Oracle first execute the WHERE clause and apply a filter on the table based on the condition to fetch required rows in memory for further processing.

Then perform the GROUP BY clause to apply the grouping of data based on the group by columns available in this clause.

Now oracle execute the HAVING Clause to apply the filter on the grouped records and finally apply the ORDER BY and return the result to user.

Q. What is the difference between % ROWTYPE and TYPE RECORD ?

Ans – Both are used for creation of composite variable (record type). But if you are defining a RECORD type, you can specify a NOT NULL constraint on fields, or give them default values. Which is not possible in %ROWTYPE.

Rest the things are same, you can create a record by %ROWTYPE also.

Q. What is a cursor ? Why Cursor is required ? Are cursors good for performance?

Ans - Cursor is a named private SQL area from where information can be accessed. Oracle have two type of cursors – implicit and explicit. Implicit cursors are opened and controlled by oracle server itself for processing of every sql statement. Explicit cursors are opened and controlled by programmers. Oracle require implicit Cursors for processing of data, some time we use explicit cursor when we require precise control over query processing. In pl/sql cursors are bad for performance because of context-switching, so we use bulk bind and bulk collect with collections.

Q. What is context-switching?

Ans – During execution of Pl/Sql Program to execute all procedural statements (like if, for, assignments...) Oracle use Pl/Sql engine and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.

Q. Explain the two type of Cursors ?

Ans -

1. Implicit Cursors: - Whenever we execute sql statements oracle server assigns a work area called private sql area to store precessed information. The most recently used work area can be accessed using SQL%. In implicit cursors open, fetch , close operations are automatically performed by the server implicitly.

2.Explicit cursors:- In explicit cursor you can explicitly assign a name to process information stored in private sql areas. This process involves four steps

I. Declaring a cursor :- Involves assign a name to cursor and associating a query with it..

II. Open the cursor :- Executes the query and identify the result set.

III. Fetch the cursor :- gets the result set and Loops through to process them

IV. Close the cursor :- Releases the cursor

Q. What are the cursor attributes used in PL/SQL ?

%ISOPEN - to check whether cursor is open or not

% ROWCOUNT - number of rows fetched/updated/deleted.

% FOUND - to check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND - to check whether cursor has fetched any row. True if no rows are fetched.

These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

Q. What is a cursor for loop ?

Ans - Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

eg. FOR emp_rec IN C1 LOOP

salary_total := salary_total +emp_rec sal;

END LOOP;

Q. What will happen after commit statement in a cursor for loop?

For example

Cursor C1 is

Select empno,

ename from emp;

Begin

open C1; loop

Fetch C1 into

eno.ename;

Exit When

C1 %notfound;

commit;

end loop;

end;

Ans -

The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.

The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

Q. What is WHERE CURRENT OF clause in cursors ?

Ans - It is used to UPDATE or DELETE the current record fetched in cursor for loop.

For example :

OPEN c1;

LOOP

FETCH c1 INTO my_emp_id, my_job_id, my_sal;

IF my_job_id = 'SA_REP' THEN

UPDATE employees SET salary = salary * 1.02

WHERE CURRENT OF c1;

END IF;

EXIT WHEN c1%NOTFOUND;

END LOOP;

The update will take place only for the record fetch by cursor loop and when the my_job_id is equals to SA_REP, this is because the WHERE CURRENT OF c1 is referring the current record in loop.

Q. What is a database trigger ? Name some usages of database trigger ?

A trigger is a named program unit that is stored in the database and fired (executed) in response to a specified event. The specified event is associated with either a table, a view, a schema, or the database, and it is one of the following:

• A DML statement (DELETE, INSERT, or UPDATE)
• A DDL statement (CREATE, ALTER, or DROP)
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)
• Instead of triggers to update complex view

The trigger is said to be defined on the table, view, schema, or database.

Q. Can i call a trigger manually or in some stored program?(imp)

Ans – NO, triggers in oracle, by nature execute in response to a specific event. We can't call the trigger manually.

Q. How many types of database triggers can be specified on a table ? What are they ?

Ans - Triggers can be classified as :

1. DML Triggers (DELETE, INSERT, or UPDATE) 

2. DDL Triggers (CREATE, ALTER, or DROP)

3. Database Trigger (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)

4. Instead of triggers to update complex view

A DML Trigger :

→ Can occur in response of INSERT, UPDATE, DELETE.

→ Can be Statement level or Row level.

→ Can be a Before of After trigger.

Q. What is autonomous trigger or procedure?

Ans. An autonomous transaction is an independent transaction which can be committed independent of other transactions (it starts with in main transaction and commit itself with out committing the mail transaction). An autonomous transaction will be committed with out committing the other non-autonomous transactions.

Let's have an example :

CREATE OR REPLACE PROCEDURE autonomous_proc
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO test_table VALUES('Autnomous Insert Procedure execution');

COMMIT;
END;

Now Execute the following code

DECLARE
BEGIN
INSERT INTO test_table VALUES('Before Autonomous Insert');

autonomous_proc;

INSERT INTO test_table VALUES('After Autonomous Insert');

ROLLBACK;
END;

And query the table

select * from test_table;

This will show only one row

A
--------------------------------------------------
Autonomous Insert Procedure execution

Conclusion

1) The commit statement in the autonomous procedure will commit the DML operations in the autonomous procedure without committing the transactions before that.

2) The rollback statement caused both the inserts before and after the autonomous transaction to be rolled back, but not the autonomous transaction

So we can use PRAGMA AUTONOMOUS_TRANSACTION; in triggers or procedures to commit the job of that trigger or procedural only, with out committing the job of calling program.

Q. What are two virtual tables available during database trigger execution ?

Ans - The table columns are referred as OLD.column_name and NEW.column_name.

For triggers related to INSERT only NEW.column_name values only available.

For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.

For triggers related to DELETE only OLD.column_name values only available.

Q. What is trigger cascading?

Ans - When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Oracle Database allows up to 32 triggers to cascade at any one time. However, we can effectively limit the number of trigger cascades using the initialization parameter OPEN_CURSORS, because a cursor must be opened for every execution of a trigger.

Q. What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?

Ans – It will create Mutation of table error.

Q. What is Mutation of tables?

Ans – To Maintain the consistency of data, oracle doesn't allow to read and write the same table at a time. If we try to do this, Oracle raise mutation table error. Generally happens in row level triggers.

Q. We use triggers and constraints to implement the business rules, can you tell me the order in which they execute while performing DML on a table?

Ans – To take better understating of this let's have an example :

create table a ( b number, c number check (c >100) );

create or replace trigger t1

before insert on a

begin

dbms_output.put_line('this is before insert trigger');

end;

create or replace trigger t2

after insert on a

begin

dbms_output.put_line('this is after insert trigger');

end;

insert into a values (1,99);

/

this is before insert trigger

insert into a values (1,99)

*

ERROR at line 1:

ORA-02290: check constraint (XXINV.SYS_C00106711) violated

insert into a values (1,199);

/

this is before insert trigger

this is after insert trigger

1 row created.

So, if it is before insert trigger then the trigger will run first before the constraints in the table. if it is after insert trigger , constraints are checked first and then trigger will run.

Will share the next part of Oracle Interview Questions soon. If you want to get answer for any question, mail me on kamleshkhaliya@gmail.com