0 Replies Latest reply: Sep 3, 2009 5:07 AM by 643012 RSS

    Function using Object types hangs when executing

    643012
      Hi,

      I have an plsql function (the only function in the package) that takes a number and a custom table type as arguments and returns a user define type. (A person type for example, which has so many other types.. so many here means around 70 types).

      All i am doing inside the function is declare all the 70 odd types and just fill the values. something like

      function x (a number, b user_tab_type) return person_type is
      person person_type;
      name name_type;
      address address_type;
      ....
      begin
      name := name_type(...);
      address := address_type(...);
      person := person_type(name, address, ....);
      return person;
      end <function>;

      that is all to it except one of the types takes a RAW value. So i used utl_raw.cast_to_raw function to convert a varchar into a RAW. The other thing being, most of the types are collection types (VARRAYS). so i have to instantiate and extend them. But there will be only one element in the collection at any point of time.

      The problem is, whenever i execute this function, either using sqlplus or from a Java program, which is our ultimate requirement, the execution is going on forever. It hangs and never returns...

      i couldn't attribute any reason for this behavior. There are no loops, no if elses and in fact nothing LOGICal is happening. Any help is greatly appreciated.

      PS: I forgot the code tag syntax and i didn't see it in the Plain Text help menu also.