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.

The differenced between procedures and functions

• Both take arguments which can be of any mode.
• Both are different forms of PL/SQL blocks, with a declarative, executable, and exception section.
• Both can be stored in the database or declared within a block.
• Both can be called using positional or named notation.
• Procedure call is a PL/SQL statement by itself, while a function call is called as part of any expression.
• A function call is an rvalue.
• If there is more than one returns value, use a procedure, if there is only one return value, a function can be used.

Restrictions on calling functions from SQL statements

• Purity level are WNDS (write no database state) , RNDS (read no database state),WNPS (writes no package state),RNPS (reads no package state).
• The PL/SQL engine can determine the purity level of stand-alone functions.
• When the function is called from a SQL statement the purity level is checked. Not like the package functions.
• Any function called from a SQL statement cannot modify any database tables (WNDS).
• In order to be executed remotely function must have WNPS, RNPS.
• Functions called from the SELECT, VALUES, SET can write packages variables.
• A function is only as pure as the subprograms it calls.
• Stored function cannot be called in CHECK constraint clause of CREATE TABLE or ALTER TABLE.
• You have to use positional notation and not named notation and all parameters must be specified.
• User defined function must also meet the following requirements.
• The function has to be stored in the database either stand-alone or as part of a package.
• The function can take only IN parameter not IN OUT or OUT
• The formal Parameter must use only database types.
• The return type must be a database type.
• The function must not end the current transaction with COMMIT or ROLLBACK.
• It must not issue any ALTER SESSION or ALTER SYSTEM command

List how a function can be invoked

• From SQL statement if it has the restrictions.
• When you call a function from SQL*Plus you need to assign the returned value a bind variable, and you need to EXECUTE command to execute the function like create a SQL*plus environment variable X and issue the command EXECUTE :X := function();

Create a function

• There can be more than one RETURN statement in a function, only one of them will be executed.
• The return value cannot have length or precision or any constraint.
• It is an error for a function to end without executing a RETURN, the function return type is required, because the function call is part of an expression.

Describe the DEFAULT option for parameters

• The formal parameters to ap procedure or function can have default values.
• If parameter has a default value, it does not have to be passed from the calling environment.
• If it is passed, the valued of the actual parameter will be used instead of the default.
• If positional notation is used, all parameters with default values that don’t have an associated actual parameter must be at the end of the parameter list.

List the methods for calling a procedure with parameters

• Positional notation: The actual parameters are associated with the formal parameters by position.
• Named notation: The formal parameter and the actual parameter are both included for each argument.
• In the positional the parameters with default values must be at the end of the argument list.
• Positional and named notation can be mixed in the same call as well, The first arguments must be specified by position, and the remaining arguments can be specified by name.
• The subprogram can be with no parameters.

Create a procedure with parameters

In a procedure declaration, it is illegal to constrain CHAR and VARCHAR2 parameters with a length and NUMBER parameters with a precision and /or scale because the constraints will be taken from the actual parameters.

List the types of parameter modes

• Formal parameters can have three modes – IN, OUT or IN OUT. The default is IN.
• We can’t change the value of IN parameter, it is considered read-only and cannot be changed, if we do so we got a compilation error.
• Any value the actual parameter has when the procedure is called is ignored. So it has the value of NULL.
• If the procedure raises an exception, the values of IN OUT, OUT format parameters are not copied to their corresponding actual parameters.
• Actual IN, IN OUT parameters must be a variable and cannot be a constant or expression.
• You can read from OUT parameter not prior to 7.3.4 or 8.0.3

Describe the difference between formal and actual parameters

• Parameters in the procedure declaration known as formal parameters. Actual parameters contain the values passed to the procedure when it is called.

• Actual parameters contain the values passed to the procedure when it is called.
• The formal parameters are the placeholders for the values of the actual parameters.
• The constraints CHAR and VARCHAR2 for parameters should always come from the actual parameters.
• The only way to get constraint on a formal parameter is to use %TYPE.

Create a procedure

• We create a procedure with the CREATE OR REPLACE PROCEDURE.
• When a procedure is created, it is first compiled and then stored in the database in compiled form.
• A procedure call is a PL/SQL statement by itself, it is not called as part of an expression.
• A procedure is PL/SQL block, with a declarative section, an executable section, and an exception-handling section. Only the executable section is required.
• The create statement is DDL operation, so an implicit commit is done both before and after the procedure is created.
• Either IS or AS keyword can be used.
• There is no declaring keyword in a procedure or function declaration.

Define a subprogram in the declarative section of a procedure

o We called local subprogram.
o It is only visible in the block in which it is declared.
o Its scope extends from the point of declaration until the end of the end of the block.
o Any local subprogram must be declared at the end of the declarative section.
o We can have overloading local subprograms.

Define what a stored procedure is

• Procedures is also known as subprograms.
• A stored procedure must have at least one executable statement in the procedure body.

Overview of PL/SQL Programs

• Describe a PL/SQL program construct:
• There are two main kinds of PL/SQL blocks anonymous and named.
• An anonymous block is compiled each time it is issued. And is not stored in the database, and cannot be called directly from other PL/SQL blocks.
• Named blocks are procedures, functions, packages, and triggers.
• They can be stored in the database and run when appropriate.
• PL/SQL uses early binding to execute SQL statement. So only DML statements can be included directly in PL/SQL block but we can with the DBMS_SQL and execute immediate.
• Describe how a stored procedure /function is invoked:
• Once procedure is created , we can call it from another PL/SQL block procedure_name(parameters);
• EXECUTE will invoke a procedure form iSQL*Plus.