pixia-club.info Religion Introduction To Oracle9i Pl-sql Part 2.pdf

INTRODUCTION TO ORACLE9I PL-SQL PART 2.PDF

Saturday, October 5, 2019


Jobs 14 - 19 Oracle 9i PL-SQL Vol 2 - Free ebook download as PDF File .pdf) or read book online for free. Oracle 9i Introduction to Oracle9i: PL/SQL Introduction to Oracle9i: SQL Student Guide • Volume 2 GC10 Production June D Student Guide. Volume 2 GC10 Production June D


Introduction To Oracle9i Pl-sql Part 2.pdf

Author:KENDAL CHESSHER
Language:English, Spanish, Arabic
Country:Mauritius
Genre:Religion
Pages:246
Published (Last):28.10.2015
ISBN:712-6-75165-467-4
ePub File Size:24.62 MB
PDF File Size:9.25 MB
Distribution:Free* [*Regsitration Required]
Downloads:44279
Uploaded by: CHARLYN

Results 5 - 21 Practice 3, Part 2 Overview .. 20 Oracle 9i Extensions to DML and DDL Statements Introduction to Oracle9i: SQL Additional Practices PART III. PL/SQL. 27 An Introduction to PL/SQL. stay with a company and the better they become. Chapter 2: The Dangers in a Relational Database 17 . Oracle9i. SQL Reference. Release 2 (). March Part No. A Oracle is a registered trademark, and Oracle7, Oracle8, Oracle8i, Oracle9i, Oracle Store, introduced the first commercially available implementation of SQL.

Adhere to normal naming conventions for invoking a procedure in a different schema, or in a different database on another node. Declaring a Bodiless Package You can declare public global variables that exist for the duration of the user session. You can create a package specification that does not need a package body. As discussed earlier in this lesson, if a specification declares only types, constants, variables, exceptions, and call specifications, the package body is unnecessary.

Example In the preceding slide, a package specification containing several conversion rates is defined. All the global identifiers are declared as constants. A package body is not required to support this package specification because implementation details are not required for any of the constructs of the package specification.

When you reference a variable, cursor, constant, or exception from outside the package, you must qualify its name with the name of the package. To remove the package specification and the body, use the following syntax: To remove the package body, use the following syntax: A package has two parts, so you can drop the whole package or just the package body and retain the package specification. Guidelines for Writing Packages Keep your packages as general as possible so that they can be reused in future applications.

Also, avoid writing packages that duplicate features provided by the Oracle server. Package specifications reflect the design of your application, so define them before defining the package bodies. The package specification should contain only those constructs that must be visible to users of the package. That way other developers cannot misuse the package by basing code on irrelevant details.

Place items in the declaration part of the package body when you must maintain them throughout a session or across transactions. When declared as a global variable in the package specification, the value of that global variable gets initialized in a session the first time a construct from the package is invoked. Changes to the package body do not require recompilation of dependent constructs, whereas changes to the package specification require recompilation of every stored subprogram that references the package.

To reduce the need for recompiling when code is changed, place as few constructs as possible in a package specification. Advantages of Using Packages Packages provide an alternative to creating procedures and functions as stand-alone schema objects, and they offer several benefits. Modularity You encapsulate logically related programming structures in a named module.

Each package is easy to understand, and the interface between packages is simple, clear, and well defined. Easier Application Design All you need initially is the interface information in the package specification. You can code and compile a specification without its body. Then stored subprograms that reference the package can compile as well.

You need not define the package body fully until you are ready to complete the application. Hiding Information You can decide which constructs are public visible and accessible or private hidden and inaccessible.

Only the declarations in the package specification are visible and accessible to applications. The package body hides the definition of the private constructs so that only the package is affected not your application or any calling programs if the definition changes. This enables you to change the implementation without having to recompile calling programs.

Also, by hiding implementation details from users, you protect the integrity of the package. Multiple subprograms of the same name. Advantages of Using Packages continued Added Functionality Packaged public variables and cursors persist for the duration of a session.

Thus, they can be shared by all subprograms that execute in the environment. They also enable you to maintain data across transactions without having to store it in the database. Private constructs also persist for the duration of the session, but can only be accessed within the package.

Better Performance When you call a packaged subprogram the first time, the entire package is loaded into memory.

Packaged subprograms also stop cascading dependencies and so avoid unnecessary compilation. Overloading With packages you can overload procedures and functions, which means you can create multiple subprograms with the same name in the same package, each taking parameters of different number or datatype. In this lesson, you should have learned how to: Summary You group related procedures and function together into a package.

Packages improve organization, management, security, and performance. A package consists of package specification and a package body. You can change a package body without affecting its package specification.

Summary continued Packages enable you to hide source code from users. When you invoke a package for the first time, the entire package is loaded into memory. This reduces the disk access for subsequent calls. Summary continued You can create, delete, and modify packages. You can drop the package body without affecting its specification. This practice covers the following topics: Practice 12 Overview In this practice, you will create package specifications and package bodies.

