Select Object Table Into Variable

Question

I've got a problem with trying to select something from a object table into my own local variable. Here's some basic code.

create type my_obj as object
(
  my_number number
)
/
create table my_table of my_obj;
/
insert into my_table values (my_obj(123))
/
declare
  my_holder my_obj;
begin
  select * into my_holder from my_table where rownum = 1;
  dbms_output.put_line(my_holder.my_number);
end;

The error I'm getting out of it is

Error starting at line : 86 in command -
declare
  my_holder my_obj;
begin
  select * into my_holder from my_table where rownum = 1;
end;
Error report -
ORA-06550: line 4, column 10:
PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Any clues on why this might be failing?

sstan · Accepted Answer · 2015-09-24 01:01:54Z

3

The select returns the variables from the object not the object itself so if you wanted to get it to work the way you have it you would need to declare that you want the object returned as below.

select my_obj(my_number) into my_holder from my_table where rownum = 1;

Hope this helps

edited Sep 24, 2015 at 1:01

sstan

36.8k7 gold badges54 silver badges72 bronze badges

answered Sep 24, 2015 at 0:56

Shaun Peterson

1,7901 gold badge16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user272735 Over a year ago

@Coat This answer is correct (it gets the job done) but (IMO) suboptimal. Instead of using constructor use value function to "convert" correlation variable associated with an object table row to an object instance stored in the row: select value(m) into my_holder from my_table m where rownum = 1; (note the syntax requires use of table alias).

Shaun Peterson Over a year ago

@user272735 Thanks for this, it was a technique I was not aware of and I agree it is a better solution.

Collectives™ on Stack Overflow

Select Object Table Into Variable

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related