Create/Drop Oracle Stored Package

Syntax of the Create Package

1. CREATE OR REPLACEPACKAGE [body] packagenameIS
3. END;

Sample:

1. CREATE OR REPLACEPACKAGE overloadedIS
2. PROCEDUREInsby (namein VARCHAR2 );
3. PROCEDUREInsby (numbin PLS_INTEGER );
4. ENDOverloaded;
5. /
7. CREATE OR REPLACEPackage body overloadedIS
8. -- ===================================================== ===
9. PROCEDURE Local(Namein VARCHAR2, nameoutOUTVARCHAR2)IS
11. BEGIN
12. Nameout: = TRANSLATE (namein,'Aeiouaeiou','Eiouaeioua');
13. END Local;
14. -- ===================================================== ===
15. PROCEDUREInsby (namein VARCHAR2)IS
16. X PLS_INTEGER;
17. BEGIN
18. SELECT MAX(Seqno) + 1
19. INTOX
20. FROMPersons;
22. INSERT INTOPersons
23. (Seqno,Name)
24. VALUES
25. (X, namein );
26. COMMIT;
27. ENDInsby;
28. -- ===================================================== ===
29. PROCEDUREInsby (numbin PLS_INTEGER)IS
30. X VARCHAR2 (30 );
31. BEGIN
32. SELECT MAX(Name)
33. INTOX
34. FROMPersons;
36. Local(X, x );
38. INSERT INTOPersons
39. (Seqno,Name)
40. VALUES
41. (Numbin, x );
42. COMMIT;
43. ENDInsby;
44. -- ===================================================== ===
45. ENDOverloaded;
46. /
48. ExecOverloaded. insby ('Helen Lofstrom');
50. ExecOverloaded. insby (4 );

Syntax of the Drop Package

DROPPACKAGE [BODY] [Schema.] Package_name;

Sample:

1. DROPPackage inv. INV_TRANSFER_ORDER_PVT;
2. DROPPackage body inv. INV_TRANSFER_ORDER_PVT;