Creating a Configuration File. Message Broker Help Home Documentation. Message Broker Help Home Documentation. Triggers, Packages, and Stored Procedures. Stored procedures provide a powerful way to code application logic that can be stored on the server. Informix Dynamic Server and Oracle both use stored procedures. Oracle also uses an additional type of subprogram called a function. The language used to code stored procedures is a database-specific procedural extension of SQL. In Oracle it is PL/SQL and in Informix Dynamic Server it is Informix Dynamic Server Stored Procedure Language (SPL). These languages differ considerably. However, most of the individual SQL statements and the procedural constructs, such as if-then-else, are similar in both languages.
The PL/SQL procedures, which the Migration Workbench generates, add appropriate comments to indicate the manual conversion required. The following sections provide a comparison of Informix Dynamic Server and Oracle: Parameter Passing An Informix Dynamic Server stored procedure contains the following logical parts: Part three is optional. Informix Dynamic Server SPL Input Parameters. The ESQL/C to Oracle Pro*C Converter. This chapter describes the E/SQL to Pro*C converter, its scope, and limitations. It includes the following sections: Introduction to E/SQL and Pro*C Oracle and Informix Dynamic Server have similar methods of embedding their SQL statements into a third generation language, in this case C (or C++).
You run a precompiler that converts the C containing embedded SQL into pure C. Oracle Pro*C is easier to write and maintain than a pure C. The SQL used in Oracle Pro*C files complies with ANSI standards, as are some of the embedded SQL commands and techniques. Using the ESQL/C to Oracle Pro*C Converter This section provides an example conversion using the ESQL/C to Oracle Pro*C Converter, and describes some common conversions handled automatically by the tool. Example Capture of ESQL/C Source Files The following example shows the use of the tool. For more information about the core code, see "Conversion Errors and Warnings" and "Using Demonstration Code" . Example Code: Oracle Pro*C Conversion. 3 Connecting to the Database. ÂOracle Database XE supports connections between SQL Command Line (SQL*Plus) and the database either locally, or remotely over a TCP/IP network.
The method that you use to connect to Oracle Database XE with SQL Command Line depends on whether you are initiating a local connection or a remote connection. Local and remote connections are explained in the following sections: About Local Connections Connecting locally means running SQL Command Line (or any other Oracle command-line utility) on the same host computer where Oracle Database XE is installed (the "Oracle Database XE host computer") and then initiating a database connection from SQL Command Line (or other utility). Environment Variables Before making a local connection on Linux, you must set environment variables. Note: About Remote Connections Oracle Client Software The remote computer must have Oracle client software installed. Connect Strings where: 2 Starting Up and Shutting Down. This section describes how to start up and shut down Oracle Database Express Edition.
It contains the following topics: Starting Up the Database Oracle Database Express Edition (Oracle Database XE) starts up automatically immediately after installation and after each system restart. Thus, there is no need to start up the database unless you previously shut it down. You can start up the database from the desktop or with SQL Command Line (SQL*Plus). Starting Up the Database from the Desktop This section explains how to start up the database from the desktop in Windows and in the following two Linux windowing managers: KDE and Gnome. To start up the database using the desktop: Do one of the following:On Windows: Log in to the Oracle Database XE host computer as a Windows administrator—that is, as a user who is a member of the Administrator group.On Linux: Log in to the Oracle Database XE host computer as a user who is a member of the dba user group. Shutting Down the Database. How to execute oracle stored procedure from command prompt. Using SQL Datatypes in Application Development.
This chapter discusses how to use SQL datatypes in database applications. Topics include the following: Representing Data with SQL Datatypes: Overview A datatype associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. These properties cause Oracle Database to treat values of one datatype differently from values of another datatype. For example, Oracle Database can add values of NUMBER datatype, but not values of RAW datatype.
Oracle Database provides a number of built-in datatypes as well as several categories for user-defined types that can be used as datatypes. The datatypes supported by Oracle Database can be divided into the following categories: The Oracle precompilers recognize other datatypes in embedded SQL programs. Representing Character Data This section contains the following topics: Representing Character Data: Overview You can use the following SQL datatypes to store alphanumeric data: Note: Using SQL Datatypes in Application Development. This chapter discusses how to use SQL datatypes in database applications.
Topics include the following: Representing Data with SQL Datatypes: Overview A datatype associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. These properties cause Oracle Database to treat values of one datatype differently from values of another datatype. For example, Oracle Database can add values of NUMBER datatype, but not values of RAW datatype. Oracle Database provides a number of built-in datatypes as well as several categories for user-defined types that can be used as datatypes. The Oracle precompilers recognize other datatypes in embedded SQL programs. Representing Character Data This section contains the following topics: Representing Character Data: Overview You can use the following SQL datatypes to store alphanumeric data: Specifying Column Lengths as Bytes or Characters Choosing Between the CHAR and VARCHAR2 Datatypes.
Parameter Declaration. A parameter declaration can appear in in following: Syntax parameter_declaration ::= Description of the illustration parameter_declaration.gif (datatype ::=, expression ::=) Keyword and Parameter Descriptions datatype The data type of the parameter that you are declaring. Parameter modes that define the action of formal parameters.
Note: Avoid using OUT and INOUT with functions. Specify NOCOPY to instruct the database to pass this argument as fast as possible. These effects might or might not occur on any particular call. Parameter_name The name of the formal parameter that you are declaring, which you can reference in the body of the subprogram. Specifies a default value for an IN parameter. Examples Related Topics. Using PL/SQL Subprograms. This chapter explains how to turn sets of statements into reusable subprograms. Subprograms are the building blocks of modular, maintainable applications. Topics: Overview of PL/SQL Subprograms A PL/SQL subprogram is a named PL/SQL block that can be invoked with a set of parameters. A subprogram can be either a procedure or a function. Typically, you use a procedure to perform an action and a function to compute and return a value.
You can create a subprogram either at schema level, inside a package, or inside a PL/SQL block (which can be another subprogram). A subprogram created at schema level is a standalone stored subprogram. A subprogram created inside a package is a packaged subprogram. A subprogram created inside a PL/SQL block is a nested subprogram. Subprogram Calls A subprogram call has this form: subprogram_name [ (parameter [, parameter]... ) ] A procedure call is a PL/SQL statement. Raise_salary(employee_id, amount); A function call is part of an expression.
Subprogram Parts Note: Oracle9i Universal Unique Identifier (UUID) Oracle sequences are frequently used to provide unique numbers for primary keys where an appropriate unique key is not available. The use of sequences can cause a problem during data migrations and replication processes where duplication of the sequences occur. The use of a Universal Unique Identifier (UUID) solves this problem as each number is unique, rather than the next value in a sequential list of numbers. In addition, the UUID can be generated within the application server layer rather than the database, negating the need for extra round-trips to query the value of sequences or automatically assigned primary key values. A UUID is a 128-bit number used to uniquely identify an object or entity on the Internet. Depending on the specific mechanisms used, a UUID is either guaranteed to be different or is, at least, extremely likely to be different from any other UUID generated until 3400 A.D. The UUID relies upon a combination of components to ensure uniqueness.
Using Java Using PL/SQL. Functions, 125 of 177. Syntax sys_guid::= Text description of sys_guid Purpose SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier and a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread. Examples The following example adds a column to the sample table hr.locations, inserts unique identifiers into each row, and returns the 32-character hexadecimal representation of the 16-byte RAW value of the global unique identifier: ALTER TABLE locations ADD (uid_col RAW(32)); UPDATE locations SET uid_col = SYS_GUID(); SELECT location_id, uid_col FROM locations; LOCATION_ID UID_COL ----------- ---------------------------------------- 1000 7CD5B7769DF75CEFE034080020825436 1100 7CD5B7769DF85CEFE034080020825436 1200 7CD5B7769DF95CEFE034080020825436 1300 7CD5B7769DFA5CEFE034080020825436 . . .
The Oracle SEQUENCE Function. Definition: The Oracle SEQUENCE function allows you to create auto-numbering fields by using sequences. An Oracle sequence is an object that is used to generate incrementing or decrementing numbers. Example Syntax: CREATE SEQUENCE <sequence_name> MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; Example Usage: CREATE SEQUENCE user_seq MINVALUE 1 MAXVALUE 20000 START WITH 1 INCREMENT BY 1 CACHE 100; The example above will create a sequence object called user_seq Because of the 'START WITH 1' parameter the first number in the sequence will be '1'. The 'MAXVALUE 20000' parameter tells Oracle to stop generating sequence numbers at 20,000. The 'INCREMENT BY 1' parameter will cause the sequence numbers to increment by 1 (i.e. 2,3,4, and so on.). The last line, 'CACHE 100' tells Oracle to pre-generate or cache the next 100 sequence values and store them in system memory for increased performance.
Incrementing By Steps Related Links: Oracle Sequences. The Oracle PL/SQL SYS_GUID Function. Unique IDs for multi-master replication - Sequence or SYS_GUID? Oracle-L is proving to be a good source of inspiration at the moment. Oracle ACE Syed Jaffar Hussain asked the question Is it possible to share a database sequence between multiple databases?
Using remote sequencesA couple of the replies took the question a bit too literally, and said yes, you can define a sequence on one database, and use it from another: Select SequenceOwner.RemoteSeqName.NextVal@DBLinkName From dual This works - and it can be made 'location transparent' by creating a local synonym for the remote sequence. But it is asymmetric (one database has to own the sequence) and it introduces a point of failure. If database 1 owns the sequence, database 2 can only insert rows if database 1 is actually available. Using a sequence local to each databaseSeveral replies (including mine) suggested using carefully defined sequences which will deliver discrete values on two or more master databases. Partitioned: Suggested by several posters, the number space is divided up in blocks:
Database - How should I store a Guid in Oracle.