Archive for the ‘Oracle PL/SQL Developer’ category

A package specification that does not need a package body is called a bodiless package. Only subprogram and cursor declarations in a package specification require an underlying implementation in the package body. If a package specification declares only those package constructs that do not require implementation details (e.g., types, constants, variables, exceptions, and call specifications), the corresponding package body does not need to be created. However, the body can be created to initialize constructs declared in the package specification.

The UTL_TCP package enables PL/SQL applications to communicate with external TCP/IP-based servers by using TCP/IP. Because many Internet application protocols are based on TCP/IP, the UTL_TCP package is useful to PL/SQL applications that use Internet protocols.

In case of local dependencies, the objects are on the same node in the database. The Oracle server automatically manages all local dependencies by using the database’s internal “depends-on” table. When a referenced object is modified, the dependent objects are invalidated. The next time an invalidated object is called, the Oracle server automatically recompiles it. If the automatic recompilation of the object fails, it remains invalid and Oracle server returns a run-time error.

If the definition of a referenced table is altered, the dependent procedures and functions become invalid. The dependent procedures and functions need implicit or explicit recompilation in order to become valid.

The recompilation of a dependent procedure or function is successful in the following cases:

When a PL/SQL variable is declared to hold column values, it must be ensured that the variable is of the correct datatype and precision. If not, a PL/SQL error occurs during execution. Rather than explicitly specifying the datatype and precision of a variable, the %TYPE attribute can be used to declare a variable according to another previously declared variable or database column.

A subprogram is a named PL/SQL block that can accept parameters and be invoked. There are two types of subprograms: procedures and functions. A procedure is generally used to perform an action, and a function is used to compute a value. Subprograms can be stored at the server or application level. By using Oracle Developer components (Forms, Reports, and Graphics), procedures and functions can be declared as part of an application (a form or report) and be called from other procedures, functions, and triggers within the same application whenever necessary.

Following is the syntax for creating a procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter [mode] datatype, parameter [mode] datatype, ……)]
IS | AS
PL/SQL block;

where,