CODEWITHSUNDEEP
oracle-databaseplsqlprocedure
user1333057
user1333057

Reputation: 617

using plsql table in a procedure

I am using plsql in a oracle database 9i I have a stored procedure, With an in parameter of "table of number" that is called numbers. I now want to select all rows from a table where a column named: ID is equal to a number inside "numbers" Just like I can do select * from table name where Id in (!,!,!,...) Thanks for the help.

Update : Just to clear up, I have a user defined type named numbers, Number is defined: table of number. So in the procedure decleration I have "P_array in numbers" I need to select * from a table where Id is found in p_array

Upvotes: 1

Views: 895

Answers (3)

Max
Max

Reputation: 4077

try the following :-

select * from tableName where id in (select c.column_value from table(cast(p_array as numbers)) c);

where numbers is table of number

Upvotes: 1

DazzaL
DazzaL

Reputation: 21973

like this?

SQL> create type numbers as table of number;
  2  /

Type created.

SQL> create table foo (id number) ;

Table created.

SQL> insert into foo select rownum from dual connect by level <= 10;

10 rows created.

SQL> select * from foo;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> create procedure testnum(p_num in numbers)
  2  is
  3  begin
  4    for r_row in (select id
  5                    from foo f
  6                   where f.id in (select /*+ cardinality(t, 10) */ column_value
  7                                    from table(p_num) t))
  8    loop
  9      dbms_output.put_line(r_row.id);
 10    end loop;
 11  end;
 12  /

Procedure created.

SQL> set serverout  on
SQL> exec testnum(numbers(2, 6, 9));
2
6
9

the cardinality hint is used to tell oracle roughly how many elements are in your table. without it, Oracle will assume ~8k rows which may be too high and cause unwanted full scans in the plans.

you can do a direct join too if you prefer.

for r_row in (select /*+ cardinality(t, 10) */ f.id
                from foo f
                     inner join table(p_num) t
                             on t.column_value = f.id)

Upvotes: 2

Floris Velleman
Floris Velleman

Reputation: 4888

SELECT * FROM A_TABLE WHERE ID IN (SELECT SAVED_NUMBERs FROM TABLE_NUMBER);

Do you mean this?

Upvotes: 0

Related Questions