You will invoke the constructs in the packages, using sample data. You can save the package body and specification in two separate files. Use the code in your previously saved script files when creating the package. Make all the constructs public. Consider whether you still need the stand-alone procedures and functions you just packaged.

Query the JOBS table to see the result. Create and invoke a package that contains private and public constructs. You can save the specification and body into separate files. If you have time: Make both constructs public. The procedure checks that the manager and employee work in the same department. If the department number and manager combination is invalid, you should raise an application error with an appropriate message. It also looks at the effect of packaging functions that are used in SQL statements.

Only local or packaged subprograms can be overloaded. You cannot overload stand-alone subprograms. Overloading This feature enables you to define different subprograms with the same name. You can distinguish the subprograms both by name and by parameters. Sometimes the processing in two subprograms is the same, but the parameters passed to them varies. In that case it is logical to give them the same name. Stand-alone subprograms cannot be overloaded. Restrictions You cannot overload: The above restrictions apply if the names of the parameters are also the same.

If you use different names for the parameters, then you can invoke the subprograms by using named notation for the parameters. Resolving Calls The compiler tries to find a declaration that matches the call. It searches first in the current scope and then, if necessary, in successive enclosing scopes. The compiler stops searching if it finds one or more subprogram declarations in which the name matches the name of the called subprogram. For like-named subprograms at the same level of scope, the compiler needs an exact match in number, order, and datatype between the actual and formal parameters.

Example The preceding slide shows the package specification of a package with overloaded procedures. The first definition takes three parameters to be able to insert a new department to the department table.

The second definition takes only two parameters, because the department ID is populated through a sequence. Overloading Example continued Most built-in functions are overloaded. The format into which the date or number has to be converted can also be specified in the function call.

To be able to access the built-in subprogram, you need to qualify it with its package name. If you redeclare a built-in subprogram as a stand-alone subprogram, to be able to access your subprogram you need to qualify it with your schema name, for example, SCOTT. You must declare identifiers before referencing them.

You must declare an identifier before using it. Therefore, a subprogram must be declared before calling it. You can solve the illegal reference problem by reversing the order of the two procedures. However, this easy solution does not always work.

Suppose the procedures call each other or you absolutely want to define them in alphabetical order. It consists of the subprogram specification terminated by a semicolon. You can use forward declarations to do the following: Forward Declarations and Packages Forward declarations typically let you group related subprograms in a package.

The subprogram specifications go in the package specification, and the subprogram bodies go in the package body, where they are invisible to the applications. In this way, packages enable you to hide implementation details. Define an Automatic, One-Time-Only Procedure A one-time-only procedure is executed only once, when the package is first invoked within the user session.

Initialize public or private variables with an automatic, one-time-only procedure when the derivation is too complex to embed within the variable declaration. In this case, do not initialize the variable in the declaration, because the value is reset by the one-time-only procedure.

The keyword END is not used at the end of a one-time-only procedure. Observe that in the example in the slide, there is no END at the end of the one-time-only procedure. A function called from: Calls to subprograms that break the above restrictions are not allowed. Controlling Side Effects For the Oracle server to execute a SQL statement that calls a stored function, it must know the purity level of a stored functions, that is, whether the functions are free of side effects.

Side effects are changes to database tables or public packaged variables those declared in a package specification. Side effects could delay the execution of a query, yield order-dependent therefore indeterminate results, or require that the package state variables be maintained across user sessions. Therefore, the following restrictions apply to stored functions called from SQL expressions: But from Oracle8i, a user-written function can be called from a SQL statement without any compile-time checking of its purity.

The restrictions on functions discussed above are the same as those discussed in the lesson "Creating Functions. The function is called from SQL statements on remote databases. If this is not specified, you get an error message saying that the function TAX does not guarantee that it will not update the database while invoking the package function in a query.

Persistent State of Package Variables This sample package illustrates the persistent state of package variables. Once the variable is assigned a value, the value persists in the session until it is modified again. The example in the following slide shows how the value of a global package variable persists for a session.

Controlling the Persistent State of a Package Variable You can keep track of the state of a package variable or cursor, which persists throughout the user session, from the time the user first references the variable or cursor to the time the user disconnects. Initialize the variable within its declaration or within an automatic, one-time-only procedure.

Change the value of the variable by means of package procedures. The value of the variable is released when the user disconnects. The sequence of steps in the preceding slide shows how the state of a package variable persists. The value 0. Because 0. Scott invoked the procedure with commission percentage value of 0.

This value is more than the maximum commission percentage 0. Hence, it was invalid. Jones rolled back the transaction and exited the session. The global value was initialized to 10 when he logged in at The procedure was successful because the new value 0. Declare the public global cursor in the package specification. In the package body: The state does not persist from session to session for the same user, nor does it persist from user to user. Summary Overloading is a feature that enables you to define different subprograms with the same name.

It is logical to give two subprograms the same name in situations when the processing in both the subprograms is the same, but the parameters passed to them varies. Forward declaration enables you to define subprograms in logical or alphabetical order, define mutually recursive subprograms, and group subprograms in a package. A one-time-only procedure is executed only when the package is first invoked within the other user session.

