| How can we access an object type(not table of objects) in Oracle TABLE function? [message #611943] |
Thu, 10 April 2014 05:46  |
 |
balaji.chelladurai
Messages: 6
Registered: April 2014
|
Junior Member |
|
|
How can we access an object type(not table of objects) in TABLE function ?
The table function works on a collection of objects, but I have a need to access a single row using TABLE function.
I get the error "ORA-22905: cannot access rows from a non-nested table item" when I try to use TABLE function with an object type instead of collection of object types.
The workaround that I use is to create a table type collection on this object and place the row in the first index, and then use the table type in the function. Any other simple solutions ?
I need to query the object as if it is a row in a table.This is to have a common code base in my application to support two different use cases.
With a common code base, I can use the same SELECT query to query the records either from an actual table or from a single-row object using TABLE function. Only the FROM clause of the Dynamic SQL changes.
CASE 1:
Querying a physical table
v_select := 'SELECT a,b,c,d '
v_from := ' FROM ' || v_actual_table
v_where := '<where_predicate>
EXECUTE IMMEDIATE v_select || v_from || v_where;
CASE 2:
Querying a row object using TABLE function. The row object is coming from Oracle AQ channel in the form of messages.
v_select := 'SELECT a,b,c,d '
v_from := ' FROM TABLE(:1)'
v_where := '<where_predicate>
EXECUTE IMMEDIATE v_select || v_from || v_where USING v_table_type;
|
|
|
|
|
|
| Re: How can we access an object type(not table of objects) in Oracle TABLE function? [message #611988 is a reply to message #611943] |
Thu, 10 April 2014 10:48  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Although object is a composite type it is still a scalar type, while TABLE operator works with collections. If you have just an object all you need is
v_select := 'SELECT t.o.a,t.o.b,t.o.c,t.o.d '
v_from := ' FROM (SELECT :1 o FROM dual) t '
v_where := '<where_predicate>'
For example:
SQL> desc emp_obj_type
Name Null? Type
----------------------------------------------------------------------------------------- -------- -------
SAL NUMBER
DEPTID NUMBER
SQL> set serveroutput on
SQL> declare
2 v_sal number;
3 v_deptid number;
4 begin
5 execute immediate 'select t.o.sal,t.o.deptid from (select :1 o from dual) t'
6 into v_sal,v_deptid
7 using emp_obj_type(9999,1);
8 dbms_output.put_line('SAL = ' || v_sal || ' DEPTID = ' || v_deptid);
9 end;
10 /
SAL = 9999 DEPTID = 1
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
| Re: How can we access an object type(not table of objects) in Oracle TABLE function? [message #611989 is a reply to message #611943] |
Thu, 10 April 2014 10:54 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
But if you want to use TABLE, you need to declare object table type in SQL and use TABLE + MULTISET:
SQL> create or replace
2 type emp_tbl_type
3 as table of emp_obj_type
4 /
Type created.
SQL> declare
2 v_sal number;
3 v_deptid number;
4 begin
5 execute immediate 'select * from table(
6 cast(
7 multiset(
8 select :1 from dual
9 )
10 as emp_tbl_type
11 )
12 )'
13 into v_sal,v_deptid
14 using emp_obj_type(9999,1);
15 dbms_output.put_line('SAL = ' || v_sal || ' DEPTID = ' || v_deptid);
16 end;
17 /
SAL = 9999 DEPTID = 1
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
|
|
| Re: How can we access an object type(not table of objects) in Oracle TABLE function? [message #612019 is a reply to message #611943] |
Fri, 11 April 2014 01:08 |
 |
vslabs
Messages: 26
Registered: March 2014
Location: Cape Town
|
Junior Member |
|
|
The simplest approach is to construct a collection with the single variable object you have.
Basic example:
SQL> create or replace type TEmployee as object(
2 id integer,
3 name varchar2(10)
4 );
5 /
Type created.
SQL>
SQL>
SQL> create or replace type TEmployeeArray is table of TEmployee;
2 /
Type created.
SQL>
SQL>
SQL> declare
2 emp TEmployee;
3 name varchar2(10);
4 begin
5 emp := new TEmployee(1,'jack');
6
7 execute immediate
8 'select e.name from TABLE( TEmployeeArray(:1) ) e'
9 into name
10 using emp;
11
12 dbms_output.put_line( 'name='||name );
13 end;
14 /
name=jack
PL/SQL procedure successfully completed.
SQL>
Thus no need to pass a collection as a bind variable. Construct the collection in the SQL statement. Pass the object as the bind variable.
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
