I have a table a based on the object a_obj:

CREATE TYPE a_obj IS OBJECT (
  a1 INTEGER,
  a2 integer
);

CREATE TABLE a OF a_obj (
  CONSTRAINT a__a1__pk PRIMARY KEY (a1)
);

Sometime i want to select the column of a:

select a.* from a  -- it works

sometime I want to select the object a_obj.

 select a_obj(a.a1,a.a2) from a; --works

But I don't want to rewrite the name of all columns because, I can have a lot of columns to write and it's easy to forget one.

I try something like that:

select a_obj(a.*) from a;

ORA-01747: invalid user.table.column, table.column, or column specification

it doesn't work.

Is there a way to do that?

that doesn't work either:

declare 
  ret a_obj;
begin
  select a.* into ret
  from a;
end;

code

CodePudding user response：

Use the OBJECT_VALUE pseudo-column:

SELECT OBJECT_VALUE FROM a;

Or, as @padders commented, use the VALUE() function:

SELECT VALUE(a_alias) FROM a a_alias;

Which needs to reference a table alias and not the table identifier.

db<>fiddle here