Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Output Cursor Results Using PL/SQL

    Hi

    I have written an Oracle package, which is working correctly. However, I am getting an error when attempting to print the output.

    The package is:
    CREATE OR REPLACE PACKAGE "PKG_SEARCH_RECS" AS
    TYPE search_t IS REF CURSOR;
    PROCEDURE proc_search (v_search IN VARCHAR2, search_rec_cv IN OUT search_t);
    END pkg_search_recs;
    /
    CREATE OR REPLACE PACKAGE BODY "PKG_SEARCH_RECS" AS

    PROCEDURE proc_search (v_search IN VARCHAR2, search_rec_cv IN OUT search_t) AS
    BEGIN
    OPEN search_rec_cv FOR
    SELECT n.NME_ID,
    n.NME_F || ' ' || n.NME_I || ' ' || NME_L AS NAME,
    t.TEL_HM, t.TEL_WK, t.TEL_MB
    FROM name n, tel t
    WHERE n.NME_ID = t.NME_ID (+)
    AND lower(n.NME_F) || lower(n.NME_L) LIKE v_search;
    END proc_search;

    END pkg_search_recs;
    /


    Script to get output, which contains the error is:
    DECLARE
    c_employees PKG_SEARCH_RECS.search_t;
    r_employee name%ROWTYPE;
    BEGIN
    PKG_SEARCH_RECS.proc_search('%test%', c_employees);

    LOOP
    FETCH c_employees INTO r_employee;
    EXIT WHEN c_employees%NOTFOUND;
    DBMS_OUTPUT.put_line ('First Name := ' || r_employee.nme_f);
    END LOOP;
    CLOSE c_employees;
    END;
    /

    The error message is:

    Error on line 0
    DECLARE
    c_employees PKG_SEARCH_RECS.search_t;
    r_employee name%ROWTYPE;

    ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
    ORA-06512: at line 8

    I think the error is in r_employee name%ROWTYPE; But, I'm not sure on how to solve it.

    Thanks for any help.

  2. #2
    Join Date
    Jun 2009
    Posts
    2

    Thumbs up

    I have now resolved the problem, it was in the declaration

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Its very nice that you solved it, but always put in the solution into the message thread so others that are searching for a similar problem can see the fix.

    Thanks.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •