function returning user-defined type: datatype error

[SimonSellick]

Hi,

I've written a function that returns a user-defined type (a PL/SQL RECORD structure). It compiles OK (within a package that defines the type) and appears to work correctly.

What syntax do I need to use the function in an UPDATE statement (again, within the package)? I'm trying this form:

update tbl
  set  col1 = func(args).col1
     , col2 = func(args).col2
;

...but I'm getting an error returned: ORA00902 invalid datatype.

Here is an example that shows the problem:
spec

.
.
.
  type a_rec is record (
    agent             tbl.agent%type
  , contract_ref      tbl.contract_ref%type
  );
.
.
.

body

.
.
.
  create or replace function a (aa varchar2) return a_rec as 
    r a_rec; 
  begin 
    r.agent := 'FRED'; 
    return r; 
  end;
.
.
.
  select pkg.a('a').agent from dual;
.
.
.

I suspect that the 'invalid datatype' error indicates that the function isn't returning a type that SQL knows about. Is there a way to do what I want to in straight SQL or will I have to resort to a cursor and collect the individual fields separately?

Any assistance welcomed.

Simon.

 

[Dagon]

I don't think you can do what you are trying to, but there is nothing to stop you assigning the output of the function to a variable of the correct type. You should be able to reference individual fields within the variable in the update.

 

[SimonSellick]

Dagon,

Yes, that's what I've done; but it meant resorting to a cursor rather than a simple update statement. Thanks, though, for confirming that I wasn't just being blind.

Regards,
Simon.