On Confusion and Recompilation

Date Added: Jan 2010
Format: HTML

With identifiable names there is less confusion. This article demonstrates with examples that it is very important to apply best practices to program naming, recompiling invalid programs, and collecting. Confusion arises when there is a package named pck_events_1 that has a function named fn_1. The author has another schema-level ("Standalone") function, also named fn_1, defined in the same schema. How can one call the schema-level function from a subprogram inside the package? It can be difficult. In such a situation one can change the name of one of the two programs or prefix the call to the schema-level function with the name of the schema or Use dynamic SQL to call the schema-level function. This paper describes the last two options with practicals. It also means that simply having different parameter lists will not be enough to tell the PL/SQL compiler which program to use. The user needs to pass a collection from one PL/SQL program to another. Does it make any difference which collection type is used? Oracle PL/SQL offers three types of collections (arraylike structures): associative array, nested table, and VARRAY. A PL/SQL developer might like associative arrays. In Oracle9i Database Release 2 associative arrays can be indexed by integers. If it has to be stored in the database then it cannot be used. So should one use nested table or VARRAY?