You can use this feature to initialize variables only once per session. You can keep track of the state of a package variable or cursor, which persists throughout the user session, from the time the user first references the variable or cursor to the time that the user disconnects. Practice 13 Overview In this practice you create a package containing an overloaded function.

The function accepts a date or character string and prints a date or a number, depending on how the function is invoked. Make sure you handle invalid input. Lesson Aim In this lesson, you learn how to use some of the Oracle server supplied packages and to take advantage of their capabilities.

Oracle-supplied packages: You may take advantage of the functionality provided by these packages when creating your application, or you may simply want to use these packages as ideas when you create your own stored procedures. Most of the standard packages are created by running catproc. Dynamic SQL: Dynamic SQL statements are not embedded in your source program but rather are stored in character strings that are input to, or built by, the program.

That is, the SQL statements can be created dynamically at run time by using variables. SQL statements go through various stages: Some stages may be skipped. Parse Every SQL statement must be parsed. Bind After parsing, the Oracle server knows the meaning of the Oracle statement but still may not have enough information to execute the statement. The Oracle server may need values for any bind variable in the statement. The process of obtaining these values is called binding variables.

Execute At this point, the Oracle server has all necessary information and resources, and the statement is executed. Fetch In the fetch stage, rows are selected and ordered if requested by the query , and each successive fetch retrieves another row of the result, until the last row has been fetched.

You can fetch queries, but not the DML statements. Some of the procedures and functions of the package include: The operations provided by this package are performed under the current user, not under the package owner SYS. Using this package to execute DDL statements can result in a deadlock.

The most likely reason for this is that the package is being used to drop a procedure that you are still using. The procedure uses dynamic SQL to delete rows from the specified table. The number of rows deleted as a result of the successful execution of the dynamic SQL are passed to the calling environment through an OUT parameter.

This function returns the number of row processed. The default parameter mode is IN. The syntax shown in the slide is not complete.

It is used only for single-row queries. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause. At run time, bind arguments replace corresponding placeholders in the dynamic string. For example, define variables and bind arguments cannot be Booleans or index- by tables. You can execute a dynamic SQL statement repeatedly, using new values for the bind arguments. This package runs with the privileges of calling user, rather than the package owner SYS.

The object type must be either procedure, function, package, package body, or trigger. It can be used to schedule batch jobs during nonpeak hours or to run maintenance programs during times of low usage. Available subprograms include: Available parameters include: The descriptions of the parameters are listed below. The job number is returned through the JOB parameter. The WHAT parameter must be enclosed in single quotation marks and must include a semicolon at the end of the text string. This job is submitted to run every day at midnight.

In the example, the parameters are passed using named notation. RUN is executed to run the job. You can change the next execution date. You can change the WHAT parameter. Each of these procedures takes the JOB parameter as an IN parameter indicating which job is to be changed. Example The following code changes job number to execute on the following day at 6: Each of these procedures can be executed on jobs owned by the username to which the session is connected.

Runs a submitted job immediately. Removes a submitted job from the job queue. Marks a submitted job as broken, and a broken job will not run. RUN procedure executes a job immediately. Pass the job number that you want to run immediately to the procedure. Pass the job number that you want to remove from the queue to the procedure. Jobs are not broken by default.

You can change a job to the broken status. A broken job will not run. There are three parameters for this procedure. The JOB parameter identifies the job to be marked as broken or not broken.

Available procedures include: You can use them to access operating system files. This package is available with database version 7. This package is available from the database version 8. Client-side security implementation uses normal operating system file permission checking.

Server-side security is implemented through restrictions on the directories that can be accessed. In the init. This parameter setting is case-sensitive on case-sensitive operating systems.

There are seven exceptions declared in the package to account for possible errors raised during execution. You then either read the file or write to the file until processing is done. The maximum size of an input record is 1, bytes unless you specify a larger size in the overloaded version of FOPEN.

These exceptions must be prefaced with the package name. The return value is the file handle that is passed to all subsequent routines that operate on the file.

It returns a Boolean value indicating whether the file has been opened but not yet closed. Users of the package should not reference or change components of this record. The names of the text file and the location for the text file are provided as parameters to the program.

Whalen earns: Hartstein earns: Fay earns: Raphaely earns: Khoo earns: Greenberg earns: Higgins earns: Gietz earns: Grant earns: This package contains two entry point functions: Both functions take a string universal resource locator URL as a parameter, contact the site, and return the HTML data obtained from the site.

If you are behind a firewall, then you need to specify proxy as a parameter, in addition to the URL. This package is covered in more detail in the course Administering Oracle9i Application Server. The second parameter to the function indicates the proxy because the client being tested is behind a firewall. The retrieved output is in HTML format. The function retrieves up to pieces of data, each of a maximum bytes from the URL.

The number of pieces and the total length of the data retrieved are printed. The package contains functions such as: The remote host is the host providing the service. The remote port is the port number on which the service is listening for connections.

The local host and port numbers represent those of the host providing the service. It takes the connection details of a previously opened connection as parameter.

