Relationship between LOVs and record groups

• All LOVs are based on another Forms object called a record group.
• Record groups are logical objects, so they have no physical properties and are never displayed to the user.
• A record group is similar to a database table in that it stores an array of values in a column and row format.
• Record groups belong to a form and are stored locally on a user’s machine rather that in a database instance.
• The group’s structure can be defined in three ways: by query at design-time, statically at design time, programmatically at run time.
• We can have multiple LOV based on the same record group.
• You should not put a semi colon after the query in the record group.

LOV

List-of-values displays a pop-up listing of available values for a form field. It’s based on record group.
LOVs serve a number of purposes. They make data entry easier, and they ensure the validity of data.

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.

Benefits of Packages

o Ability to reference the objects from other Pl/SQL blocks, provide global variables for PL/SQL.
o The package header does not depend on anything; this is the advantage of packages.
o We can change the package body without having to change the header.
o So other objects depend on the header won’t have to be recompiled at all.

Designate a package construct as either public or private

o The package constructs that are declared in the package specification and defined in the package body are public, they are visible and accessible in the package as well as outside of the package.
o The package constructs that are solely defined in the package body are private; they are visible and accessible only within the package.

Identify a package specification and body

o A package has two separate parts – the specification and the body.
o Each of them is stored separately in the data dictionary.
o This enables you to change to definition of a program construct in the package body without causing the Oracle server to invalidate other schema objects that call or reference the program construct.
o Package cannot be local unlike procedures and functions.
o You can drop the body of a package while retaining the package specification.
o You cannot drop the package specification while retaining the package body.
o If you remove the package specification, then the package body is removed.
o This is because a package specification can exist without the package body but the package body cannot exist without the package specification.
o The package specification is required but the package body is optional.

Views in the data dictionary to manage stored objects

o User_objects view contains information about all objects, including stored subprograms, when object is created and last modified, and the Status for the object valid/ invalid.
o User_source contains the original source code for the object.
o User_errors, show errors: contains information about compile errors.
o All_procedures lists all function and procedures along with their associated properties.

Contrast invoker’s rights with definers rights

o The procedure owner is the definer and the user who runs the procedure is the invoker’s
o However if the AUTHID CURRENT_USER clause is used while creating the subprogram (function, procedure, package), the subprogram is executed under the privileges of the executing user and not the owner (invoker’s rights).
o However, references in PL/SQL statements (procedure) are still resolved under the owner’s privilege set so it does not need to be granted to the invoker’s user. P. 438 9i.
o In the case of the invoker’s running the procedure under his privileges he has to grant the DML statement either explicitly or via role.
o A database trigger will always be executed with definer’s rights, and will execute under the privilege set of the schema that owns the triggering table. This also true for a PL/SQL function that is called from a view.

Grant Privileges to subprograms

o For stored subprograms and packages the relevant privilege is EXECUTE.
o Grant EXECUTE ON …….to userB;
o Then userB can execute the procedure like userA.procedure.
o A subprogram executes under the privilege set of its owner.
o Like if we have the same table name in the schema of the user who execute the subprogram and the user who owns the subprogram, the table for the user who owns the subprogram will be used.
o A subprogram executes under the privileges that have been granted explicitly to its owner, not via a role.
o The only objects available inside a stored procedure, function, package, or trigger are the ones owned by the owner of the subprogram, or explicitly granted to the owner not via a role the reason of that the set roles applies for the database session only, while GRANT and REVOKE apply to all sessions.
o Because pl/sql is early binding all roles are disabled inside stored procedures and triggers.
o The privileges are checked at compile time, not runtime.
o All roles are disabled inside stored procedures, functions, packages, and triggers.
o GRANT and REVOKE are both DDL statements, they take effect immediately, and the new privileges are recorded in the data dictionary.
o All database sessions will see the new privilege set.