| How do I execute this package [message #369698] |
Thu, 17 August 2000 13:45  |
stant_98
Messages: 4
Registered: February 2000
|
Junior Member |
|
|
Hi, guys!
Real quick question here:
How do I exec this from SQL PLUS:
CREATE OR REPLACE PACKAGE emp_actions AS
TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);
CURSOR desc_salary RETURN EmpRecTyp;
FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS
CURSOR desc_salary RETURN EmpRecTyp
IS
SELECT empno, sal
FROM emp
ORDER BY sal DESC;
FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp
IS
emp_rec EmpRecTyp;
BEGIN
OPEN desc_salary;
FOR i IN 1..n LOOP
FETCH desc_salary INTO emp_rec;
END LOOP;
CLOSE desc_salary;
RETURN emp_rec;
END nth_highest_salary;
END emp_actions;
/
|
|
|
|
|
|
|
|
| Re: How do I execute this package [message #369733 is a reply to message #369698] |
Thu, 14 September 2000 16:15  |
mimi
Messages: 11
Registered: August 2000
|
Junior Member |
|
|
you can call your package from an annonymous block in sqlplus.
firstly, you need to declare a variable that will take the value of your rec. itis wrong the way you have used it
e.g emp_rec EmpRec
then in the annony block, declare another variable of record that the value returned by your function
declare
d emp_rec;
begin
d =: emp_actions.nth_highest_salary(actual value of your n);
end;
|
|
|
|
| Re: How do I execute this package [message #369734 is a reply to message #369698] |
Thu, 14 September 2000 16:16 |
mimi
Messages: 11
Registered: August 2000
|
Junior Member |
|
|
you can call your package from an annonymous block in sqlplus.
firstly, you need to declare a variable that will take the value of your rec. itis wrong the way you have used it
e.g emp_rec EmpRec
then in the annony block, declare another variable of record that the value returned by your function
declare
d emp_rec;
begin
d =: emp_actions.nth_highest_salary(actual value of your n);
end;
|
|
|
|
| Re: How do I execute this package [message #369735 is a reply to message #369698] |
Thu, 14 September 2000 16:17 |
mimi
Messages: 11
Registered: August 2000
|
Junior Member |
|
|
you can call your package from an annonymous block in sqlplus.
firstly, you need to declare a variable that will take the value of your rec. itis wrong the way you have used it
e.g emp_rec EmpRec
then in the annony block, declare another variable of record that the value returned by your function
declare
d emp_rec;
begin
d =: emp_actions.nth_highest_salary(actual value of your n);
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