This function receives binary, text, or text line data from a service on an open connection. This function transmits binary, text, or text line message to a service on an open connection. Exceptions are raised when buffer size for the input is too small, when generic network error occurs, when no more data is available to read from the connection, or when bad arguments are passed in a function call. This package is discussed in detail in the course Administering Oracle9i Application Server.

Other Oracle-supplied packages include: The following list summarizes and provides a brief description of the packages supplied with Oracle9i. Repeat the test outlined in steps 1b and 1c.. Your program should accept two parameters. The first parameter identifies the output directory.

The second parameter identifies the text file name to which your procedure writes. Your instructor will inform you of the directory location. Sample output from this file follows: Lesson Aim Databases have long been used to store large objects.

However, the mechanisms built into databases have never been as useful as the new large object LOB data types provided in Oracle8. This lesson describes the characteristics of the new data types, comparing and contrasting them with earlier data types. Examples, syntax, and issues regarding the LOB types are also presented. A LOB is a data type and should not be confused with an object type.

LOBs are used to store large unstructured data such as text, graphic images, films, and sound waveforms. Overview A LOB is a data type that is used to store large, unstructured data such as text, graphic images, video clippings, and so on.

Structured data such as a customer record may be a few hundred bytes, but even small amounts of multimedia data can be thousands of times larger. Also, multimedia data may reside on operating system OS files, which may need to be accessed from a database. There are four large object data types: LOBs can be stored internally inside the database or in host files.

There are two categories of LOBs: The other implicit conversions between LOBs are not possible. Data stored in-line Data stored in-line or out-of-line. These data types are superseded by the LOB data types.

It is beneficial to discuss LOB functionality in comparison to the older types. A table can have only one LONG column. In addition, LOBs allow data to be stored in a separate segment and tablespace, or in a host file.

The data that constitutes the real object being stored. A pointer to the location of the LOB value stored in the database. Regardless of where the value of the LOB is stored, a locator is stored in the row. Programmatic interfaces operate on the LOB values, using these locators in a manner similar to operating system file handles. The CLOB data type is interpreted as a single-byte character stream. The NCLOB data type is interpreted as a multiple-byte character stream, based on the byte length of the database national character set.

Create and populate the table containing the LOB data type. Declare and initialize the LOB locator in the program. The Oracle9i server provides for: BFILEs are read-only, so they do not participate in transactions. Any support for integrity and durability must be provided by the operating system. The user must create the file and place it in the appropriate directory, giving the Oracle process privileges to read the file.

When the LOB is deleted, the Oracle server does not delete the file. The administration of the actual files and the OS directory structures to house the files is the responsibility of the database administrator DBA , system administrator, or user.

The maximum size of an external large object is operating system dependent but cannot exceed four gigabytes. User Access permissions. The Oracle9i Server can act as a security mechanism to shield the operating system from unsecured access while removing the need to manage additional user accounts on an enterprise computer system. File Location and Access Privileges The file must reside on the machine where the database exists. However, there could be restrictions related to the file itself, such as: Any support for integrity and durability must be provided by the underlying file system and the OS.

A New Database Object: By granting suitable privileges for these items to users, you can provide secure access to files in the corresponding directories on a user-by-user basis certain directories can be made read-only, inaccessible, and so on. This provides application abstraction from hard-coded path names, and gives flexibility in portably managing file locations.

Directory objects have object privileges, unlike any other nonschema object. Logical path names are not supported. The permissions for the actual directory are operating system dependent. Oracle does not create the OS path. Create the OS directory as an Oracle user and set permissions so that the Oracle server can read the contents of the OS directory.

Load files into the the OS directory. Grant the READ privilege to it.

Declare and initialize the LOB locator in a program. For the Oracle server to access an external file, the server needs to know the location of the file on the operating system.

Syntax Definition: This parameter is set in the init. Its default value is Syntax Definitions Where: The images are. Executing the procedure yields the following results: The function returns 0 if the file does not exist, and returns 1 if the file does exist. This is the specification for the function: Data migration: In Oracle9i, this operation can be performed using the syntax shown in the slide. You can use the syntax shown to: For example, if you had a table with the following definition: Application Migration: Implicit assignment and parameter passing: Explicit conversion functions: Function and procedure parameter passing: These details are discussed in detail later in this lesson.

The catproc. Then users can be granted appropriate privileges to use the package. The user is responsible for locking the row containing the destination internal LOB before calling any subprograms that involve writing to the LOB value. Two constants are used in the specification of procedures in this package: Only positive, absolute offsets are allowed.

They represent the number of bytes or characters from the beginning of LOB data from which to start the operation. Negative offsets and ranges observed in SQL string functions and operators are not allowed. Corresponding exceptions are raised upon violation. The default value for an offset is 1, which indicates the first byte or character in the LOB value. Negative values are not allowed.

Murach’s Oracle SQL and PL/SQL for Developers (2nd Edition)

An exception is raised when no more data remains to be read from the source LOB. Make sure the allocated system resources are adequate to support these buffer sizes for the given number of user sessions. Otherwise, the Oracle server raises the appropriate memory exceptions. Make sure especially with multibyte characters that the amount in bytes corresponds to the amount of buffer data.

