IMPORTANT POINTS ABOUT
SUBPROGRAMS
Ø When a stored subprogram
is created, it is stored in the data
dictionary.
Ø The subprogram is stored
in compile form which is known as p-code
in addition to the source text.
Ø The p-code has all of the
references in the subprogram evaluated, and the source code is translated into
a form that is easily readable by PL/SQL engine.
Ø When the subprogram is
called, the p-code is read from the disk, if necessary, and executed.
Ø Once it reads from the
disk, the p-code is stored in the shared pool portion of the system global area
(SGA), where it can be accessed
by multiple users as needed.
Ø Like all of the contents
of the shared pool, p-code is aged out of the shared pool according to a least
recently used (LRU) algorithm.
Ø Subprograms can be local.
Ø Local subprograms must be
declared in the declarative section of PL/SQL block and called from the executable section.
Ø Subprograms can not have
the declarative section separately.
Ø Stored subprograms can
have local subprograms;
Ø Local subprograms also can
have local subprograms.
Ø If the subprogram contains
a variable with the same name as the column name of the table then use the dot
method to differentiate (subprogram_name.sal).
Ø Subprograms can be
invalidated.
PROCEDURES V FUNCTIONS
Ø Procedures may return
through out and in out parameters where as function must return.
Ø Procedures can not have
return clause where as functions must.
Ø We can use call statement
directly for executing procedure where as we need to declare a variable in case
of functions.
Ø Functions can use in
select statements where as procedures can not.
Ø Functions can call from
reports environment where as procedures can not.
Ø We can use exec for
executing procedures where as functions can not.
Ø Function can be used in
dbms_output where as procedure can not.
Ø Procedure call is a
standalone executable statement where as function call is a part of an
executable statement.
STORED V LOCAL SUBPROGRAMS
Ø The stored subprogram is
stored in compiled p-code in the database, when the procedure is called it does
not have to be compiled.
The
local subprogram is compiled as part of its containing block. If the containing
block is anonymous and is run multiple
times, the subprogram has to be compiled
each time.
Ø Stored subprograms can be
called from any block submitted by a user who has execute privileges on the
subprogram.
Local subprograms can be called only from
the block containing the subprogram.
Ø By keeping the stored subprogram
code separate from the calling block, the calling block is shorter and easier
to understand.
The local subprogram and the calling block
are one and the same, which can lead to
part confusion. If a change to the calling
block is made, the subprogram will be
recompiled as of the recompilation of the
containing block.
Ø The compiled p-code can be
pinned in the shared pool using the DBMS_SHARED_POOL Package. This can improve
performance.
Local subprograms cannot be pinned in the
shared pool by themselves.
Ø Stand alone stored
subprograms can not be overloaded, but packaged subprograms can be overloaded
within the same package.
Ø Local subprograms can be
overloaded within the same block.
Ex1:
CREATE OR
REPLACE PROCEDURE P IS
BEGIN
dbms_output.put_line('Stored subprogram');
END;
Output:
SQL> exec p
Stored
subprogram
Ex2:
DECLARE
PROCEDURE P IS
BEGIN
dbms_output.put_line('Local
subprogram');
END;
BEGIN
p;
END;
Output:
Local subprogram
COMPILING SUBPROGRAMS
Ø SQL> Alter procedure P1
compile;
Ø SQL> Alter function F1
compile;
SUBPROGRAMS DEPENDECIES
Ø A stored subprogram is
marked as invalid in the data dictionary if it has compile errors.
Ø A stored subprogram can
also become invalid if a DDL operation is performed on one of its dependent
objects.
Ø If a subprogram is
invalidated, the PL/SQL engine will automatically attempt to recompile in the next time
it is called.
Ø If we have two procedures
like P1 and P2 in which P1 depends on P2. If we compile P2 then P1 is invalidated.
SUBPROGRAMS DEPENDENCIES
IN REMOTE DATABASES
Ø We will call remote
subprogram using connect string like P1@ORACLE;
Ø If we have two procedures
like P1 and P2 in which P1 depends on P2 but P2 was in remote database. If we
compile P2 it will not invalidate P1 immediately because the data dictionary
does not track remote dependencies.
Ø Instead the validity of
remote objects is checked at runtime. When P1 is called, the remote data
dictionary is queried to determine the status of P2.
Ø P1 and P2 are compared to
see it P1 needs to be recompiled, there are two different methods of
comparision
ü Timestamp Model
ü Signature Model
TIMESTAMP MODEL
Ø This is the default model
used by oracle.
Ø With this model, the
timestamps of the last modifications of the two objects are
compared.
Ø The last_ddl_time field of user_objects
contains the timestamp.
Ø If the base object has a
newer timestamp than the dependent object, the
dependent object will be recompiled.
ISSUES WITH THIS MODEL
Ø If the objects are in
different time zones, the comparison is invalid.
Ø When P1 is in a client
side PL/SQL engine such as oracle
forms, in this case it may not possible to recompile P1, because the source for
it may not be included with the forms.
SIGNATURE MODEL
Ø When a procedure is
created, a signature is stored in the data dictionary in addition to the
p-code.
Ø The signature encodes the
types and order of the parametes.
Ø When P1 is compiled the
first time, the signature of P2 is included. Thus, P1 only needs to recompiled
when the signature of P2 changes.
Ø In order to use the
signature model, the parameter REMOTE_DEPENDENCIES_MODE must be set to SIGNATURE. This is a parameter in the database initialization file.
THREE WAYS OF SETTING THIS
MODE
Ø Add the line REMOTE_DEPENDENCIES_MODE=SIGNATURE to the database
initialization file. The next time the database is started, the mode will be
set to SIGNATURE for all sessions.
Ø Alter system set
remote_dependencies_mode = signature;
This will affect the entire database (all
sessions) from the time the statement is
issued. You must have the ALTER SYSTEM privilege to issue this
command.
Ø Alter session set
remote_dependencies_mode = signature;
This will only affect your session
ISSUES WITH THIS MODEL
Ø Signatures don’t get
modified if the default values of formal parameters are
changed.
Ø Suppose P2 has a default
value for one of its parameters, and P1 is using this
default value. If the default in the
specification for P2 is changed, P1 will not be
recompiled by default. The old value
for the default parameter will still be used until
P1 is manually recompiled.
Ø If P1 is calling a
packaged procedure P2, and a new overloaded version of P2 is added to the
remote package, the signature is not changed. P1 will still use the old
version(not the new overloaded one) until P1 is recompiled manually.
No comments:
Post a Comment