Yurttas/PL/DBL/oracle/F/02/Bank/v01.sp

 1CREATE OR REPLACE
 2PROCEDURE v01 IS
 3
 4  cursor_handle INTEGER;
 5
 6  view_created INTEGER;
 7
 8BEGIN
 9
10  DBMS_OUTPUT.PUT('Creating a view consisting of branch names and the names of customers.');
11  
12  cursor_handle := DBMS_SQL.OPEN_CURSOR;
13
14  DBMS_SQL.PARSE(cursor_handle, 
15                'CREATE VIEW AllCustomer '              ||
16                '  AS (SELECT branchname, customername '||
17                '      FROM Depositor) '                ||
18                '      UNION '                          ||
19                '     (SELECT branchname, customername '||
20                '      FROM Borrower)',
21                 DBMS_SQL.V7);
22
23  view_created := DBMS_SQL.EXECUTE(cursor_handle);
24
25  DBMS_SQL.CLOSE_CURSOR(cursor_handle);
26
27  DBMS_OUTPUT.PUT_LINE('Done.');
28
29EXCEPTION
30  WHEN OTHERS
31  THEN
32    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
33    DBMS_OUTPUT.PUT_LINE('Error creating view.');
34
35END;
36/