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, from which cursor variables are declared.
• 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.

Some of these TYPEs can be used unchanged throughout your application (there is only one way, for example, to declare an index-by table of dates); other types are specific to some part of an application but are standard within that. In either case, create a package to hold these standard TYPEs, so that they can be used in multiple programs.

The package has no body - only the specification:

Code: Select all
CREATE OR REPLACE PACKAGE pck_types IS
   TYPE integer_tab IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
   TYPE integer_ntab IS TABLE OF INTEGER;

   TYPE number_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   TYPE number_ntab IS TABLE OF NUMBER;

   TYPE varchar2_tab IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
   TYPE varchar2_ntab IS TABLE OF VARCHAR2(4000);

   TYPE date_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
   TYPE date_ntab IS TABLE OF DATE;

   TYPE boolean_tab IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
   TYPE boolean_ntab IS TABLE OF BOOLEAN;

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



IF you want to ((lose weight) OR (have unbelievable (brain function AND mental clarity))) THEN click:




cron
free counters

eXTReMe Tracker