samir25
asked on
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
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
ASKER
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'
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'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
should be replaced too right? any specific reason to add p_??? or just to differentiate?
ASKER
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.
-- 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.
ASKER
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?
>> 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
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
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;
/