Mega Code Archive

 

Categories / Oracle PLSQL / System Packages

 

Pass where clause to a stored procedure

SQL> SQL> CREATE TABLE employees   2  ( employee_id          number(10)      not null,   3    last_name            varchar2(50)      not null,   4    email                varchar2(30),   5    hire_date            date,   6    job_id               varchar2(30),   7    department_id        number(10),   8    salary               number(6),   9    manager_id           number(6)  10  ); Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)   2                values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)   2                values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 ); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)   2                 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)   2                 values( 1004,  'Kyte', 'YourName@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)   2                 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)   2                 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)   2                 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)   2                 values( 1008, 'Oracle', 'wvelasq@g.com', SYSDATE, 'DBA', 20000, 4, 1006); 1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE showemps (   2     where_in IN VARCHAR2 := NULL   3     )   4  IS   5     cur INTEGER := DBMS_SQL.OPEN_CURSOR;   6     fdbk INTEGER;   7   8     last_name_tab DBMS_SQL.VARCHAR2_TABLE;   9     hire_date_tab DBMS_SQL.DATE_TABLE;  10  BEGIN  11     DBMS_SQL.PARSE  (cur,  12        'SELECT last_name, hire_date FROM employees  13          WHERE ' || NVL (where_in, '1=1') ||  14         'ORDER BY hire_date',  15          DBMS_SQL.NATIVE);  16  17     DBMS_SQL.DEFINE_ARRAY (cur, 1, last_name_tab, 100, 1);  18     DBMS_SQL.DEFINE_ARRAY (cur, 2, hire_date_tab, 100, 1);  19  20     fdbk := DBMS_SQL.EXECUTE_AND_FETCH (cur);  21  22     DBMS_SQL.COLUMN_VALUE (cur, 1, last_name_tab);  23     DBMS_SQL.COLUMN_VALUE (cur, 2, hire_date_tab);  24  25     FOR rowind IN last_name_tab.FIRST .. last_name_tab.LAST  26     LOOP  27        DBMS_OUTPUT.PUT_LINE (  28           last_name_tab(rowind) ||  29           ' was hired on ' ||  30           hire_date_tab(rowind));  31     END LOOP;  32  33     DBMS_SQL.CLOSE_CURSOR (cur);  34  END;  35  / Procedure created. SQL> SQL> drop table employees; Table dropped. SQL> SQL>