469,326 Members | 1,320 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

PL/SQL theoretical questions

kiss07
99
Hi,

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

please help..

Arun
Apr 23 '07 #1
70 5554
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;
Apr 24 '07 #2
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
May 3 '07 #3
debasisdas
8,127 Expert 4TB
Dear
mailtothiyagarajan

have u tested the code before posting
May 3 '07 #4
select sal from (select sal from emp order by sal desc) where rownum in(2,3)
May 3 '07 #5
kiss07
99
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..
May 7 '07 #6
kiss07
99
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...
May 7 '07 #7
debasisdas
8,127 Expert 4TB
PLease follow this Link

Hope it might be useful for u.
May 7 '07 #8
debasisdas
8,127 Expert 4TB
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.
May 7 '07 #9
debasisdas
8,127 Expert 4TB
ZERO_DIVIDE exception
=======================
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. I INT:=&I;
  3. J INT:=&J;
  4. K INT;
  5. BEGIN
  6. K:=I/J;
  7. DBMS_OUTPUT.PUT_LINE(K);
  8. EXCEPTION
  9. WHEN ZERO_DIVIDE THEN
  10. RAISE_APPLICATION_ERROR(-20002,'CANT DIVIDE BY ZERO.....!');
  11. WHEN OTHERS THEN
  12. RAISE_APPLICATION_ERROR(-20003,'SOME OTHER ERROR........!');
  13. END;
  14.  
  15.  
May 7 '07 #10
debasisdas
8,127 Expert 4TB
A sample example of
NO_DATA_FOUND exception
============================
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. NAME VARCHAR2(20);
  3. NO INT:=&NO;
  4. BEGIN
  5. SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=NO;
  6. DBMS_OUTPUT.PUT_LINE(NAME);
  7. EXCEPTION
  8. WHEN NO_DATA_FOUND THEN
  9. RAISE_APPLICATION_ERROR(-20002,'NO DATA IS FOUND FOR THIS RECORD.....!');
  10. WHEN OTHERS THEN
  11. RAISE_APPLICATION_ERROR(-20003,'SOME OTHER ERROR........!');
  12. END;
  13.  
May 7 '07 #11
debasisdas
8,127 Expert 4TB
A sample example of
INVALID_CURSOR exception
=======================
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. CURSOR c1 IS SELECT ename FROM emp WHERE ROWNUM < 11;
  3. name emp.ename%TYPE;
  4. BEGIN
  5. OPEN c1;
  6. LOOP
  7. FETCH c1 INTO name;
  8. EXIT WHEN c1%NOTFOUND;
  9. dbms_output.put_line(c1%ROWCOUNT || '. ' || name);
  10. END LOOP;
  11. CLOSE c1;
  12. dbms_output.put_line(c1%ROWCOUNT); 
  13. exception
  14. when invalid_cursor then
  15. raise_application_error(-20001,'invalid operation in cursor');
  16. END;
  17.  
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.
May 7 '07 #12
kiss07
99
Dear Debasisdas,

Am really proud of Your answer. Thanku Very much.

Regards,
Arun..
May 8 '07 #13
kiss07
99
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
May 8 '07 #14
debasisdas
8,127 Expert 4TB
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.
May 8 '07 #15
pradeep kaltari
102 Expert 100+
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.
May 8 '07 #16
kiss07
99
Dear friends,

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

Regards,
Arun..
May 9 '07 #17
kiss07
99
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..
May 9 '07 #18
kiss07
99
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..
May 9 '07 #19
debasisdas
8,127 Expert 4TB
If u r using for loop then no need to open or close the cursor.
May 9 '07 #20
kiss07
99
Can u send some coding pls..
May 9 '07 #21
debasisdas
8,127 Expert 4TB
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. CURSOR DD IS SELECT * FROM EMP WHERE EMPNO=7788;
  4. BEGIN
  5. FOR D IN DD 
  6. LOOP
  7. INSERT INTO EMP10(ID ,NAME)VALUES(D.EMPNO,D.ENAME);
  8. END LOOP;
  9. COMMIT;
  10. END;
  11.  
