GeekInterview.com
Series: Subject: Topic:

Oracle Interview Questions

 
Sub Categories (+ View)
Showing Questions 1 - 20 of 90 Questions
First | Prev | | Next | Last Page
Sort by: 
 | 

What is the difference between trim and truncate functions in Oracle

Asked By: Josna | Asked On: Aug 13th, 2006

Answered by: Deepika S Verma on: Mar 14th, 2014

TRUNC applies to numeric and Dates values Syntax- ------- TRUNC( number, [ decimal_places ] ) TRUNC ( date, [ format ] ) Example - --------- TRUNC(125.815) would retur...

Answered by: shiiva on: Aug 6th, 2013

We cannot compare Truncate & Trim. Truncate is a DDL command which deletes the content of a table completely, without effecting the table structure.

Trim is a function which alters the output of one of the column output of a select query.

Which one is faster delete/truncate? Why?

Asked By: SaratKumar | Asked On: Oct 10th, 2005

Answered by: Bharath on: Feb 18th, 2014

Truncate will be faster than delete. Because truncate wont make any backup of deleted rows where in delete it will take backup of deleted records for purpose of rollback. In other words Truncate will...

Answered by: Babu Lal Roy on: Dec 24th, 2012

1) Delete is a DML statement and it generate redo log entry. Truncate is a DDL statement and it does not generate redo log entry. 2) Truncate reset the high water mark to release space consumed by ta...

Difference between varray and nested table

Asked By: Kanhucharan | Asked On: Apr 10th, 2008

What is the difference between varray and nested table?

Answered by: Vishnu Bharath on: Jan 8th, 2014

Varrays are generally dense , that mean you cannot delete elements of the varray.But the elements of the Nested Table are generally sparse in nature and hence the elements can be deleted in between us...

Answered by: Sheraz Baig on: Dec 3rd, 2013

Both are type of collections in Oracle. In Varray because its not stored in database columns so no DML operation can be implemented but on other hand as Nested tables we can perform DML operations

What do you mean by conceptual?

Asked By: sprajarajan | Asked On: Mar 22nd, 2008

How its helps? Why we go for conceptual model?

Answered by: vivekanand on: Dec 9th, 2013

Conceptual modeling is the very first level of data modeling once gathering the Business requirements. It tells about only the Entity and attributes , not the relations(constraints) or physical tables...

Answered by: Sheraz Baig on: Dec 3rd, 2013

When we talk about Conceptual Model , so we definitely mean the High Level of information. Unlike Physical Model, Conceptual model doesn't discuss the data, but it discuss the process. as we told its ...

No_data_found or too_many_row

Asked By: jkverma29 | Asked On: Aug 19th, 2008

In PL/SQL if we write select statement with into clause it may return two exceptions no_data_found or too_many_row .To avoid these execeptions. How do you write SQL statement in alternative way?

Answered by: Ananda Kumar Behera on: Nov 12th, 2013

To avoid use:

Code
  1. DECLARE a variable
  2. SELECT COUNT(*) INTO num WHERE <Condition OF your data retreival FOR SELECT INTO statement>;
  3. IF num==1 THEN
  4. EXECUTE your SELECT INTO statement
  5. END IF

Answered by: tinku981 on: Jun 16th, 2013

We can use explicit cursor to avoid Oracle exception - NO_DATA_FOUND and TOO_MANY_ROWS.


Write a query to display all the odd numbers from a table

Asked By: Sudhir | Asked On: Jun 21st, 2006

Answered by: Rakesh Anand on: Aug 24th, 2013

Code
  1. SELECT * FROM (SELECT COLUMN_NAME, ROWNUM RN FROM TABLE)
  2. WHERE MOD (RN, 2) <> 0;

Answered by: shivigupta on: Oct 9th, 2012

set serveroutput on;
begin
for v_c1 in (select num from tab_no) loop
if mod(v_c1.num,2) = 1 then
dbms_output.put_line(v_c1.num);
end if;
end loop;
end;

SQL query to find nth maximum of nth row?

Asked By: DEBPROSAD BANERJEE | Asked On: Jul 14th, 2006

1. Queries for nth maximum nth row?2. In one table there are 5 fields. Empno, ename, deptcd, managr_id, salary.Select the departments whose sum of the salary greater than the sum of salaries of any department?3. When index will be usd in the query?

Answered by: mahesh on: Aug 12th, 2013

Code
  1. SELECT* FROM (SELECT RANK()
  2. over(ORDER BY price DESC)
  3. odrd,products.* FROM products)
  4. WHERE odrd =5;

Answered by: priyank on: May 10th, 2012

Use Case in update statement

When we give select * from emp; how does Oracle respond:

Asked By: SaratKumar | Asked On: Oct 10th, 2005

Answered by: Rahul on: Jul 11th, 2013

It retrieve or fetch all data from Emp table,and show all data of table like Emp as result.

Answered by: vaasukk@yahoo.com on: Dec 13th, 2012

First it will check the syntax and semantics in library cache, after that it will create execution plan. Already data in the buffer cache it will directly return to the client. If not it write the fetched to the dB buffer cache after that it will send server. Server sends to the client.

Compute sum

Asked By: rudhra97 | Asked On: Jun 9th, 2008

In order to 'compute sum' clause in the SQL reports which of the following is mandatory1) break on 2) order by 3) set page size 4) set feedback

Answered by: tinku981 on: Jun 13th, 2013

Break and compute are required, but for spooling output you should be using all the another 3 functions.

Answered by: Gaurav Saxena on: Apr 24th, 2013

Break on is compulsory for compute function.

Open cursors

Asked By: ranjith12 | Asked On: Apr 11th, 2009

If you have an open cursor on a table, modify the same table and commit, what would happen?

