Oracle Interview Questions | Oracle Interview Questions and answers

Oracle Interview Questions list for experienced

How to fetch the row which has the Max value for a column?
what is oracle update statement with inner join ?
How do I limit the number of rows returned by an Oracle query after ordering?
How to create id with AUTO_INCREMENT on Oracle?
what is the SQL Query to concatenate column values from multiple rows in Oracle?
Why does Oracle 9i treat an empty string as NULL?
How can I combine multiple rows into a comma-delimited list in Oracle?
Is there an Oracle SQL query that aggregates multiple rows into one row?
Difference between Inner join vs Where ?
Difference between Oracle\'s plus (+) notation and ansi JOIN notation?
how to UPSERT (update or insert into a table?)
How do I escape a reserved word in Oracle?
When do I need to use a semicolon vs a slash in Oracle SQL?
How to do Paging with Oracle?
Is there a combination of "LIKE" and "IN" in SQL?
How to put more than 1000 values into an Oracle IN clause?
How to Compare Dates in Oracle SQL?
How do I reset a sequence in Oracle?
Calculate difference between 2 date / times in Oracle SQL
What is the minimum client footprint required to connect C# to an Oracle database?
What is different types of JOINs?
How to store only time; not date and time?
How do I spool to a CSV formatted file using SQLPLUS?
What is the string concatenation operator in Oracle?
How to best split csv strings in oracle 9i
Is there any function in oracle similar like group_concat of mysql?
How to load Excel data sheet to Oracle database
Is there a tool to trace queries, like Profiler for sql server?
What are the oracle Differences between NVL and Coalesce
What is the dual table in Oracle?
What exactly do quotation marks around the table name do?
How to speed up Webrick?
What is the best way to do multi-row insert in Oracle?
How can i introduce multiple conditions in LIKE operator?
How can I get column names from a table in Oracle?
Java JDBC - How to connect to Oracle using Service Name instead of SID
How to connect to Oracle using Service Name instead of SID?
How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
how to convert csv to table in oracle?
How to handle a single quote in Oracle SQL?
What is the difference between a User and a Schema in Oracle?
How to create a temporary table in Oracle?
Why do I get java.lang.AbstractMethodError when trying to load a blob in the db?
Is there a function to split a string in plsql?
How to get last row ID?
How do you get nicely formatted results from an Oracle procedure that returns a reference cursor?
What is the difference between "AS" and "IS" in an Oracle stored procedure?
How to load a large xml file?
What does `(+)` do in a WHERE clause?

Oracle interview questions and answers on advance and basic Oracle with example so this page for both freshers and experienced condidate. Fill the form below we will send the all interview questions on Oracle also add your Questions if any you have to ask and for apply in Oracle Tutorials and Training course just send a mail on info@pcds.co.in in detail about your self.

What is Oracle ?

Answer : Oracle Server is an Object-relational Database Management System (ORDBMS) created by Oracle Corporation. Do NOT use this tag for other products owned by Oracle, such as Java and MySQ

Questions : 1 :: How to fetch the row which has the Max value for a column?

This will retrieve all rows for which the my_date column value is equal to the maximum value of my_date for that userid. This may retrieve multiple rows for the userid where the maximum date is on...View answers

Questions : 2 :: what is oracle update statement with inner join ?

UPDATE table1 SET table1.value =(SELECT table2.CODE FROM table2 WHERE table1.value = table2.DESC)WHERE...View answers

Questions : 3 :: How do I limit the number of rows returned by an Oracle query after ordering?

ou can use a subquery for this likeselect *from ( select * from emp order by sal desc ) where ROWNUM <= 5;Have also a look at the topic On ROWNUM and limiting results at...View answers

Questions : 4 :: How to create id with AUTO_INCREMENT on Oracle?

