0

I have a function with table of UDT type as in parameter. The objective of the function is to return a JSON clob to the calling environment. Unfortunately package body is compiled with an error:

Compilation errors for PACKAGE BODY JOHN.packageA #13#10Error: PLS-00382: expression is of wrong type Line: 8 Text: from persons where id member of v_ids;

Is it not possible to use the member of operator with associative arrays? What different approach should i take?

CREATE OR REPLACE Package packageA As

   TYPE ids_tbl is Table of Number(10) INDEX BY PLS_INTEGER;   

   function get_people_json (p_custIds in ids_tbl) return clob;

End;

CREATE OR REPLACE Package Body PackageA As

  function get_people_json (p_custIds in ids_tbl) 
    return clob

    is

    cursor people_cur(v_ids ids_tbl) select * from persons where id member of v_ids;

    TYPE people_aat IS TABLE OF people_cur%ROWTYPE
    INDEX BY PLS_INTEGER;



    ....

    Begin

      open people_cur(p_custIds);
      ....



      return l_clob;

    End;
 End;

Regards Magnus

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
MrM
  • 389
  • 1
  • 8
  • 23
  • UDTs are schema-level types (not sure if that's defined anywhere, but it's how it's used at least); you have an associative array, which is a PL/SQL collection type, and you can't use those in SQL statements in 11g. SO, do you have to use the type as you've defined it, or can you control/modify that? Which partly depends on who is going to call it and how they will do that. And then can you create your own schema-level type? If not there are some built-in ones available. – Alex Poole Jul 02 '18 at 11:01
  • Thanks Alex for the input. Can you pinpoint specifically which built-in types that are available? – MrM Jul 02 '18 at 11:04
  • 1
    [See here for some options](https://stackoverflow.com/q/8785459/266304). Stick with supported/documented types if you can though. – Alex Poole Jul 02 '18 at 11:11

0 Answers0