May 9 '07 #22
debasisdas
8,127 Expert 4TB
Varrying Array-----variable Array

Expand|Select|Wrap|Line Numbers
  1. Create Or Replace Type Mt Is Varray(5) Of Number(4);
  2.  
  3. Create Or Replace Type Name Is Varray(5) Of Varchar2(20);
  4.  
  5. Create Table Vt (id Mt,name Name);
  6.  
  7. Insert Into Vt Values(mt(1,2,3,4,5), Name('a','b','c','d','e'));
  8.  
In This Case We Can't Insert More Records Or Update Values.
May 9 '07 #23
debasisdas
8,127 Expert 4TB
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.

Expand|Select|Wrap|Line Numbers
  1. Create Type Book As Object
  2. (
  3. Book_id Number(3),
  4. Book_name Varchar2(20)
  5. );
  6. ---------
  7. Create Type Book_type As Table Of Book;
  8. ---------
  9. Create Table Student
  10. (
  11. Id Number(2),
  12. Name Varchar2(20),
  13. Bookinfo Book_type
  14. )
  15. Nested Table  Bookinfo Store As Library_info;
  16. ---------
  17. Select Type_name,typecode From User_types;
  18. ---------
  19. Insert Into Student Values
  20. (10,'scott',book_type(book(100,'java'),book(101,'vb')));
  21. ---------
  22. Insert Into The (select Bookinfo From Student) Values (102,'ds');
  23. ---------
  24. Update The(select Bookinfo From Student) Set Book_name='dba' Where Book_id=102;
  25. ---------
  26. Delete From The(select Bookinfo From Student) Where Book_id=101;
  27.  
May 9 '07 #24
debasisdas
8,127 Expert 4TB
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.
May 9 '07 #25
debasisdas
8,127 Expert 4TB
This one is a sample code.
Modify as suitable for you.

Multilevel VARRAY
======================
Expand|Select|Wrap|Line Numbers
  1. declare
  2. type t1 is varray(10) of integer;
  3. type nt1 is varray(10) of t1; -- multilevel varray type
  4. va t1 := t1(2,3,5);
  5. -- initialize multilevel varray
  6. nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va);
  7. i integer;
  8. va1 t1;
  9. begin
  10. -- multilevel access
  11. i := nva(2)(3); -- i will get value 73
  12. dbms_output.put_line('I = ' || i);
  13. -- add a new varray element to nva
  14. nva.extend;
  15. -- replace inner varray elements
  16. nva(5) := t1(56, 32);
  17. nva(4) := t1(45,43,67,43345);
  18. -- replace an inner integer element
  19. nva(4)(4) := 1; -- replaces 43345 with 1
  20. -- add a new element to the 4th varray element
  21. -- and store integer 89 into it.
  22. nva(4).extend;
  23. nva(4)(5) := 89;
  24. end;
  25.  
May 9 '07 #26
debasisdas
8,127 Expert 4TB
Why won't u try such things by yourself instead of posting here.
May 9 '07 #27
kiss07
99
Dear debas,

Thanku very much.


Arun..
May 9 '07 #28
kiss07
99
dear debas,

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

i ask u any possibilities more columns..


Arun
May 9 '07 #29
kiss07
99
Dear debasisdas,

What is the difference between DBMS_SQL vs DYNAMIC SQL.



Regrads,

Arunkumar.
May 9 '07 #30
rsrinivasan
221 100+
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
May 9 '07 #31
kiss07
99
Dear friends,

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

Arun...
May 9 '07 #32
kiss07
99
Dear friends,

Briefly explain about subquery and Correlated sub query.give some

query is better .


Arun..
May 9 '07 #33
frozenmist
179 Expert 100+
Hi Arun,
A sub query is when u have a query inside another query.

eg:
Expand|Select|Wrap|Line Numbers
  1. select name from table_1 where ID in (select id from table_2)
  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:
