Archive for the ‘OCA DBA 9i PL/SQL’ 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,

The UTL_HTTP Oracle supplied package allows a user to make HTTP requests directly from the database. The UTL_HTTP package makes HTTP callouts from SQL and PL/SQL. The UTL_HTTP package can be used to access data on the Internet or to call Oracle Web Server Cartridges. This package contains two entry point functions: REQUEST and REQUEST_PIECES. Both functions take a string URL (uniform resource locator) as a parameter, contact the site, and return the HTML data obtained from the site. The REQUEST function returns up to the first 2000 bytes of data retrieved from the given URL. The REQUEST_PIECES function returns a PL/SQL table of 2000-byte pieces of the data retrieved from the given URL.

Reading and writing data into tables by using database triggers are subject to a restriction. The restriction is that the session that issues a triggering statement cannot query (SELECT) or modify (INSERT, UPDATE, DELETE, etc.) a mutating table. A mutating table is a table that is currently being modified by an INSERT, UPDATE, or DELETE statement, or a table that might be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. This restriction prevents a trigger from seeing an inconsistent set of data.

A system event trigger is a database trigger defined on a system event. A system event trigger fires when a system event (such as startup, shutdown, logon, logoff, or error) occurs. Database triggers on system events can be defined at the database or schema level. For example, a trigger on database startup or shutdown can be defined only at the database level. However, a trigger on user logon or logoff can be defined at either the database level or schema level.