Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 27, 2007 - 1:00 pm UTC

Last updated: March 29, 2011 - 3:40 am UTC

Version: 9.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Could you please explain what is the most efficient way of doing this? I found many examples with pipelines, (table(function(cast)) examples, but somehow i feel there should be a more elegant way. 

create table t1 (n number);
create table t2 (d date, n number references t1(n));

create type ref_cur_type is ref cursor;
create type num_table is table of number;

create procedure p (p_tab num_table, r out ref_cur_type)
is
begin
  open r for
    select * from t1, t2
    where t1.n = t2.n 
      and t2.n <b>in p_tab</b>; --what's the best way to do this?
end; 


declare
  v_tab num_table;
  r ref_cur_type;
begin
  select n into v_tab from t2 where d=truncate(sysdate);
  p(v_tab, r);
end;



Best regards.
Milos.

and Tom said...

nothing more annoying than an example that

a) doesn't compile
b) is just an example without any explanation of what the goal is - so one is forced to read and parse code to try to figure out what the question was.


Here is my presumed question:

what is the way to use a collection variable in a sql statement as a set. For example, so I can use "where column in <that collection>"...


ops$tkyte%ORA10GR2> create table t1 (n number primary key);

Table created.

ops$tkyte%ORA10GR2> insert into t1 select rownum from all_users;

35 rows created.

ops$tkyte%ORA10GR2> create table t2 (d date, n number references t1(n));

Table created.

ops$tkyte%ORA10GR2> insert into t2 select trunc(sysdate)+mod(rownum,3), rownum from all_users;

35 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type num_table is table of number
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p (p_tab num_table, r out sys_refcursor )
  2  is
  3  begin<b>
  4    open r for
  5      with data as (select /*+ materialize */ * from table(p_tab) )
  6      select * from t1, t2
  7      where t1.n = t2.n
  8        and t2.n in (select * from data);</b>
  9  end;
 10  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable r refcursor
ops$tkyte%ORA10GR2> declare
  2    v_tab num_table;
  3  begin
  4    select n bulk collect into v_tab from t2 where d=trunc(sysdate);
  5    p(v_tab, :r);
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print r

         N D                  N
---------- --------- ----------
         3 28-DEC-07          3
         6 28-DEC-07          6
         9 28-DEC-07          9
        12 28-DEC-07         12
        15 28-DEC-07         15
        18 28-DEC-07         18
        21 28-DEC-07         21
        24 28-DEC-07         24
        27 28-DEC-07         27
        30 28-DEC-07         30
        33 28-DEC-07         33

11 rows selected.



The materialize hint may or may not be useful, that would be something to test - depends on how many times the plsql variable is referenced in the execution plan - if the plan has it being scanned thousands of times - probably more efficient to materialize (load into a temporary table behind the scenes, automagically).

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

In a package

Karthick, January 02, 2008 - 12:26 am UTC

I am trying to do the same inside a package but was not sucessfull. Can you help.

11:04:34 [SYSADM@INLABTST]> create or replace package my_package
11:04:52 2 as
11:04:52 3 type r is record ( no number, name varchar2(100));
11:04:52 4 type t is table of r index by binary_integer;
11:04:52 5 type rc is ref cursor;
11:04:52 6 function f(p out rc) return number;
11:04:52 7 end;
11:04:52 8 /

Package created.

11:04:54 [SYSADM@INLABTST]> create or replace package body my_package
11:05:04 2 is
11:05:04 3 function f(p out rc) return number
11:05:04 4 is
11:05:04 5 j integer := 1;
11:05:04 6 my_t t;
11:05:04 7 begin
11:05:04 8 for i in (select object_id, object_name from all_objects where rownum<11)
11:05:04 9 loop
11:05:04 10 my_t(j).no := i.object_id;
11:05:04 11 my_t(j).name := i.object_name;
11:05:04 12 j := j+1;
11:05:04 13 end loop;
11:05:04 14
11:05:04 15 open p for select * from table(my_t);
11:05:04 16
11:05:04 17 return 1;
11:05:04 18 end;
11:05:04 19 end;
11:05:04 20 /