The contents of a LOB column is stored in the LOB segment, while the column in the table contains only a reference to that specific storage area, called the LOB locator. Insert a row into a table with LOB columns: To populate the LOB column, you can use an update statement. This initializes the LOB columns with locators. You must lock the row prior to the update only when updating a piece of the LOB. WRITE lobloc, amount, offset, text ; text: In those releases, it was not possible to fetch a CLOB column directly into a character column.

An example later in this lesson shows that you can directly fetch a CLOB column by binding it to a character variable. An example is shown later in this lesson. This function returns the numerical position of the information. Delete a row containing LOBs: Disassociate a LOB value from a row: Features of temporary LOBs: Summary There are four LOB data types: The table contains the following attributes and data types: This table contains annual review information for each employee.

The script also contains two statements to insert review details for two employees. The binary file is a picture of the country. The image files are named after the country IDs. This object is already created for you. If the file is not existing, your procedure should display a message that the file can not be opened. Have your program report information about the load to the screen. Note that you should pass the directory object in single quotation marks.

Sample output follows: A trigger: Fires whenever an event occurs with a particular application — Database trigger: Fires whenever a data event such as DML or system event such as logon or shutdown occurs on a schema or database. Types of Triggers Application triggers execute implicitly whenever a particular data manipulation language DML event occurs within an application. An example of an application that uses triggers extensively is one developed with Oracle Forms Developer.

Database triggers also execute implicitly when some user actions or database system actions occur, for example, when a user logs on, or the DBA shut downs the database.

Database triggers can be defined on tables and on views. If these actions include DML operations on tables, then any triggers on the base tables are fired. Database triggers can be system triggers on a database or a schema.

With a database, triggers fire for each event for all users; with a schema, triggers fire for each event for that specific user. This course covers creating database triggers. For example do not define triggers to implement integrity rules that can be done by using declarative constraints. An easy way to remember the design order for a business rule is to: Only use triggers when necessary, and beware of recursive and cascading effects. Values that are out of range according to the job category can be rejected, or can be allowed and recorded in an audit table.

A triggering statement contains: Database Trigger Before coding the trigger body, decide on the values of the components of the trigger: If multiple triggers are defined for a table, be aware that the order in which multiple triggers of the same type fire is arbitrary.

To ensure that triggers of the same type are fired in a particular order, consolidate the triggers into one trigger that calls separate procedures in the desired order. Trigger timing: When should the trigger fire? Execute the trigger body before the triggering DML event on a table. Execute the trigger body after the triggering DML event on a table. Execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.

This situation enables you to eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the triggering action.

Triggering user event: Which DML statement causes the trigger to execute? You can use any of the following: Trigger type: Should the trigger body execute for each row the statement affects or only once? The trigger body executes once for the triggering event.

This is the default. A statement trigger fires once, even if no rows are affected at all. The trigger body executes once for each row affected by the triggering event. A row trigger is not executed if the triggering event affects no rows.

Statement Triggers and Row Triggers You can specify that the trigger will be executed once for every row affected by the triggering statement such as a multiple row UPDATE or once for the triggering statement, no matter how many rows it affects. Statement Trigger A statement trigger is fired once on behalf of the triggering event, even if no rows are affected at all.

Statement triggers are useful if the trigger action does not depend on the data from rows that are affected or on data provided by the triggering event itself: Row Trigger A row trigger fires each time the table is affected by the triggering event. If the triggering event affects no rows, a row trigger is not executed. Row triggers are useful if the trigger action depends on data of rows that are affected or on data provided by the triggering event itself.

Trigger body: What action should the trigger perform? Trigger Body The trigger action defines what needs to be done when the triggering event is issued. Additionally, row triggers use correlation names to access the old and new column values of the row being processed by the trigger.

The size of a trigger cannot be more than 32 K. Creating Row or Statement Triggers Create a statement trigger or a row trigger based on the requirement that the trigger must fire once for each row affected by the triggering statement, or just once for the triggering statement, regardless of the number of rows affected.

When the triggering data manipulation statement affects a single row, both the statement trigger and the row trigger fire exactly once. Example This SQL statement does not differentiate statement triggers from row triggers, because exactly one row is inserted into the table using this syntax. Use the following firing sequence for a trigger on a table, when many rows are manipulated: Creating Row or Statement Triggers continued When the triggering data manipulation statement affects many rows, the statement trigger fires exactly once, and the row trigger fires once for every row affected by the statement.

Example The SQL statement in the slide above causes a row-level trigger to fire a number of times equal to the number of rows that satisfy the WHERE clause, that is, the number of employees reporting to department Trigger names must be unique with respect to other triggers in the same schema. Syntax for Creating a Statement Trigger trigger name Is the name of the trigger timing Indicates the time when the trigger fires in relation to the triggering event: Trigger names do not need to be unique with respect to other schema objects, such as tables, views, and procedures.

Using column names along with the UPDATE clause in the trigger improves performance, because the trigger fires only when that particular column is updated and thus avoids unintended firing when any other column is updated.

