Results for BETWEEN


41 to 80 of 3258 results. Prev  Next
Results per page:  20 |  40  | 60


User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Using Autonomous TriggersAmong other things, you can use database triggers to log events transparently. Suppose you want to track all inserts into a table, even those that roll back. In the example below, you use a trigger to insert duplicate rows into a shadow table. Because it is autonomous, the trigger can commit inserts into the shadow table whether or not you commit inserts into the main table. -- create a main table

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Overview of Transaction Processing in PL/SQLDoing Independent Units of Work with Autonomous Transactions

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Overview of SQL Support in PL/SQLEnsuring Backward Compatibility of PL/SQL Programs Overview of SQL Support in PL/SQL By extending SQL, PL/SQL offers a unique combination of power and ease of use. You can manipulate Oracle data flexibly and safely because PL/SQL fully supports all SQL data manipulation statements (except EXPLAIN PLAN ), transaction control statements, functions, pseudocolumns, and operators. PL/SQL also supports

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Transaction ControlOracle is transaction oriented; that is, Oracle uses transactions to ensure data integrity. A transaction is a series of SQL data manipulation statements that does a logical unit of work. For example, two UPDATE statements might credit one bank account and debit another. Simultaneously, Oracle makes permanent or undoes all database changes made by a transaction. If your program fails in the middle

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Managing CursorsPL/SQL uses two types of cursors: implicit and explicit. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. However, for queries that return more than one row, you must declare an explicit cursor, use a cursor FOR loop, or use the BULK COLLECT clause.

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Using Subqueries Instead of Explicit CursorsYou need not declare a cursor because PL/SQL lets you substitute a subquery. The following cursor FOR loop calculates a bonus, then inserts the result into a database table: DECLARE bonus REAL; BEGIN FOR emp_rec IN (SELECT empno, sal, comm FROM emp) LOOP bonus:= (emp_rec.sal * 0.05) + (emp_rec.comm * 0.25); INSERT INTO bonuses VALUES (emp_rec.empno, bonus); END LOOP; COMMIT; END;

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Defining REF CURSOR TypesTo create cursor variables, you take two steps. First, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, subprogram, or package using the syntax TYPE ref_type_name IS REF CURSOR [RETURN return_type]; where ref_type_name is a type specifier used in subsequent declarations of cursor variables and return_type must represent

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Defining Autonomous TransactionsTo define autonomous transactions, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler to mark a routine as autonomous (independent). In this context, the term routine includes Top-level (not nested) anonymous PL/SQL blocks Local, standalone, and packaged functions and procedures Methods of a SQL object type Database triggers You can code the pragma

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Ensuring Backward Compatibility of PL/SQL ProgramsPL/SQL Version 2 allows some abnormal behaviors that are no longer allowed. Specifically, Version 2 lets you: Make forward references to RECORD and TABLE types when declaring variables Specify the name of a variable (not a datatype) in the RETURN clause of a function spec Assign values to the elements of an index-by table IN parameter Pass the fields of a record IN parameter to another subprogram

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

