Re: PL/SQL question

Dana Jian wrote:
>
> Hi,
>
> I need implement a procedure which takes input parameters for limiting
> records in the cursor defined in the proc.
> 1) the input parameter is a string contains the name list of the employee
> which need to be retrieved, like '(Mulan, Tom, Laysee...)"
> 2) If the input parameter is empty, return all the employees in the cursor
>
> Let's see the simple code,
>
> PROCEDURE SP_TEST
> (
> in_names IN VARCHAR2,
> .....
> CURSOR cr_result IS
> SELECT id, name
> FROM employee
> WHERE name IN in_names;
>
> The problem is this code doesn't suit my need of #2. A bad idea is just
> conditionally creating several cursor by checking the input parameter---that
> would not be a good one.
>
> Any good idea?
>
> Thanks a lot!
>
> Dana
> djian_at_trasa.com

You will need dynamic SQL (DBMS_SQL in 8.0 and below, and execute_immediate in 8i)...

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

"Beta, n: Latin for 'still does not work'"