Restrictions on using packaged functions in SQL statements

o The function has to be stored in the database, either alone or as part of package.
o Parameters of the function must use the positional notation method. They cannot use the named notation method.
o Any function called from a SQL statement cannot modify any database tables WNDS.
o A function called from DML statement must not read from or modify the table being modified by DML statement but it can update other tables.
o In order to be executed remotely (via a database line) a function must not read or write the value from packaged variable WNPS, RNPS.
o Function called from SELECT, VALUES, SET clauses can write packaged variables, and functions in all other clauses must have the WNPS purity level.
o If a function calls a stored procedure that does an update, the function does not have the WNDS purity level and cannot be used inside an SQL.
o PL/SQL functions cannot be called from a check constraint clause for create or alter table.
o The formal parameters must use only data base types no Boolean, record, table.
o The return type of the function must be a database type.
o The function cannot contain statements that end the transaction or session control statements or system control statements neither DDL statements.
o Stored procedures cannot be called from SQL statements.
o The function can take only IN parameter, no IN OUT or OUT.
o For packaged functions the RESTRICT_REFERENCES pragma is required.
o For packaged functions the RESTRICT_REFERENCES pragma is required not for Oracle8i and higher because PL/SQL engine can verify the purity level of all functions at runtime as needed.
o This pragma specifies the purity level of a given function.
o PRAGMA RESTRICT_REFERENCES (subprogram_or_package_name,WNDS, [,RNDS]…..
o WNDS is required for the pragma.
o The pragma goes in the package header, with the specification for the function because the body may not exist when the calling block is created and the Pl/sql compiler needs the pragma to determine the purity levels of the packaged function to verify that it is being used correctly in the calling block, so the pragma is check at compile time, not runtime.
o Using the pragma means that the engine does not need to verify the level at runtime, which is a performance benefit, so it s advantageous to use RESTRICT_REFERENCES, even though it is no longer required.
o The initialization section of a package can have a purity level as well.
o The first time any function in the package is called, the initialization section is run so the packaged function is only as pure as the initialization section of the containing package.
o The default keyword is used instead of the subprogram name in the pragma.
o The TRUST keyword is necessary for the pragma if the body is not in PL/SQL.

Persistent states in package variables and cursors

o You can keep track of the state of a package variable or cursor, which persists throughout the user session, form the time the user first references the variable or cursor to the time the user disconnects.
o It persists across transactions within a session
o It does not persist from session to session for the same user.
o The value of the variable is released when the user disconnects.
o Each session will have its own value for the variables.
o It persists across the transactions and through the user session.
o Reusable package will last only for each database call.
o PRAGMA SERIALLY_REUSABLE;
o Serially reusable version resets the state (and thus the output) each time.
o By the first time you call a procedure, function, or reference a global variable within the package, the whole package will be loaded into the memory and stay there.

Use Forward Referencing

o We need the forward referencing if we have 2 procedures call each other.
o This will be only the procedure name and its formal parameters.
o Forward declarations are also used in package headers.

Overloading feature

o There is more than one procedure or function with the same name, but with different parameters.
o You cannot overload two subprograms if their parameters differ only in name or mode.
o You cannot overload two functions based only on their return type.
o The parameters of overloaded functions must differ by type family.
o The pragma applies to the nearest definition prior to the pragma.