• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1454
  • Last Modified:

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
0
samir25
Asked:
samir25
  • 4
  • 3
1 Solution
 
sujith80Commented:
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
 
sujith80Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
sujith80Commented:
>> 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now