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> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, 'Barbara', 'Blues','Economics', 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, 'David', 'Large','Music', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, 'Rose', 'Bond','Music', 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);
1 row created.
SQL> set serveroutput on
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.NATIVE);
22
23 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':first_name', myFirstName);
24 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':last_name', v_LastName);
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 DBMS_OUTPUT.PUT('ID: ' || p_ID || ' ');
33 DBMS_OUTPUT.PUT_LINE(myFirstName || ' ' || v_LastName);
34
35 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
36 EXCEPTION
37 WHEN OTHERS THEN
38 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
39 RAISE;
40 END DynamicPLSQL;
41 /
Procedure created.
SQL>
SQL> REM This will raise ORA-6502.
SQL> exec DynamicPLSQL(10010)
BEGIN DynamicPLSQL(10010); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "RNTSOFT.DYNAMICPLSQL", line 39
ORA-06512: at line 1
SQL>
SQL> drop table lecturer;
Table dropped.