When a database trigger fails, the triggering statement is automatically rolled back by the Oracle server. Create a trigger to allow only certain employees to be able to earn a salary of more than 15, If a user attempts to do this, the trigger raises an error.

Row triggers can decrease the performance if you do a lot of updates on larger tables. The trigger records the values of several columns both before and after the data changes by using the OLD and NEW qualifiers with the respective column name. Example To restrict the trigger action to those rows that satisfy a certain condition, provide a WHEN clause. For example, if a view consists of more than one table, an insert to the view may entail an insertion into one table and an update to another.

Instead of the original insertion, the trigger body executes, which results in an insertion of data into one table and an update to another table. Assume that an employee name will be inserted using the view. Database Triggers and Stored Procedures There are differences between database triggers and stored procedures: It is possible to body. If errors occur during the compilation of a trigger, the trigger is still created.

Database Trigger Form Builder Trigger Executed by actions from any database tool Executed only within a particular Form Builder or application application Always triggered by a SQL DML, DDL, or a Can be triggered by navigating from field to field, by certain database action pressing a key, or by many other actions Is distinguished as either a statement or row Is distinguished as a statement or row trigger trigger Upon failure, causes the triggering statement Upon failure, causes the cursor to freeze and may to roll back cause the entire transaction to roll back Fires independently of, and in addition to, Fires independently of, and in addition to, database Form Builder triggers triggers Executes under the security domain of the Executes under the security domain of the Form author of the trigger Builder user.

Disable or reenable a database trigger: Disable or reenable all triggers for a table: Trigger Modes: In addition, the Oracle server provides read-consistent views for queries and constraints, manages the dependencies, and provides a two-phase commit process if a trigger updates remote tables in a distributed database.

About this book

You may also want to disable the trigger when it references a database object that is currently unavailable, owing to a failed network connection, disk crash, offline data file, or offline tablespace. All triggers on a table are dropped when the table is dropped. You can also use the Procedure Builder debugging tool to debug triggers.

Loop for each row affected: Execute the DML statement and perform integrity constraint checking. A triggering event or a statement within the trigger can cause one or more integrity constraints to be checked.

Triggers can also cause other triggers to fire cascading triggers. All actions and checks done as a result of a SQL statement must succeed. If an exception is raised within a trigger and the exception is not explicitly handled, all actions performed because of the original SQL statement are rolled back. This includes actions performed by firing triggers. This guarantees that integrity constraints can never be compromised by triggers.

In all cases, a read-consistent image is guaranteed for modified values the trigger needs to read query or write update. Trigger Execution Model and Constraint Checking: Example The example in the slide explains a situation in which the integrity constraint can be taken care of by using a trigger. Then, the foreign key constraint is checked. This process works with Oracle8i and later releases. The example described in the preceding slide produces a run-time error with in releases prior to Oracle8i.

The sequence of events: Because this is a row trigger, the trigger fires once for each row that you updated. This trigger invokes the package procedures depending on the type of DML performed. Procedure Package Trigger xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv Procedure A xxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx declaration vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx Procedure B definition.

Procedure A definition Local variable. Develop different types of procedural database constructs depending on their usage. Practice 16 Overview You create statement and row triggers in this practice. You create procedures that will be invoked from the triggers.

Changes to data are allowed on tables only during normal office hours of 8: Create a statement trigger on the JOBS table that calls the above procedure. Test the proc edure by temporarily modifying the hours in the procedure and attempting to insert a new record into the JOBS table. After testing, reset the procedure hours as specified in step 1.

Employees should receive an automatic increase in salary if the minimum salary for a job is increased. Implement this requirement through a trigger on the JOBS table. This procedure accepts two parameters: This procedure is executed from the trigger on the JOBS table. Increase the minimum salary for the Programmer job from 4, to 5, Employee Lorentz employee ID had a salary of less than 4, Verify that her salary has been increased to the new minimum of 5, Lesson Aim In this lesson, you learn how to create more database triggers and learn the rules governing triggers.

You also learn many applications of triggers. Creating Database Triggers Before coding the trigger body, decide on the components of the trigger. Triggers on system events can be defined at the database or schema level. For example, a database shutdown trigger is defined at the database level. Triggers on data definition language DDL statements, or a user logging on or off, can also be defined at either the database level or schema level.

Triggers on DML statements are defined on a specific table or a view. A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only when the triggering event involves that schema or table. Triggering events that can cause a trigger to fire: DDL triggers fire only if the object being created is a cluster, function, index, package, procedure, role, sequence, synonym, table, tablespace, trigger, type, view, or user.

The call can reference the trigger attributes: NEW and: OLD as parameters as in the following example: There is no semicolon at the end of the CALL statement. Rules Governing Triggers Reading and writing data using triggers is subject to certain rules. This restriction prevents a row trigger from seeing an inconsistent set of data. Mutating Table: The trigger code queries the same table that is being updated. Example continued Try to read from a mutating table. If you restrict the salary within a range between the minimum existing value and the maximum existing value you get a run-time error.

