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
mailtothiyagarajan

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]

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]

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]

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.

[kiss07]

Can u send some coding pls..

[debasisdas]

Usually this is the life cycle of cursor


DECLARE-->OPEN-->FETCH-->CHECK LAST RECORD-->CLOSE

But if u r using FOR-NO NEED TO OPEN AND CLOSE.

 
DECLARE
CURSOR DD IS SELECT * FROM EMP WHERE EMPNO=7788;
BEGIN
FOR D IN DD 
LOOP
INSERT INTO EMP10(ID ,NAME)VALUES(D.EMPNO,D.ENAME);
END LOOP;
COMMIT;
END;
 

[debasisdas]

Varrying Array-----variable Array

Create Or Replace Type Mt Is Varray(5) Of Number(4);
 
Create Or Replace Type Name Is Varray(5) Of Varchar2(20);
 
Create Table Vt (id Mt,name Name);
 
Insert Into Vt Values(mt(1,2,3,4,5), Name('a','b','c','d','e'));
 

In This Case We Can't Insert More Records Or Update Values.

[debasisdas]

Nested Table
=======================
In Nested Table We Can Add Unlimited No Of Records Which Is Not Possible In Varray.the Records Will Be Stored In The Nested Table Which Is Physically Stored Out Side The Table.
To Perform Any Dml We Have To Use "the" Operator.
Records Can't Be Directly Inserted Or Retrived From The Nested Table, For That We Have To Take Help Of The Main Table.

Create Type Book As Object
(
Book_id Number(3),
Book_name Varchar2(20)
);
---------
Create Type Book_type As Table Of Book;
---------
Create Table Student
(
Id Number(2),
Name Varchar2(20),
Bookinfo Book_type
)
Nested Table  Bookinfo Store As Library_info;
---------
Select Type_name,typecode From User_types;
---------
Insert Into Student Values
(10,'scott',book_type(book(100,'java'),book(101,'vb')));
---------
Insert Into The (select Bookinfo From Student) Values (102,'ds');
---------
Update The(select Bookinfo From Student) Set Book_name='dba' Where Book_id=102;
---------
Delete From The(select Bookinfo From Student) Where Book_id=101;
 

[debasisdas]

NOTE:

1.CAN'T DROP THE NESTED TABLE OR SELECT FROM THE NESTED TABLE directly.
2.IF THE MAIN TABLE IS DROPED IT AUTOMATICALLY DROPS THE NESTED TABLE.

[debasisdas]

This one is a sample code.
Modify as suitable for you.

Multilevel VARRAY
======================

declare
type t1 is varray(10) of integer;
type nt1 is varray(10) of t1; -- multilevel varray type
va t1 := t1(2,3,5);
-- initialize multilevel varray
nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va);
i integer;
va1 t1;
begin
-- multilevel access
i := nva(2)(3); -- i will get value 73
dbms_output.put_line('I = ' || i);
-- add a new varray element to nva
nva.extend;
-- replace inner varray elements
nva(5) := t1(56, 32);
nva(4) := t1(45,43,67,43345);
-- replace an inner integer element
nva(4)(4) := 1; -- replaces 43345 with 1
-- add a new element to the 4th varray element
-- and store integer 89 into it.
nva(4).extend;
nva(4)(5) := 89;
end;
 

[debasisdas]

Why won't u try such things by yourself instead of posting here.

[kiss07]

Dear debas,

Thanku very much.


Arun..

[kiss07]

dear debas,

i try such things. i define upto two columns only in foreign keys .So

i ask u any possibilities more columns..


Arun

[kiss07]

Dear debasisdas,

What is the difference between DBMS_SQL vs DYNAMIC SQL.



Regrads,

Arunkumar.

[rsrinivasan]

Hi,

DBMS_SQL:

1. It is very complicated package.
2. And it is very slow.
3. We can execute from Client side applications.

Dynamic SQL:

1. Works with all SQL data types.
2. In this u can fetch multiple columns. whereas we can't in DBMS_SQL.

Thanks,
Srinivasan r

[kiss07]

Dear friends,

How can i call function to function ? is possible ?pls code..
commit is possible inside functions?

Arun...

[kiss07]

Dear friends,

Briefly explain about subquery and Correlated sub query.give some

query is better .


Arun..

[frozenmist]

Hi Arun,
A sub query is when u have a query inside another query.

eg:

select name from table_1 where ID in (select id from table_2)
 

The query in the where clause is called a sub query.

A correlated sub query means that the outer query value will affect the result of the inner query.
eg:

select NAME from table_1 a where ID in (select id from table_2 b where b.location= a.location)
 

Cheers

[kiss07]

dear frozen,

Thanks a lot..

[kiss07]

Dear friends,

I thinking study in Oracle Apps.Anybody tell me pls.

which part i studying? oracle apps technical or functional.I have no ideas

