Declaring Cursor Variables → Once 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
Cursor Variable Example: Client-Side PL/SQL Block → A 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
Overview of Implicit Cursors → Oracle 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\".
Restrictions on Cursor Expressions → You 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
How Transactions Guard Your Database → A 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
Making Changes Permanent with COMMIT → The 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,
Transporting Tablespaces Between Databases: A Procedure → fully 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
Synchronization, Provisioning, and the Difference Between Them → Provisioning 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
Mapping between Oracle Errors and XA Errors → Oracle 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
Pipelining Between PL/SQL Table Functions → With 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).
Choosing Between Nested Tables and Associative Arrays → between host arrays and associative arrays that use numeric key values. The most efficient way to
Differences Between Data Warehouse and OLTP Systems → differences between typical data warehouses and OLTP systems: Workload Data warehouses are designed to
Converting Between Oracle Types and C Types → The 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,
Relationship Between SQLT and OCI_TYPECODE Values → between 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
Choosing Between NOARCHIVELOG and ARCHIVELOG Mode → This 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
Copying Data between Tables on One Database → You 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
Differences Between Directory Trees by Release → OFA has necessitated changes to the Oracle database directory tree. This section lists the differences.
Differences Between Dimensions and Dimension Surrogates → You 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,
Firewall Between the Console and Management Server → In 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
Defining Relationships Between Parent and Child Tables → Several 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
How the CBO Evaluates the BETWEEN Operator → The optimizer always replaces a condition that uses the BETWEEN comparison operator with an … equivalent condition that uses the >= and salary BETWEEN 2000 AND 3000 is transformed into salary >= 2000 AND salary
Differences between UNIX and Windows NT → For Event Handler entries that require a directory path, use the syntax that is appropriate to the operating system. For example UNIX: /com/oracle/sysman/em/eventlogger/logfile=/app/oracle/8.1.6/sysman/log/MyEvents. log Windows NT: /com/oracle/sysman/em/eventlogger/logfile=c:\\\\orant\\\\sysman\\\\log\\\\MyEvents.log
Switching Between Global and Local Transactions → As of JDBC 3.0, applications can switch connections between local transactions and global … using this connection. Each connection switches automatically between these modes depending on the
Creating Scheduled Links Between the Master Sites → Complete the following steps to create scheduled links between the master sites. Step 1 Create … database links between master sites. The database links provide the necessary distributed mechanisms to … use. You then must create a database link between all replication administrators at each of the
Differences Between Versions of Oracle Names → Oracle Names version 8 and 9 differs significantly from earlier versions. The differences between … administrator could choose between continuing Oracle Names server configuration, as in version 1 or using … and 9, the administrator may choose between continuing Oracle Names Server configuration as in
Differences Between HIERARCHY and DESCENDANTS Keywords → done using the relation mlv.market, which is a relation between market and marketlevel. The
Implicit Conversions Between CLOB and VARCHAR2 → made the following operations between CLOBs and VARCHAR2s possible: CLOB columns can be selected … into VARCHAR2 PL/SQL variables VARCHAR2 columns can be selected into CLOB variables Assignment and parameter passing between CLOBs and VARCHAR2s
Pipelining Data Between PL/SQL Table Functions → With 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).
Choosing Between Nested Tables and Varrays → Varrays are a good choice when the number of elements is known in advance, and when the elements are usually all accessed in sequence. When stored in the database, varrays retain their ordering and subscripts. Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray
Oracle Workflow Guide Release 2.6.2 → Contents Index Glossary Master Index Feedback
Comparison Between OFA on Windows NT and UNIX → You implement OFA on Windows NT and UNIX in the same way. However, differences exist with regard to the following: Directory Naming ORACLE_BASE Directory Support for Symbolic Links on Windows NT See Also: Your UNIX operating system-specific administrator's reference for information about OFA on UNIX
Other Differences Between Windows NT and Windows 2000 → This section discusses differences between Windows NT and Windows 2000 that are not related to common tasks.
Controlling the Array Fetch Between Agent and Non-Oracle Server → The initialization parameter HS_FDS_FETCH_ROWS determines the number of rows to be retrieved from a non-Oracle system. Note that the array fetch must be supported by the agent. See your agent-specific documentation to ensure that your agent supports array fetching.
VPN Connections Between the Enterprise Manager Client and Management Server → VPN software configured to provide a secure communication channel between the two. Specifically, the … configuration, all the network traffic between the Console and the Management Server will be tunneled … , subsequent communication between the Console and Management Server across the virtual network will appear
User-Defined Character Cross-References Between Character Sets → User-defined character cross-references between Japanese character sets, Korean character sets … correctly between JA16SJIS and JA16DBCS. You can find out that both Shift-JIS UDC value F040 and
Controlling the Array Fetch Between Oracle Database Server and Agent → parameter HS_RPC_FETCH_SIZE defines the number of bytes sent for each fetch between the agent and the
Choosing Between Native Dynamic SQL and the DBMS_SQL Package → Oracle provides two methods for using dynamic SQL within PL/SQL: native dynamic SQL and the DBMS_SQL package. Native dynamic SQL lets you place dynamic SQL statements directly into PL/SQL code. These dynamic statements include DML statements (including queries), PL/SQL anonymous blocks, DDL statements, transaction control statements, and session control statements. To process most native dynamic SQL
Migration Between Oracle8 i JPublisher and Oracle9 i JPublisher → JPublisher-Generated Classes\" for differences between Oracle8 i functionality and Oracle9 i functionality
SSL between Non-Oracle Clients and Oracle Database Servers → You can use the Oracle Advanced Security SSL feature to secure connections between non-Oracle … secure connections between Oracle and non-Oracle entities over the Internet. In this example, a Web
Character Set Conversion Between Clients and the Server → to avoid loss of data. Character set conversion may require copying strings between buffers several
Mapping Locale Information Between Oracle and Other Standards → The OCINlsNameMap function maps Oracle character set names, language names, and territory names to and from Internet Assigned Numbers Authority (IANA) and International Organization for Standardization (ISO) names.
Passing Data Between a JSP Page and a Servlet → the semantics are different between the servlet 2.1 specification and the servlet 2.2 specification
Implicit Datatype Conversion Between NCHAR and Other Datatypes → Oracle supports implicit conversions between SQL NCHAR datatypes and other Oracle datatypes, such … SQL CHAR datatypes. Keep in mind these points about implicit conversions: Type conversions between SQL … CHAR or NCHAR. Implicit conversion between CLOB and NCLOB datatypes is not possible. You can, however
Compatibility and Interoperability Issues Between Release 9.2 and Previous Releases → because of differences between Oracle releases: Applications The STARTUP Command Tablespaces and
Propagating Messages Between a SYS.AnyData Queue and a Typed Queue → following sections provide examples of propagating non-LCR user messages and LCRs between a SYS.AnyData … and SYS.AnyData Queues\" for more information about propagation between SYS.AnyData and typed … => 'strmadmin.oe_q_address'); END; / Create a database link between dbs1.net and dbs2.net if one does … between
Choosing Between CHECK and NOT NULL Integrity Constraints → According to the ANSI/ISO standard, a NOT NULL integrity constraint is an example of a CHECK integrity constraint, where the condition is the following: CHECK (Column_name IS NOT NULL) Therefore, NOT NULL integrity constraints for a single column can, in practice, be written in two forms: using the NOT NULL constraint or a CHECK constraint. For ease of use, you should always choose to define NOT NULL
Choosing Between Partitioning and Clustering for Parallel Execution → Partitioning and clustering are easily confused, but they do different things. For example, sometimes partitioning can be sufficient without clustering in parallel execution. Consider a function SmallAggr that performs in-memory aggregation of salary for each department_id, where department_id can be either 1, 2, or 3. The input rows to the function can be partitioned by HASH on department_id such
SDO_GEOM.SDO_DISTANCE Computes the distance between two geometry objects. → operation) of two geometry objects. SDO_GEOM.SDO_DISTANCE Computes the distance between two geometry objects.
VPN Connections Between the Management Server and Intelligent Agents → to note that communication between the Management Server node and Agent node is bi-directional, so
Compatibility and Interoperability Issues Between Release 9.2 and Release 9.0.1 → because of differences between release 9.2 and release 9.0.1: Locally Managed SYSTEM Tablespace New
Behavior Differences Between SQL*Loader and External Tables → This section describes important differences between loading data with external tables as opposed
Synchronizing Between Oracle Internet Directory and iPlanet Directory Server → This section contains these topics: Preparing for Synchronization The Synchronization Process
1.2.9 Communication Between the Service Administrator and the Dynamic Services Engine → The service administrator interfaces with the Dynamic Services engine through the administrator tools. The administrator tools use an extended version of the client library to communicate with the Dynamic Services engine. The Oracle Dynamic Services administrative shell, shipped with the Dynamic Services engine, is an example of these tools. This is an interactive, scriptable, easy-to-use command-line
Differences between using Enterprise Manager on Windows NT and Windows 2000 → Feature On Windows NT On Windows 2000 Services Choose Start > Settings > Control Panel > Services to access the Services dialog box. On Windows 2000, the Services Directory is located further down the Control Panel directory. Choose Start > Settings > Control Panel > Administrative Tools > Services User Manager Enables you to manage Windows computer security and create user accounts on Windows NT.
Table B-1 Port Usage Port Number Usage 3339 Communication between the Enterprise Manger HTTP server → and the Enterprise Manager browser client. 7771, 7773, 7776 Communication between the Enterprise … Manager Console and the Management Server. 7774 Communication between the Oracle Applications Manager … and the Management Server. 7775, 7777 Communication between the paging server and the Management
1.2.8 Communication Between the Service Consumer Application and the Dynamic Services Engine → The client library is responsible for handling the communication between the service consumer … asynchronous messages between the client library and the Dynamic Services engine. Service consumer applications
How to Choose Between a Unicode Database and a Unicode Datatype Solution → To choose the right Unicode solution for your database, consider the following questions: Programming environment: What are the main programming languages used in your applications? How do they support Unicode? Ease of migration: How easily can your data and applications be migrated to take advantage of the Unicode solution? Performance: How much performance overhead are you willing to accept in order
How do I decide between using varrays and nested tables for modeling collections? → Varrays are useful when you need to maintain ordering of your collection elements. Varrays are very efficient when you always manipulate the entire collection as a unit, and that you don't require querying on individual elements of the collections. Varrays are stored inline with the containing row if it is small and automatically stored as a LOBs by the system when its size is beyond a certain threshold.
What Is the Relationship Between the Classes That Produce a Dynamic Source? → The classes that produce a dynamic Source work together as follows: A Template has methods that create a DynamicDefinition and that get and set the current state of a MetadataState. An extension to the Template abstract class adds methods that get and set the values of fields on the MetadataState. The MetadataState implementation has fields for storing the data to use in generating the Source for
To enable network communication between the Enterprise Manager Console and Management Server, several → network ports must be opened in the firewall to allow TCP traffic. The port range of 7771-7777 covers all these. If the Console is running in a browser, then the firewall needs to allow HTTP traffic over port 3339 from the Enterprise Manager Website HTTP server to any browser client. Functional assignments for these ports are shown in the following table.