Use a record as OUT parameter in stored procedure

Question

Is It possible to use a defined record as a OUT paramter?

I want to do something like this:

create or replace PACKAGE WEBSEARCH
AS

TYPE rec_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); --YB added 2014-05-01

CURSOR columns RETURN rec_general_item;

    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 columns);
END WEBSEARCH;

EDIT I have tried to do the following:

create or replace PACKAGE MIX_WEBSEARCH
AS

TYPE rec_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); --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 rec_general_item);
END MIX_WEBSEARCH;

And here is the body:

create or replace PACKAGE BODY MIX_WEBSEARCH 
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 c_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;

When I try to create the body of the package, I get the following errors:

Error(3,5): PL/SQL: Item ignored
Error(6,16): PLS-00201: identifier 'C_GENERAL_ITEM' must be declared
Error(41,16): PLS-00323: subprogram or cursor 'GENERAL_GET_ITEM' is declared in a package specification and must be defined in the package body

What am I doing wrong here? Can someone explain? :)

Answer 1

The error that you are currently getting like PLS-00201 and PLS-00323, are due to the mismatch in the name of TYPE in PACKAGE declaration and package body. In your package body you have written "c_general_item" which should be "rec_general_item". Try this and your current error will be resolved ----

create or replace PACKAGE BODY MIX_WEBSEARCH 
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 rec_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;

Answer 2

You can declare a CURSOR type to specify a strongly-typed cursor.

CREATE PACKAGE WEBSEARCH
AS
  TYPE rec_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
  );

  TYPE general_item_cursor IS REF CURSOR RETURN rec_general_item;

  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_cursor
  );
END WEBSEARCH;
/

CREATE PACKAGE BODY WEBSEARCH
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_cursor
  )
  IS
  BEGIN
    OPEN p_item FOR
    SELECT item_no, item_type, item_state
    FROM   item_t
    WHERE  item_no = p_item_no AND item_type = p_item_type;
  END;
END WEBSEARCH;
/

Answer 3

Yes, write it like this:

create or replace PACKAGE WEBSEARCH
AS

    TYPE rec_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); 

    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 rec_general_item);
END; 

create or replace PACKAGE BODY WEBSEARCH 
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 rec_general_item) is

    begin
       SELECT p_item_no, p_item_type, 1
       INTO p_item
       FROM dual;
    end;

END;