Nested table [message #612818] |
Fri, 25 April 2014 06:08 |
|
VladGab
Messages: 11 Registered: March 2014
|
Junior Member |
|
|
Hi. Why in oracle 11g R2 is not allowed pass sql value to nested table.
Example
declare
type TTI is table of int;
i integer;
var TTI := TTI();
begin
var.extend;
select var(T.dummy)
into i
from (select 1 DUMMY from dual) T;
end;
I got this PLS-00201: identifier 'T.DUMMY' must be declared.. THANKS!
|
|
|
Re: Nested table [message #612819 is a reply to message #612818] |
Fri, 25 April 2014 06:31 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You can't use PL/SQL type like that. PL/SQL variable referenced in SQL statement is nothing but bind variable. So when Oracle finds
select var(T.dummy)
into i
from (select 1 DUMMY from dual) T;
It transforms it to:
select :B1
into i
from (select 1 DUMMY from dual) T;
and tries to bind var(T.dummy). Obviously this fails since T.dummy is unknown at this time. You need to select T.dummy into i and then use var(i):
SQL> declare
2 type TTI is table of int;
3 i integer;
4 var TTI := TTI();
5 begin
6 var.extend;
7 select T.dummy
8 into i
9 from (select 1 DUMMY from dual) T;
10 i := var(i);
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
Re: Nested table [message #612821 is a reply to message #612820] |
Fri, 25 April 2014 07:00 |
|
VladGab
Messages: 11 Registered: March 2014
|
Junior Member |
|
|
But there is one moment Consider this code. IT works correctly, and did the same BINDING. I think, that it connected with nested tables..
declare
type TTI is table of int;
i integer;
var TTI := TTI();
begin
var.extend;
select p_MyProc.m_MyFunc(T.dummy)
into i
from (select 1 DUMMY from dual) T;
end;
[Updated on: Fri, 25 April 2014 07:00] Report message to a moderator
|
|
|
Re: Nested table [message #612826 is a reply to message #612821] |
Fri, 25 April 2014 07:48 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I'm not sure what you think that example proves since the nested table isn't used anywhere in the select statement.
|
|
|
Re: Nested table [message #612830 is a reply to message #612821] |
Fri, 25 April 2014 08:41 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
No it did not. Again, select plsql_variable means "pass PL/SQL variable plsql_variable value to SQL". And your last example has no PL/SQL variables and no binding. It is pure SQL statement. Both T.Dummy and p_MyProc.m_MyFunc are SQL known objects.
SY.
|
|
|
|
|
Re: Nested table [message #612836 is a reply to message #612832] |
Fri, 25 April 2014 10:06 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
VladGab wrote on Fri, 25 April 2014 10:25So my conclusion is that I can't create any nested table object which is SQL known.AM I right???
Wrong. You can populate nested table of either PL/SQL or SQL type from SQL statement.
SY.
|
|
|