Remember that functions can also cause a mutating table error when they are invoked in a DML statement. You can use trigger for: Implementing Triggers Develop database triggers in order to enhance features that cannot otherwise be implemented by the Oracle server or as alternatives to those provided by the Oracle server.

Feature Enhancement Security The Oracle server allows table access to users or roles. Triggers allow table access according to data values. Auditing The Oracle server tracks data operations on tables. Triggers track values for data operations on tables. Data integrity The Oracle server enforces integrity constraints. Triggers implement complex integrity rules.

Referential integrity The Oracle server enforces standard referential integrity rules. Triggers implement nonstandard functionality. Table replication The Oracle server copies tables asynchronously into snapshots. Triggers copy tables synchronously into replicas.

Derived data The Oracle server computes derived data values manually.

Triggers compute derived data values automatically. Event logging The Oracle server logs events explicitly. Triggers log events transparently. Controlling Security Within the Server Develop schemas and roles within the Oracle server to control the security of data operations on tables according to the identity of the user. Easier Application Design All you need initially is the interface information in the package specification. You can code and compile a specification without its body.

Then stored subprograms that reference the package can compile as well. You need not define the package body fully until you are ready to complete the application. Hiding Information You can decide which constructs are public visible and accessible or private hidden and inaccessible. Only the declarations in the package specification are visible and accessible to applications. The package body hides the definition of the private constructs so that only the package is affected not your application or any calling programs if the definition changes.

This enables you to change the implementation without having to recompile calling programs. Also, by hiding implementation details from users, you protect the integrity of the package. Advantages of Using Packages continued Added Functionality Packaged public variables and cursors persist for the duration of a session. Thus, they can be shared by all subprograms that execute in the environment. They also enable you to maintain data across transactions without having to store it in the database.

Private constructs also persist for the duration of the session, but can only be accessed within the package. Better Performance When you call a packaged subprogram the first time, the entire package is loaded into memory.

Packaged subprograms also stop cascading dependencies and so avoid unnecessary compilation. Overloading With packages you can overload procedures and functions, which means you can create multiple subprograms with the same name in the same package, each taking parameters of different number or datatype. Summary In this lesson, you should have learned how to: Summary You group related procedures and function together into a package.

Packages improve organization, management, security, and performance. A package consists of package specification and a package body. You can change a package body without affecting its package specification. Summary continued Packages enable you to hide source code from users.

When you invoke a package for the first time, the entire package is loaded into memory. This reduces the disk access for subsequent calls. Summary continued You can create, delete, and modify packages. You can drop the package body without affecting its specification. Practice 12 Overview This practice covers the following topics: Practice 12 Overview In this practice, you will create package specifications and package bodies. You will invoke the constructs in the packages, using sample data.

Practice 12 1. You can save the package body and specification in two separate files. Use the code in your previously saved script files when creating the package. Make all the constructs public. Consider whether you still need the stand-alone procedures and functions you just packaged.

Query the JOBS table to see the result. Create and invoke a package that contains private and public constructs. You can save the specification and body into separate files. If you have time: Make both constructs public. The procedure checks that the manager and employee work in the same department. If the department number and manager combination is invalid, you should raise an application error with an appropriate message. Practice 12 continued b.

It also looks at the effect of packaging functions that are used in SQL statements. Only local or packaged subprograms can be overloaded. You cannot overload stand-alone subprograms. Overloading This feature enables you to define different subprograms with the same name. You can distinguish the subprograms both by name and by parameters. Sometimes the processing in two subprograms is the same, but the parameters passed to them varies. In that case it is logical to give them the same name.

Stand-alone subprograms cannot be overloaded. Restrictions You cannot overload: The above restrictions apply if the names of the parameters are also the same. If you use different names for the parameters, then you can invoke the subprograms by using named notation for the parameters.

Resolving Calls The compiler tries to find a declaration that matches the call. It searches first in the current scope and then, if necessary, in successive enclosing scopes. The compiler stops searching if it finds one or more subprogram declarations in which the name matches the name of the called subprogram. For like-named subprograms at the same level of scope, the compiler needs an exact match in number, order, and datatype between the actual and formal parameters.

Example The preceding slide shows the package specification of a package with overloaded procedures. The first definition takes three parameters to be able to insert a new department to the department table. The second definition takes only two parameters, because the department ID is populated through a sequence.

Overloading Example continued Most built-in functions are overloaded. The format into which the date or number has to be converted can also be specified in the function call. To be able to access the built-in subprogram, you need to qualify it with its package name.

If you redeclare a built-in subprogram as a stand-alone subprogram, to be able to access your subprogram you need to qualify it with your schema name, for example, SCOTT.

Using Forward Declarations You must declare identifiers before referencing them. You must declare an identifier before using it. Therefore, a subprogram must be declared before calling it. You can solve the illegal reference problem by reversing the order of the two procedures. However, this easy solution does not always work. Suppose the procedures call each other or you absolutely want to define them in alphabetical order.

It consists of the subprogram specification terminated by a semicolon. You can use forward declarations to do the following: Forward Declarations and Packages Forward declarations typically let you group related subprograms in a package.

