Home » SQL & PL/SQL » SQL & PL/SQL » update collection of object type using update statement (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
update collection of object type using update statement [message #675783] Fri, 19 April 2019 22:09 Go to next message
fachoch@gmail.com
Messages: 2
Registered: January 2019
Junior Member
how to update collection of object type using update statement, below is my code,


create or replace type sm_test_obj as object (
  x int, y int
);

create or replace type sm_test_obj_arr as table of sm_test_obj;



function test1 return number as
   v_tbl   sm_test_obj_arr;
   v_sm_test_obj  sm_test_obj;
   begin

     v_tbl := sm_test_obj_arr ( sm_test_obj ( 1, 2 ), sm_test_obj ( 3, 4 ) );
  
   select value(t) into v_sm_test_obj from table ( v_tbl ) t where t.x=3;
   v_sm_test_obj.x:=5;
   update table(v_tbl) t set t=v_sm_test_obj where t.x=3;
   dbms_output.put_line ( v_sm_test_obj.x || ' ' || v_sm_test_obj.y );
  
  for rws in (
    select t.x, t.y from table ( v_tbl ) t
  ) loop
    dbms_output.put_line ( rws.x || ' ' || rws.y );
  end loop;
  return 1;
end;
Re: update collection of object type using update statement [message #675784 is a reply to message #675783] Fri, 19 April 2019 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
Please post URL to Oracle documentation that show UPDATE is valid against Object Type.
Start by correcting the syntax errors
Wrote file afiedt.buf

  1  create or replace function test1 return number as
  2	v_tbl	sm_test_obj_arr;
  3	v_sm_test_obj  sm_test_obj;
  4	begin
  5	  v_tbl := sm_test_obj_arr ( sm_test_obj ( 1, 2 ), sm_test_obj ( 3, 4 ) );
  6	select value(t) into v_sm_test_obj from table ( v_tbl ) t where t.x=3;
  7	v_sm_test_obj.x:=5;
  8	update table(v_tbl) t set t=v_sm_test_obj where t.x=3;
  9	dbms_output.put_line ( v_sm_test_obj.x || ' ' || v_sm_test_obj.y );
 10    for rws in (
 11	 select t.x, t.y from table ( v_tbl ) t
 12    ) loop
 13	 dbms_output.put_line ( rws.x || ' ' || rws.y );
 14    end loop;
 15    return 1;
 16* end;
 17  /

Warning: Function created with compilation errors.

SQL> show error
Errors for FUNCTION TEST1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/4	 PL/SQL: SQL Statement ignored
8/11	 PL/SQL: ORA-00903: invalid table name
SQL> 

[Updated on: Fri, 19 April 2019 22:43]

Report message to a moderator

Re: update collection of object type using update statement [message #675796 is a reply to message #675784] Sat, 20 April 2019 09:17 Go to previous messageGo to next message
fachoch@gmail.com
Messages: 2
Registered: January 2019
Junior Member
If update statement on collection is not available , only option I know is to query the index of object and replace with modified object instance , is there any other better approach?

[Updated on: Sat, 20 April 2019 09:18]

Report message to a moderator

Re: update collection of object type using update statement [message #675797 is a reply to message #675796] Sat, 20 April 2019 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
fachoch@gmail.com wrote on Sat, 20 April 2019 07:17

only option I know is to query the index of object and replace with modified object instance

I have no idea what above means.

We don't have your tables.
We don't have your data.
We don't know what are the requirements.
I don't know what problem you are trying to solve.
I have no idea what a correct solution would look like.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

Please provide Test Case.

Re: update collection of object type using update statement [message #675798 is a reply to message #675796] Sat, 20 April 2019 09:47 Go to previous message
Solomon Yakobson
Messages: 2858
Registered: January 2010
Location: Connecticut, USA
Senior Member
No, it isn't possible. And is not needed. You simply do it in PL/SQL:

create or replace
  function test1
    return number
    as
        v_tbl         sm_test_obj_arr;
    begin
        v_tbl := sm_test_obj_arr(sm_test_obj(1,2),sm_test_obj(3,4));
        for v_i in 1..v_tbl.count loop
          if v_tbl(v_i).x = 3
            then
              v_tbl(v_i).x := 5;
          end if;
        end loop;
        for rws in (select t.x, t.y from table(v_tbl) t) loop
          dbms_output.put_line(rws.x || ' ' || rws.y);
        end loop;
        return 1;
end;
/

Function created.

SQL> set serveroutput on
SQL> declare
  2      v_res number;
  3  begin
  4     v_res := test1;
  5  end;
  6  /
1 2
5 4

PL/SQL procedure successfully completed.

SQL> 

SY.
Previous Topic: json fetching using JSON_TABLE
Next Topic: update with subqueries possibilty
Goto Forum:
  


Current Time: Sat Jun 15 20:09:17 CDT 2019