Event Propagation Between Queues → You can use Streams to configure event propagation between two queues, which may reside in … different databases. Streams uses job queues to propagate events. A propagation is always between a source … queue and a destination queue. Although propagation is always between two queues, a single queue may … . However, only one propagation
Differences Between Documentation Formats → As a result of publishing deadlines, the online versions of manuals in this library may contain updates or corrections that are not included in the printed books. Additionally, the HTML version of a manual may contain minor updates or corrections that do not appear in the PDF version.
Transporting Tablespaces Between Databases → This section describes how to transport tablespaces between databases, and contains the following … Transportable Tablespaces Transporting Tablespaces Between Databases: A Procedure Object Behaviors Using Transportable Tablespaces
oracle.ultrasearch.query Class Between → DETAIL: FIELD | CONSTR | METHOD oracle.ultrasearch.query Class Between java.lang.Object … | +--oracle.ultrasearch.query.Between public class Between extends java.lang.Object implements Query Find … documents that has an attribute value within a range. Between can be applied to Date attribute, or Number … attribute. Since:
Mapping Between WF_EVENT_T and OMBAQ_TEXT_MSG → messages between systems. OMB queues require messages to be stored in a structure defined by a Java … called WF_EVENT_OMB_QH which you can use to translate between the standard Workflow WF_EVENT_T message
Determining Differences Between Replicated Tables → optionally rectify, the differences between two tables when both sites are Oracle release 7.3 or higher.
Transport of Tablespaces Between Databases → the tablespace between databases, or you can unplug a tablespace from one Oracle database and plug … it into another Oracle database, moving the tablespace between databases on the same platform … transport tablespaces only between Oracle databases that use the same character set and that run on
Converting Between Different LOB Types → Is Implicit LOB Conversion Between Different LOB Types Allowed? Question There are no implicit LOB … conversions between different LOB types? For example, in PL/SQL, I cannot use: INSERT INTO t VALUES
JPublisher Compatibility Between JDK Versions → of.sqlj files that are completely compatible between JDK 1.1.x and JDK 1.2.x or higher. (With this … i JPublisher default behavior, and is what makes translated.java code incompatible between JDK 1.1
Analyzing the Relationships Between Labels → This section describes relationships between labels. It contains these topics: Dominant and
Example: Relation Between Two Dimensions → between them to associate each city with the state that it is in. Assume that the state.city relation was
Overview of SQL Support in PL/SQL → Managing Cursors Separating Cursor Specs and Bodies with Packages
Overview of Explicit Cursors → The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria. When a query returns multiple rows, you can explicitly declare a cursor to process the rows. Moreover, you can declare a cursor in the declarative part of any PL/SQL block, subprogram, or package. You use three commands to control a cursor: OPEN, FETCH, and CLOSE. First,
Using Cursor Attributes → Every explicit cursor and cursor variable has four attributes: %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement. You can use cursor attributes in procedural statements but not in SQL statements.
Ending Transactions → A good programming practice is to commit or roll back every transaction explicitly. Whether you issue the commit or rollback in your PL/SQL program or in the host environment depends on the flow of application logic. If you neglect to commit or roll back a transaction explicitly, the host environment determines its final state. For example, in the SQL*Plus environment, if your PL/SQL block does not
Calling Autonomous Functions from SQL → A function called from SQL statements must obey certain rules meant to control side effects. (See \"Controlling Side Effects of PL/SQL Subprograms\".) To check for violations of the rules, you can use the pragma RESTRICT_REFERENCES. The pragma asserts that a function does not read or write database tables or package variables. (For more information, See Oracle9i Application Developer's Guide - Fundamentals.)
Using Cursor Attributes → Using Cursor Expressions
SQL Pseudocolumns → PL/SQL recognizes the following SQL pseudocolumns, which return specific data items: CURRVAL, LEVEL, NEXTVAL, ROWID, and ROWNUM. Pseudocolumns are not actual columns in a table but they behave like columns. For example, you can select values from a pseudocolumn. However, you cannot insert into, update, or delete from a pseudocolumn. Also, pseudocolumns are allowed in SQL statements, but not in procedural
Using Cursor Variables → pass cursor variables back and forth between an application and server through remote procedure calls (RPCs).
Cursor Variable Example: Master Table and Details Tables → Consider the stored procedure below, which searches the database of a main library for books, periodicals, and tapes. A master table stores the title and category code (where 1 = book, 2 = periodical, 3 = tape) of each item. Three detail tables store category-specific information. When called, the procedure searches the master table by title, uses the associated category code to pick an OPEN-FOR statement,
Overview of Implicit Cursor Attributes → Implicit cursor attributes return information about the execution of an INSERT, UPDATE, DELETE, or SELECT INTO statement. The values of the cursor attributes always refer to the most recently executed SQL statement. Before Oracle opens the SQL cursor, the implicit cursor attributes yield NULL. Note: The SQL cursor has another attribute, %BULK_ROWCOUNT, designed for use with the FORALL statement. For
Advantages of Autonomous Transactions → Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. So, you can log events, increment retry counters, and so on, even if the main transaction rolls back. More important, autonomous transactions help you build modular, reusable software components. For example, stored procedures can start and finish autonomous
Undoing Partial Changes with SAVEPOINT → SAVEPOINT names and marks the current point in the processing of a transaction. Used with the ROLLBACK TO statement, savepoints let you undo parts of a transaction instead of the whole transaction. In the example below, you mark a savepoint before doing an insert. If the INSERT statement tries to store a duplicate value in the empno column, the predefined exception DUP_VAL_ON_INDEX is raised. In that
How Oracle Does Implicit Rollbacks → Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back. If you exit a stored subprogram with
Using Cursor FOR Loops → In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed. Consider the PL/SQL
Passing Parameters to a Cursor FOR Loop → You can pass parameters to the cursor in a cursor FOR loop. In the following example, you pass a department number. Then, you compute the total wages paid to employees in that department. Also, you determine how many employees have salaries higher than $2000 and/or commissions larger than their salaries. -- available online in file 'examp8' DECLARE CURSOR emp_cursor(dnum NUMBER) IS SELECT sal, comm
Why Use Cursor Variables? → Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and
Controlling Cursor Variables → You use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result set. When all the rows are processed, you CLOSE the cursor variable. Opening a Cursor Variable The OPEN-FOR statement associates a cursor variable with a multi-row query, executes the query, and identifies the result set. Here
Cursor Variable Example: Manipulating Host Variables in SQL*Plus → A host variable is a variable you declare in a host environment, then pass to one or more PL/SQL programs, which can use it like any other variable. In the SQL*Plus environment, to declare a host variable, use the command VARIABLE. For example, you declare a variable of type NUMBER as follows: VARIABLE return_code NUMBER Both SQL*Plus and PL/SQL can reference the host variable, and SQL*Plus can display
Using Cursor Expressions → A cursor expression returns a nested cursor. Each row in the result set can contain values as usual, plus cursors produced by subqueries involving the other values in the row. Thus, a single query can return a large set of related values retrieved from multiple tables. You can process the result set with nested loops that fetch first from the rows of the result set, then any nested cursors within
Overview of Transaction Processing in PL/SQL → This section explains how to do transaction processing. You learn the basic techniques that safeguard the consistency of your database, including how to control whether changes to Oracle data are made permanent or undone. The jobs or tasks that Oracle manages are called sessions. A user session is started when you run an application program or an Oracle tool and connect to Oracle. To allow user sessions
6 Interaction Between PL/SQL and Oracle → Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. -- Samuel Johnson This chapter helps you harness the power of Oracle. You learn how PL/SQL supports the SQL commands, functions, and operators that let you manipulate Oracle data. You also learn how to manage cursors, use cursor variables, and process transactions. This chapter discusses the following
SQL Functions → PL/SQL lets you use all the SQL functions including the following aggregate functions, which summarize entire columns of Oracle data: AVG, COUNT, GROUPING, MAX, MIN, STDDEV, SUM, and VARIANCE. Except for COUNT(*), all aggregate functions ignore s. You can use the aggregate functions in SQL statements, but not in procedural statements. Aggregate functions operate on entire columns unless you use the
Using Cursor Subqueries → You can use cursor subqueries, also know as cursor expressions, to pass sets of rows as parameters to functions. For example, this statement passes a parameter to the StockPivot function consisting of a REF CURSOR that represents the rows returned by the cursor subquery: SELECT * FROM TABLE(StockPivot(CURSOR(SELECT * FROM StockTable))); Cursor subqueries are often used with table functions, which
What Are Cursor Variables? → Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. So, declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype REF CURSOR. To execute a multi-row query, Oracle opens
Avoiding Errors with Cursor Variables → If both cursor variables involved in an assignment are strongly typed, they must have the same datatype. In the following example, even though the cursor variables have the same return type, the assignment raises an exception because they have different datatypes: DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv ( emp_cv
Restrictions on Cursor Variables → Currently, cursor variables are subject to the following restrictions: You cannot declare cursor variables in a package. For example, the following declaration is not allowed:CREATE PACKAGE emp_stuff AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv EmpCurTyp; -- not allowed END emp_stuff; Remote subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot
Overview of Explicit Cursor Attributes → Explicit cursor attributes return information about the execution of a multi-row query. When an explicit cursor or a cursor variable is opened, the rows that satisfy the associated query are identified and form the result set. Rows are fetched from the result set. %FOUND Attribute: Has a Row Been Fetched? After a cursor or cursor variable is opened but before the first fetch, %FOUND yields NULL. Thereafter,
Setting Transaction Properties with SET TRANSACTION → You use the SET TRANSACTION statement to begin a read-only or read-write transaction, establish an isolation level, or assign your current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables. During a read-only transaction, all queries refer to the same snapshot of the database,
Doing Independent Units of Work with Autonomous Transactions → A transaction is a series of SQL statements that does a logical unit of work. Often, one transaction starts another. In some applications, a transaction must operate outside the scope of the transaction that started it. This can happen, for example, when a transaction calls out to a data cartridge. An autonomous transaction is an independent transaction started by another transaction, the main transaction.