Pass where clause to a stored procedure : dbms_sql « System Packages « Oracle PL / SQL






  1. Oracle PL / SQL
  2. System Packages
  3. dbms_sql

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>








Related examples in the same category

1.First DBMS_SQL Example
2.Pass a query statement to a stored procedure
3.Use dbms_sql.open_cursor create a cursor
4.Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
5.Use dbms_sql.bind_array to bind array to a cursor
6.Use dbms_sql.parse to bind a select statement to a cursor
7.Call dbms_sql.describe_columns2 to get info for a column
8.Non-Query DML and DDL Statements
9.Use DBMS_SQL with the RETURNING clause.
10.DBMS_SQL.NATIVE;
11.DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
12.Dynamic sql statement with variable binding
13.Dynamic select statement and row fetch
14.Execute PL/SQL block
15.DBMS_SQL package: dynamic SQL
16.Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
17.Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
18.drop the supplied table using dynamic SQL.
19.DBMS_SQL.BIND_VARIABLE_CHAR
20.illustrate the importance of setting out_value_size.
21.Calling a function which uses dynamic SQL from within an SQL statement.
22.Illustrates the interaction of roles and dynamic SQL.
23.illustrates a DDL statement which is built dynamically from the procedure parameters.
24.Use dbms_sql to process query, cursor and value
25.Use dbms_sql.bind_variable to bind variable
26.dbms_sql.varchar2_table type variable