CODEWITHSUNDEEP
databaseoracle-databasepackage
DBProgrammer
DBProgrammer

Reputation: 3

Error when Compiling Oracle Package Body

The Package Specification succesfully Compiles with the message "SPBomPackage Compiled" But i have two errors when compiling the package body they are as follows, "Error(17,6): PL/SQL: Statement ignored" and "Error(17,17): PLS-00302: component 'ITEM_ID' must be declared" , the package specification, package body, and the three tables im using that this package will use are pasted and formatted nicely for you to view at this link:

Upvotes: 0

Views: 1139

Answers (2)

Hamidreza
Hamidreza

Reputation: 3128

i think this sample can help you:

create or replace
PACKAGE       PKG_YourPackage AS
  TYPE T_CURSOR IS REF CURSOR;
PROCEDURE sp_Test(InputTest in number,   T_List OUT T_CURSOR);
END;
/
create or replace
PACKAGE BODY       PKG_PKG_YourPackage as
PROCEDURE sp_Test(InputTest in number,   T_List OUT T_CURSOR)

  IS

    BEGIN
        OPEN T_List FOR
select
t1.*,t2.*
from
Table1 t1,
Table2 t2
where
t1.id = t2.Table1ID and
t1.id = InputTes ;
End sp_Test;
END;

Upvotes: 0

OldProgrammer
OldProgrammer

Reputation: 12179

Welcome to SO. For future reference, please post code in the question. We are a lazy bunch. I don't know why, but I think there is some problem with the duplicate ITEM_ID columns from the two tables ending up in the join, then the %ROWTYPE seems to get confused. When I changed the cursor to explicitly declare each column to return, as shown below, it compiled properly.

  CURSOR c1
  IS
     SELECT bi.item_id, bi.item_desc, bi.item_category, bi.item_image,
            ia.parent_item_id, ia.assembly_qty
       FROM bicycle_item bi JOIN item_assembly ia ON bi.item_id =
                                                               ia.item_id
      WHERE ia.parent_item_id = item_param_id;

Upvotes: 1

Related Questions