How to insert into oracle custom object from select result?-CodePudding

I have a obj like below

CREATE TYPE SOME_CUSTOM_OBJ FORCE AS OBJECT(
   ID number,
   Name varchar(30)
)

and want to insert value in that, I read the document for oracle database, but only way that I found is use initial function.

DECLARE
  SOME_OBJ SOME_CUSTOM_OBJ;
BEGIN
  SOME_OBJ := SOME_CUSTOM_OBJ(1,'PETER');
  DBMS_OUTPUT.PUT_LINE(SOME_OBJECT.ID);
END;

// OUTPUT : 1

I want to insert those value from another table, the imagine like below

DECLARE
  SOME_OBJECT SOME_CUSTOM_OBJECT;
BEGIN
  SOME_OBJECT := SOME_CUSTOM_OBJECT(
  SELECT ID,NAME FROM ANTHER_TABLE WHERE {SOME CONDITIONS} AND rownum = 1
);
  DBMS_OUTPUT.PUT_LINE(SOME_OBJECT.ID);
END;
// OUTPUT : 1

The value who select from table would be only 1 row.

If use Object_table could be easy to solve my question, but I don't want to add too much things in the database.

CodePudding user response：

You can use the object constructor in the SELECT clause:

DECLARE
  SOME_OBJECT SOME_CUSTOM_OBJ;
BEGIN
  SELECT some_custom_obj(ID,NAME)
  INTO   some_object
  FROM   another_table
  WHERE  rownum = 1;
  
  DBMS_OUTPUT.PUT_LINE(SOME_OBJECT.ID);
END;
/

Which, for the sample data:

CREATE TABLE another_table (id, name) AS
SELECT 1, 'Alice' FROM DUAL;

Outputs:

db<>fiddle here