Home » SQL & PL/SQL » SQL & PL/SQL » unknown procedure or function
unknown procedure or function [message #632186] Tue, 27 January 2015 03:15 Go to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo all Smile

i am am getting the following error on my program,
ORA-06550: Line 2, Column4: PLS-00221:'Hire_Emp' is not procedure or not defined
ORA-06550: Line 2, Column4: PL/SQL Statement ignored

can anyone please help me where is the Problem?

PS. the two other procedure works but not the first one(Function- Hire_Emp).

Thanking you.
Regards,


CREATE SEQUENCE emp_sequence 
    START WITH 8000 
    INCREMENT BY 10;

CREATE OR REPLACE PACKAGE Employee_Management
AS
FUNCTION hire_emp( name        VARCHAR2,
                      job         VARCHAR2,
                      mgr         NUMBER,
                      hiredate    DATE,
                      sal         NUMBER,
                      comm        NUMBER,
                      Deptno      NUMBER)
      RETURN NUMBER;

PROCEDURE fire_emp (emp_id IN NUMBER);

PROCEDURE sal_raise (emp_id IN NUMBER, sal_incr IN NUMBER);

END Employee_Management;
/

CREATE OR REPLACE PACKAGE BODY Employee_Management
AS
   FUNCTION hire_emp (name        VARCHAR2,
                      job         VARCHAR2,
                      mgr         NUMBER,
                      hiredate    DATE,
                      sal         NUMBER,
                      comm        NUMBER,
                      Deptno      NUMBER)
      RETURN NUMBER
   IS
      new_empno   NUMBER (6);
   BEGIN
      SELECT emp_sequence.NEXTVAL INTO new_empno FROM DUAL;

      INSERT INTO emp
           VALUES (new_empno,
                   name,
                   job,
                   mgr,
                   hiredate,
                   sal,
                   comm,
                   deptno);

      RETURN (new_empno);
   END hire_emp;

   PROCEDURE fire_emp (emp_id IN NUMBER)
   IS
   BEGIN
      DELETE FROM emp
            WHERE empno = emp_id;

      IF SQL%NOTFOUND
      THEN
         raise_application_error (
            -20011,
            'Invalid Employee ID: ' || TO_CHAR (emp_id));
      END IF;
   END fire_emp;

   PROCEDURE sal_raise (emp_id IN NUMBER, sal_incr IN NUMBER)
   IS
   BEGIN
      UPDATE emp
         SET sal = sal + sal_incr
       WHERE empno = emp_id;

      IF SQL%NOTFOUND
      THEN
         raise_application_error (
            -20011,
            'Invalid Employee Number: ' || TO_CHAR (emp_id));
      END IF;
   END sal_raise;
END Employee_Management;
/

BEGIN
   employee_management.hire_emp ('dipesh',
                                 'manager',
                                 7566,
                                 '2014.10.10',
                                 5000,
                                 200,
                                 30);
--employee_management.sal_raise(7934, 1000);
--employee_management.fire_emp(7934);

END;
/
Re: unknown procedure or function [message #632187 is a reply to message #632186] Tue, 27 January 2015 03:28 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member


FUNCTION hire_emp;
This is a function and returning some value also. So you have to store in some variable what you get in return after calling the function.
Re: unknown procedure or function [message #632188 is a reply to message #632187] Tue, 27 January 2015 03:36 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
hire_emp is function, you've called it as though it's a procedure.
Functions are called like this:
<variable> := <function>
Re: unknown procedure or function [message #632189 is a reply to message #632188] Tue, 27 January 2015 03:47 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
thankx for your promt reply .. but i m still confused.. :/ can you please help me?
I have returned type Number (new_empno) as it defined in the funtion and have called the package.function at annonymus block...
so where should i Need to call it?

regards
Re: unknown procedure or function [message #632190 is a reply to message #632189] Tue, 27 January 2015 03:51 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
palpali wrote on Tue, 27 January 2015 15:17
and have called the package.function at annonymus block...
so where should i Need to call it?


As CM already said, in your anonymous block :

<variable> := <function>


It would be better if you show us what you are doing.
Re: unknown procedure or function [message #632191 is a reply to message #632190] Tue, 27 January 2015 03:58 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There is something funny.....

ORA-06550: Line 2, Column4: PLS-00221:'Hire_Emp' is not procedure or not defined

Shouldn't the name in the error message be all in caps, unless you called it with double quotes?

SQL> begin FooBar(1); end;
  2  /
begin FooBar(1); end;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'FOOBAR' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> begin "FooBar"(1); end;
  2  /
begin "FooBar"(1); end;
       *
ERROR at line 1:
ORA-06550: line 1, column 8:
PLS-00201: identifier 'FooBar' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored

Re: unknown procedure or function [message #632204 is a reply to message #632191] Tue, 27 January 2015 07:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
posted code for HIRE_EMP defines a FUNCTION that RETURNS NUMBER; which is different & distinct from a PROCEDURE.

BEGIN
   employee_management.hire_emp ('dipesh',
                                 'manager',
                                 7566,
                                 '2014.10.10',
                                 5000,
                                 200,
                                 30);
--employee_management.sal_raise(7934, 1000);
--employee_management.fire_emp(7934);

END;


above has no place for returned value
Re: unknown procedure or function [message #632205 is a reply to message #632204] Tue, 27 January 2015 07:16 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo BlackSwan
Thank you very much for your Response..
well, is my function Definition above is correct or not?
and how can i return the empid in annonymus block?
can you please help me... i don't know how can i do this..

regards.
Re: unknown procedure or function [message #632206 is a reply to message #632205] Tue, 27 January 2015 07:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
DECLARE
EMPID NUMBER;
BEGIN
EMPID :=    employee_management.hire_emp ('dipesh',
                                 'manager',
                                 7566,
                                 '2014.10.10',
                                 5000,
                                 200,
                                 30);

END;

[Updated on: Tue, 27 January 2015 07:21]

Report message to a moderator

Re: unknown procedure or function [message #632208 is a reply to message #632205] Tue, 27 January 2015 07:25 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo all... thank you for your Suggestion and guidance Smile
well, i found my fehler and solved it.. Smile
so, my final Anonymus Blcok is :

Declare
    new_empno   Number;
BEGIN

   new_empno := Employee_Management.hire_emp (
                                                'aabbcc',
                                                'Clerk',
                                                  null,
                                                '01.12.2014',
                                                300,
                                                200,
                                                30
                                             ); 
                                 
  
END;
/


Thank you all Smile
Re: unknown procedure or function [message #632209 is a reply to message #632205] Tue, 27 January 2015 07:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Is this what you are looking for -

begin
   dbms_output.put_line(package.function(parameters));
end;
/
Re: unknown procedure or function [message #632211 is a reply to message #632209] Tue, 27 January 2015 07:42 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo Lalit,
thank you very much for your input, well its also the another way to insert data thru' my function into emp table Smile
thankx
regards Smile
Re: unknown procedure or function [message #632212 is a reply to message #632209] Tue, 27 January 2015 07:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>well, i found my fehler and solved it.

You are FUNNY!
It appears you could not find your own rear end even after having both your hands jammed into the back of your own pants.
Re: unknown procedure or function [message #632213 is a reply to message #632212] Tue, 27 January 2015 07:53 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Quote:
'2014.10.10'


Why are you inserting a string into hiredate? You should explicitly convert it into a date. Never rely on implicit data type conversion.
Re: unknown procedure or function [message #632214 is a reply to message #632212] Tue, 27 January 2015 07:53 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
I am sorry @BlackSwan.. what do you mean? ..
I tried to solve it myself, and i cound not then i post it here for help and got the Suggestion, guidance and way to solve it.. so i found the way to correct the Problem....
is there anything i did mistake?
actually, the Forum is so, where we can get the help? right?
Re: unknown procedure or function [message #632215 is a reply to message #632214] Tue, 27 January 2015 07:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
palpali wrote on Tue, 27 January 2015 05:53
I am sorry @BlackSwan.. what do you mean? ..
I tried to solve it myself, and i cound not then i post it here for help and got the Suggestion, guidance and way to solve it.. so i found the way to correct the Problem....
is there anything i did mistake?
actually, the Forum is so, where we can get the help? right?


both COOKIEMONSTER & Lalit Kumar B showed you hours ago what was the problem.
All you did was change the variable name in my code to claim your "solution".
Re: unknown procedure or function [message #632216 is a reply to message #632214] Tue, 27 January 2015 07:58 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Thankx a lot @Lalit.. i did so as per your Suggestion. Smile
to_date('2014.01.01', 'YYYY-MM-DD')
actually, I m learning and making my skill better in pl SQL.. thats why i have to ask even the Little things Smile but anyway thanks alot
regards.
Re: unknown procedure or function [message #632217 is a reply to message #632216] Tue, 27 January 2015 08:09 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@palpali, when you think you are dealing with the basics, then it is the time to get back to this wonderful link https://docs.oracle.com/database/121/nav/portal_5.htm

Once in a while everybody is bound to get back to the basics, so don't waste time and spend some quality time to go through the link I posted. Good luck!
Re: unknown procedure or function [message #632218 is a reply to message #632217] Tue, 27 January 2015 08:12 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Quote:
to_date('2014.01.01', 'YYYY-MM-DD')


Different separators, is it a typo or intentional?
Re: unknown procedure or function [message #632219 is a reply to message #632217] Tue, 27 January 2015 08:12 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Thank you very much Smile
Re: unknown procedure or function [message #632240 is a reply to message #632215] Wed, 28 January 2015 01:23 Go to previous message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo @BlackSwan

I am sorry for your Feedback, i have not seen your final Suggestion, that's why i was wonder about your comment.
but anyway, thankx alot Smile
regards.
Previous Topic: Group by clause doubt - Can you please suggest me the query to this problem???
Next Topic: How to insert with100 Million record's sql query result into another table 1 million by 1 million
Goto Forum:
  


Current Time: Mon Mar 18 21:21:03 CDT 2024