Warning: Package Body created with compilation errors.

11:05:06 [SYSADM@INLABTST]> show error
Errors for PACKAGE BODY MY_PACKAGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/16 PL/SQL: SQL Statement ignored
15/30 PL/SQL: ORA-22905: cannot access rows from a non-nested table
item

15/36 PLS-00382: expression is of wrong type
11:05:10 [SYSADM@INLABTST]>
Tom Kyte
January 02, 2008 - 11:15 am UTC

you use create type at the SQL level, to create a new datatype, then you can use it in SQL.

create type myScalarType as object ( ..... );
create type myTableType as table of myScalarType;



you can reference myTableTable in SQL now

Karthick Pattabiraman, January 22, 2008 - 5:02 am UTC

How can i do this is 8.1.2

15:08:18 [SYSADM@INLABTST]> drop type mytbl
15:08:27   2  /

Type dropped.

15:08:27 [SYSADM@INLABTST]> drop type myobj
15:08:27   2  /

Type dropped.

15:08:27 [SYSADM@INLABTST]> create or replace type myobj as object(no number, name varchar2(100))
15:08:27   2  /

Type created.

15:08:27 [SYSADM@INLABTST]> create or replace type mytbl as table of myobj
15:08:27   2  /

Type created.

15:08:27 [SYSADM@INLABTST]>
15:08:27 [SYSADM@INLABTST]> declare
15:08:27   2    lTbl mytbl := mytbl(myobj(1,'karthick'),myobj(2,'tom'));
15:08:27   3  begin
15:08:27   4    for i in (select * from table(lTbl))
15:08:27   5    loop
15:08:27   6            dbms_output.put_line(i.no||'-'||i.name);
15:08:27   7    end loop;
15:08:27   8  end;
15:08:27   9  /
1-karthick
2-tom


any alternative for the table function
Tom Kyte
January 22, 2008 - 7:43 am UTC

if you have 8.1.2, you have a rather unique release.

So, I'll presume you meant 8.1.7

ops$tkyte@ORA817DEV> declare
  2    lTbl mytbl := mytbl(myobj(1,'karthick'),myobj(2,'tom'));
  3  begin
  4    for i in (select * from table(<b>cast( lTbl as myTbl)</b> ))
  5    loop
  6            dbms_output.put_line(i.no||'-'||i.name);
  7    end loop;
  8  end;
  9  /
1-karthick
2-tom

PL/SQL procedure successfully completed.


the datatype was lost in the binding done by plsql way back when, the CAST restores it (and works in current releases as well, it is harmless)

Karthick Pattabiraman, January 22, 2008 - 8:41 am UTC

Thank you, that helps a lot.

And sorry about the version, yes iam using 8.1.7

Clarification

Muhammad Riaz Shahid, February 13, 2008 - 4:10 pm UTC

Tom,
I am using 10gR2.
Taking your example, if I declare a global variable of type num_table, how I can use it in SQL? For example:

SQL> create or replace package test_pkg
  2  as
  3   tbl_num num_table;
  4  end;
  5  /

Package created.

SQL> SELECT *
  2  FROM t1, t2
  3  WHERE t1.n = t2.n
  4  AND t1.n IN (SELECT * FROM table(test_pkg.tbl_num));
AND t1.n IN (SELECT * FROM table(test_pkg.tbl_num))
                                 *
ERROR at line 4:
ORA-06553: PLS-221: 'TBL_NUM' is not a procedure or is undefined


What I think is since tbl_num belongs to PL/SQL, SQL doesn't recognize it. But what i need to do on SQL side to do above?

Regards
Tom Kyte
February 13, 2008 - 10:54 pm UTC

if you put the sql INSIDE PLSQL, then it'll work.

else, you have to write a function that returns that variable and then select from that function

plsql functions - visible in sql

plsql anything else - not visible in sql

Example required

Muhammad Riaz Shahid, February 14, 2008 - 7:17 pm UTC

<Quote>
else, you have to write a function that returns that variable and then select from that function
</Quote>

Tom,
could you please give us an example?
Tom Kyte
February 15, 2008 - 8:08 am UTC 

