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

[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]

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.

[pradeep kaltari]

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.

Arun,
You can also use
SHOW ERRORS command. This will also give you the line number where the error has occured.

[kiss07]

Dear friends,

what is nested table?
i want a sample program in nested table and varray in oracle pl/sql.

Regards,
Arun..

[kiss07]

Dear debas,

Is there possible a cursor,not needed open and close in static cursor.one person ask to me in interview.
I think is possible .You have any idea ? possible means some code to me..

Regards,
Arun..

[kiss07]

Dear debas,

1)how many unique are allowed in a table?
2) How many foreign key are allowed in table?
3)create trigger t_trig
after insert on emp

begin

insert into log values(username ,sysdate);

end;


now i insert 100 records in emp table .How many records insert in log table

choose:

1)100 records.
2) 1 record.


thanks,

Arun..

[debasisdas]

If u r using for loop then no need to open or close the cursor.