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
In real time projects where we use triggers, give me an example.
What is the difference between ref cursor & normal cursor?
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.
What is the difference between trim and truncate functions in Oracle
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...
What is the actual processing being done at backend or server side to retrive the data or complete the dml operation.
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 ...
If you have an open cursor on a table, modify the same table and commit, what would happen?
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.
a employee geting 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 employe table empname ,salarydate,salary ?
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;
Passing a function name in parameter
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?
Yes we can pass but the function should return a value of type appropriate for the calling function
Example
round(trunc(100))
Which one is faster delete/truncate? Why?
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...
When I type 'ed' or 'edit' in Oracle I did n't presented by any editor,what is the problem?
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
When we give select * from emp; how does Oracle respond:
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...
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?
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
Write a query to display all the odd numbers from a table
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
What is pipeline view?Please mail to sarat.Puranam@bt.Com
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...
What are the tools for Oracle ?
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;
You have a batch job which has hundreds of SQL queries in it. Earlier is was finishing in expected time. However, now it is taking twice the time. There is no cpu utilization issue due to other jobs. How would you know that which of the queries are taking more time now and why?
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
What is the difference between snapshot & materialized view ?
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.
What is snapshot-too-old error? How does snapshot-too-old error can be rectified in Oracle 9i?
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...
Oracle Backup and Recovery Interview Questions
Oracle Basics Interview Questions
Oracle Architecture Interview Questions
Data Access Interview Questions
Oracle DBA Interview Questions
Oracle security Interview Questions
Oracle Distributed Processing Interview Questions
Oracle Concepts Interview Questions
Oracle Memory Management Interview Questions
Oracle Forms Interview Questions
Programmatic Constructs Interview Questions
Real time Oracle Interview Questions
Oracle SQL Interview Questions
Oracle System Architecture Interview Questions
Break on is compulsory for compute function.