The subprogram specifications go in the package specification, and the subprogram bodies go in the package body, where they are invisible to the applications.

In this way, packages enable you to hide implementation details. Define an Automatic, One-Time-Only Procedure A one-time-only procedure is executed only once, when the package is first invoked within the user session. Initialize public or private variables with an automatic, one-time-only procedure when the derivation is too complex to embed within the variable declaration. In this case, do not initialize the variable in the declaration, because the value is reset by the one-time-only procedure.

The keyword END is not used at the end of a one-time-only procedure. Observe that in the example in the slide, there is no END at the end of the one-time-only procedure. Calls to subprograms that break the above restrictions are not allowed.

Controlling Side Effects For the Oracle server to execute a SQL statement that calls a stored function, it must know the purity level of a stored functions, that is, whether the functions are free of side effects. Side effects are changes to database tables or public packaged variables those declared in a package specification.

Side effects could delay the execution of a query, yield order-dependent therefore indeterminate results, or require that the package state variables be maintained across user sessions. Therefore, the following restrictions apply to stored functions called from SQL expressions: But from Oracle8i, a user-written function can be called from a SQL statement without any compile-time checking of its purity.

The restrictions on functions discussed above are the same as those discussed in the lesson "Creating Functions. User Defined Package: The function is called from SQL statements on remote databases.

If this is not specified, you get an error message saying that the function TAX does not guarantee that it will not update the database while invoking the package function in a query. Persistent State of Package Variables: Persistent State of Package Variables This sample package illustrates the persistent state of package variables. Once the variable is assigned a value, the value persists in the session until it is modified again.

The example in the following slide shows how the value of a global package variable persists for a session. Controlling the Persistent State of a Package Variable You can keep track of the state of a package variable or cursor, which persists throughout the user session, from the time the user first references the variable or cursor to the time the user disconnects.

Initialize the variable within its declaration or within an automatic, one-time-only procedure. Change the value of the variable by means of package procedures. The value of the variable is released when the user disconnects. The sequence of steps in the preceding slide shows how the state of a package variable persists. The value 0. Because 0. Scott invoked the procedure with commission percentage value of 0.

This value is more than the maximum commission percentage 0. Hence, it was invalid. Jones rolled back the transaction and exited the session. The global value was initialized to 10 when he logged in at The procedure was successful because the new value 0. Declare the public global cursor in the package specification.

Oracle 9i PL-SQL Vol 2

In the package body: The state does not persist from session to session for the same user, nor does it persist from user to user. Summary Overloading is a feature that enables you to define different subprograms with the same name. It is logical to give two subprograms the same name in situations when the processing in both the subprograms is the same, but the parameters passed to them varies.

Forward declaration enables you to define subprograms in logical or alphabetical order, define mutually recursive subprograms, and group subprograms in a package. A one-time-only procedure is executed only when the package is first invoked within the other user session. You can use this feature to initialize variables only once per session. You can keep track of the state of a package variable or cursor, which persists throughout the user session, from the time the user first references the variable or cursor to the time that the user disconnects.

Practice 13 Overview This practice covers the following topics: Practice 13 Overview In this practice you create a package containing an overloaded function. Practice 13 1. The function accepts a date or character string and prints a date or a number, depending on how the function is invoked. Make sure you handle invalid input. Lesson Aim In this lesson, you learn how to use some of the Oracle server supplied packages and to take advantage of their capabilities.

Using Supplied Packages Oracle-supplied packages: You may take advantage of the functionality provided by these packages when creating your application, or you may simply want to use these packages as ideas when you create your own stored procedures. Most of the standard packages are created by running catproc. Dynamic SQL statements are not embedded in your source program but rather are stored in character strings that are input to, or built by, the program.

That is, the SQL statements can be created dynamically at run time by using variables. Execution Flow SQL statements go through various stages: Some stages may be skipped. Parse Every SQL statement must be parsed. Bind After parsing, the Oracle server knows the meaning of the Oracle statement but still may not have enough information to execute the statement. The Oracle server may need values for any bind variable in the statement.

The process of obtaining these values is called binding variables. Execute At this point, the Oracle server has all necessary information and resources, and the statement is executed.

Fetch In the fetch stage, rows are selected and ordered if requested by the query , and each successive fetch retrieves another row of the result, until the last row has been fetched.

La Dra. Martínez habla Inglés y Español

You can fetch queries, but not the DML statements. Some of the procedures and functions of the package include: The operations provided by this package are performed under the current user, not under the package owner SYS.Note the use of the colon: You will invoke the constructs in the packages, using sample data.

Performing DML Operations on a View continued You can add data through a view unless it contains any of the items listed in the slide or ther e are NOT NULL columns, without default values, in the base table that are not selected by the view. If this documentation is delivered to a U. When you invoke a package for the first time, the entire package is loaded into memory. For example, to view the top five earners, enter 5. Select the appropriate Statement check boxes to choose the events component.

ANIBAL from Raleigh
Please check my other articles. I have a variety of hobbies, like pencak silat. I love reading books famously .