Oracle Interview Questions

Break on is compulsory for compute function.

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.

Trim is Oracle pre-defined function it is used for deleting free spaces of given string.

Truncate is Oracle DDL command it is used for deleting a table.

->Trim is a oracle system defined function used for deleting the free spaces in a given string or number.

->Truncate is a ddl command in oracle used for deleting all the data of a 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 ...

changes will save..

To provide read consistency, the same records as per when the cursor was opened will be used till the cursor is closed.

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;

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

Example

round(trunc(100))

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

truncate will be faster than delete .because delete will have an copy of deleted item where us truncate will not have a copy then delete is use to delete from the particular form and truncate will del...

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

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.

This is good question. You have to understand it properly. When You Write Select * from EMP; First Oracle try to find weather this Object (EMP) is there in the data base or not , If it is not there t...

By Using the Exception Block ,
when_no_data_found then
//Put your code here
when_too_many_rows then
//Put your code here
when others then
//Put your code here
end;

using Exception block to catch error

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;

truncate table table name... by using this command we can delete all the data from the table which can not be rollback

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

Code
SELECT TO_CHAR(ADD_MONTHS (SYSDATE,-LEVEL ),DD-MON-YYYY)  AS DATES    FROM DUAL   
CONNECT BY LEVEL 
<25 ORDER BY LEVEL DESC

Try this one:

Code
SELECT ADD_MONTHS (TRUNC(SYSDATE), (-1*(LEVEL-1))) date_calculated
  FROM dual
CONNECT BY LEVEL < 25
ORDER BY date_calculated;

Please find the below methods for finding one of the query which is taking longer time in the batch script. Methods :- 1) Put the dbms_output after each of the SQL query. 2) Create a logfile using th...

1. At first enable the trace, at the beginning of the sqls.
2. Then check the trace file.
3. Using tkprof create the output file.
4. According to explain plan check the elapsed time for each query.
5. Then tune them

View : View exists logically.Its a virtual table.Whenever we are firing a query on a view we are getting a data from a table. But for a snapshot it is not a case... snapshot itself store a data.. refr...

snapshot is just like print screen. or materialized view is actual table.
snapshot is read only.you will find it same stage every time.
but materialized view is update mode.always you will find it a new stage.

UNDO tablespace contain the old information. it is circular process. it will overwrite the first extents. so if you need the overwritten statement that time it will show as snap_shot is to old.

First you have to understand that Oracle is a MVCC (multiversion concurrency control) or "repeatable read" database system.  Queries are returned with all rown consistent with respect to the point in...