ops$tkyte%ORA11GR1> create or replace type num_table as table of number
  2  /

Type created.

ops$tkyte%ORA11GR1> create or replace package my_pkg
  2  as
  3          function foo return num_table;
  4  end;
  5  /

Package created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create or replace package body my_pkg
  2  as
  3          g_data  num_table := num_table( 1, 3, 5, 7 );
  4
  5          function foo return num_table
  6          as
  7          begin
  8                  return g_data;
  9          end;
 10  end;
 11  /

Package body created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select * from TABLE( my_pkg.foo );

COLUMN_VALUE
------------
           1
           3
           5
           7

ops$tkyte%ORA11GR1>


it really was as simple as I made it sound - create a function that returns that variable - that is all

Karthick Pattabiraman, February 15, 2008 - 8:28 am UTC

My god did you write this once he asked or you had it with you. Even its simple you wrote it once he asked.

I am online now when you are writing followups. Each question you answer is completely different one.

What did you dirnk before taking up the followups :-)

dml on plsql table in sql

Martin, February 17, 2008 - 4:43 pm UTC

Doing DML (update, delete) on a pl/sql table is (to my knowledge) possible only by
1) writing it to a (temporary) table
2) doing DML on it
3) bulk collecting it back to a pl/sql table

It is possible entirely within pl/sql if using two pl/sql tables and coding the Change-functionality using a select statement.
However this does not work if using only one (=the same) pl/sql table (tested both on 10gR2 and 11g):

SQL> declare
  2    v_tab  num_table := num_table(8,9,10,11,12);
  3    n number;
  4  begin
  5    select max(column_value) into n from table (v_tab);
  6    dbms_output.put_line('Before-Update: ' || n);
  7
  8    -- I'd like to do DML-operations on this array,
  9    --   e.g. increment n with 1 for values > 10
 10    select case when column_value > 10 then column_value + 1 else column_value end
 11           bulk collect into v_tab
 12    from   table(v_tab);
 13
 14    select max(column_value) into n from table (v_tab);
 15    dbms_output.put_line('After-Update: ' || n);
 16  end;
 17  /
Before-Update: 12
After-Update:

PL/SQL procedure successfully completed.

Can you give a hint to understand why this does not work?
Tom Kyte
February 17, 2008 - 5:03 pm UTC

