SQL>
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 DynamicPLSQL (
2 p_ID IN lecturer.ID%TYPE) IS
3
4 v_CursorID INTEGER;
5 v_BlockStr VARCHAR2(500);
6 myFirstName lecturer.first_name%TYPE;
7 v_LastName lecturer.last_name%TYPE;
8 v_Dummy INTEGER;
9
10 BEGIN
11 v_CursorID := DBMS_SQL.OPEN_CURSOR;
12
13 v_BlockStr :=
14 'BEGIN
15 SELECT first_name, last_name
16 INTO :first_name, :last_name
17 FROM lecturer
18 WHERE ID = :ID;
19 END;';
20
21 DBMS_SQL.PARSE(v_CursorID, v_BlockStr, DBMS_SQL.V7);
22
23 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':first_name', myFirstName, 30);
24 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':last_name', v_LastName, 30);
25 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':ID', p_ID);
26
27 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
28
29 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':first_name', myFirstName);
30 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':last_name', v_LastName);
31
32 INSERT INTO MyTable (num_col, char_col)
33 VALUES (p_ID, myFirstName || ' ' || v_LastName);
34
35 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
36
37 COMMIT;
38 EXCEPTION
39 WHEN OTHERS THEN
40 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
41 RAISE;
42 END DynamicPLSQL;
43 /
Procedure created.
SQL>
SQL> drop table mytable;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.