update collection of object type using update statement [message #675783] |
Fri, 19 April 2019 22:09 |
|
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 |
|
BlackSwan
Messages: 26766 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 #675798 is a reply to message #675796] |
Sat, 20 April 2019 09:47 |
Solomon Yakobson
Messages: 3275 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.
|
|
|