Home » SQL & PL/SQL » SQL & PL/SQL » showing errors while creating stored procedure (pls-00103)
showing errors while creating stored procedure (pls-00103) [message #184483] Wed, 26 July 2006 14:33 Go to next message
sheharyararshad
Messages: 10
Registered: May 2006
Location: KUWAIT
Junior Member

hello every one
can any one help me aout am creating a stored procedures and getting an error can any one tell me that what mistake i am doing......................

create or replace package depart_pkg is
procedure add_depart (deptno number,name varchar2:='shary',loc number:=123) is
BEGIN
insert into depart (DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID)
values(deptno,name,loc);
END add_depart;
procedure add_depart (name varchar2:='razi',loc number:=1234) is
BEGIN
insert into depart (DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID)
values(depart_sequence.nextval,name,loc);
END add_depart;
end depart_pkg;


it showing errors
pls-00103


plz help me out
take care bye
Re: showing errors while creating stored procedure (pls-00103) [message #184485 is a reply to message #184483] Wed, 26 July 2006 14:43 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Number of BEGINs must match number of ENDs
Re: showing errors while creating stored procedure (pls-00103) [message #184495 is a reply to message #184485] Wed, 26 July 2006 16:24 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, code you provided should be a PACKAGE BODY, not its specification. This code (for itself) is OK. Complete code, though, should look like this:
CREATE OR REPLACE PACKAGE depart_pkg
IS
   PROCEDURE add_depart (
      deptno   NUMBER,
      NAME     VARCHAR2 := 'shary',
      loc      NUMBER := 123
   );

   PROCEDURE add_depart (NAME VARCHAR2 := 'razi', loc NUMBER := 1234);
END depart_pkg;

CREATE OR REPLACE PACKAGE BODY depart_pkg
IS
   PROCEDURE add_depart (
      deptno   NUMBER,
      NAME     VARCHAR2 := 'shary',
      loc      NUMBER := 123
   )
   IS
   BEGIN
      INSERT INTO depart
                  (department_id, department_name, location_id
                  )
           VALUES (deptno, NAME, loc
                  );
   END add_depart;

   PROCEDURE add_depart (NAME VARCHAR2 := 'razi', loc NUMBER := 1234)
   IS
   BEGIN
      INSERT INTO depart
                  (department_id, department_name, location_id
                  )
           VALUES (depart_sequence.NEXTVAL, NAME, loc
                  );
   END add_depart;
END depart_pkg;
Re: showing errors while creating stored procedure (pls-00103) [message #184670 is a reply to message #184495] Thu, 27 July 2006 07:28 Go to previous messageGo to next message
sheharyararshad
Messages: 10
Registered: May 2006
Location: KUWAIT
Junior Member

hello
thanks alot firstly for yours quick response i have created a procedure its created successfully as below.

CREATE OR REPLACE PACKAGE depart_pkg
IS
PROCEDURE add_depart (
deptno NUMBER,
NAME VARCHAR2 := 'shary',
loc NUMBER := 123
);

PROCEDURE add_depart (NAME VARCHAR2 := 'razi', loc NUMBER := 1234);
END depart_pkg;

but when i m trying to give the values its showing errors through
SQL> execute depart_pkg.add_depart('shary',420);



ERRORS ARE AS FOLLOWS


BEGIN depart_pkg.add_depart('shary',420); END;

*
ERROR at line 1:
ORA-04063: package body "HR.DEPART_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
Re: showing errors while creating stored procedure (pls-00103) [message #184673 is a reply to message #184670] Thu, 27 July 2006 07:34 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



Create the Package Body also ....

Understand the Littlefoot's explanation carefullyy..

Rajuvan.
Previous Topic: Insert is too slow......
Next Topic: MERGE/ORA-00904/DECODE: Doesn't work with DB LINK
Goto Forum:
  


Current Time: Sat Dec 03 03:38:10 CST 2016

Total time taken to generate the page: 0.13892 seconds