Home » RDBMS Server » Server Administration » How do I execute this package
How do I execute this package [message #369698] Thu, 17 August 2000 13:45 Go to next message
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 #369701 is a reply to message #369698] Thu, 17 August 2000 18:12 Go to previous messageGo to next message
Nick
Messages: 64
Registered: February 2000
Member
This text is in a file, I'm guessing. Let's say that file is called filename.sql; in SQLPLUS, you would type '@package' (no quotes) to execute the contents of the file.
Re: How do I execute this package [message #369703 is a reply to message #369698] Fri, 18 August 2000 08:55 Go to previous messageGo to next message
stant_98
Messages: 4
Registered: February 2000
Junior Member
I meant how do I execute it from SQL PLUS, not how to compile it.
I've compiled it already now I want to try invoking it.
How do I build PL/SQL block that calls it?

Thanks a lot!
Re: How do I execute this package [message #369733 is a reply to message #369698] Thu, 14 September 2000 16:15 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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;
Previous Topic: OCILogon problems
Next Topic: Re: Installing Oracle 8
Goto Forum:
  


Current Time: Fri Mar 29 04:32:28 CDT 2024