Centralize TYPE definitions in one package (Oracle)


Link to this posting

Postby Ursego » 30 Jul 2019, 11:36

Create used-defined types for datatypes which are used to declare variables and procedures arguments all over the application. Create them in one package so any type definition appears only once, and the developers know for sure where to look for all the definitions.

We are talking about types for:

• Collections (tables and nested tables) of different primitive datatypes.
• Strongly typed REF CURSORs.
• Scalar datatypes.
• Records.

Remarks:

• VARRAYs are not here since they have a maximum length, so it doesn't make sense to create generic TYPE based on them (in most cases, its length would be too big or too small).
• If you pass cursors between procedures very extensively, then, maybe, the weak REF_CURSOR is a more practical solution (even though type-unsafe) than a lot of strongly typed REF CURSORs, declared in our package. Alternatively, the strongly typed REF CURSORs can be declared in different packages which exist (or created if needed) for different business entities (which may be made up of one or more tables and views) rather than in one central package.
• For scalar datatypes and records, create SUBTYPEs only for datatypes that aren't anchorable back to the database. Otherwise, declare variables with %TYPE and %ROWTYPE.

The package has no body - only the specification:

Code: Select all
CREATE OR REPLACE PACKAGE pck_types IS
   TYPE integer_tt IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; -- tt - table type
   TYPE integer_ntt IS TABLE OF INTEGER; -- ntt - nested table type

   TYPE number_tt IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   TYPE number_ntt IS TABLE OF NUMBER;

   TYPE varchar2_tt IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
   TYPE varchar2_ntt IS TABLE OF VARCHAR2(4000);

   TYPE date_tt IS TABLE OF DATE INDEX BY BINARY_INTEGER;
   TYPE date_ntt IS TABLE OF DATE;

   TYPE boolean_tt IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
   TYPE boolean_ntt IS TABLE OF BOOLEAN;

   ...
END pck_types;
User avatar
Ursego
Site Admin
 
Posts: 143
Joined: 19 Feb 2013, 20:33



Ketones are a more high-octane fuel for your brain than glucose. Become a biohacker and upgrade yourself to version 2.0!



cron
Traffic Counter

eXTReMe Tracker