There is no such thing as "auto_increment" or "identity" columns in Oracle. However, you can model it easily with a sequence and a trigger: Table definition: CREATETABLE departments ( ID...View answers

Questions : 5 :: what is the SQL Query to concatenate column values from multiple rows in Oracle?

SELECT pid, LISTAGG(Desc,' ')WITHINGROUP(ORDERBY seq)ASdescFROM B GROUPBY...View answers

Questions : 6 :: Why does Oracle 9i treat an empty string as NULL?

Back in the olden days before there was a SQL standard, Oracle made the design decision that empty strings in VARCHAR/VARCHAR2 columns were NULL and that there was only one sense of NULL (there...View answers

Questions : 7 :: How can I combine multiple rows into a comma-delimited list in Oracle?

createtable countries ( country_name varchar2 (100));insertinto countries values('Albania');insertinto countries values('Andorra');insertinto countries values('Antigua');SELECT SUBSTR...View answers

Questions : 8 :: Is there an Oracle SQL query that aggregates multiple rows into one row?

It depends on the version of Oracle you're using. If it supports the wm_concat() function, then you can simply do something like this: SELECT field1, wm_concat(field2)FROM YourTable GROUPBY...View answers

Questions : 9 :: Difference between Inner join vs Where ?

CREATETABLE table1 ( id INT, name VARCHAR(20));CREATETABLE table2 ( id INT, name VARCHAR(20)); The execution plan for the query using the inner join: -- with inner join EXPLAIN...View answers

Questions : 10 :: Difference between Oracle's plus (+) notation and ansi JOIN notation?

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and...View answers

Questions : 11 :: how to UPSERT (update or insert into a table?)

createor replace procedure ups(xa number)asbeginmergeinto mergetest m using dual on(a = xa)whennotmatchedtheninsert(a,b)values(xa,1)whenmatchedthenupdateset b = b+1;end ups;/droptable...View answers

Questions : 12 :: How do I escape a reserved word in Oracle?

Oracle normally requires double-quotes to delimit the name of identifiers in SQL statements, e.g. SELECT"MyColumn"AS"MyColAlias"FROM"MyTable"AS"Alias"WHERE"ThisCol"='That...View answers

Questions : 13 :: When do I need to use a semicolon vs a slash in Oracle SQL?

It's a matter of preference, but I prefer to see scripts that consistently use the slash - this way all "units" of work (creating a PL/SQL object, running a PL/SQL anonymous block, and executing...View answers

Questions : 14 :: How to do Paging with Oracle?

SELECT*FROM(SELECT a.*, rownum r__ FROM(SELECT*FROM ORDERS WHERE CustomerID LIKE'A%'ORDERBY OrderDate DESC, ShippingDate DESC) a WHERE rownum <((pageNumber * pageSize)+1))WHERE r__...View answers

Questions : 15 :: Is there a combination of "LIKE" and "IN" in SQL?

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative. Both...View answers

Questions : 16 :: How to put more than 1000 values into an Oracle IN clause?

select*from table1 where ID in(1,2,3,4,...,1000)unionallselect*from table1 where ID...View answers

Questions : 17 :: How to Compare Dates in Oracle SQL?

31-DEC-95 isn't a string, nor is 20-JUN-94. They're numbers with some extra stuff added on the end. This should be '31-DEC-95' or '20-JUN-94' - note the inverted commas, '. This will enable...View answers

Questions : 18 :: How do I reset a sequence in Oracle?

reate or replaceprocedure reset_seq( p_seq_name in varchar2 )is l_val number;begin execute immediate 'select ' || p_seq_name || '.nextval from...View answers

Questions : 19 :: Calculate difference between 2 date / times in Oracle SQL

with tbl_demo AS (SELECT TO_DATE('11/26/2013 13:18:50', 'MM/DD/YYYY HH24:MI:SS') dt1 , TO_DATE('11/28/2013 21:59:12', 'MM/DD/YYYY HH24:MI:SS') dt2...View answers

Questions : 20 :: What is the minimum client footprint required to connect C# to an Oracle database?

To minimize the footprint, I suggest the following : Use the Microsoft provider for Oracle (System.Data.OracleClient), which ships with the framework. Download the...View answers

Questions : 21 :: What is different types of JOINs?

There are a total of five JOINs. They are : 1. JOIN or INNER JOIN 2. OUTER JOIN 2.1 LEFT OUTER JOIN or LEFT JOIN 2.2 RIGHT OUTER JOIN or...View answers

Questions : 22 :: How to store only time; not date and time?

You could try the INTERVAL DAY TO SECOND data type but it won't save you any disk space ... it is very suitable for this purpose though.create table t1 (time_of_day interval day (0) to...View answers

Questions : 23 :: How do I spool to a CSV formatted file using SQLPLUS?

set colsep , -- separate columns with a commaset pagesize 0 -- No header rowsset trimspool on -- remove trailing blanksset headsep off -- this may or may not...View answers

Questions : 24 :: What is the string concatenation operator in Oracle?

It is ||, for example:select 'Mr ' || ename from emp;The only "interesting" feature I can think of is that 'x' || null returns 'x', not null as you might perhaps...View answers

Questions : 25 :: How to best split csv strings in oracle 9i

create or replace function splitter_count(str in varchar2, delim in char) return int asval int;begin val := length(replace(str, delim, delim || ' ')); return val - length(str);...View answers

Questions : 26 :: Is there any function in oracle similar like group_concat of mysql?

CREATE OR REPLACE FUNCTION get_comma_separated_value (input_val in number) RETURN VARCHAR2IS return_text VARCHAR2(10000) := NULL;BEGIN FOR x IN (SELECT col2 FROM...View answers

Questions : 27 :: How to load Excel data sheet to Oracle database

Excel -> CSV -> OracleSave the Excel spreadsheet as file type 'CSV' (Comma-Separated Values).Transfer the .csv file to the Oracle server.Create the Oracle table, using the SQL CREATE...View answers

Questions : 28 :: Is there a tool to trace queries, like Profiler for sql server?

alter system set timed_statistics=true--oralter session set timed_statistics=true --if want to trace your own session-- must be big enough:select value from v$parameter pwhere...View answers

Questions : 29 :: What are the oracle Differences between NVL and Coalesce

SELECT SUM(val)FROM ( SELECT NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val ...View answers

Questions : 30 :: What is the dual table in Oracle?

DUAL was originally a table and the database engine would perform disk IO on the table when selecting from DUAL. This disk IO was usually logical IO (not involving physical disk access) as the disk...View answers

Questions : 31 :: What exactly do quotation marks around the table name do?

Under the covers, Oracle is always doing case-sensitive identifier matching. But it always casts identifiers that are not double-quoted to upper case before doing the matching. If you put...View answers

Questions : 32 :: How to speed up Webrick?

Look for the file /usr/lib/ruby/1.9.1/webrick/config.rb and edit it.Replace the line:DoNotReverseLookup => nil,with:DoNotReverseLookup =>...View answers

Questions : 33 :: What is the best way to do multi-row insert in Oracle?

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE) select 8000,0,'Multi 8000',1 from dualunion all select 8001,0,'Multi 8001',1 from...View answers

Questions : 34 :: How can i introduce multiple conditions in LIKE operator?

select * from tbl where col like 'ABC%'unionselect * from tbl where col like 'XYZ%'unionselect * from tbl where col like 'PQR%';Here is the test code to verify:create table tbl (col...View answers

Questions : 35 :: How can I get column names from a table in Oracle?

You can query the USER_TAB_COLUMNS table for table column metadata.SELECT table_name, column_name, data_type, data_lengthFROM USER_TAB_COLUMNSWHERE table_name =...View answers

Questions : 36 :: Java JDBC - How to connect to Oracle using Service Name instead of SID

Thin-style Service Name Syntax Thin-style service names are supported only by the JDBC Thin driver. The syntax is: ...View answers

Questions : 37 :: How to connect to Oracle using Service Name instead of SID?

Thin-style service names are supported only by the JDBC Thin driver. The syntax is:@//host_name:port_number/service_nameFor...View answers

Questions : 38 :: How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?

I think what you're looking for is to do a LEFT JOIN starting from the main-table to return all records from the main table regardless if they have valid data in the joined ones (as indicated by the...View answers

Questions : 39 :: how to convert csv to table in oracle?

The following works invoke it as select * from table(splitter('a,b,c,d'))create or replace function splitter(p_str in varchar2) return sys.odcivarchar2listisv_tab sys.odcivarchar2list:=new...View answers

Questions : 40 :: How to handle a single quote in Oracle SQL?

Use two single-quotesSQL> SELECT 'D''COSTA' name FROM DUAL;NAME-------D'COSTAAlternatively, use the new (10g+) quoting method:SQL> SELECT q'$D'COSTA$' NAME FROM...View answers

Questions : 41 :: What is the difference between a User and a Schema in Oracle?

You should consider a schema to be the user account and collection of all objects therein as a schema for all intents and purposes.SCOTT is a schema that includes the EMP, DEPT and BONUS tables with...View answers

Questions : 42 :: How to create a temporary table in Oracle?

CREATE GLOBAL TEMPORARY TABLE today_sales ON COMMIT PRESERVE ROWS AS SELECT * FROM orders WHERE order_date = SYSDATE;

Questions : 43 :: Why do I get java.lang.AbstractMethodError when trying to load a blob in the db?

Which JDBC drivers support which versions of Javasoft's JDK?pre-8i OCI and THIN Drivers - JDK 1.0.x and JDK 1.1.x8.1.5 OCI and THIN Drivers - JDK 1.0.x and JDK 1.1.x8.1.6SDK THIN Driver - JDK 1.1.x...View answers

Questions : 44 :: Is there a function to split a string in plsql?

You could use a combination of SUBSTR and INSTR as follows :Example string : field = 'DE124028#@$1048708#@$000#@$536967136#@$'The seperator being #@$.To get the '1048708' for example :If the...View answers

Questions : 45 :: How to get last row ID?

String sql = "BEGIN INSERT INTO mytable(id, content) VALUES (seq_mytable.NEXTVAL(), ?) RETURNING id INTO ?; END;";Connection connection = null;CallableStatement statement = null;try...View answers

Questions : 46 :: How do you get nicely formatted results from an Oracle procedure that returns a reference cursor?

create package mypackage as function getquestions(user in varchar2) return sys_refcursor;end mypackage;/create package body mypackage as function getquestions(user...View answers

Questions : 47 :: What is the difference between "AS" and "IS" in an Oracle stored procedure?

Given a loose object type:CREATE TYPE someRecordType AS OBJECT( SomeCol VARCHAR2(12 BYTE));You can create a loose Table type of this object type with either AS or ISCREATE OR...View answers

Questions : 48 :: How to load a large xml file?

You can access the XML files on the server via SQL. With your data in the /tmp/tmp.xml, you would first declare the directory:SQL> create directory d as '/tmp';Directory createdYou could then...View answers

Questions : 49 :: What does `(+)` do in a WHERE clause?

Depending on which side of the "=" the "(+) is on, it denotes a LEFT OUTER or a RIGHT OUTER join (in this case, it's a left outer join). It's old Oracle syntax that is sometimes preferred by...View answers

More Question

Ask your interview questions on Oracle

Write Your comment or Questions if you want the answers on Oracle from Oracle Experts

*Name :**
*Email Id :**
*Mob no :**
Question Or Comment* :

Oracle Interview Questions And Answers