in oracle apps. pls guide me.


Arun..

[debasisdas]

U r most welcome.

[debasisdas]

Sampleexample of Multilevel Nested Table
==================================

declare
type tb1 is table of varchar2(20);
type ntb1 is table of tb1; -- table of table elements
type tv1 is varray(10) of integer;
type ntb2 is table of tv1; -- table of varray elements
vtb1 tb1 := tb1('one', 'three');
vntb1 ntb1 := ntb1(vtb1);
vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3)); -- table of varray elements
begin
vntb1.extend;
vntb1(2) := vntb1(1);
-- delete the first element in vntb1
vntb1.delete(1);
-- delete the first string from the second table in the nested table
vntb1(2).delete(1);
end;
 

Hope that helps you.

[frozenmist]

You can call another function within a function
eg:

CREATE OR REPLACE FUNCTION divide (first integer,second integer)
RETURN integer IS
begin
return first/second;
end;
 
Create or replace function main_func(number1 integer,number2 integer,number3 integer) return integer is
v_variable integer;
result integer;
begin
 
v_varaiable:=number2*number3;
result:=divide(number1,v_variable);
return result;
end
 

You cant have DML statements inside functions.

Cheers

[debasisdas]

I don't understand why u put such questions in the forum.
You are asking for differences, can you tell me any single similarity.


while DBMS_SQL is a predefined package.
Since performance wise this is very slow ,it is not used any more.
It is still supported in version 10 only to support backward compartability.

DYNAMIC SQL is how to call SQL from with in PL/SQL.


So there is no comparision between these two .

[debasisdas]

Subqueries
=================
The sub query is often referred to as a nested SELECT, Sub - SELECT, or inner SELECT statement.

The sub query executes once before the main query.
The result of the sub query is used by the main query (outer Query).

You can place the sub query in a number of SQL clauses.
WHERE clause
HAVING clause
FROM clause (Inline sub query/inline view)

Restrictions
------------------
1.THE NESTED QUERY MUST RETURN A SINGLE COLUMN.
2.THE BETWEEN OPERATOR CAN'T BE USED IN SUBQUERIES.
3.THE RESULT CAN ONLY CONTAIN COLUMNS FROM THE TABLES REFERENCED IN THE OUTER MOST QUERIES.

Types of sub query
---------------------------
Single -row sub queries: -
Queries that return only one row from the inner SELECT statement.

Multiple -row sub queries: -
Return more than one row from the inner SELECT statement.

Syntax
---------
SELECT select_list FROM table
WHERE expr operator (SELECT select_list FROM table);

**Do not add an ORDER BY clause to a sub query.
*Use single-row operators with single-row sub queries.
Single - row operators (>, >=, =, <>, <, <=)

*Use multiple-row operators with multiple-row sub queries.
Multiple - row operators (IN, ANY, SOME, ALL)

[debasisdas]

For further examples follow the Link


and check post number 21,22,23,24,25

[kiss07]

Dear friends frozen and debas,

can u explain about PRAGMA RESTRICT_REFERENCES.
Sample code is better pls..



Arun..

[kiss07]

Dear friends,

pls give me a program of NO COPY parameter.i refer some things can't understood.pls help me.



Arun...

[kiss07]

Dear friends,

What is the dup_val_on _index exception .when this exception is raised?
I expecting better Programs..


Arun..

[kiss07]

Dear friend,

How to use cursor With execute immediate statement?pls tell me.. sample prgm..



Arun..

[pradeep kaltari]

Dear friend,

How to use cursor With execute immediate statement?pls tell me.. sample prgm..



Arun..

Hi Arun,
Could you be more clear as in what exactly you are looking for.

[frozenmist]

Hi Arun,
On a column you have a unique index i.e; only unique values will exist in that column, no duplicates should be there.
This Error occurs if you try to insert or update so that a duplicate value is created in column with unique index.
Hope you understood,
Cheers

[debasisdas]

In the example below, procedure ADD_ENTRY refers to varray LEXICON both as a parameter and as a global variable. When ADD_ENTRY is called, the identifiers WORD_LIST and LEXICON point to the same varray.

DECLARE
TYPE Definition IS RECORD
(
word VARCHAR2(20),
meaning VARCHAR2(200)
);
 

type

TYPE Dictionary IS VARRAY(2000) OF Definition;
lexicon Dictionary := Dictionary();
 

procedure

PROCEDURE add_entry
(
word_list IN OUT NOCOPY Dictionary
) IS
BEGIN
word_list(1).word := 'sort';
lexicon(1).word := 'arrange';
END;
BEGIN
lexicon.EXTEND;
add_entry(lexicon);
dbms_output.put_line(lexicon(1).word);
END;
 

[frozenmist]

Hi Arun,
Just read through this page.You should be able to understand it. There is an example also at the end.

http://download-east.oracle.com/docs...3_elems039.htm

Cheers