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 email@example.com in detail about your self.
Top Oracle interview questions and answers for freshers and experienced
What is Oracle ?
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
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:
CREATETABLE departments (
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
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 (
name VARCHAR(20));CREATETABLE table2 (
The execution plan for the query using the inner join:
-- with inner join
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?)
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.
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.
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