What is package in oracle database Ferr / 27.04.202127.04.2021 Introduction To PL/SQL Packages In Oracle Database A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. A package always has a specification, which declares the public items that can be referenced from outside the package. A package includes variables, constants, cursors, exceptions, procedures, functions, and subprograms. It is compiled and stored in the Oracle Database. Typically, a package has a specification and a body. A package specification is mandatory while the package body can be required or optional, depending on the package specification. The package might include a set of procedures that forms an API, or a pool of type definitions and variable declarations. The package is compiled and stored in the database, where its contents can be shared by many applications. Packages usually have two parts, a specification spec and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms. You can think of the spec as an interface and of the body as a black box. You can debug, enhance, or replace a package body without changing the package spec. The spec holds public declarations, which are visible to stored procedures and other code outside the package. You must declare subprograms at the end of the spec after all other items except pragmas that name a specific function; such pragmas must follow the function spec. The body holds implementation details and private declarations, which are hidden from code outside the package. Following the declarative part of the package body is the optional initialization part, which holds statements that initialize package variables and do any other one-time setup steps. The AUTHID clause determines whether all the packaged subprograms execute with the privileges of their definer the default or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker. A call spec lets how to use cubase le5 map a package subprogram to a Java method or external C function. Get and Set methods for the package variables, if you want to avoid letting other procedures read and write them directly. Cursor declarations with the text of SQL queries. Reusing exactly the same query text in multiple locations is faster than retyping the same query each time with slight differences. It is also easier to maintain if you need to change a query that is used in many places. Declarations for exceptions. Typically, you need to be able to reference these from different procedures, so that you can handle exceptions within called subprograms. Declarations for procedures and functions that call each other. You do not need to worry about compilation order for packaged procedures and functions, making them more convenient than standalone stored procedures and functions when they call back and forth to each other. Declarations for overloaded procedures and functions. You can create multiple variations of a procedure or function, using the same names but different sets of parameters. Variables that you want to remain available between procedure calls in the same session. You can treat variables in a package like global variables. To pass a collection as a parameter between stored procedures or functions, you must declare the type in a package so that both the calling and called subprogram can refer to it. For additional information, see "Package Declaration". Only the declarations in the package spec are visible and accessible to applications. Implementation details in the package body are hidden and inaccessible. You can change the body implementation without having to recompile calling programs. Packages have a long history in software engineering, offering important features for reliable, maintainable, reusable code, often in team development efforts for large systems. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development. When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application. With packages, you can specify which types, items, and subprograms are public visible and accessible or private hidden and inaccessible. For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package not your application is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package. Packaged public variables and cursors persist for the duration of a session. They can be shared by all subprograms that execute in the environment. They let you maintain data across transactions without storing it in the database. When you call how to burn drm protected music packaged subprogram for the first time, the whole package is loaded into memory. Packages stop cascading dependencies and avoid unnecessary recompiling. For example, if you change the body of a packaged function, Oracle does not recompile other subprograms that call the function; these subprograms only depend on the parameters and return value that are declared in the spec, so they are only recompiled if the spec changes. The package specification contains public declarations. The declared items are accessible from anywhere in the package and to any other subprograms in the same schema. Figure illustrates the scoping. The spec lists the package resources available to applications. All the information your application needs to use the resources is in the spec. That is all the information you need to call the function. You need not consider its underlying implementation whether it is iterative or recursive for example. If a spec declares only types, constants, variables, exceptions, and call specs, the package body is unnecessary. Only subprograms and cursors have an underlying implementation. In Examplethe package needs no body because it declares types, exceptions, and variables, but no subprograms or cursors. Such packages let you define global variables, usable by stored procedures and functions and triggers, that persist throughout a session. To reference the types, items, subprograms, and call specs declared within a package spec, use dot notation:. You can reference package contents from database triggers, stored subprograms, 3GL application programs, and various Oracle tools. For example, you can call package procedures as shown in What are active studio monitors"Calling a Procedure in a Package" or Example You cannot reference remote packaged variables, either directly or indirectly. For example, you cannot call the a procedure through a database link if the procedure refers to a packaged variable. The package body contains the implementation of every cursor and subprogram declared in the package spec. Subprograms defined in a package body are accessible outside the package only if their specs also appear in the package spec. If a subprogram spec is not included in the package spec, that subprogram can only be how to get h1b visa sponsorship by other subprograms in the same package. A package body must be in the same schema as the package spec. Except for white space, the headers must match word for word. Example Matching Package Specifications and Bodies. The package body can also contain private declarations, which define types and items necessary for the internal workings of the package. The scope of these declarations is local to the package body. Therefore, the declared types and items are inaccessible except from within the package body. Unlike a package spec, the declarative part of a package body can contain subprogram bodies. Following the declarative part of a package body is the optional initialization part, which typically holds statements that initialize some of the variables previously declared in the package. The initialization part of a package plays a minor role because, unlike subprograms, a package cannot be called or passed parameters. As a result, the initialization part of a package is run only once, the first time you reference the package. Remember, if a package spec declares only types, constants, variables, exceptions, and call specs, the package body is unnecessary. However, the body can still be used to initialize items declared in the package spec. The package specification declares the following types, items, and subprograms:. After writing the package, you can develop applications that reference its types, call its subprograms, use its cursor, and raise its exception. When you create the package, it is stored in an Oracle database for use by any application that has execute privilege on the package. Remember, the initialization part of a package is run just once, the first time you reference the package. That is, the count reflects the number of new employees processed by one user, not the number processed by all users. This option is useful when you want a subprogram to accept similar sets of parameters that have different datatypes. The first procedure accepts a number, while the second procedure accepts string. Each procedure handles the data appropriately. For the rules that apply to overloaded subprograms, see "Overloading Subprogram Names". Items declared in the body are restricted to use within the package. Such items are called private. Such items are called public. To maintain items throughout a session or across transactions, place them in the declarative part of the package body. The value is lost when the session ends. To make the items public, how to solve http status 404 error them in the package specification. You do not need to qualify references to its contents by prefixing the package name. For example, you might call ABS from a database trigger, stored subprogram, Oracle tool, or 3GL application, as follows:. If you declare your own version of ABSyour local declaration overrides the global declaration. You can still call the built-in function by specifying its full name:. What is a Package? 7 rows · What is Package in Oracle? PL/SQL package is a logical grouping of a related subprogram. A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. What are PL/SQL Packages in Oracle Database? Packages are stored libraries in the database which allow us to group related PL/SQL objects under one name. Or in simple words, Packages are logical groups of related PL/SQL objects. Packages are named PL/SQL Blocks which mean they are permanently stored into the database schema and can be referenced or reused by your program. What is a Package? Package Instantiation and Initialization. Package State. A package is compiled and stored in the database, where many applications can share its contents. A package always has a specification , which declares the public items that can be referenced from outside the package. If the public items include cursors or subprograms, then the package must also have a body. The body must define queries for public cursors and code for public subprograms. The body can also declare and define private items that cannot be referenced from outside the package, but are necessary for the internal workings of the package. Finally, the body can have an initialization part , whose statements initialize variables and do other one-time setup steps, and an exception-handling part. You can change the body without changing the specification or the references to the public items; therefore, you can think of the package body as a black box. In either the package specification or package body, you can map a package subprogram to an external Java or C subprogram by using a call specification , which maps the external subprogram name, parameter types, and return type to their SQL counterparts. The AUTHID clause of the package specification determines whether the subprograms and cursors in the package run with the privileges of their definer the default or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker. You use this clause in situations like these:. You want clients to have access to the API, but not to the helper packages. Packages support the development and maintenance of reliable, reusable code with the following features:. You can make each package easy to understand, and make the interfaces between packages simple, clear, and well defined. This practice aids application development. When designing an application, all you need initially is the interface information in the package specifications. You can code and compile specifications without their bodies. Next, you can compile standalone subprograms that reference the packages. You need not fully define the package bodies until you are ready to complete the application. Packages let you share your interface information in the package specification, and hide the implementation details in the package body. Hiding the implementation details in the body has these advantages:. Application users cannot develop code that depends on implementation details that you might want to change. Package public variables and cursors can persist for the life of a session. They can be shared by all subprograms that run in the environment. They let you maintain data across transactions without storing it in the database. For the situations in which package public variables and cursors do not persist for the life of a session, see " Package State ". The first time you invoke a package subprogram, Oracle Database loads the whole package into memory. Packages prevent cascading dependencies and unnecessary recompiling. For example, if you change the body of a package function, Oracle Database does not recompile other subprograms that invoke the function, because these subprograms depend only on the parameters and return value that are declared in the specification. A package specification declares public items. The scope of a public item is the schema of the package. A public item is visible everywhere in the schema. To reference a public item that is in scope but not visible, qualify it with the package name. For information about scope, visibility, and qualification, see " Scope and Visibility of Identifiers ". Each public item declaration has all information needed to use the item. For example, suppose that a package specification declares the function factorial this way:. Invokers need not know how factorial is implemented for example, whether it is iterative or recursive. Creating Package Specifications. You cannot declare an associative array type at schema level. Therefore, to pass an associative array variable as a parameter to a standalone subprogram, you must declare the type of that variable in a package specification. Doing so makes the type available to both the invoked subprogram which declares a formal parameter of that type and to the invoking subprogram or anonymous block which declares a variable of that type. See Example Provide these subprograms to discourage package users from reading and writing public variables directly. You need not worry about compilation order for package subprograms, as you must for standalone subprograms that invoke each other. Overloaded subprograms are variations of the same subprogram. That is, they have the same name but different formal parameters. For more information about them, see " Overloaded Subprograms ". You cannot reference remote package public variables, even indirectly. For example, if a subprogram refers to a package public variable, you cannot invoke the subprogram through a database link. If a package specification declares cursors or subprograms, then a package body is required; otherwise, it is optional. The package body and package specification must be in the same schema. Every cursor or subprogram declaration in the package specification must have a corresponding definition in the package body. The headings of corresponding subprogram declarations and definitions must match word for word, except for white space. The cursors and subprograms declared in the package specification and defined in the package body are public items that can be referenced from outside the package. The package body can also declare and define private items that cannot be referenced from outside the package, but are necessary for the internal workings of the package. Finally, the body can have an initialization part , whose statements initialize public variables and do other one-time setup steps. The initialization part runs only the first time the package is referenced. The initialization part can include an exception handler. You can change the package body without changing the specification or the references to the public items. When a session references a package item, Oracle Database instantiates the package for that session. Every session that references a package has its own instantiation of that package. When Oracle Database instantiates a package, it initializes it. Initialization includes whichever of the following are applicable:. The values of the variables, constants, and cursors that a package declares in either its specification or body comprise its package state. Each session that references a package item has its own instantiation of that package. If the package is stateful, the instantiation includes its state. Therefore, previous changes to the package state are lost. All of a session's package instantiations including package states can be lost if any of the session's instantiated packages are invalidated and revalidated. Oracle Database treats a package as stateless if its state is constant for the life of a session or longer. This is the case for a package whose items are all compile-time constants. A constant whose initial value is a literal is always a compile-time constant. A constant whose initial value is not a literal, but which the optimizer reduces to a literal, is also a compile-time constant. Therefore, a package that is stateless when compiled at one optimization level might be stateful when compiled at a different optimization level. Oracle Database Development Guide for information about invalidation and revalidation of schema objects. Therefore, the amount of UGA memory needed increases linearly with the number of users, limiting scalability. The package state can persist for the life of a session, locking UGA memory until the session ends. In some applications, such as Oracle Office, a typical session lasts several days. The package state persists only for the life of a server call. After the server call, the work area returns to the pool. If a subsequent server call references the package, then Oracle Database reuses an instantiation from the pool. Reusing an instantiation re-initializes it; therefore, changes made to the package state in previous server calls are invisible. For information about initialization, see " Package Instantiation and Initialization ". You must use its public variables only within the work unit. If you make a mistake and depend on the value of a public variable that was set in a previous work unit, then your program can fail. Each package declares public variable n with initial value 5. Then, an anonymous block changes the value of each variable to Next, another anonymous block prints the value of each variable. The value of pkg. To re-open the cursor, you must make a new server call. A server call can be different from a subprogram invocation, as Example shows. Become familiar with the packages that Oracle Database supplies, and avoid writing packages that duplicate their features. This practice prevents other developers from building unsafe dependencies on your implementation details and reduces the need for recompilation. If you change the package specification, you must recompile any subprograms that invoke the public subprograms of the package. If you change only the package body, you need not recompile those subprograms. Declare public cursors in package specifications and define them in package bodies, as in Example This practice lets you hide cursors' queries from package users and change them without changing cursor declarations. If computing an initial value raises an exception, the initialization part can handle it with its own exception handler.