6 Replies Latest reply: Jan 14, 2013 5:16 AM by Bawer RSS

    ORA-06504:PL/SQL:Return type of Return set variables or query do not match

    819888
      Hi,

      I want to use the out plsql table returned by a function into SQL query. doing following.

      create or replace type rt_emp as object(
      view_type varchar2(1),
      req_emplid varchar2(11),
      emplid varchar2(11),
      full_name VARCHAR2(100));

      create or replace type tt_emp is table of rt_emp ;

      Function get_team_members(i_req_emplid IN employee_ref.emplid%type,
      i_view_type varchar2,
      i_sort_by IN NUMBER,
      i_page_rows IN NUMBER DEFAULT 20,
      i_current_page_no IN NUMBER
      )
      return tt_emp IS
      team_members_CUR t_ref_cur;
      v_team_members_tab tt_emp;

      BEGIN
      open team_members_CUR for
      select 'A' view_type,
      emplid req_emplid, --varchar2(11)
      emplid emplid, --varchar2(11)
      full_name -- varchar2(100)
      from employee_vw;
      FETCH team_members_CUR BULK COLLECT
      INTO v_team_members_tab;

      CLOSE team_members_CUR;

      return v_team_members_tab;

      END get_team_members;

      --Till this step everything is okay. Function got created.

      select a.view_type
      ,a.req_emplid
      ,a.emplid
      ,a.full_name
      from Table(get_team_members(222,
      'A',
      2,
      20,
      1
      )) a
      -- This query give the error given in suject line.

      I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0.

      Appreciate help.

      Regards
      Sajid

      Edited by: Mosaq on Jan 14, 2013 2:30 AM