Yurttas/PL/DBL/oracle/F/02/SPJ/Shipments-def.sp

 1/*
 2REM
 3REM Shipments-def.sp
 4REM
 5*/
 6
 7DECLARE
 8
 9  cursor_handle INTEGER;
10
11  parts_created INTEGER;
12
13BEGIN
14
15  cursor_handle :=DBMS_SQL.OPEN_CURSOR;
16
17  DBMS_SQL.PARSE(cursor_handle,
18                 'CREATE TABLE Shipments(sn VARCHAR2(3) NOT NULL,'||
19                                        'pn VARCHAR2(3) NOT NULL,'||
20                                        'jn VARCHAR2(3) NOT NULL,'||
21                                        'quantity INTEGER,'||
22                            'PRIMARY KEY(sn,pn,jn),'||
23                            'FOREIGN KEY(sn) REFERENCES Suppliers(SN),'||
24                            'FOREIGN KEY(pn) REFERENCES Parts(pn),'||
25                            'FOREIGN KEY(jn) REFERENCES Projects(jn))',
26                 DBMS_SQL.V7);
27
28  parts_created := DBMS_SQL.EXECUTE(cursor_handle);
29
30  DBMS_SQL.CLOSE_CURSOR(cursor_handle);
31
32  DBMS_OUTPUT.PUT_LINE('Table ''Shipments'' created Successfully');
33
34EXCEPTION
35  WHEN OTHERS THEN
36    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
37    DBMS_OUTPUT.PUT_LINE('Table ''Shipments'' cannot be created');
38    DBMS_OUTPUT.PUT_LINE('Table ''Shipments'' might be already existing');
39
40END;
41/