agreed, it would be done like this (i'll have someone look at this though - to see if it is "broken")

ops$tkyte%ORA10GR1> create or replace type num_table is table of number
  2  /

Type created.

ops$tkyte%ORA10GR1>
ops$tkyte%ORA10GR1> declare
  2    v_tab  num_table := num_table(8,9,10,11,12);
  3    l_tab  num_table;
  4    n number;
  5  begin
  6    for i in 1 .. v_tab.count
  7    loop
  8          dbms_output.put_line( 'v_tab(' || i || ') = ' || v_tab(i) );
  9    end loop;
 10    select max(column_value) into n from table (v_tab);
 11    dbms_output.put_line('Before-Update: ' || n);
 12
 13    -- I'd like to do DML-operations on this array,
 14    --   e.g. increment n with 1 for values > 10
 15    select data BULK COLLECT into l_tab
 16      from ( select case when column_value > 10 then column_value + 1 else column_value end data
 17               from table(v_tab)
 18                   );
 19
 20    for i in 1 .. l_tab.count
 21    loop
 22          dbms_output.put_line( 'l_tab(' || i || ') = ' || l_tab(i) );
 23    end loop;
 24    select max(column_value) into n from table (l_tab);
 25    dbms_output.put_line('After-Update: ' || n);
 26  end;
 27  /
v_tab(1) = 8
v_tab(2) = 9
v_tab(3) = 10
v_tab(4) = 11
v_tab(5) = 12
Before-Update: 12
l_tab(1) = 8
l_tab(2) = 9
l_tab(3) = 10
l_tab(4) = 12
l_tab(5) = 13
After-Update: 13

PL/SQL procedure successfully completed.



Clarification

Muhammad Riaz Shahid, February 19, 2008 - 3:09 pm UTC

Dear Tom,
Thanks for your clear example. I wanted to have setter and getter routines so i modified your example as:

 create or replace package my_pkg
    as
       procedure p_set_num_table(p_val IN NUMBER);
            function foo return num_table;
    end;

create or replace package body my_pkg
    as
       g_data  num_table := num_table();
       g_num_table_index NUMBER:=0;
       procedure p_set_num_table (p_val IN NUMBER)
       IS
       BEGIN
           g_data.extend;
      g_num_table_index := g_num_table_index +1;
           g_data(g_num_table_index) := p_val;
      end;
           function foo return num_table
            as
            begin
                    return g_data;
            end;
   end;

And then:

 exec my_pkg.p_set_num_table(10);

 SELECT *
 FROM emp
 WHERE empno in (SELECT * FROM table(my_pkg.foo) );

A reader, March 28, 2011 - 10:43 am UTC

Hi tom am great follower of your website since 1 year.

This is my first post. Am weak in pl/sql data types.

This the version am using.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production

our requirement: For some purpose we need to query a table using a where condition ( single condition say col_name1 = ‘something’) then we need to take that resultset and load into another pl/sql table or any and again we need to query that pl/sql table using another where condition ( single condition say col_name1 = ‘something1’) and repeating it one more time with another where condition to get a result.

For eg.

This is the dummy code I am trying out.


create or replace procedure ETT_POC
as
cursor c1 is select * from user_objects where object_type IN ('PROCEDURE','FUNCTION','INDEX');
type myobj is table of user_objects%rowtype;
type myvar is table of myobj;
ett_type2 myvar;
ett_type3 myvar;

BEGIN

open c1;
fetch c1 into ett_type2;
select * into ett_type3 from table(ett_type2);
for i in (select * from table(ett_type2))loop
dbms_output.put_line(i.OBJECT_NAME);
end loop;
close c1;

open c2 for select * from table (ett_type3) where object_type IN ('PROCEDURE');
fetch c2 into ett_type4;

.
.
.
Close c2;

end;

am getting lots of error which am not able to fix it as I am weak in pl/sql data types. Can you please help on resolving it.

thanks in advance :)
Tom Kyte
March 29, 2011 - 3:40 am UTC

this isn't really the way we work - why cannot you just have a query query the actual data?

We can do this, but it isn't "natural" or "normal"


ops$tkyte%ORA11GR2> create type myScalarType as object (
  2  user_id number,
  3  username varchar2(30),
  4  created date )
  5  /

Type created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create type myTableType as table of myScalarType
  2  /

Type created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_data myTabletype;
  3      l_data2 myTabletype;
  4  begin
  5      select myScalarType( user_id, username, created )
  6        bulk collect into l_data
  7        from all_users
  8       where user_id > 1;
  9  
 10      dbms_output.put_line( 'there was ' || l_data.count || ' selected initially ');
 11  
 12      select myScalarType( x.user_id, username, created )
 13        bulk collect into l_data2
 14        from TABLE( l_data ) x
 15       where x.username like '%S%';
 16  
 17      dbms_output.put_line( 'there was ' || l_data2.count || ' selected second time ');
 18  
 19      select myScalarType( x.user_id, username, created )
 20        bulk collect into l_data
 21        from TABLE( l_data2 ) x
 22       where to_char(x.created) like '%DEC%';
 23  
 24      dbms_output.put_line( 'there was ' || l_data.count || ' selected third time ');
 25  end;
 26  /
there was 40 selected initially
there was 25 selected second time
there was 1 selected third time

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select count( case when user_id > 1 then 1 end ) cnt1,
  2         count( case when user_id > 1 and username like '%S%' then 1 end ) cnt2,
  3         count( case when user_id > 1 and username like '%S%' and to_char(created) like '%DEC%' then 1 end ) cnt3
  4    from all_users
  5  /

      CNT1       CNT2       CNT3
---------- ---------- ----------
        40         25          1

A reader, March 29, 2011 - 5:42 am UTC

Thanks for your spontaneous response. it helped me a lot. keep rocking..
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library