SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, 'Mar', 'Wells','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, 'Man', 'Kyte','Economics', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, 'Pat', 'Poll','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, 'Tim', 'Viper','History', 4);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE DynamicQuery (
2 p_Major1 IN lecturer.major%TYPE DEFAULT NULL,
3 p_Major2 IN lecturer.major%TYPE DEFAULT NULL) AS
4
5 v_CursorID INTEGER;
6 v_SelectStmt VARCHAR2(500);
7 myFirstName lecturer.first_name%TYPE;
8 v_LastName lecturer.last_name%TYPE;
9 v_Major lecturer.major%TYPE;
10 v_Dummy INTEGER;
11
12 BEGIN
13 v_CursorID := DBMS_SQL.OPEN_CURSOR;
14
15 v_SelectStmt := 'SELECT first_name, last_name, major
16 FROM lecturer
17 WHERE major IN (:m1, :m2)
18 ORDER BY major, last_name';
19
20 DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);
21
22 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m1', p_Major1);
23 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m2', p_Major2);
24
25 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, myFirstName, 20);
26 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 2, v_LastName, 20);
27 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 3, v_Major, 30);
28
29 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
30
31 LOOP
32 IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
33 EXIT;
34 END IF;
35
36 DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, myFirstName);
37 DBMS_SQL.COLUMN_VALUE(v_CursorID, 2, v_LastName);
38 DBMS_SQL.COLUMN_VALUE(v_CursorID, 3, v_Major);
39
40 INSERT INTO MyTable (char_col)VALUES (myFirstName || ' ' || v_LastName || ' is a ' ||v_Major || ' major.');
41 END LOOP;
42
43 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
44
45 COMMIT;
46 EXCEPTION
47 WHEN OTHERS THEN
48 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
49 RAISE;
50 END DynamicQuery;
51 /
Procedure created.
SQL>
SQL> show error
No errors.
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL> drop table mytable;
Table dropped.