Use Type as output parameter for procedure-CodePudding

I'm trying to return a type as output parameter, but i'm getting error : [Error] Compilation (12: 27): PLS-00382: expression is of wrong type

Here is the code:

create or replace TYPE OBJ_DCP FORCE as OBJECT (
  ID                           NUMBER,
  xxx_PROFILES_ID              NUMBER,
  DCP_NAME                     VARCHAR2(500 BYTE),
  L_R_NUMBER                   VARCHAR2(150 BYTE),
  STREET                       VARCHAR2(150 BYTE)
  };

and

create or replace TYPE T_DCP_TYPE as TABLE OF OBJ_DCP;

and

  create or replace procedure get_dcp_profiles(p_id in number, dcp_array_var OUT T_DCP_TYPE) is
    cursor c_dcp_profiles  is
      select * 
      from table1  ca -- table1 has same structure of OBJ_DCP 
      where ca.id = p_id;

    i number := 1;
  begin
    for r in c_dcp_profiles loop
      dcp_array_var(i) := r; -- this line is errored
      i := i   1;
    end loop;
  end get_dcp_profiles;

CodePudding user response：

Types are OK.

Sample table:

SQL> SELECT * FROM table1 ORDER BY id;

        ID XXX_PROFILES_ID D L S
---------- --------------- - - -
         1               1 a b c
         1               2 d e f
         2               3 x y z

SQL>

Procedure, modified:

SQL> CREATE OR REPLACE PROCEDURE get_dcp_profiles (
  2     p_id           IN     NUMBER,
  3     dcp_array_var     OUT T_DCP_TYPE)
  4  IS
  5     CURSOR c_dcp_profiles IS
  6        SELECT *
  7          FROM table1 ca                 -- table1 has same structure of OBJ_DCP
  8         WHERE ca.id = p_id;
  9
 10     i      NUMBER := 1;
 11     l_arr  T_DCP_TYPE := T_DCP_TYPE ();
 12  BEGIN
 13     FOR r IN c_dcp_profiles
 14     LOOP
 15        l_arr.EXTEND;
 16        l_arr (i) :=
 17           OBJ_DCP (id               => r.id,
 18                    xxx_profiles_id  => r.xxx_profiles_id,
 19                    dcp_name         => r.dcp_name,
 20                    l_r_number       => r.l_r_number,
 21                    street           => r.street);
 22        i := i   1;
 23     END LOOP;
 24
 25     dcp_array_var := l_arr;
 26  END get_dcp_profiles;
 27  /

Procedure created.

Testing:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     l_tab  t_dcp_type;
  3  BEGIN
  4     get_dcp_profiles (1, l_tab);
  5
  6     FOR i IN 1 .. l_tab.COUNT
  7     LOOP
  8        DBMS_OUTPUT.put_line (l_tab (i).id || ' ' || l_tab (i).dcp_name);
  9     END LOOP;
 10  END;
 11  /
1 a
1 d

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

You do not need to use cursors or to manually populate the collection.

If you declare the table as an object-derived table:

CREATE TABLE table1 OF OBJ_DCP;

Then you can simplify the procedure down to:

CREATE PROCEDURE get_dcp_profiles(
  p_id          IN  number,
  dcp_array_var OUT T_DCP_TYPE
)
IS
BEGIN
  SELECT VALUE(ca)
  BULK COLLECT INTO dcp_array_var
  FROM   table1 ca
  WHERE  ca.id = p_id;
END get_dcp_profiles;
/

For the sample data:

INSERT INTO table1 (id, xxx_Profiles_id, DCP_NAME, L_R_NUMBER, STREET)
SELECT 1, 42, 'ABC name', 'ABC number', 'ABC street' FROM DUAL UNION ALL
SELECT 1, 63, 'DEF name', 'DEF number', 'DEF street' FROM DUAL UNION ALL
SELECT 2, 12, 'GHI name', 'GHI number', 'GHI street' FROM DUAL;

Then:

DECLARE
  dcps t_dcp_type;
BEGIN
  get_dcp_profiles (1, dcps);
  FOR i IN 1 .. dcps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE (dcps(i).id || ' ' || dcps(i).dcp_name);
  END LOOP;
END;
/

Outputs:

1 ABC name
1 DEF name

If you do not want to use an object-derived table then you can use:

CREATE OR REPLACE PROCEDURE get_dcp_profiles(
  p_id          IN  number,
  dcp_array_var OUT T_DCP_TYPE
)
IS
BEGIN
  SELECT OBJ_DCP(id, xxx_profiles_id, dcp_name, l_r_number, street)
  BULK COLLECT INTO dcp_array_var
  FROM   table1
  WHERE  id = p_id;
END get_dcp_profiles;
/

db<>fiddle here