Home » SQL & PL/SQL » SQL & PL/SQL » how to execute this procedure
how to execute this procedure [message #261237] Wed, 22 August 2007 03:42 Go to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

Here below is my package and procedure..

CREATE OR REPLACE PACKAGE emp_actions AS -- spec

/* TYPE EmpRecTyp IS RECORD (emp_id number, salary number);
CURSOR desc_salary RETURN EmpRecTyp;*/

type erec is record (eid tw_emp.employee_id%type,jid tw_emp.job_id%type,nam tw_emp.ename%type);
PROCEDURE hire_employee (evar erec);
PROCEDURE fire_employee (emp_id NUMBER);

END emp_actions;





CREATE OR REPLACE PACKAGE BODY emp_actions AS -- body

/*CURSOR desc_salary RETURN EmpRecTyp IS
SELECT a.employee_id, a.job_id FROM employees a ORDER BY a.job_id DESC;*/

PROCEDURE hire_employee (evar erec) IS
BEGIN
INSERT INTO tw_emp VALUES (empno_seq.NEXTVAL,evar.jid,evar.nam);
END hire_employee;

PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM tw_emp WHERE employee_id = emp_id;
commit;
END fire_employee;
END emp_actions;

I tried to execute in below manner. But its errored out.

exec emp_actions.hire_employee
(evar.jid=>'jackson',evar.nam=>'fffff');

Thanks in advance
Re: how to execute this procedure [message #261239 is a reply to message #261237] Wed, 22 August 2007 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to follow the guidelines?

Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: how to execute this procedure [message #261247 is a reply to message #261237] Wed, 22 August 2007 03:51 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

How to excute a procedure which is a part of this package. How can we pass arguments to that procedure.

CREATE OR REPLACE PACKAGE emp_actions AS -- spec

/* TYPE EmpRecTyp IS RECORD (emp_id number, salary number);
CURSOR desc_salary RETURN EmpRecTyp;*/

type erec is record (eid tw_emp.employee_id%type,jid tw_emp.job_id%type,nam tw_emp.ename%type);
PROCEDURE hire_employee (evar erec);
PROCEDURE fire_employee (emp_id NUMBER);

END emp_actions;





CREATE OR REPLACE PACKAGE BODY emp_actions AS -- body

/*CURSOR desc_salary RETURN EmpRecTyp IS
SELECT a.employee_id, a.job_id FROM employees a ORDER BY a.job_id DESC;*/

PROCEDURE hire_employee (evar erec) IS
BEGIN
INSERT INTO tw_emp VALUES (empno_seq.NEXTVAL,evar.jid,evar.nam);
END hire_employee;

PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM tw_emp WHERE employee_id = emp_id;
commit;
END fire_employee;
END emp_actions;



I tried to execute a procedure which is a part of above package

exec emp_actions.hire_employee(evar.jid=>'jackson',evar.nam=>'fffff');


Thanks in advance

[Updated on: Wed, 22 August 2007 03:53]

Report message to a moderator

Re: how to execute this procedure [message #261262 is a reply to message #261247] Wed, 22 August 2007 04:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Did it ever occur to you that the actual error that you're getting might be useful to us in helping fix the error?

Fortunately, we're very good at this.

You're problem is that you are passing in the wrong type and number of parameters to your procedure.
It is expecting a single parameter of type erec, and you're giving it two meaningless asignation statements.

Try this:
DECLARE
  r_in  erec;
BEGIN
  r_in.jid := 'jackson';
  r_in.nam := 'fffff';

  emp_actions.hire_employee(r_in);
END;


You probably want to add a function to that package that takes an id and a name, and builds and returns a variable of type erec, that you can use in the rest of your code.
Re: how to execute this procedure [message #261273 is a reply to message #261262] Wed, 22 August 2007 04:30 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
I tried this but its giving an error as erec is not declared.
Re: how to execute this procedure [message #261350 is a reply to message #261273] Wed, 22 August 2007 06:33 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, I know what the error is.
I'll give you a clue - what would you have to add to the line " r_in erec;" to correctly descibe where type EREC is declared?
Previous Topic: Bulk inserts
Next Topic: Run through files with wildcard..?
Goto Forum:
  


Current Time: Tue Dec 06 10:27:53 CST 2016

Total time taken to generate the page: 0.12639 seconds