SQL Operatorsyields TRUE if any of the individual comparisons yields TRUE. BETWEEN Tests whether a value lies in

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Defining Aliases for Expression Values in a Cursor FOR LoopFields in the implicitly declared record hold column values from the most recently fetched row. The fields have the same names as corresponding columns in the SELECT list. But, what happens if a select item is an expression? Consider the following example: CURSOR c1 IS SELECT empno, sal+NVL(comm,0), job FROM... In such cases, you must include an alias for the select item. In the following example,

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Cursor Variable Example: Pro*C ProgramThe following Pro*C program prompts the user to select a database table, opens a cursor variable for a query of that table, then fetches rows returned by the query: #include #include void sql_error(); main() { char temp[32]; EXEC SQL BEGIN DECLARE SECTION; char * uid = \"scott/tiger\"; SQL_CURSOR generic_cv; /* cursor variable */ int table_num; /* selector */ struct /* EMP record */ { int emp_num;

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Reducing Network Traffic When Passing Host Cursor Variables to PL/SQLWhen passing host cursor variables to PL/SQL, you can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens five cursor variables in a single round trip: /* anonymous PL/SQL block in host environment */ BEGIN OPEN:emp_cv FOR SELECT * FROM emp; OPEN:dept_cv FOR SELECT * FROM dept; OPEN:grade_cv FOR SELECT * FROM salgrade; OPEN:pay_cv FOR SELECT * FROM

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Example of Cursor ExpressionsIn this example, we find a specified location ID, and a cursor from which we can fetch all the departments in that location. As we fetch each department's name, we also get another cursor that lets us fetch their associated employee details from another table. CREATE OR REPLACE procedure emp_report(p_locid number) is TYPE refcursor is ref cursor; -- The query returns only 2 columns, but the second

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Undoing Changes with ROLLBACKThe ROLLBACK statement ends the current transaction and undoes any changes made during that transaction. Rolling back is useful for two reasons. First, if you make a mistake like deleting the wrong row from a table, a rollback restores the original data. Second, if you start a transaction that you cannot finish because an exception is raised or a SQL statement fails, a rollback lets you return to

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Overriding Default LockingBy default, Oracle locks data structures for you automatically. However, you can request specific data locks on rows or tables when it is to your advantage to override default locking. Explicit locking lets you share or deny access to a table for the duration of a transaction. With the LOCK TABLE statement, you can explicitly lock entire tables. With the SELECT FOR UPDATE statement, you can explicitly

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Controlling Autonomous TransactionsThe first SQL statement in an autonomous routine begins a transaction. When one transaction ends, the next SQL statement begins another transaction. All SQL statements executed since the last commit or rollback make up the current transaction. To control autonomous transactions, use the following statements, which apply only to the current (active) transaction: COMMIT ROLLBACK [TO savepoint_name]

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Using Cursor FOR LoopsUsing Cursor Variables

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Data ManipulationTo manipulate Oracle data, you use the INSERT, UPDATE, DELETE, SELECT, and LOCK TABLE commands. INSERT adds new rows of data to database tables; UPDATE modifies rows; DELETE removes unwanted rows; SELECT retrieves rows that meet your search criteria; and LOCK TABLE temporarily limits access to a table.

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Separating Cursor Specs and Bodies with PackagesYou can separate a cursor specification (spec for short) from its body for placement in a package. That way, you can change the cursor body without having to change the cursor spec. You code the cursor spec in the package spec using this syntax: CURSOR cursor_name [(parameter[, parameter]...)] RETURN return_type; In the following example, you use the %ROWTYPE attribute to provide a record type that

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Declaring Cursor VariablesOnce you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram. In the following example, you declare the cursor variable dept_cv: DECLARE TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE; dept_cv DeptCurTyp; -- declare cursor variable Note: You cannot declare cursor variables in a package. Unlike packaged variables, cursor variables do not have

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Cursor Variable Example: Client-Side PL/SQL BlockA client-side application in a branch library might use the following PL/SQL block to display the retrieved information: DECLARE lib_cv cv_types.LibCurTyp; book_rec books%ROWTYPE; periodical_rec periodicals%ROWTYPE; tape_rec tapes%ROWTYPE; BEGIN get_title(:title); -- title is a host variable find_item(:title, lib_cv); FETCH lib_cv INTO book_rec; display_book(book_rec); EXCEPTION WHEN ROWTYPE_MISMATCH

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Overview of Implicit CursorsOracle implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor. You can refer to the most recent implicit cursor as the SQL cursor. Although you cannot use the OPEN, FETCH, and CLOSE statements to control the SQL cursor, you can use cursor attributes to get information about the most recently executed SQL statement. See \"Using Cursor Attributes\".

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Restrictions on Cursor ExpressionsYou cannot use a cursor expression with an implicit cursor. Cursor expressions can appear only: In a SELECT statement that is not nested in any other query expression, except when it is a subquery of the cursor expression itself. As arguments to table functions, in the FROM clause of a SELECT statement. Cursor expressions can appear only in the outermost SELECT list of the query specification. Cursor

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

How Transactions Guard Your DatabaseA transaction is a series of SQL data manipulation statements that does a logical unit of work. Oracle treats the series of SQL statements as a unit so that all the changes brought about by the statements are either committed (made permanent) or rolled back (undone) at the same time. If your program fails in the middle of a transaction, the database is automatically restored to its former state. The

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Making Changes Permanent with COMMITThe COMMIT statement ends the current transaction and makes permanent any changes made during that transaction. Until you commit the changes, other users cannot access the changed data; they see the data as it was before you made the changes. Consider a simple transaction that transfers money from one bank account to another. The transaction requires two updates because it debits the first account,

Administrator's Guide, 9i Release 2 (9.2.0.2)

Managing Tablespaces

Transporting Tablespaces Between Databases: A Procedurefully in succeeding sections that detail transporting tablespaces sales_1 and sales_2 between … : Pick a Self-Contained Set of Tablespaces There may be logical or physical dependencies between … --------------------------------------------------------------------------- Constraint DEPT_FK between table JIM.EMP in tablespace SALES_1 and

Administrator's Guide, 9i Release 2 (9.2.0.2)

Oracle Directory Integration Platform Concepts and Components

Synchronization, Provisioning, and the Difference Between ThemProvisioning has to do with applications. It notifies them of changes to user or group entries or attributes that the application needs to track. Synchronization has to do with directories rather than applications. It ensures the consistency of entries and attributes that reside in both Oracle Internet Directory and other connected directories. This section contains these topics: Synchronization Provisioning

