क्या आउट पैरामीटर के रूप में परिभाषित रिकॉर्ड का उपयोग करना संभव है?

मैं ऐसा कुछ करना चाहता हूं:

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;

संपादित करें मैंने निम्नलिखित करने का प्रयास किया है:

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;

और यहाँ शरीर है:

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;

जब मैं पैकेज का मुख्य भाग बनाने का प्रयास करता हूं, तो मुझे निम्न त्रुटियां मिलती हैं:

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

मुझसे यहां क्या गलत हो रहा है? क्या कोई समझा सकता है? :)

0
Bryan 31 अगस्त 2017, 11:03

3 जवाब

सबसे बढ़िया उत्तर

आप दृढ़ता से टाइप किए गए कर्सर को निर्दिष्ट करने के लिए 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;
/
0
MT0 31 अगस्त 2017, 12:05

हाँ, इसे इस तरह लिखें:

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;
1
Wernfried Domscheit 31 अगस्त 2017, 11:36

PLS-00201 और PLS-00323 की तरह वर्तमान में आपको जो त्रुटि मिल रही है, वह पैकेज घोषणा और पैकेज बॉडी में TYPE के नाम में बेमेल होने के कारण है। आपके पैकेज बॉडी में आपने "c_general_item" लिखा है जो "rec_general_item" होना चाहिए। इसे आज़माएं और आपकी वर्तमान त्रुटि का समाधान हो जाएगा ----

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;
0
Pratik K Chatterjee 31 अगस्त 2017, 12:17