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?
 
SujithConnect With a Mentor Data 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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.