Oracle Interview Questions

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

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.

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...

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...

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...

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

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...

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 ...

To avoid use:

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

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

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

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;

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

Use Case in update statement

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

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.

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

Break on is compulsory for compute function.

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.

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...

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

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

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.

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

Example

round(trunc(100))

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...

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...

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

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.

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

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 ...

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.

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

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....

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

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...

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

Toad, Putty,Bmc remedy