PL/SQL use VARRAY in IN CLAUSE

oracle plsql oracle10g varray
Lohit picture Lohit · Dec 12, 2011 · Viewed 11.5k times · Source

Is it possible to use VARRAY in IN CLAUSE of pl/sql?

Answer

Codo picture Codo · Dec 12, 2011

Yes, you can, provided that the VARRAY type is a global type (and not local to some PL/SQL code):

CREATE OR REPLACE TYPE str_tab_type IS VARRAY(10) OF VARCHAR2(200);

DECLARE
  l_str_tab str_tab_type;
  l_count NUMBER;
BEGIN
  l_str_tab := str_tab_type();
  l_str_tab.extend(2);
  l_str_tab(1) := 'TABLE';
  l_str_tab(2) := 'INDEX';

  SELECT COUNT(*) INTO l_count
  FROM all_objects
  WHERE object_type IN (SELECT COLUMN_VALUE FROM TABLE(l_str_tab));
END;
/

Related questions

How to execute an oracle stored procedure?

I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures. Procedure: create or replace procedure temp_proc is begin DBMS_OUTPUT.PUT_LINE('Test'); end it …

Read More
How to use BOOLEAN type in SELECT statement

I have a PL/SQL function with BOOLEAN in parameter: function get_something(name in varchar2, ignore_notfound in boolean); This function is a part of 3rd party tool, I cannot change this. I would like to use this function …

Read More
check if "it's a number" function in Oracle

I'm trying to check if a value from a column in an oracle (10g) query is a number in order to compare it. Something like: select case when ( is_number(myTable.id) and (myTable.id >0) ) then 'Is a number …

Read More