Re: PL/SQL variable number of ORs in SELECT

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/05
Message-ID: <33e99464.28176095_at_newshost>#1/1

On 5 Aug 1997 13:25:42 -0400, curti_at_rci.rutgers.edu (Frank Curti) wrote:

>I have a stored procedure that must build cursors based on an input
>string that contains a variable number of search parameters that are
>ORed together in a select statement.
>For instance if I pass in the string 22;34;67 I would have the
>following statement
>OPEN cursor_name FOR SELECT * FROM my_table WHERE my_num = '22'
> OR my_num = '34' OR my_num = '67';
>
>Can I build this dynamically, in some instances we may have to pass in up
>to 100 search parameters. I looked at the DBMS_SQL package but was unable
>to find a way to use it in this case.
>
>Thank you
>Frank Curti

Using static sql, you can do something like:

create or replace procedure search( p_str in varchar2 ) as

    type myArray is table of number index by binary_integer;     type EmpCurType is ref cursor return emp%rowtype;    

    l_nums  myArray;
    l_str   varchar2(4096) default p_str;
    l_n     number;
    c1      EmpCurType;

    emp_rec emp%rowtype;
begin  

    for i in 1 .. 100 loop

        l_nums(i) := NULL;
        if ( l_str is not null ) then
            l_n := instr( l_str, ';' );
            if ( l_n = 0 ) then
                l_n := length(l_str)+1;
            end if;
            l_nums(i) := substr( l_str, 1, l_n-1 );
            l_str := substr( l_str, l_n+1 );
        end if;

    end loop;  

    open C1 for select * from emp where empno in     ( l_nums(1), l_nums(2), l_nums(3), l_nums(4), l_nums(5) );  

    loop

        fetch c1 into emp_rec;
        exit when c1%notfound;
        dbms_output.put_line( emp_rec.ename );

    end loop;  

    close c1;
end;
/

so you can just use an IN (my example goes upto 5, you can go higher of course).

Dynamically, you can use dbms_sql as follows to achieve the same results (since I don't necessarily know the number/types of columns, I bind them all to a varchar2(2000) to print them out and count them as I go)

create or replace procedure search( p_str in varchar2 ) as

    l_query         varchar2(5192) default
                    'select * from emp where empno in (';
    l_theCursor     integer;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;

begin  

    l_query := l_query || replace( p_str, ';', ',' ) || ')';  

    l_theCursor := dbms_sql.open_cursor;  

    dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );  

    for i in 1 .. 255 loop

        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
            l_colCnt := l_colCnt + 1;
        exception
            when others then
                if ( sqlcode = -1007 ) then
                    exit;
                else
                    raise;
                end if;
        end;

    end loop;  

    l_status := dbms_sql.execute(l_theCursor);  

    loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            dbms_output.put( 'field ' || to_char(i) || ': '  );
            dbms_output.put_line( l_columnValue );
        end loop;
        dbms_output.put_line( '----------------' );

    end loop;
    dbms_sql.close_cursor(l_theCursor);
end;
/

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 05 1997 - 00:00:00 CEST