Urgent-Define package spec error Encountered the symbol "end-of-file"

here is my package body declaration
CREATE OR REPLACE PACKAGE BODY "DOTNET" AS
 Procedure ParseXML(ItemNo In Varchar2, Operation In Varchar2, Status In Varchar2, Data_Cursor IN OUT sys_refcursor)
IS
  Data_Cursor sys_refcursor;
BEGIN
  IF ItemNo <> NULL and Operation <> Null and Status <> Null THEN
    OPEN Data_Cursor FOR
      SELECT test_id, serial, operation, start_time
      FROM ttable
      WHERE status='SOMEERR' and Operation='TEST' and item='001'
      ORDER BY start_time;
  END IF;
END;
and my spec is
CREATE OR REPLACE PACKAGE "DOTNET" AS
      Procedure ParseXML(ItemNo In Varchar2, Operation In Varchar2, Status In Varchar2, Data_Cursor IN OUT sys_refcursor);
END;
I am getting this error when i complie the spec
Error(17): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     begin end function package pragma procedure form

why is this coming
LVL 1
samir25Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
Compile the following code.
See the changes

-- no need to declare Data_Cursor again
-- If you are checking against null then you have to use IS NOT NULL
-- END is missing for package body

CREATE OR REPLACE PACKAGE "DOTNET" AS
      Procedure ParseXML(ItemNo In Varchar2, Operation In Varchar2, Status In Varchar2, Data_Cursor IN OUT sys_refcursor);
END;
/
CREATE OR REPLACE PACKAGE BODY "DOTNET"
AS
 Procedure ParseXML(ItemNo In Varchar2, Operation In Varchar2, Status In Varchar2, Data_Cursor IN OUT sys_refcursor)
IS
--  Data_Cursor sys_refcursor;
BEGIN
  IF ItemNo is not NULL and Operation is not Null and Status is not Null THEN
    OPEN Data_Cursor FOR
      SELECT test_id, serial, operation, start_time
      FROM ttable
      WHERE status='SOMEERR' and Operation='TEST' and item='001'
      ORDER BY start_time;
  END IF;
END;
END;
/

0
samir25Author Commented:
ok great..thanks. can you tell me how can i refer the IN variabels in my sql query?
right now i am hard coding the values.where as i want to refer the IN variable values in the
 WHERE status='SOMEERR' and Operation='TEST' and item='001'
0
SujithData ArchitectCommented:
Sure. See the code.
-- Point to note, parameters should have a name different from the column names, otherwise oracle will confuse the parameter name to be the column name.

CREATE OR REPLACE PACKAGE BODY "DOTNET"
AS
 Procedure ParseXML(p_ItemNo In Varchar2, p_Operation In Varchar2, p_Status In Varchar2, Data_Cursor IN OUT sys_refcursor)
IS
--  Data_Cursor sys_refcursor;
BEGIN
  IF ItemNo is not NULL and Operation is not Null and Status is not Null THEN
    OPEN Data_Cursor FOR
      SELECT test_id, serial, operation, start_time
      FROM ttable
      WHERE status= p_Status and Operation= p_Operation and item= p_ItemNo
      ORDER BY start_time;
  END IF;
END;
END;
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

samir25Author Commented:
IF ItemNo is not NULL and Operation is not Null and Status is not Null THEN
should be replaced too right? any specific reason to add p_??? or just to differentiate?
0
samir25Author Commented:
ok read this
-- Point to note, parameters should have a name different from the column names, otherwise oracle will confuse the parameter name to be the column name.

0
samir25Author Commented:
ok on emore thing. my cursor is something which will only be the output from teh pl/sql so should it be defined as IN OUT or just OUT?
0
SujithData ArchitectCommented:
>> should be replaced too right?
Yes all occurances of the parameter names should be changed.

>> any specific reason to add p_??? or just to differentiate?
Its just a naming convention. p_ indicates that it is a "parameter"

>> should it be defined as IN OUT or just OUT?
OUT
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.