PL/SQL theoretical questions

[kiss07]

Hi,

i want 2 and 3 rd max salary in a table .i want a single query.

please help..

Arun

[mailtothiyagarajan]

Hi,

i want 2 and 3 rd max salary in a table .i want a single query.

please help..

Arun

I have used the inline view concept for this


Query for nth max salary :

select s.salary from
(
(
select dense_rank() over (order by max(salary) desc) n,salary
from
employee
) s
)
where s.n=&n;

[bonzi1405]

Below will return n,n+1 higest salary of a table.


select min(salary) sal
from
( select salary from employee order by salary desc)
where rownum<=&n
union
select min(salary) sal
from
( select salary from employee order by salary desc)
where rownum<=&n+1

[debasisdas]

Dear
mailtothiyagara jan

have u tested the code before posting

[Mani kansal]

select sal from (select sal from emp order by sal desc) where rownum in(2,3)

[kiss07]

Exceptions

Dear friends,

I known about pre defined exception:
i want a sample programs in each of predefined exception.

example:

invalid cursor :

one sample program. when exception is raised in ivvalid cursor
same as others exceptions...


pls anybody help....

arun..

[kiss07]

sql Tunings

Dear debas,

Can u explain about sql tuning:

what are the steps are doing sql tuning . I want to know about basic ideas

in sql tuning and performance tuning. what are the types?..

Pls...

Arunkumar...

[debasisdas]

PLease follow this Link

Hope it might be useful for u.

[debasisdas]

Invalid cursor exception is raised when u try to do somthing which is not allowed .
for ex

accesing the cursor variable after the cursor is closed.

[debasisdas]

ZERO_DIVIDE exception
=============== ========

DECLARE
I INT:=&I;
J INT:=&J;
K INT;
BEGIN
K:=I/J;
DBMS_OUTPUT.PUT_LINE(K);
EXCEPTION
WHEN ZERO_DIVIDE THEN
RAISE_APPLICATION_ERROR(-20002,'CANT DIVIDE BY ZERO.....!');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20003,'SOME OTHER ERROR........!');
END;

[debasisdas]

A sample example of
NO_DATA_FOUND exception
=============== =============

DECLARE
NAME VARCHAR2(20);
NO INT:=&NO;
BEGIN
SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=NO;
DBMS_OUTPUT.PUT_LINE(NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20002,'NO DATA IS FOUND FOR THIS RECORD.....!');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20003,'SOME OTHER ERROR........!');
END;

[debasisdas]

A sample example of
INVALID_CURSOR exception
=============== ========

DECLARE
CURSOR c1 IS SELECT ename FROM emp WHERE ROWNUM < 11;
name emp.ename%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO name;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line(c1%ROWCOUNT || '. ' || name);
END LOOP;
CLOSE c1;
dbms_output.put_line(c1%ROWCOUNT); 
exception
when invalid_cursor then
raise_application_error(-20001,'invalid operation in cursor');
END;

In the above example the cursor is invalid because of trying to access the cursor attribute %rowcount after the cursor is closed(in 2nd dbms_output ......). This raises INVALID_CURSOR exception.

[kiss07]

Dear Debasisdas,

Am really proud of Your answer. Thanku Very much.

Regards,
Arun..

[kiss07]

Error handling

dear friends,

I have some doubt .
This is sample program

DECLARE
---
BEGIN
SELECT employee_id, last_name, salary INTO emp_id, emp_lastname,
emp_salary FROM employees WHERE employee_id = 120;
UPDATE emp_name SET salary = salary * 1.1 WHERE employee_id = emp_id;
DELETE FROM emp_name WHERE employee_id = 130;
INSERT INTO emp_name VALUES (emp_id, emp_lastname, emp_salary);

--SOME DML STATEMENTS
--this is sample . but contain lot of statemnts are there in this body.
Delete .............
insert ...............
select ............... ...

END;


i got an error .i don't known which statement.
How can i find out which statement is got an error.How can i handle this error statement..

Pls explain briefly........ ...


Arunkumar

[debasisdas]

If u r using sql* plus

To solve this better use any tool like TOAD or PLSQL DEVELOPER.

Since u r new to pl/sql it will be difficult for you to find the bug using sql* plus.