Expand|Select|Wrap|Line Numbers
  1. select NAME from table_1 a where ID in (select id from table_2 b where b.location= a.location)
  2.  
Cheers
May 9 '07 #34
kiss07
99
dear frozen,

Thanks a lot..
May 9 '07 #35
kiss07
99
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..
May 9 '07 #36
debasisdas
8,127 Expert 4TB
U r most welcome.
May 9 '07 #37
debasisdas
8,127 Expert 4TB
Sampleexample of Multilevel Nested Table
==================================
Expand|Select|Wrap|Line Numbers
  1. declare
  2. type tb1 is table of varchar2(20);
  3. type ntb1 is table of tb1; -- table of table elements
  4. type tv1 is varray(10) of integer;
  5. type ntb2 is table of tv1; -- table of varray elements
  6. vtb1 tb1 := tb1('one', 'three');
  7. vntb1 ntb1 := ntb1(vtb1);
  8. vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3)); -- table of varray elements
  9. begin
  10. vntb1.extend;
  11. vntb1(2) := vntb1(1);
  12. -- delete the first element in vntb1
  13. vntb1.delete(1);
  14. -- delete the first string from the second table in the nested table
  15. vntb1(2).delete(1);
  16. end;
  17.  
Hope that helps you.
May 9 '07 #38
frozenmist
179 Expert 100+
You can call another function within a function
eg:
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION divide (first integer,second integer)
  2. RETURN integer IS
  3. begin
  4. return first/second;
  5. end;
  6.  
  7. Create or replace function main_func(number1 integer,number2 integer,number3 integer) return integer is
  8. v_variable integer;
  9. result integer;
  10. begin
  11.  
  12. v_varaiable:=number2*number3;
  13. result:=divide(number1,v_variable);
  14. return result;
  15. end
  16.  
You cant have DML statements inside functions.

Cheers
May 9 '07 #39
debasisdas
8,127 Expert 4TB
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 .
May 9 '07 #40
debasisdas
8,127 Expert 4TB
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)
May 9 '07 #41
debasisdas
8,127 Expert 4TB
For further examples follow the Link


and check post number 21,22,23,24,25
May 9 '07 #42
kiss07
99
Dear friends frozen and debas,

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



Arun..
May 10 '07 #43
kiss07
99
Dear friends,

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



Arun...
May 10 '07 #44
kiss07
99
Dear friends,

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


Arun..
May 10 '07 #45
kiss07
99
Dear friend,

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



Arun..
May 10 '07 #46
pradeep kaltari
102 Expert 100+
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.
May 10 '07 #47
frozenmist
179 Expert 100+
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
May 10 '07 #48
debasisdas
8,127 Expert 4TB
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.
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. TYPE Definition IS RECORD
  3. (
  4. word VARCHAR2(20),
  5. meaning VARCHAR2(200)
  6. );
  7.  
type
Expand|Select|Wrap|Line Numbers
  1. TYPE Dictionary IS VARRAY(2000) OF Definition;
  2. lexicon Dictionary := Dictionary();
  3.  
procedure
Expand|Select|Wrap|Line Numbers
  1. PROCEDURE add_entry
  2. (
  3. word_list IN OUT NOCOPY Dictionary
  4. ) IS
  5. BEGIN
  6. word_list(1).word := 'sort';
  7. lexicon(1).word := 'arrange';
  8. END;
  9. BEGIN
  10. lexicon.EXTEND;
  11. add_entry(lexicon);
  12. dbms_output.put_line(lexicon(1).word);
  13. END;
  14.  
May 10 '07 #49
frozenmist
179 Expert 100+
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
May 10 '07 #50

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

16 posts views Thread by Materialised | last post: by
reply views Thread by softwareengineer2006 | last post: by
reply views Thread by connectrajesh | last post: by
8 posts views Thread by Krypto | last post: by
reply views Thread by Joseph Ferris | last post: by
reply views Thread by ramu | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.