Home » SQL & PL/SQL » SQL & PL/SQL » Nested table
Nested table [message #612818] Fri, 25 April 2014 06:08 Go to next message
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 Go to previous messageGo to next message
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 #612820 is a reply to message #612819] Fri, 25 April 2014 06:54 Go to previous messageGo to next message
VladGab
Messages: 11
Registered: March 2014
Junior Member
Thanks Solomon!!
Re: Nested table [message #612821 is a reply to message #612820] Fri, 25 April 2014 07:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #612832 is a reply to message #612830] Fri, 25 April 2014 09:25 Go to previous messageGo to next message
VladGab
Messages: 11
Registered: March 2014
Junior Member
So my conclusion is that I can't create any nested table object which is SQL known.AM I right???
Re: Nested table [message #612833 is a reply to message #612832] Fri, 25 April 2014 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You would be wise to avoid nested tables even if you can construct a valid SQL statement against it.

Re: Nested table [message #612836 is a reply to message #612832] Fri, 25 April 2014 10:06 Go to previous message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
VladGab wrote on Fri, 25 April 2014 10:25
So 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.
Previous Topic: Comma separated
Next Topic: how to update hierarchical query
Goto Forum:
  


Current Time: Fri Apr 26 08:16:34 CDT 2024