Answered by: tinku981 on: Jun 14th, 2013

There will not be any impact on the output even if we will insert (and commit) the data after cursor is opened. The output will be same as of the record set when cursor was opened and new row will not be selected in that operations.

Answered by: tinku981 on: Jun 14th, 2013

There will not be any impact on the output even if we will insert (and commit) the data after cursor is opened. The output will be same as of the record set when cursor was opened and new row will not...

Write the query

Asked By: Koonal saxena | Asked On: Dec 20th, 2012

A employee getting variable salary like jan 3000,feb 5000,mar 1000 ,apr 7000,may 500 write query to display his next salary "increse" or "decrese" as compare to previous month ,three column in the employee table empname ,salarydate,salary ?

Answered by: tinku981 on: Jun 14th, 2013

This can be done by analytical function using the LEAD function.

Answered by: amr on: Jan 20th, 2013

Code
  1. SELECT empname, salarydate, salary, (case when salary < LAG(salary, 1, 0) OVER (ORDER BY empname, salarydate) then increase else decrease end) AS trend
  2. FROM emp;

Passing a function name in parameter

Asked By: srividhya_85 | Asked On: Jan 7th, 2013

Can we pass a function name as a parameter value?? Will it have any effect in performance degradation when used in a large online transaction process?

Answered by: tinku981 on: Jun 14th, 2013

About performance, it depends upon the computations you will be performing inside that function. Also, if we are using (user-defined) function in SQL (rather than creating PL/SQL), then it will give better performance.

While using user-defined functions, we need to avoid Mutating functions error.

Answered by: murugesan on: Jan 19th, 2013

Yes we can pass but the function should return a value of type appropriate for the calling function

Example

round(trunc(100))

In real time projects where we use triggers, give me an example.

Asked By: ramchandar | Asked On: Apr 24th, 2013

Answered by: tinku981 on: Jun 13th, 2013

We need to use triggers whenever we wish to perform an automatic operation on a DML query. In our organization we have Unix and OpenVMS servers. Due to some reason, Unix server is used to send and re...

Answered by: talktobishu on: May 20th, 2013

We use trigger when we want some event should happen automatically on certain desirable scenario. Lets take an example. You have a table which changes frequently,now you want to know how many times a...

What is the difference between ref cursor & normal cursor?

Asked By: SaratKumar | Asked On: Oct 10th, 2005

Answered by: rammohan on: Apr 8th, 2013

REF cursor is a dynamic cursor where as normal cursor is static cursor,In dynamic cursor single segment are process multiple SELECT statements dynamically at run time, where as in normal cursor we process only one select statement

Answered by: Ramesh.RV on: Feb 26th, 2013

The main reason is refcursor is a address it give the address of the location instead of item. It hold the different type of structures. Normal cursor holds a one structure of the table.

Oracle query execution

Asked By: snehalberde | Asked On: Aug 27th, 2008

What is the actual processing being done at backend or server side to retrive the data or complete the dml operation.

Answered by: KSPradeep Rayavarapu on: Jan 28th, 2013

The order of Query Execution is following
1)From Clause
2)Where Clause
3)Group By Clause
4)Having Clause
5)Select
6)Order By Clause

Answered by: Sachin Garg on: May 22nd, 2012

Hi Friends, When we pass a SELECT query it executes under some phases. That includes 1) Checking by Server Process - Which type of Query is this - Sql or Pl/sql. 2) If sql - server process puts the ...

When I type 'ed' or 'edit' in Oracle I did n't presented by any editor,what is the problem?

Asked By: haisubbu | Asked On: Feb 25th, 2006

Answered by: SRINIVAS on: Dec 22nd, 2012

You just copy the SQLPlusw file and copy in another location and access from that copied file.
I think you will not get the ed or edit problem.

Answered by: Koonal saxena on: Dec 20th, 2012

Hi just right click on sql application->run as administrator

What is pipeline view?Please mail to sarat.Puranam@bt.Com

Asked By: SaratKumar | Asked On: Oct 10th, 2005

Answered by: janardhan akki on: Sep 26th, 2012

In Case of normal views when ever u call the view it will get data from base table......

in case of pipeline view: if u call the view it vil get data from another view .....means it also logically getting data from base tables only.......the inner view may be materialized view....

What is materialised view?

Asked By: rekha | Asked On: Nov 17th, 2005

Answered by: shanitha on: Sep 11th, 2012

Materialized view is a database object that contains the results of a query..

Answered by: subodhrnc on: May 19th, 2008

A materialized view (MV) is similar to a view but the data is actually stored on disk (view that materializes). Materialized views are often used for summary and pre-joined tables, or just to make a s...

What are the tools for Oracle ?

Asked By: amarjit singh | Asked On: Jan 24th, 2007

Answered by: RAM on: Aug 8th, 2012

TOAD,PUTTY WITH LIINUX/UNIX ,ORACLE FORMS,ORACLE REPORTS.

Answered by: srikanth on: Jul 22nd, 2012

Toad, Putty,Bmc remedy

First | Prev | | Next | Last Page

 

 

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Question Categories

Oracle Backup and Recovery Interview Questions

Oracle Basics Interview Questions

Oracle Architecture Interview Questions

D2K Interview Questions

Data Access Interview Questions

Oracle DBA Interview Questions

Oracle security Interview Questions

Database Tuning Questions

Oracle Distributed Processing Interview Questions

Oracle Concepts Interview Questions

Oracle Memory Management Interview Questions

Oracle Forms Interview Questions

PL/SQL Interview Questions

Programmatic Constructs Interview Questions

RMAN Interview Questions

Real time Oracle Interview Questions

Oracle SQL Interview Questions

SQL*Plus interview Questions

Oracle System Architecture Interview Questions

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.