JDBC Developer's Guide and Reference, 9i Release 2 (9.2.0.2)

Distributed Transactions

Mapping between Oracle Errors and XA ErrorsOracle errors correspond to XA errors in OracleXAException instances as documented in Table 15-2. Table 15-2 Oracle-XA Error Mapping  Oracle Error Code XA Error Code ORA 3113 XAException.XAER_RMFAIL ORA 3114 XAException.XAER_RMFAIL ORA 24756 XAException.XAER_NOTA ORA 24764 XAException.XA_HEURCOM ORA 24765 XAException.XA_HEURRB ORA 24766 XAException.XA_HEURMIX ORA 24767 XAException.XA_RDONLY ORA 25351

Data Cartridge Developer's Guide, 9i Release 2 (9.2.0.2)

Pipelined and Parallel Table Functions

Pipelining Between PL/SQL Table FunctionsWith serial execution, results are pipelined from one PL/SQL table function to another using an approach similar to co-routine execution. For example, the following statement pipelines results from function g to function f: SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g())))); Parallel execution works similarly except that each function executes in a different process (or set of processes).

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Collections and Records

Choosing Between Nested Tables and Associative Arraysbetween host arrays and associative arrays that use numeric key values. The most efficient way to

Concepts, 9i Release 2 (9.2.0.2)

Introduction to the Oracle Server

Differences Between Data Warehouse and OLTP Systemsdifferences between typical data warehouses and OLTP systems: Workload Data warehouses are designed to

Application Developer's Guide - Object-Relational Features, 9i Release 2 (9.2.0.2)

Object Support in Oracle Programming Environments

Converting Between Oracle Types and C TypesThe C representation for objects that is generated by the Oracle Type Translator (OTT) uses OCI types whose internal details are hidden, such as LNOCIString and LNOCINumber for scalar attributes. Collection types and object references are similarly represented using LNOCITable, LNOCIArray, and LNOCIRef types. While using these \"opaque\" types insulates you from changes to their internal formats,

Programmer's Guide, 9i Release 2 (9.2.0.2)

Datatypes

Relationship Between SQLT and OCI_TYPECODE Valuesbetween Oracle and OCI client applications. The OCI_TYPECODE types are used by Oracle's type system … cases there are direct mappings between SQLT and OCI_TYPECODE values. In other cases, however, there … . The following table illustrates the mappings between SQLT and OCI_TYPECODE types. Table 3-9

Administrator's Guide, 9i Release 2 (9.2.0.2)

Managing Archived Redo Logs

Choosing Between NOARCHIVELOG and ARCHIVELOG ModeThis section describes the issues you must consider when choosing to run your database in NOARCHIVELOG or ARCHIVELOG mode, and contains these topics: Running a Database in NOARCHIVELOG Mode Running a Database in ARCHIVELOG Mode

SQL*Plus® User's Guide and Reference, 9i Release 2 (9.2.0.2)

SQL*Plus COPY Command

Copying Data between Tables on One DatabaseYou can copy data from one table to another in a single database (local or remote). To copy between … @MYDATABASE - INSERT EMPLOYEE_COPY2 - USING SELECT * FROM EMPLOYEE_COPY To copy between tables on a

Getting Started, 9i Release 2 (9.2.0.2)

Multiple Oracle Homes and Optimal Flexible Architecture

Differences Between Directory Trees by ReleaseOFA has necessitated changes to the Oracle database directory tree. This section lists the differences.

Developer's Guide to the OLAP DML, 9i Release 2 (9.2.0.2)

Defining Data Objects, 5 of 11

Differences Between Dimensions and Dimension SurrogatesYou cannot dimension an object by a dimension surrogate. However, you can dimension an object, such as a variable, by a dimension, define a dimension surrogate for the dimension, and then use the values of the surrogate instead of the dimension in LIMIT commands, models, QDRs, and data loading. You cannot define a valueset on a dimension surrogate. However, you can define a valueset on a dimension,

Administrator's Guide, 9i Release 2 (9.2.0.2)

Firewalls and Virtual Private Networks

Firewall Between the Console and Management ServerIn this configuration, the Enterprise Manager Console and Management Server are separated by a firewall. Figure B-1 Console and Management Server on Opposite Sides of a Firewall Text description of the illustration firewala.gif

Application Developer's Guide - Fundamentals, 9i Release 2 (9.2.0.2)

Maintaining Data Integrity Through Constraints

Defining Relationships Between Parent and Child TablesSeveral relationships between parent and child tables can be determined by the other types of … establishes a \"one-to-many\" relationship between the parent and foreign keys that allows undetermined values … between the employee and department tables. Each department (parent key) has many employees (foreign key … establishes





Was this page helpful?

Integrated Cloud Applications & Platform Services