Reputation: 3541
I have created the following package in Oracle:
CREATE OR REPLACE PACKAGE PackageName AS
TYPE general_item
IS
RECORD
(
item_no item_t.item_no%TYPE ,
item_type item_t.item_type%TYPE,
item_state item_t.item_state%TYPE,
item_name item_t.item_name%TYPE,
prodname_no item_t.prodname_no%TYPE,
prod_name item_t.prod_name%TYPE,
prodname_no2 item_t.prodname_no2%TYPE,
prod_name2 item_t.prod_name2%TYPE,
prodtype_no item_t.prodtype_no%TYPE,
prodtype_name item_t.prodtype_name%TYPE,
designer_no item_t.designer_no%TYPE,
designer_name item_t.designer_name%TYPE,
req_assembly item_t.req_assembly%TYPE,
unit_name item_t.unit_name%TYPE,
valid_designer item_t.valid_designer%TYPE,
sale_start_date item_t.sale_start_date%TYPE,
sale_end_date item_t.sale_end_date%TYPE,
short_material_text item_t.short_material_text%TYPE,
imeas_ref_imp item_t.imeas_ref_imp%TYPE,
imeas_ref_met item_t.imeas_ref_met%TYPE,
valid_design_text item_t.valid_design_text%TYPE,
pe_no item_t.pe_no%TYPE,
hfb_no item_t.hfb_no%TYPE,
hfb_name item_t.hfb_name%TYPE,
pra_no item_t.pra_no%TYPE,
pra_name item_t.pra_name%TYPE,
pa_no item_t.pa_no%TYPE,
pa_name item_t.pa_name%TYPE,
rec_sales_price item_t.rec_sales_price%TYPE,
currency_code item_t.currency_code%TYPE,
base_item_no item_cty_spec_t.item_no%TYPE, --YB added 2014-05-01
comclass_name item_comclass_t.comclass_name%TYPE); --YB added 2014-05-01
PROCEDURE general_get_item(
p_item_no IN item_t.item_no%TYPE,
p_item_type IN item_t.item_type%TYPE,
p_item OUT general_item);
END PackageName;
Here is my procedure that I'm trying to create:
create or replace PACKAGE BODY PackageName
AS
PROCEDURE general_get_item(
p_item_no IN item_t.item_no%TYPE,
p_item_type IN item_t.item_type%TYPE,
p_item OUT general_item)
IS
BEGIN
OPEN p_item FOR SELECT it.item_no, it.item_type, it.item_state, it.item_name, it.prodname_no , it.prod_name, it.prodname_no2, it.prod_name2, it.prodtype_no, it.prodtype_name, it.designer_no, it.designer_name, it.req_assembly, it.unit_name, it.valid_designer, it.sale_start_date, it.sale_end_date, it.short_material_text, it.imeas_ref_imp, it.imeas_ref_met, it.valid_design_text, it.pe_no, it.hfb_no, it.hfb_name, it.pra_no, it.pra_name, it.pa_no, it.pa_name, it.rec_sales_price, it.currency_code, icst.item_no as base_item_no, ict.comclass_name
FROM item_t it, item_cty_spec_t icst,
(SELECT item_no, item_type, comclass_name FROM item_comclass_t
WHERE valid_from < SYSDATE AND valid_to >= SYSDATE) ict
WHERE it.item_no = icst.item_no_cty_spec (+) AND it.item_type = icst.item_type_cty_spec (+)
AND it.item_no = ict.item_no (+) AND it.item_type = ict.item_type (+)
AND it.item_no = p_item_no
AND it.item_type = p_item_type;
END general_get_item;
END PackageName;
When I try to create the procedure, I get the following messages:
Error(8,3): PL/SQL: SQL Statement ignored Error(8,8): PLS-00456: item 'P_ITEM' is not a cursor
I'm new to this with packages and procedures in Oracle.
Upvotes: 0
Views: 928
Reputation: 13527
You dont need to declare a cursor if you want to return a result in Oracle. Oracle have already have it on your behalf as SYS_REFCURSOR.
So your code might look like -
CREATE OR REPLACE PACKAGE PackageName AS
PROCEDURE general_get_item(
p_item_no IN item_t.item_no%TYPE,
p_item_type IN item_t.item_type%TYPE,
p_item OUT SYS_REFCURSOR);
END PackageName;
Then your package body might look like -
create or replace PACKAGE BODY PackageName
AS
PROCEDURE general_get_item(
p_item_no IN item_t.item_no%TYPE,
p_item_type IN item_t.item_type%TYPE,
p_item OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_item FOR
SELECT it.item_no
,it.item_type
,it.item_state
,it.item_name
,it.prodname_no
,it.prod_name
,it.prodname_no2
,it.prod_name2
,it.prodtype_no
,it.prodtype_name
,it.designer_no
,it.designer_name
,it.req_assembly
,it.unit_name
,it.valid_designer
,it.sale_start_date
,it.sale_end_date
,it.short_material_text
,it.imeas_ref_imp
,it.imeas_ref_met
,it.valid_design_text
,it.pe_no
,it.hfb_no
,it.hfb_name
,it.pra_no
,it.pra_name
,it.pa_no
,it.pa_name
,it.rec_sales_price
,it.currency_code
,icst.item_no as base_item_no
,ict.comclass_name
FROM item_t it
LEFT JOIN item_cty_spec_t icst ON it.item_no = icst.item_no_cty_spec
AND it.item_type = icst.item_type_cty_spec
LEFT JOIN (SELECT item_no
,item_type
,comclass_name
FROM item_comclass_t
WHERE valid_from < SYSDATE AND valid_to >= SYSDATE) ict ON it.item_no = ict.item_no
AND it.item_type = ict.item_type
WHERE it.item_no = p_item_no
AND it.item_type = p_item_type;
END general_get_item;
END PackageName;
Also always use proper explicit join syntax over ANSI-92 syntax.
Upvotes: 0
Reputation: 41
You can't fetch cursor (probably multiple rows) into TYPE. Without table structure is quite hard to imagine what type is this field. Anyway easiser way is to use item_t%ROWTYPE if the cursor returns one row.
Upvotes: 0
Reputation: 31716
You cannot open a cursor on a TYPE
RECORD
. It has to be of CURSOR
type. So, Just replace this whole statement -
TYPE general_item
IS
RECORD
(
item_no item_t.item_no%TYPE ,
item_type item_t.item_type%TYPE,
item_state i
.....
.....
comclass_name item_comclass_t.comclass_name%TYPE);
WITH
TYPE general_item IS REF CURSOR;
Upvotes: 1