PK w|Joa,mimetypeapplication/epub+zipPKx|JOEBPS/adfns_idcode.htm Using the Identity Code Package

22 Using the Identity Code Package

The Identity Code Package is a feature in the Oracle Database that offers tools and techniques to store, retrieve, encode, decode, and translate between various product or identity codes, including Electronic Product Code (EPC), in an Oracle Database. The Identity Code Package provides data types, metadata tables and views, and PL/SQL packages for storing EPC standard RFID tags or new types of RFID tags in a user table.

The Identity Code Package empowers Oracle Database with the knowledge to recognize EPC coding schemes, support efficient storage and component level retrieval of EPC data, and comply with the EPCglobal Tag Data Translation 1.0 (TDT) standard that defines how to decode, encode, and translate between various EPC RFID tag representations.

The Identity Code Package also provides an extensible framework that allows developers to use pre-existing coding schemes with their applications that are not included in the EPC standard and make the Oracle Database adaptable to these older systems and to any evolving identity codes that may some day be part of a future EPC standard.

The Identity Code Package also lets developers create their own identity codes by first registering the encoding category, registering the encoding type, and then registering the components associated with each encoding type.

Topics:

22.1 Identity Concepts

A database object MGD_ID is defined that lets users use EPC standard identity codes and use their own existing identity codes. See Section 22.7 for a brief description of EPC concepts. The MGD_ID object serves as the base code object to which belong certain categories, or types of the RFID tag, such as the EPC category, NASA category, and many other categories. Each category has a set of tag schemes or documents that define tag representation structures and their components. For the EPC category, the metadata needed to define encoding schemes (SGTIN-64, SGTIN-96, GID-96, and so on) representing different encoding types (defined in the EPC standard v1.1) is loaded by default into the database. Users can define encoding their own categories and schemes as shown in Figure 22-1 and load these into the database as well.

Figure 22-1 RFID Code Categories and Their Schemes

Description of Figure 22-1 follows

An MGD_ID object contains two attributes, a category_id and a list of components consisting of name-value pairs. When MGD_ID objects are stored, the tag representation must be parsed into these component name-value pairs upon object creation.

EPC standard version 1.1 defines one General Identifier type (GID) that is independent of any known, existing code schemes, five Domain Identifier types that are based on EAN.UCC specifications, and the identity type United States Department of Defense (USDOD). The five EAN.UCC based identity types are the serialized global trade identification number (SGTIN), the serial shipping container code (SSCC), the serialized global location number (SGLN), the global returnable asset identifier (GRAI) and the global individual asset identifier (GIAI).

Except GID, which has one bit-level encoding, all the other identity types each have two encodings depending on their length: 64-bit and 96-bit. So in total there are thirteen different standard encodings for EPC tags. Also, tags can be encoded in representations other than binary, such as the tag URI and pure identity representations.

Each EPC encoding has its own structure and organization, see Table 22-1. The EPC encoding structure field names relate to the names in the parameter_list parameter name-value pairs in the Identity Code Package API. For example, for SGTIN-64, the structure field names are Filter Value, Company Prefix Index, Item Reference, and Serial Number.

Table 22-1 General Structure of EPC Encodings

Encoding NameHeader Length in bitsField Names (parameter_list name-value pairs) and (length in bits)

GID-96

8

General Manager Number (8), Object Class (24), Serial Number (36)

SGTIN-64

2

Filter Value (3), Company Prefix Index (14), Item Reference 20), Serial Number (25)

SGTIN-96

8

Filter Value (3), Partition (3), Company Prefix (20-40), Item Reference (24-4), Serial Number (38)

SSCC-64

8

Filter Value (3), Company Prefix Index (14), Serial Reference (39)

SSCC-96

8

Filter Value (3), Partition (3), Company Prefix (20-40), Serial Reference (38-18), Unallocated (24)

SGLN-64

8

Filter Value (3), Company Prefix Index (14), Location Reference (20), Serial Number (19)

SGLN-96

8

Filter Value (3), Partition (3), Company Prefix (20-40), Location Reference (21-1), Serial Number (41)

GRAI-64

8

Filter Value (3), Company Prefix Index (14), Asset Type (20), Serial Number (19)

GRAI-96

8

Filter Value (3), Partition (3), Company Prefix (20-40), Asset Type (24-4), Serial Number (38)

GIAI-64

8

Filter Value (3), Company Prefix Index (14), Individual Asset Reference (39)

GIAI-96

8

Filter Value (3), Partition (3), Company Prefix (20-40), Individual Asset Reference (62-42)

USDOD-64

8

Filter Value (2), Government Managed Identifier (30), Serial Number (24)

USDOD-96

8

Filter Value (4), Government Managed Identifier (48), Serial Number (36)


EPCglobal defines eleven tag schemes (GID-96, SGTIN-64, SGTIN-96, and so on). Each of these schemes has various representations; today, the most often used are BINARY, TAG_URI, and PURE_IDENTITY. For example, information in an SGTIN-64 can be represented in these ways:

BINARY: 1001100000000000001000001110110001000010000011111110011000110010
PURE_IDENTITY:  urn:epc:id:sgtin:0037000.030241.1041970
TAG_URI: urn:epc:tag:sgtin-64:3.0037000.030241.1041970
LEGACY: gtin=00037000302414;serial=1041970
ONS_HOSTNAME: 030241.0037000.sgtin.id.example.com

Some representations contain all information about the tag (BINARY and TAG_URI), while other representations contain partial information (PURE_IDENTITY). It is therefore possible to translate a tag from its TAG_URI to its PURE_IDENTITY representation, but it is not possible to translate in the other direction without more information being provided, namely the filter value must be supplied.

EPCglobal released a Tag Data Translation 1.0 (TDT) standard that defines how to decode, encode, and translate between various EPC RFID tag representations. Decoding refers to parsing a given representation into field/value pairs, and encoding refers to reconstructing representations from these fields. Translating refers to decoding one representation and instantly encoding it into another.TDT defines this information using a set of XML files, each referred to as a scheme. For example, the SGTIN-64 scheme defines how to decode, encode, and translate between various SGTIN-64 representations, such as binary and pure identity. For details about the EPCglobal TDT schema, see the EPCglobal Tag Data Translation specification.

A key feature of the TDT specification is its ability to define any EPC scheme using the same XML schema. This approach creates a standard way of defining EPC metadata that RFID applications can then use to write their parsers, encoders, and translators. When the application is written according to the TDT specification, it must be able to update its set of EPC tag schemes and modify its action according to the metadata.

The Oracle Database metadata structure is similar, but not identical to the TDT standard. To fit the EPCglobal TDT specification, the Oracle RFID package must be able to ingest any TDT compatible scheme and seamlessly translate it into the generic Oracle Database defined metadata. See the EPC_TO_ORACLE Function in Table 22-4 for more information.

Reconstructing tag representation from fields, or in other words, encoding tag data into predefined representations is easily accomplished using the MGD_ID.format function. Likewise, the decoding of tag representations into MGD_ID objects and then encoding these objects into tag representations is also easily accomplished using the MGDID.translate function. See the FORMAT Member Function and the TRANSLATE Static Function in Table 22-3 for more information.

Because the EPCglobal TDT standard is powerful and highly extensible, the Oracle RFID standard metadata is a close relative of the TDT specification. See Section 22.8 for the actual Oracle Database TDT XML schema. Developers can refer to this Oracle Database TDT XML schema to define their own tag structures.

Figure 22-2 shows the Oracle Database Tag Data Translation Markup Language Schema diagram.

Figure 22-2 Oracle Database Tag Data Translation Markup Language Schema

Description of Figure 22-2 follows

The top level element in a tag data translation xml is 'scheme'. Each scheme defines various tag encoding representations, or levels. SGTIN-64 and GID-96 are examples of tag encoding schemes, and BINARY or PURE_IDENTITY are examples of levels within these schemes. Each level has a set of options that define how to parse various representations into fields, and rules that define how to derive values for fields that require additional work, such as an external table lookup or the concatenation of other parsed fields. See the EPCGlobal Tag Translator Specification for more information.

22.2 What is the Identity Code Package?

The Identity Code Package provides an extensible framework that supports the current RFID tags with the standard family of EPC bit encodings for the supported encoding types and new and evolving tag encodings that are not included in the current EPC standard.

The Identity Code Package defines these ADTs:

  • MGD_ID -- defines these (see MGD_ID ADT in Table 22-2 for more information):

    • Two attributes, category_id and components.

    • Four MGD_ID constructor functions for constructing identity code type objects to represent RFID tags.

    • A set of member subprograms for operating on these ADTs.

    Section 22.3 describes how to use these ADTs and member functions.

    Section 22.4 and Section 22.5 briefly describe the reference information for these ADTs along with a set of utility subprograms. See Oracle Database PL/SQL Packages and Types Reference for detailed reference information.

  • MGD_ID_COMPONENT — defines two attributes, comp_name, which identifies the name of the component and comp_value, which identifies the components value.

  • MGD_ID_COMPONENT_VARRAY — defines an array type that can store up to 128 elements of MGD_IDCOMPONENT type, which is used in two constructor functions for creating an identity code type object with a list of components.

The Identity Code Package supports EPC spec v1.1 by supplying the predefined EPC_ENCODING_CATEGORY encoding_category attribute definition with its bit-encoding structures for the supported encoding types. This information is stored as meta information in the supplied encoding metadata views, MGD_USR_ID_CATEGORY, MGD_USR_ID_SCHEME, the read-only views MGD_ID_CATEGORY, MGD_ID_SCHEME, and their underlying tables: MGD_ID_CATEGORY_TAB, MGD_ID_SCHEME_TAB, MGD_ID_XML_VALIDATOR. See these topics and files for more information:

  • Section 22.7 describes the EPC spec v1.1 product code and its family of coding schemes.

  • Section 22.6 describes the structure of the identity code meta tables and views and how metadata are used by the Identity Code Package to interpret the various RFID tags.

  • The mgdmeta.sql file describes the meta table data for the EPC_ENCODING_CATEGORY categories and each of its specific encoding schemes.

After storing many thousands of RFID tags into the column of MGD_ID column type of your user table, you can improve query performance by creating an index on this column. See these topics for more information:

  • Section 22.3.2 describes how to create a function based index or bitmap function based index using the member functions of the MGD_ID ADT.

The Identity Code Package provides a utility package that consists of various utility subprograms. See this topic for more information:

  • Section 22.4 and Section 22.5 describes each of the member subprograms. A proxy utility sets and removes proxy information. A metadata utility gets a category ID, refreshes a tag scheme for a category, removes a tag scheme for a category, and validates a tag scheme. A conversion utility translates standard EPCglobal Tag Data Translation (TDT) files into Oracle Database TDT files.

The Identity Code Package is extensible and lets you create your own identity code types for your new or evolving RFID tags. You can define your identity code types, catagory_id attribute values, and components structures for your own encoding types. See these topics for more information:

  • Section 22.3.4.1 describes how to create your own identity codes by first registering the encoding category, and then registering the schemes associated to the encoding category.

  • Section 22.6 describes the structure of the identity code meta tables and views and how to register meta information by storing it in the supplied metadata tables and views.

22.3 Using the Identity Code Package

Topics:

22.3.1 Storing RFID Tags in Oracle Database Using MGD_ID ADT

Topics:

22.3.1.1 Creating a Table with MGD_ID Column Type and Storing EPC Tag Encodings in the Column

You can create tables using MGD_ID as the column type to represent RFID tags, for example:

Example 1. Using the MGD_ID column type:

CREATE TABLE Warehouse_info (
             Code          MGD_ID,
             Arrival_time  TIMESTAMP, 
             Location      VARCHAR2(256);
             ...); 

SQL*Plus command:

describe warehouse_info;

Result:

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
CODE                                      NOT NULL MGDSYS.MGD_ID
ARRIVAL_TIME                                       TIMESTAMP(6)
LOCATION                                           VARCHAR2(256)

22.3.1.2 Constructing MGD_ID Objects to Represent RFID Tags

There are several ways to construct MGD_ID objects:

22.3.1.2.1 Constructing an MGD_ID Object (SGTIN-64) Passing in the Category ID and a List of Components

If a RFID tag complies to the EPC standard, an MGD_ID object can be created using its category ID and a list of components. For example:

call DBMS_MGD_ID_UTL.set_proxy('example.com', '80');
call DBMS_MGD_ID_UTL.refresh_category('1');
select MGD_ID ('1', 
               MGD_ID_COMPONENT_VARRAY(
               MGD_ID_COMPONENT('companyprefix','0037000'),
               MGD_ID_COMPONENT('itemref','030241'),
               MGD_ID_COMPONENT('serial','1041970'),
               MGD_ID_COMPONENT('schemes','SGTIN-64')
              )
             ) from DUAL;
call DBMS_MGD_ID_UTL.remove_proxy();

@constructor11.sql
.
.
.
MGD_ID ('1', MGD_ID_COMPONENT_VARRAY
        (MGD_ID_COMPONENT('companyprefix', '0037000'),
        MGD_ID_COMPONENT('itemref', '030241'), 
        MGD_ID_COMPONENT('serial', '1041970'),
        MGD_ID_COMPONENT('schemes', 'SGTIN-64')))
.
.
.
22.3.1.2.2 Constructing an MGD_ID object (SGTIN-64) and Passing in the Category ID, the Tag Identifier, and the List of Additional Required Parameters

Use this constructor when there is a list of additional parameters required to create the MGD_ID object. For example:

call DBMS_MGD_ID_UTL.set_proxy('example.com', '80');
call DBMS_MGD_ID_UTL.refresh_category('1');
select MGD_ID('1', 
              'urn:epc:id:sgtin:0037000.030241.1041970', 
              'filter=3;scheme=SGTIN-64') from DUAL;
call DBMS_MGD_ID_UTL.remove_proxy();


@constructor22.sql
.
.
.
MGD_ID('1', MGD_ID_COMPONENT_VARRAY(MGD_ID_COMPONENT('filter', '3'), 
       MGD_ID_COMPONENT('schemes', 'SGTIN-64'), 
       MGD_ID_COMPONENT('companyprefixlength', '7'), 
       MGD_ID_COMPONENT('companyprefix', '0037000'), 
       MGD_ID_COMPONENT('scheme', 'SGTIN-64'), 
       MGD_ID_COMPONENT('serial', '1041970'), 
       MGD_ID_COMPONENT('itemref', '030241')))
.
.
.
22.3.1.2.3 Constructing an MGD_ID object (SGTIN-64) and Passing in the Category Name, Category Version (if null, then the latest version is used), and a List of Components

Use this constructor when a category version must be specified along with a category ID and a list of components. For example:

call DBMS_MGD_ID_UTL.set_proxy('example.com', '80');
call DBMS_MGD_ID_UTL.refresh_category
  (DBMS_MGD_ID_UTL.get_category_id('EPC', NULL));
select MGD_ID('EPC', NULL, 
              MGD_ID_COMPONENT_VARRAY(
              MGD_ID_COMPONENT('companyprefix','0037000'),
              MGD_ID_COMPONENT('itemref','030241'),
              MGD_ID_COMPONENT('serial','1041970'),
              MGD_ID_COMPONENT('schemes','SGTIN-64')
             )
            ) from DUAL;
call DBMS_MGD_ID_UTL.remove_proxy();

@constructor33.sql
.
.
.
MGD_ID('1', MGD_ID_COMPONENT_VARRAY
             (MGD_ID_COMPONENT('companyprefix', '0037000'),
              MGD_ID_COMPONENT('itemref', '030241'), 
              MGD_ID_COMPONENT('serial', '1041970'),
              MGD_ID_COMPONENT('schemes', 'SGTIN-64')
             )
       )
.
.
.
22.3.1.2.4 Constructing an MGD_ID object (SGTIN-64) and Passing in the Category Name and Category Version, the Tag Identifier, and the List of Additional Required Parameters

Use this constructor when the category version and an additional list of parameters is required.

call DBMS_MGD_ID_UTL.set_proxy('example.com', '80');
call DBMS_MGD_ID_UTL.refresh_category
  (DBMS_MGD_ID_UTL.get_category_id('EPC', NULL));
select MGD_ID('EPC', NULL,
              'urn:epc:id:sgtin:0037000.030241.1041970', 
              'filter=3;scheme=SGTIN-64') from DUAL;
call DBMS_MGD_ID_UTL.remove_proxy();

@constructor44.sql
.
.
.
MGD_ID('1', MGD_ID_COMPONENT_VARRAY
       (MGD_ID_COMPONENT('filter', '3'),
        MGD_ID_COMPONENT('schemes', 'SGTIN-64'), 
        MGD_ID_COMPONENT('companyprefixlength', '7'), 
        MGD_ID_COMPONENT('companyprefix', '0037000'), 
        MGD_ID_COMPONENT('scheme', 'SGTIN-64'), 
        MGD_ID_COMPONENT('serial', '1041970'), 
        MGD_ID_COMPONENT('itemref', '030241')
       )
      )
.
.
.

22.3.1.3 Inserting an MGD_ID Object into a Database Table

This example shows how to populate the WAREHOUSE_INFO table by inserting each MGD_ID object into the table along with the additional column values:

call DBMS_MGD_ID_UTL.set_proxy('example.com', '80');

call DBMS_MGD_ID_UTL.refresh_category
  (DBMS_MGD_ID_UTL.get_category_id('EPC', NULL));

INSERT INTO WAREHOUSE_INFO (code, arrival_time, location)
   values (MGDSYS.MGD_ID ('EPC',
                          NULL,
                          'urn:epc:id:sgtin:0037000.030241.1041970',
                          null
                         ),
           SYSDATE,
           'SHELF_123');

INSERT INTO WAREHOUSE_INFO (code, arrival_time, location)
  values (MGDSYS.MGD_ID ('EPC',
                         NULL,
                         'urn:epc:id:sgtin:0037000.053021.1012353',
                         null
                        ),
          SYSDATE,
          'SHELF_456');
INSERT INTO WAREHOUSE_INFO (code, arrival_time, location)
  values (MGDSYS.MGD_ID ('EPC',
                         NULL,
                         'urn:epc:id:sgtin:0037000.020140.10174832',
                         null
                        ),
          SYSDATE,
          'SHELF_1034');

COMMITT;
call DBMS_MGD_ID_UTL.remove_proxy();

22.3.1.4 Querying MGD_ID Column Type

There are three ways to query on MGD_ID column type.

  • Query the MGD_ID column type. Find all items with item reference 030241.

    SELECT location, wi.code.get_component('itemref') as itemref, 
                     wi.code.get_component('serial') as serial 
    FROM warehouse_info wi WHERE wi.code.get_component('itemref') = '030241';
    
    LOCATION       |ITEMREF   |SERIAL
    ---------------|----------|----------
    SHELF_123      |030241    |1041970
    
  • Query using the member functions of the MGD_ID ADT. Select the pure identity representations of all RFID tags in the table.

    SELECT wi.code.format(null,'PURE_IDENTITY')
       as PURE_IDENTITY FROM warehouse_info wi;
    
    PURE_IDENTITY
    -------------------------------------------------------------------------------
    urn:epc:id:sgtin:0037000.030241.1041970
    urn:epc:id:gid:0037000.053021.1012353
    urn:epc:id:sgtin:0037000.020140.10174832
    

    See Section 22.3.3.1 for more information and see Table 22-3 for a list of member functions.

22.3.2 Building a Function-Based Index Using the Member Functions of the MGD_ID Column Type

You can improve the performance of queries based on a certain component of the RFID tags by creating a function-based index that uses the get_component member function or its variation convenience functions. For example:

CREATE INDEX warehouseinfo_idx2
  on warehouse_info(code.get_component('itemref'));

You can also improve the performance of queries based on a certain component of the RFID tags by creating a bitmap function based index that uses the get_component member function or its variation convenience functions. For example:

CREATE BITMAP INDEX warehouseinfo_idx3
  on warehouse_info(code.get_component('serial'));

22.3.3 Using MGD_ID ADT Functions

The MGD_ID ADT contains member subprograms that operate on these ADTs. See Table 22-2 for MGD_ID_COMPONENT, MGD_ID_COMPONENT_VARRAY, MGD_ID ADT reference information. See the mgdtyp.sql file for the MGD_ID ADT definition and its member subprograms.

Topics:

22.3.3.1 Using the get_component Function with the MGD_ID Object

The get_component function is defined as follows:

MEMBER FUNCTION get_component(component_name IN VARCHAR2)
   RETURN VARCHAR2 DETERMINISTIC,

Each component in a identity code has a name. It is defined when the code type is registered. See Section 22.3.4 for more information about how to create a identity code type.

The get_component function takes the name of the component, component_name as a parameter, uses the metadata registered in the metadata table to analyze the identity code, and returns the component with the name component_name.

The get_component function can be used in a SQL query. For example, find the current location of the coded item for the component named itemref; or, in other words find all items with the item reference of 03024. Because the code tag has encoded itemref as a component, you can use this SQL query:

SELECT location,
       w.code.get_component('itemref') as itemref,
       w.code.get_component('serial')  as serial
FROM   warehouse_info w
       WHERE  w.code.get_component('itemref')  = '030241';

LOCATION       |ITEMREF   |SERIAL
---------------|----------|----------
SHELF_123      |030241    |1041970

See Table 22-3 for a list of other member functions.

22.3.3.2 Parsing Tag Data from Standard Representations

RFID readers read the bit strings stored in the tags. The tag data and other information, such as the reader ID and the time stamp, first go through an edge server to be processed, normalized, and preliminarily filtered. Then, in many application scenarios, the information must be persistently stored and later on be retrieved. The Oracle Database understands the code structures representations of various EPC tags as described in Table 22-1 because these code representation schemes defined in the EPC Standard are preregistered. This gives the Oracle Database the ability to understand all the EPC code schemes and parse various tag representations into fields. Users can also register their own coding structures for the identity codes that use other encoding technologies. In this way the system is extensible.

As mentioned in Section 22.1, each of the EPCGlobal tag schemes (GID-96, SGTIN-64, SGTIN-96, and so on) has various representations with the most often used being BINARY, TAG_URI, and PURE_IDENTITY.

Some representations contain all the information about the tag (BINARY and TAG_URI), while representations contain partial information (PURE_IDENTITY). It is therefore possible to translate a tag from it's TAG_URI to it's PURE_IDENTITY representation, but it is not possible to translate in the other direction (PURE_IDENTITY to TAG_URI) without supplying more information, namely the filter value.

One MGD_ID constructor takes in four fields, the category name (such as EPC), the category version, the tag identifier (for EPC, the identifier must be in a representation previously described), and a parameter list for any additional parameters required to parse the tag representation. For example, this code creates an MGD_ID object from its BINARY representation.

SELECT MGD_ID 
   ('EPC',
    null,
    '1001100000000000001000001110110001000010000011111110011000110010',
    null
   )
   AS NEW_RFID_CODE FROM DUAL;

NEW_RFID_CODE(CATEGORY_ID, COMPONENTS(NAME, VALUE))
--------------------------------------------------------------------------------
MGD_ID ('1', 
        MGD_ID_COMPONENT_VARRAY(MGD_ID_COMPONENT('filter', '3'), 
        MGD_ID_COMPONENT('schemes', 'SGTIN-64'), 
        MGD_ID_COMPONENT('companyprefixlength', '7'), 
        MGD_ID_COMPONENT('companyprefix', '0037000'), 
        MGD_ID_COMPONENT('companyprefixindex', '1'), 
        MGD_ID_COMPONENT('serial', '1041970'), 
        MGD_ID_COMPONENT('itemref', '030241')
       )
      )

For example, an identical object can be created if the call is done with the TAG_URI representation of the tag as follows with the addition of the value of the filter value:

SELECT MGD_ID ('EPC',
                null,
                'urn:epc:tag:sgtin-64:3.0037000.030241.1041970',
                null
              )
  as NEW_RFID_CODE FROM DUAL;

NEW_RFID_CODE(CATEGORY_ID, COMPONENTS(NAME, VALUE))
--------------------------------------------------------------------------------
MGD_ID ('1', 
        MGD_ID_COMPONENT_VARRAY (
         ( MGD_ID_COMPONENT('filter', '3'), 
           MGD_ID_COMPONENT('schemes', 'SGTIN-64'), 
           MGD_ID_COMPONENT('companyprefixlength', '7'), 
           MGD_ID_COMPONENT('companyprefix', '0037000'), 
           MGD_ID_COMPONENT('serial', '1041970'), 
           MGD_ID_COMPONENT('itemref', '030241')
         )
       )

22.3.3.3 Reconstructing Tag Representations from Fields

Another useful feature of the Identity Code package is the ability to encode tag data into predefined representations. For example, a warehouse wants to send certain inventory to a retailer, but first it wants to send an invoice that tells the retailer what inventory to expect. The invoice can be a list of pure identity URIs that the warehouse intends to send. If all the inventory in the WAREHOUSE_INFO table is to be sent, this example constructs the desired URIs:

SELECT wi.code.format (null,'PURE_IDENTITY') 
  as PURE_IDENTITY FROM warehouse_info wi;

PURE_IDENTITY
--------------------------------------------------------------------------------
urn:epc:id:sgtin:0037000.030241.1041970
urn:epc:id:gid:0037000.053021.1012353
urn:epc:id:sgtin:0037000.020140.10174832

22.3.3.4 Translating Between Tag Representations

The Identity Code package can decode tag representations into MGD_ID objects and encode these objects into tag representations. These two steps can be combined into one step using the MGD_ID.translate function. Static translation allows for the conversion of an RFID tag from one representation to another. For example:

SELECT MGD_ID.translate ('EPC',
                         null,
                         'urn:epc:id:sgtin:0037000.030241.1041970',
                         'filter=3;scheme=SGTIN-64',
                         'BINARY'
                        )
  as BINARY FROM DUAL;

BINARY
--------------------------------------------------------------------------------
1001100000000000001000001110110001000010000011111110011000110010

In this example, the binary representation contains more information than the pure identity representation. Specifically, it also contains the filter value and in this case the scheme value must also be specified to distinguish SGTIN-64 from SGTIN-96. Thus, the function call must provide the missing filter parameter information and specify the scheme name in order for translation call to succeed.

22.3.4 Defining a Category of Identity Codes and Adding Encoding Schemes to an Existing Category

Topics:

22.3.4.1 Creating a Category of Identity Codes

Because the EPCglobal TDT standard is powerful and highly extensible, the Oracle Database RFID standard metadata is a close relative of the TDT specification. Thus, the Identity Code package is extensible: You can create your own categories and tag structures using generic metadata. To create a category of identity codes, use the DBMS_MGD_ID_UTIL.create_category function.

For example, suppose you want to create a category called MGD_SAMPLE_CATEGORY, which has two types of tags, a CONTRACTOR_TAG and an EMPLOYEE_TAG. This category and its two metadata schemes might be used within a company that must grant different access privileges to people who are full time employees from those who are contractors, and thus require that their security software be able to identify quickly between the two badge types at an RFID reader. This script creates a category named MGD_SAMPLE_CATEGORY, with a 1.0 category version, having an agency name as Oracle, with a URI as http://www.oracle.com/mgd/sample. See Section 22.3.4.2 for an example.

22.3.4.2 Adding Two Metadata Schemes to a Newly Created Category

Next, create an CONTRACTOR_TAG metadata scheme such as:

<?xml version="1.0" encoding="UTF-8"?>
<TagDataTranslation version="0.04" date="2005-04-18T16:05:00Z" 
                    xmlns:xsi="http://www.w3.org/2001/XMLSchema" 
                    xmlns="oracle.mgd.idcode">
 <scheme name="CONTRACTOR_TAG" optionKey="1" xmlns="">
  <level type="URI" prefixMatch="mycompany.contractor.">
   <option optionKey="1" pattern="mycompany.contractor.([0-9]*).([0-9]*)" 
           grammar="''mycompany.contractor.'' contractorID ''.'' divisionID">
    <field seq="1" characterSet="[0-9]*" name="contractorID"/>
    <field seq="2" characterSet="[0-9]*" name="divisionID"/>
   </option>
  </level>
  <level type="BINARY" prefixMatch="11">
   <option optionKey="1" pattern="11([01]{7})([01]{6})" 
           grammar="''11'' contractorID divisionID ">
    <field seq="1" characterSet="[01]*" name="contractorID"/>
    <field seq="2" characterSet="[01]*" name="divisionID"/>
   </option>
  </level>
 </scheme>
</TagDataTranslation>

The CONTRACTOR_TAG scheme contains two encoding levels, or ways in which the tag can be represented. The first level is URI and the second level is BINARY. The URI representation starts with the prefix "mycompany.contractor." and is then followed by two numeric fields separated by a period. The names of the two fields are contractorID and divisionID. The pattern field in the option tag defines the parsing structure of the tag URI representation, and the grammar field defines how to reconstruct the URI representation. The BINARY representation can be understood in a similar fashion. This representation starts with the prefix "01" and is then followed by the same two fields, contractorID and divisionID, this time, in their respective binary formats. Given this XML metadata structure, contractor tags can now be decoded from their URI and BINARY representations and the resulting fields can be re-encoded into one of these representations.

The EMPLOYEE_TAG scheme is defined in a similar fashion and is shown as follows.

<?xml version="1.0" encoding="UTF-8"?>
<TagDataTranslation version="0.04" date="2005-04-18T16:05:00Z" 
                    xmlns:xsi="http://www.w3.org/2001/XMLSchema" 
                    xmlns="oracle.mgd.idcode">
 <scheme name="EMPLOYEE_TAG" optionKey="1" xmlns="">
  <level type="URI" prefixMatch="mycompany.employee.">
   <option optionKey="1" pattern="mycompany.employee.([0-9]*).([0-9]*)" 
           grammar="''mycompany.employee.'' employeeID ''.'' divisionID">
    <field seq="1" characterSet="[0-9]*" name="employeeID"/>
    <field seq="2" characterSet="[0-9]*" name="divisionID"/>
   </option>
  </level>
  <level type="BINARY" prefixMatch="01">
   <option optionKey="1" pattern="01([01]{7})([01]{6})" 
           grammar="''01'' employeeID divisionID ">
    <field seq="1" characterSet="[01]*" name="employeeID"/>
    <field seq="2" characterSet="[01]*" name="divisionID"/>
   </option>
  </level>
 </scheme>
</TagDataTranslation>;

To add these schemes to the category ID previously created, use the DBMS_MGD_ID_UTIL.add_scheme function.

This script creates the MGD_SAMPLE_CATEGORY category, adds a contractor scheme and an employee scheme to the MGD_SAMPLE_CATEGORY category, validates the MGD_SAMPLE_CATEGORY scheme, tests the tag translation of the contractor scheme and the employee scheme, then removes the contractor scheme, tests the tag translation of the contractor scheme and this returns the expected exception for the removed contractor scheme, tests the tag translation of the employee scheme and this returns the expected values, then removes the MGD_SAMPLE_CATEGORY category:

--contents of add_scheme2.sql
SET LINESIZE 160
CALL DBMS_MGD_ID_UTL.set_proxy('example.com', '80');
---------------------------------------------------------------------
---CREATE CATEGORY, ADD_SCHEME, REMOVE_SCHEME, REMOVE_CATEGORY-------
---------------------------------------------------------------------
DECLARE
  amt          NUMBER;
  buf          VARCHAR2(32767);
  pos          NUMBER;
  tdt_xml      CLOB;
  validate_tdtxml VARCHAR2(1042);
  category_id  VARCHAR2(256);
BEGIN
  -- remove the testing category if it exists
  DBMS_MGD_ID_UTL.remove_category('MGD_SAMPLE_CATEGORY', '1.0');
  -- create the testing category 'MGD_SAMPLE_CATEGORY', version 1.0
  category_id := DBMS_MGD_ID_UTL.CREATE_CATEGORY('MGD_SAMPLE_CATEGORY', '1.0', 'Oracle', 
'http://www.oracle.com/mgd/sample');
  -- add contractor scheme to the category
  DBMS_LOB.CREATETEMPORARY(tdt_xml, true);
  DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE);
 
  buf := '<?xml version="1.0" encoding="UTF-8"?>
<TagDataTranslation version="0.04" date="2005-04-18T16:05:00Z" 
                    xmlns:xsi="http://www.w3.org/2001/XMLSchema" 
                    xmlns="oracle.mgd.idcode">
 <scheme name="CONTRACTOR_TAG" optionKey="1" xmlns="">
  <level type="URI" prefixMatch="mycompany.contractor.">
   <option optionKey="1" pattern="mycompany.contractor.([0-9]*).([0-9]*)" 
           grammar="''mycompany.contractor.'' contractorID ''.'' divisionID">
    <field seq="1" characterSet="[0-9]*" name="contractorID"/>
    <field seq="2" characterSet="[0-9]*" name="divisionID"/>
   </option>
  </level>
  <level type="BINARY" prefixMatch="11">
   <option optionKey="1" pattern="11([01]{7})([01]{6})" 
           grammar="''11'' contractorID divisionID ">
    <field seq="1" characterSet="[01]*" name="contractorID"/>
    <field seq="2" characterSet="[01]*" name="divisionID"/>
   </option>
  </level>
 </scheme>
</TagDataTranslation>';

  amt := length(buf);
  pos := 1;
  DBMS_LOB.WRITE(tdt_xml, amt, pos, buf);
  DBMS_LOB.CLOSE(tdt_xml);

  DBMS_MGD_ID_UTL.ADD_SCHEME(category_id, tdt_xml);

  -- add employee scheme to the category
  DBMS_LOB.CREATETEMPORARY(tdt_xml, true);
  DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE);
 
  buf := '<?xml version="1.0" encoding="UTF-8"?>
<TagDataTranslation version="0.04" date="2005-04-18T16:05:00Z" 
                    xmlns:xsi="http://www.w3.org/2001/XMLSchema" 
                    xmlns="oracle.mgd.idcode">
 <scheme name="EMPLOYEE_TAG" optionKey="1" xmlns="">
  <level type="URI" prefixMatch="mycompany.employee.">
   <option optionKey="1" pattern="mycompany.employee.([0-9]*).([0-9]*)" 
           grammar="''mycompany.employee.'' employeeID ''.'' divisionID">
    <field seq="1" characterSet="[0-9]*" name="employeeID"/>
    <field seq="2" characterSet="[0-9]*" name="divisionID"/>
   </option>
  </level>
  <level type="BINARY" prefixMatch="01">
   <option optionKey="1" pattern="01([01]{7})([01]{6})" 
           grammar="''01'' employeeID divisionID ">
    <field seq="1" characterSet="[01]*" name="employeeID"/>
    <field seq="2" characterSet="[01]*" name="divisionID"/>
   </option>
  </level>
 </scheme>
</TagDataTranslation>';

  amt := length(buf);
  pos := 1;
  DBMS_LOB.WRITE(tdt_xml, amt, pos, buf);
  DBMS_LOB.CLOSE(tdt_xml);
  DBMS_MGD_ID_UTL.ADD_SCHEME(category_id, tdt_xml);

  -- validate the scheme
  dbms_output.put_line('Validate the MGD_SAMPLE_CATEGORY Scheme');
  validate_tdtxml := DBMS_MGD_ID_UTL.validate_scheme(tdt_xml);
  dbms_output.put_line(validate_tdtxml);
  dbms_output.put_line('Length of scheme xml is: '||DBMS_LOB.GETLENGTH(tdt_xml));

  -- test tag translation of contractor scheme
  dbms_output.put_line(
    mgd_id.translate('MGD_SAMPLE_CATEGORY', NULL, 
                     'mycompany.contractor.123.45', 
                     NULL, 'BINARY'));

  dbms_output.put_line(
    mgd_id.translate('MGD_SAMPLE_CATEGORY', NULL, 
                     '111111011101101',
                     NULL, 'URI'));

  -- test tag translation of employee scheme
  dbms_output.put_line(
    mgd_id.translate('MGD_SAMPLE_CATEGORY', NULL, 
                     'mycompany.employee.123.45', 
                     NULL, 'BINARY'));

  dbms_output.put_line(
    mgd_id.translate('MGD_SAMPLE_CATEGORY', NULL, 
                     '011111011101101',
                     NULL, 'URI'));

  DBMS_MGD_ID_UTL.REMOVE_SCHEME(category_id, 'CONTRACTOR_TAG');

  -- Test tag translation of contractor scheme. Doesn't work any more.
  BEGIN
    dbms_output.put_line(
      mgd_id.translate('MGD_SAMPLE_CATEGORY', NULL, 
                       'mycompany.contractor.123.45', 
                       NULL, 'BINARY'));

    dbms_output.put_line(
      mgd_id.translate('MGD_SAMPLE_CATEGORY', NULL, 
                       '111111011101101',
                       NULL, 'URI'));
  EXCEPTION 
    WHEN others THEN
      dbms_output.put_line('Contractor tag translation failed: '||SQLERRM);
  END;

  -- Test tag translation of employee scheme. Still works.
  BEGIN
    dbms_output.put_line(
      mgd_id.translate('MGD_SAMPLE_CATEGORY', NULL, 
                       'mycompany.employee.123.45', 
                       NULL, 'BINARY'));
    dbms_output.put_line(
      mgd_id.translate('MGD_SAMPLE_CATEGORY', NULL, 
                       '011111011101101',
                       NULL, 'URI'));
  EXCEPTION 
    WHEN others THEN
      dbms_output.put_line('Employee tag translation failed: '||SQLERRM);
  END;

  -- remove the testing category, which also removes all the associated schemes
  DBMS_MGD_ID_UTL.remove_category('MGD_SAMPLE_CATEGORY', '1.0');
END;
/
SHOW ERRORS;
call DBMS_MGD_ID_UTL.remove_proxy();

@add_scheme3.sql
.
.
.
Validate the MGD_SAMPLE_CATEGORY Scheme
EMPLOYEE_TAG;URI,BINARY;divisionID,employeeID
Length of scheme xml is: 933
111111011101101
mycompany.contractor.123.45
011111011101101
mycompany.employee.123.45
Contractor tag translation failed: ORA-55203: Tag data translation level not found
ORA-06512: at "MGDSYS.DBMS_MGD_ID_UTL", line 54
ORA-06512: at "MGDSYS.MGD_ID", line 242
ORA-29532: Java call terminated by uncaught Java
exception: oracle.mgd.idcode.exceptions.TDTLevelNotFound: Matching level not
found for any configured scheme
011111011101101
mycompany.employee.123.45
.
.
.

22.4 Identity Code Package Types

Table 22-2 describes the Identity Code Package ADTs.

Table 22-2 Identity Code Package ADTs

ADT NameDescription

MGD_ID_COMPONENT ADT

A data type that specifies the name and value pair attributes that define a component.

MGD_ID_COMPONENT_VARRAY ADT

A data type that specifies a list of up to 128 components as name-value attribute pairs used in two constructor functions for creating an identity code type object.

MGD_ID ADT

Represents an identity code type that specifies the category identifier for the code category for this identity code and its list of components.


Table 22-3 describes the subprograms in the MGD_ID ADT.

All the values and names passed to the subprograms defined in the MGD_ID ADT are case-insensitive unless otherwise noted. To preserve case, enclose values in double quotation marks.

Table 22-3 MGD_ID ADT Subprograms

SubprogramDescription

MGD_ID Constructor Function

Creates an identity code type object, MGD_ID, and returns self.

FORMAT Member Function

Returns a representation of an identity code given an MGD_ID component.

GET_COMPONENT Member Function

Returns the value of an MGD_ID component.

TO_STRING Member Function

Concatenates the category_id parameter value with the components name-value attribute pair.

TRANSLATE Static Function

Translates one MGD_ID representation of an identity code into a different MGD_ID representation.


22.5 DBMS_MGD_ID_UTL Package

Table 22-4 describes the Utility subprograms in the DBMS_MGD_ID_UTL package.

All the values and names passed to the subprograms defined in the MGD_ID ADT are case-insensitive unless otherwise noted. To preserve case, enclose values in double quotation marks.

Table 22-4 DBMS_MGD_ID_UTL Package Utility Subprograms

SubprogramDescription

ADD_SCHEME Procedure

Adds a tag data translation scheme to an existing category.

CREATE_CATEGORY Function

Creates a category or a version of a category.

EPC_TO_ORACLE Function

Converts the EPCglobal tag data translation (TDT) XML to Oracle Database tag data translation XML.

GET_CATEGORY_ID Function

Returns the category ID given the category name and the category version.

GET_COMPONENTS Function

Returns all relevant separated component names separated by semicolon (';') for the specified scheme.

GET_ENCODINGS Function

Returns a list of semicolon (';') separated encodings (formats) for the specified scheme.

GET_JAVA_LOGGING_LEVEL Function

Returns an integer representing the current Java trace logging level.

GET_PLSQL_LOGGING_LEVEL Function

Returns an integer representing the current PL/SQL trace logging level.

GET_SCHEME_NAMES Function

Returns a list of semicolon (';') separated scheme names for the specified category.

GET_TDT_XML Function

Returns the Oracle Database tag data translation XML for the specified scheme.

GET_VALIDATOR Function

Returns the Oracle Database tag data translation schema.

REFRESH_CATEGORY Function

Refreshes the metadata information about the Java stack for the specified category.

REMOVE_CATEORY Function

Removes a category including all the related TDT XML.

REMOVE_PROXY Procedure

Unsets the host and port of the proxy server.

REMOVE_SCHEME Procedure

Removes the tag scheme for a category.

SET_JAVA_LOGGING_LEVEL Procedure

Sets the Java logging level.

SET_PLSQL_LOGGING_LEVEL Procedure

Sets the PL/SQL tracing logging level.

SET_PROXY Procedure

Sets the host and port of the proxy server for Internet access.

VALIDATE_SCHEME Function

Validates the input tag data translation XML against the Oracle Database tag data translation schema.


22.6 Identity Code Metadata Tables and Views

This topic describes the structure of identity code metadata tables and views and explains how the metadata are used by the Identity Code Package to interpret the various RFID tags. The creation of these meta tables, views, and triggers is done automatically during the Identity Code Package installation.

Encoding metadata views are used to store encoding categories and schemes. Application developers can insert the meta information of their own identity codes into these views. The MGD_ID ADT is designed to understand the encodings if the metadata for the encodings are stored in the meta tables. If an application developer uses only the encodings defined in the EPC specification v1.1, the developer does not have to worry about the meta tables because product codes specified in EPC spec v1.1 are predefined.

There are two encoding metadata views:

  • user_mgd_id_category stores the encoding category information defined by the session user.

  • user_mgd_id_scheme stores the encoding type information defined by the session user.

You can query the following read-only views to see the system's predefined encoding metadata and the metadata defined by the user:

  • mgd_id_category lets you query the encoding category information defined by the system or the session user

  • mgd_id_scheme lets you query the encoding type information defined by the system or the session user.

The underlying metadata tables for the preceding views are:

  • mgd_id_xml_validator

  • mgd_id_category_tab

  • mgd_id_scheme_tab

Users other than the Identity Code Package system users cannot operate on these tables. Users must not use the metadata tables directly. They must use the read-only views and the metadata functions described in the DBMS_MGD_ID_UTL package.


See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_MGD_ID_UTL package

Metadata View Definitions

Table 22-5, Table 22-6, Table 22-7, and Table 22-8 describe the metadata view definitions for the MGD_ID_CATEGORY, USER_ID_CATEGORY, MGD_ID_SCHME, and USER_MGD_ID_SCHME respectively as defined in the mgdview.sql file.

Table 22-5 Definition and Description of the MGD_ID_CATEGORY Metadata View

Column NameData TypeDescription

CATEGORY_ID

NUMBER(4)

Category identifier

CATEGORY_NAME

VARCHAR2(256)

Category name

AGENCY

VARCHAR2(256)

Organization that defined the category

VERSION

VARCHAR2(256)

Category version

URI

VARCHAR2(256)

URI that describes the category


Table 22-6 Definition and Description of the USER_MGD_ID_CATEGORY Metadata View

Column NameData TypeDescription

CATEGORY_ID

NUMBER(4)

Category identifier

CATEGORY_NAME

VARCHAR2(256)

Category name

AGENCY

VARCHAR2(256)

Organization that defined the category

VERSION

VARCHAR2(256)

Category version

URI

VARCHAR2(256)

URI that describes the category


Table 22-7 Definition and Description of the MGD_ID_SCHEME Metadata View

Column NameData TypeDescription

CATEGORY_ID

NUMBER(4)

Category identifier

TYPE_NAME

VARCHAR2(256)

Encoding scheme name, for example, SGTIN-96, GID-96, and so on

TDT_XML

CLOB

Tag data translation XML for this encoding scheme

ENCODINGS

VARCHAR2(256)

Encodings separated by a comma (,), for example, LEGACY, TAG_ENCODING, PURE_IDENTITY, BINARY (for SGTIN-96)

COMPONENTS

VARCHAR2(1024)

Relevant component names, extracted from each level and then combined. Each is separated by a comma (,). For example, objectclass, generalmanager, serial (for GID-96)


Table 22-8 Definition and Description of the USER_MGD_ID_SCHEME Metadata View

Column NameData TypeDescription

CATEGORY_ID

NUMBER(4)

Category identifier

TYPE_NAME

VARCHAR2(256)

Encoding scheme name, for example, SGTIN-96, GID-96, and so on

TDT_XML

CLOB

Tag data translation XML for this encoding scheme

ENCODINGS

VARCHAR2(256)

Encodings separated by a comma (,), for example, LEGACY, TAG_ENCODING, PURE_IDENTITY, BINARY (for SGTIN-96)

COMPONENTS

VARCHAR2(1024)

Relevant component names, extracted from each level and then combined. Each is separated by a comma (,). For example, objectclass, generalmanager, serial (for GID-96)


22.7 Electronic Product Code (EPC) Concepts

Topics:

22.7.1 RFID Technology and EPC v1.1 Coding Schemes

Radio Frequency Identification (RFID) technology continues to gain momentum with suppliers, distributors, manufacturers, and retailers for its ability to eliminate line-of-site processes and automate critical supply chain transactions. Electronic Product Code (EPC), an identification scheme for universally identifying objects using RFID tags and other means, is gaining widespread acceptance as an emerging standard. Its capabilities enable companies to reduce warehouse and distribution costs through improved inventory control and extended supply chain visibility.

The standardized EPC Identifier is a metacoding scheme designed to support the needs of various industries. Therefore, the EPC represents a family of coding schemes and a means to make them unique across all possible EPC-compliant tags. EPC Version 1.1 includes these specific coding schemes:

  • General Identifier (GID)

  • Serialized version of the EAN.UCC Global Trade Item Number (GTIN)

  • EAN.UCC Serial Shipping Container Code (SSCC)

  • EAN.UCC Global Location Number (GLN)

  • EAN.UCC Global Returnable Asset Identifier (GRAI)

  • EAN.UCC Global Individual Asset Identifier (GIAI)

RFID applications require the storage of a large volume of EPC data into a database. The efficient use of EPC data also requires that the database recognizes the different coding schemes of EPC data.

EPC is an emerging standard. It does not cover all the numbering schemes used in the various industries and is itself still evolving (the changes from EPC version 1.0 to EPC version 1.1 are significant).

Identity Code Package empowers the Oracle Database with the knowledge to recognize EPC coding schemes. It makes the Oracle Database a database system that not only provides efficient storage and component level retrieval for EPC data, but also has features to support EPC data encoding and decoding, and conversion between bit encoding and URI encoding.

Identity Code Package provides an extensible framework that allows developers to define their own coding schemes that are not included in the EPC standard. This extensibility feature also makes the Oracle Database adaptable to the evolving future EPC standard.

This chapter describes the requirement of storing, retrieving, encoding and decoding various product codes, including EPC, in an Oracle Database and shows how the Identity Code Package solution meets all these requirements by providing data types, metadata tables, and PL/SQL packages for these purposes.

22.7.2 Product Code Concepts and Their Current Use

This topic describes these product codes:

22.7.2.1 Electronic Product Code (EPC)

The Electronic Product Code™ (EPC™) is an identification scheme for universally identifying physical objects using Radio Frequency Identification (RFID) tags and other means. The standardized EPC data consists of an EPC (or EPC Identifier) that uniquely identifies an individual object, and an optional Filter Value when judged to be necessary to enable effective and efficient reading of the EPC tags. In addition to this standardized data, certain classes of EPC tags allow user-defined data.

The EPC Identifier is a meta-coding scheme designed to support the needs of various industries by accommodating both existing coding schemes where possible and defining schemes where necessary. The various coding schemes are referred to as Domain Identifiers, to indicate that they provide object identification within certain domains such as a particular industry or group of industries. As such, EPC represents a family of coding schemes (or "namespaces") and a means to make them unique across all possible EPC-compliant tags.

The EPCGlobal EPC Data Standards Version 1.1 defines the abstract content of the Electronic Product Code, and its concrete realization in the form of RFID tags, Internet URIs, and other representations. In EPC Version 1.1, the specific coding schemes include a General Identifier (GID), a serialized version of the EAN.UCC Global Trade Item Number (GTIN®), the EAN.UCC Serial Shipping Container Code (SSCC®), the EAN.UCC Global Location Number (GLN®), the EAN.UCC Global Returnable Asset Identifier (GRAI®), and the EAN.UCC Global Individual Asset Identifier (GIAI®).

22.7.2.1.1 EPC Pure Identity

The EPC pure identity is the identity associated with a specific physical or logical entity, independent of any particular encoding vehicle such as an RF tag, bar code or database field. As such, a pure identity is an abstract name or number used to identify an entity. A pure identity consists of the information required to uniquely identify a specific entity, and no more.

22.7.2.1.2 EPC Encoding

EPC encoding is a pure identity with more information, such as filter value, rendered into a specific syntax (typically consisting of value fields of specific sizes). A given pure identity might have several possible encodings, such as a Barcode Encoding, various Tag Encodings, and various URI Encodings. Encodings can also incorporate additional data besides the identity (such as the Filter Value used in some encodings), in which case the encoding scheme specifies what additional data it can hold.

For example, the Serial Shipping Container Code (SSCC) format as defined by the EAN.UCC System is an example of a pure identity. An SSCC encoded into the EPC- SSCC 96-bit format is an example of an encoding.

22.7.2.1.3 EPC Tag Bit-Level Encoding

EPC encoding on a tag is a string of bits, consisting of a tiered, variable length header followed by a series of numeric fields whose overall length, structure, and function are completely determined by the header value.

22.7.2.1.4 EPC Identity URI

The EPC identity URI is a representation of a pure identity as a Uniform Resource Identifier (URI).

22.7.2.1.5 EPC Tag URI Encoding

The EPC tag URI encoding represents a specific EPC tag bit-level encoding, for example, urn:epc:tag:sgtin-64:3.0652642.800031.400.

22.7.2.1.6 EPC Encoding Procedure

The EPC encoding procedure generates an EPC tag bit-level encoding using various information.

22.7.2.1.7 EPC Decoding Procedure

The EPC decoding procedure converts an EPC tag bit-level encodi%~ng to an EAN.UCC code.

22.7.2.2 Global Trade Identification Number (GTIN) and Serializable Global Trade Identification Number (SGTIN)

A Global Trade Identification Number (GTIN) is used for the unique identification of trade items worldwide within the EAN.UCC system. The Serialized Global Trade Identification Number (SGTIN) is an identity type in EPC standard version1.1. It is based on the EAN.UCC GTIN code defined in the General EAN.UCC Specifications [GenSpec5.0]. A GTIN identifies a particular class of object, such as a particular kind of product or SKU. The combination of GTIN and a unique serial number is called a Serialized GTIN (SGTIN).

22.7.2.3 Serial Shipping Container Code (SSCC)

The Serial Shipping Container Code (SSCC) is defined by the General EAN.UCC Specifications [GenSpec5.0]. The unique identification of logistics units is achieved in the EAN.UCC system by the use of the SSCC. The SSCC is intended for assignment to individual objects.

22.7.2.4 Global Location Number (GLN) and Serializable Global Location Number (SGLN)

The Global Location Number (GLN) is defined by the General EAN.UCC Specifications [GenSpec5.0]. A GLN can represent either a discrete, unique physical location such as a dock door or a warehouse slot, or an aggregate physical location such as an entire warehouse. Also, a GLN can represent a logical entity such as an organization that performs a business function (for example, placing an order). The combination of GLN and a unique serial number is called a Serialized GLN (SGLN). However, until the EAN.UCC community determines the appropriate way to extend GLN, the serial number field is reserved and must not be used.

22.7.2.5 Global Returnable Asset Identifier (GRAI)

A returnable asset is a reusable package or transport equipment of a certain value. Global Returnable Asset Identifier is (GRAI) is defined by the General EAN.UCC Specifications [GenSpec5.0] for the unique identification of a returnable asset.

22.7.2.6 Global Individual Asset Identifier (GIAI)

The Global Individual Asset Identifier (GIAI) is defined by the General EAN.UCC Specifications [GenSpec5.0]. Unlike the GTIN, the GIAI is intended for assignment to individual objects. Global Individual Asset Identifier (GIAI) uniquely identifies an entity that is part of the fixed inventory of a company. The GIAI identifies any fixed asset of an organization.

22.7.2.7 RFID EPC Network

The RFID EPC network identifies, tracks, and locates assets. Physical objects are identified by a unique RFID enabled EPC.

22.8 Oracle Database Tag Data Translation Schema

The Oracle Database Tag Data Translation Schema is closely related to the EPCglobal TDT schema, however it is not exact. The Oracle Database TDT is shown as follows:

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema targetNamespace="oracle.mgd.idcode" 
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
     xmlns:tdt="oracle.mgd.idcode" elementFormDefault="qualified" 
       attributeFormDefault="unqualified" version="1.0">

 <xsd:simpleType name="InputFormatList">
  <xsd:restriction base="xsd:string">
   <xsd:enumeration value="BINARY"/>
   <xsd:enumeration value="STRING"/>
  </xsd:restriction>
 </xsd:simpleType>

 <xsd:simpleType name="LevelTypeList">
  <xsd:restriction base="xsd:string">
  </xsd:restriction>
 </xsd:simpleType>
 <xsd:simpleType name="SchemeNameList">
  <xsd:restriction base="xsd:string">
  </xsd:restriction>
 </xsd:simpleType>

 <xsd:simpleType name="ModeList">
  <xsd:restriction base="xsd:string">
   <xsd:enumeration value="EXTRACT"/>
   <xsd:enumeration value="FORMAT"/>
  </xsd:restriction>
 </xsd:simpleType>

 <xsd:simpleType name="CompactionMethodList">
  <xsd:restriction base="xsd:string">
   <xsd:enumeration value="32-bit"/>
   <xsd:enumeration value="16-bit"/>
   <xsd:enumeration value="8-bit"/>
   <xsd:enumeration value="7-bit"/>
   <xsd:enumeration value="6-bit"/>
   <xsd:enumeration value="5-bit"/>
  </xsd:restriction>
 </xsd:simpleType>

 <xsd:simpleType name="PadDirectionList">
  <xsd:restriction base="xsd:string">
   <xsd:enumeration value="LEFT"/>
   <xsd:enumeration value="RIGHT"/>
  </xsd:restriction>
 </xsd:simpleType>

 <xsd:complexType name="Field">
  <xsd:attribute name="seq" type="xsd:integer" use="required"/>
  <xsd:attribute name="name" type="xsd:string" use="required"/>
  <xsd:attribute name="bitLength" type="xsd:integer"/>
  <xsd:attribute name="characterSet" type="xsd:string" use="required"/>
  <xsd:attribute name="compaction" type="tdt:CompactionMethodList"/>
  <xsd:attribute name="compression" type="xsd:string"/>
  <xsd:attribute name="padChar" type="xsd:string"/>
  <xsd:attribute name="padDir" type="tdt:PadDirectionList"/>
  <xsd:attribute name="decimalMinimum" type="xsd:long"/>
  <xsd:attribute name="decimalMaximum" type="xsd:long"/>
  <xsd:attribute name="length" type="xsd:integer"/>
 </xsd:complexType>

 <xsd:complexType name="Option">
  <xsd:sequence>
   <xsd:element name="field" type="tdt:Field" maxOccurs="unbounded"/>
  </xsd:sequence>
  <xsd:attribute name="optionKey" type="xsd:string" use="required"/>
  <xsd:attribute name="pattern" type="xsd:string"/>
  <xsd:attribute name="grammar" type="xsd:string" use="required"/>
 </xsd:complexType>

 <xsd:complexType name="Rule">
  <xsd:attribute name="type" type="tdt:ModeList" use="required"/>
  <xsd:attribute name="inputFormat" type="tdt:InputFormatList" use="required"/>
  <xsd:attribute name="seq" type="xsd:integer" use="required"/>
  <xsd:attribute name="newFieldName" type="xsd:string" use="required"/>
  <xsd:attribute name="characterSet" type="xsd:string" use="required"/>
  <xsd:attribute name="padChar" type="xsd:string"/>
  <xsd:attribute name="padDir" type="tdt:PadDirectionList"/>
  <xsd:attribute name="decimalMinimum" type="xsd:long"/>
  <xsd:attribute name="decimalMaximum" type="xsd:long"/>
  <xsd:attribute name="length" type="xsd:string"/>
  <xsd:attribute name="function" type="xsd:string" use="required"/>
  <xsd:attribute name="tableURI" type="xsd:string"/>
  <xsd:attribute name="tableParams" type="xsd:string"/>
  <xsd:attribute name="tableXPath" type="xsd:string"/>
  <xsd:attribute name="tableSQL" type="xsd:string"/>
 </xsd:complexType>

 <xsd:complexType name="Level">
  <xsd:sequence>
   <xsd:element name="option" type="tdt:Option" minOccurs="1" 
     maxOccurs="unbounded"/>
   <xsd:element name="rule" type="tdt:Rule" minOccurs="0" 
     maxOccurs="unbounded"/>
  </xsd:sequence>
  <xsd:attribute name="type" type="tdt:LevelTypeList" use="required"/>
  <xsd:attribute name="prefixMatch" type="xsd:string"/>
  <xsd:attribute name="requiredParsingParameters" type="xsd:string"/>
  <xsd:attribute name="requiredFormattingParameters" type="xsd:string"/>
 </xsd:complexType>

 <xsd:complexType name="Scheme">
  <xsd:sequence>
   <xsd:element name="level" type="tdt:Level" minOccurs="4" maxOccurs="5"/>
  </xsd:sequence>
  <xsd:attribute name="name" type="tdt:SchemeNameList" use="required"/>
  <xsd:attribute name="optionKey" type="xsd:string" use="required"/>
 </xsd:complexType>
 <xsd:complexType name="TagDataTranslation">
  <xsd:sequence>
   <xsd:element name="scheme" type="tdt:Scheme" maxOccurs="unbounded"/>
  </xsd:sequence>
  <xsd:attribute name="version" type="xsd:string" use="required"/>
  <xsd:attribute name="date" type="xsd:dateTime" use="required"/>
 </xsd:complexType>
 <xsd:element name="TagDataTranslation" type="tdt:TagDataTranslation"/>
</xsd:schema>
PKm:[PKx|J OEBPS/toc.htm Oracle Database Development Guide, 12c Release 1 (12.1)

Contents

List of Examples

List of Figures

List of Tables

Preface

Changes in This Release for Oracle Database Development Guide

Part I Database Development Fundamentals

1 Design Basics

2 Performance and Scalability

3 Security

4 High Availability

5 Advanced PL/SQL Features

Part II SQL for Application Developers

6 SQL Processing for Application Developers

7 Using SQL Data Types in Database Applications

8 Using Regular Expressions in Database Applications

9 Using Indexes in Database Applications

10 Maintaining Data Integrity in Database Applications

Part III PL/SQL for Application Developers

11 Coding PL/SQL Subprograms and Packages

12 Using PL/Scope

13 Using the PL/SQL Hierarchical Profiler

14 Developing PL/SQL Web Applications

15 Using Continuous Query Notification (CQN)

Part IV Advanced Topics for Application Developers

16 Using Oracle Flashback Technology

17 Choosing a Programming Environment

18 Developing Applications with Multiple Programming Languages

19 Developing Applications with Oracle XA

20 Developing Applications with the Publish-Subscribe Model

21 Using the Oracle ODBC Driver

22 Using the Identity Code Package

23 Understanding Schema Object Dependency

24 Using Edition-Based Redefinition

25 Using Transaction Guard

26 Ensuring Application Continuity

Index

PKn]PKx|JOEBPS/adfns_environments.htm Choosing a Programming Environment

17 Choosing a Programming Environment

To choose a programming environment for a development project, read:

Sometimes the choice of programming environment is obvious, for example:

If no programming language provides all the features you need, you can use multiple programming languages, because:

For more information about multilanguage programming, see Chapter 18, "Developing Applications with Multiple Programming Languages."

Topics:

17.1 Overview of Application Architecture

In this topic, application architecture refers to the computing environment in which a database application connects to an Oracle Database.

Topics:


See Also:

Oracle Database Concepts for more information about application architecture

17.1.1 Client/Server Architecture

In a traditional client/server program, your application code runs on a client system; that is, a system other than the database server. Database calls are transmitted from the client system to the database server. Data is transmitted from the client to the server for insert and update operations and returned from the server to the client for query operations. The data is processed on the client system. Client/server programs are typically written by using precompilers, whereas SQL statements are embedded within the code of another language such as C, C++, or COBOL.


See Also:

Oracle Database Concepts for more information about client/server architecture

17.1.2 Server-Side Programming

You can develop application logic that resides entirely inside the database by using triggers that are executed automatically when changes occur in the database or stored subprograms that are invoked explicitly. Off-loading the work from your application lets you reuse code that performs verification and cleanup and control database operations from a variety of clients. For example, by making stored subprograms invocable through a web server, you can construct a web-based user interface that performs the same functions as a client/server application.


See Also:

Oracle Database Concepts for more information about server-side programming

17.1.3 Two-Tier and Three-Tier Architecture

Client/server computing is often referred to as a two-tier model: your application communicates directly with the database server. In the three-tier model, a separate application server processes the requests. The application server might be a basic web server, or might perform advanced functions like caching and load-balancing. Increasing the processing power of this middle tier lets you lessen the resources needed by client systems, resulting in a thin client configuration in which the client system might need only a web browser or other means of sending requests over the TCP/IP or HTTP protocols.


See Also:

Oracle Database Concepts for more information about multitier architecture

17.2 Overview of the Program Interface

The program interface is the software layer between a database application and Oracle Database. The program interface:

  • Provides a security barrier, preventing destructive access to the SGA by client user processes

  • Acts as a communication mechanism, formatting information requests, passing data, and trapping and returning errors

  • Converts and translates data, particularly between different types of computers or to external user program data types

The Oracle code acts as a server, performing database tasks on behalf of an application (a client), such as fetching rows from data blocks. The program interface consists of several parts, provided by both Oracle Database software and operating system-specific software.


See Also:

Oracle Database Concepts for more information about the program interface

Topics:

17.2.1 User Interface

The user interface is what your application displays to end users. It depends on the technology behind the application and the needs of the users themselves. Experienced users can enter SQL statements that are passed on to the database. Novice users can be shown a graphical user interface that uses the graphics libraries of the client system (such as Windows or X-Windows). Any traditional user interface can also be provided in a web browser through HTML and Java.

17.2.2 Stateful and Stateless User Interfaces

In traditional client/server applications, the application can keep a record of user actions and use this information over the course of one or more sessions. For example, past choices can be presented in a menu so that they not be entered again. When the application can save information in this way, the application is considered stateful.

Web or thin-client applications that are stateless are easier to develop. Stateless applications gather all the required information, process it using the database, and then start over with the next user. This is a popular way to process single-screen requests such as customer registration.

There are many ways to add stateful action to web applications that are stateless by default. For example, an entry form on one web page can pass information to subsequent web pages, enabling you to construct a wizard-like interface that remembers user choices through several different steps. You can use cookies to store small items of information about the client system, and retrieve them when the user returns to a website. You can use servlets to keep a database session open and store variables between requests from the same client.

17.3 Overview of PL/SQL

PL/SQL, the Oracle procedural extension of SQL, is a completely portable, high-performance transaction-processing language. PL/SQL lets you manipulate data with SQL statements; control program flow with conditional selection and loops; declare constants and variables; define subprograms; define types, subtypes, and ADTs and declare variables of those types; and trap runtime errors.

Applications written in any Oracle Database programmatic interface can invoke PL/SQL stored subprograms and send blocks of PL/SQL code to Oracle Database for execution. Third-generation language (3GL) applications can access PL/SQL scalar and composite data types through host variables and implicit data type conversion. A 3GL language is easier than assembler language for a human to understand and includes features such as named variables. Unlike a fourth-generation language (4GL), it is not specific to an application domain.

You can use PL/SQL to develop stored procedures that can be invoked by a web client.


See Also:


17.4 Overview of Oracle Database Java Support

This section provides an overview of Oracle Database features that support Java applications. The database includes the core JDK libraries such as java.lang, java.io, and so on. The database supports client-side Java standards such as JDBC and SQLJ, and provides server-side JDBC and SQLJ drivers that enable data-intensive Java code to run within the database.

Topics:

17.4.1 Overview of Oracle JVM

Oracle JVM, the Java Virtual Machine provided with the Oracle Database, is compliant with the J2SE version 1.5.x specification and supports the database session architecture.

Any database session can activate a dedicated JVM. All sessions share the same JVM code and statics; however, private states for any given session are held, and subsequently garbage collected, in an individual session space.

This design provides these benefits:

  • Java applications have the same session isolation and data integrity as SQL operations.

  • You need not run Java in a separate process for data integrity.

  • Oracle JVM is a robust JVM with a small memory footprint.

  • The JVM has the same linear Symmetric Multiprocessing (SMP) scalability as the database and can support thousands of concurrent Java sessions.

Oracle JVM works consistently with every platform supported by Oracle Database. Java applications that you develop with Oracle JVM can easily be ported to any supported platform.

Oracle JVM includes a deployment-time native compiler that enables Java code to be compiled once, stored in executable form, shared among users, and invoked more quickly and efficiently.

Security features of the database are also available with Oracle JVM. Java classes must be loaded in a database schema (by using Oracle JDeveloper, a third-party IDE, SQL*Plus, or the loadjava utility) before they can be called. Java class calls are secured and controlled through database authentication and authorization, Java 2 security, and invoker's rights (IR) or definer's rights (DR).

Effective with Oracle Database 12c Release 1 (12.1.0.1), Oracle JVM provides complete support for the latest Java Standard Edition. Compatibility with latest Java standards increases application portability and enables direct execution of client-side Java classes in the database.


See Also:


17.4.2 Overview of Oracle JDBC

Java Database Connectivity (JDBC) is an Applications Programming Interface (API) that enables Java to send SQL statements to an object-relational database such as Oracle Database.

Oracle Database includes these extensions to the JDBC 1.22 standard:

  • Support for Oracle data types

  • Performance enhancement by row prefetching

  • Performance enhancement by execution batching

  • Specification of query column types to save round trips

  • Control of DatabaseMetaData calls

Oracle Database supports all APIs from the JDBC 2.0 standard, including the core APIs, optional packages, and numerous extensions. Some highlights include datasources, JTA, and distributed transactions.

Oracle Database supports these features from the JDBC 3.0 standard:

  • Support for JDK 1.5.

  • Toggling between local and global transactions.

  • Transaction savepoints.

  • Reuse of prepared statements by connection pools.


Note:

JDBC code and SQLJ code interoperate. For more information, see Section 17.4.4.

Topics:


See Also:

Oracle Database Concepts for additional general information about Java support in Oracle Database

17.4.2.1 Oracle JDBC Drivers

The JDBC standard defines four types of JDBC drivers:

TypeDescription
1A JDBC-ODBC bridge. Software must be installed on client systems.
2Native methods (calls C or C++) and Java methods. Software must be installed on the client.
3Pure Java. The client uses sockets to call middleware on the server.
4The most pure Java solution. Talks directly to the database by using Java sockets.

JDBC is based on Part 3 of the SQL standard, "Call-Level Interface."

You can use JDBC to do dynamic SQL. In dynamic SQL, the embedded SQL statement to be executed is not known before the application is run and requires input to build the statement.

The drivers that are implemented by Oracle have extensions to the capabilities in the JDBC standard that was defined by Sun Microsystems.

Topics:


See Also:


17.4.2.1.1 JDBC Thin Driver

The JDBC Thin driver is a Type 4 (100% pure Java) driver that uses Java sockets to connect directly to a database server. It has its own implementation of a Two-Task Common (TTC), a lightweight implementation of TCP/IP from Oracle Net. It is written entirely in Java and is therefore platform-independent.

The thin driver does not require Oracle software on the client side. It does need a TCP/IP listener on the server side. Use this driver in Java applets that are downloaded into a web browser or in applications for which you do not want to install Oracle client software. The thin driver is self-contained, but it opens a Java socket, and thus can run only in a browser that supports sockets.

17.4.2.1.2 JDBC OCI Driver

The JDBC OCI driver is a Type  2 JDBC driver. It makes calls to OCI written in C to interact with Oracle Database, thus using native and Java methods.

The OCI driver provides access to more features than the thin driver, such as Transparent Application Fail-Over, advanced security, and advanced LOB manipulation.

The OCI driver provides the highest compatibility between different Oracle Database versions. It also supports all installed Oracle Net adapters, including IPC, named pipes, TCP/IP, and IPX/SPX.

Because it uses native methods (a combination of Java and C) the OCI driver is platform-specific. It requires a client installation of version Oracle8i or later including Oracle Net, OCI libraries, CORE libraries, and all other dependent files. The OCI driver usually runs faster than the thin driver.

The OCI driver is not appropriate for Java applets, because it uses a C library that is platform-specific and cannot be downloaded into a web browser. It is usable in J2EE components running in middle-tier application servers, such as Oracle Application Server. Oracle Application Server provides middleware services and tools that support access between applications and browsers.

17.4.2.1.3 JDBC Server-Side Internal Driver

The JDBC server-side internal driver is a Type 2 driver that runs inside the database server, reducing the number of round trips needed to access large amounts of data. The driver, the Java server VM, the database, the Java native compiler (which speeds execution by as much as 10 times), and the SQL engine all run within the same address space.

This driver provides server-side support for any Java program used in the database. You can also call PL/SQL stored subprograms and triggers.

The server driver fully supports the same features and extensions as the client-side drivers.

17.4.2.2 Sample JDBC 2.0 Program

This example shows the recommended technique for looking up a data source using JNDI in JDBC 2.0:

// import the JDBC packages 
import java.sql.*; 
import javax.sql.*; 
import oracle.jdbc.pool.*; 
...
   InitialContext ictx = new InitialContext(); 
   DataSource ds = (DataSource)ictx.lookup("jdbc/OracleDS"); 
   Connection conn = ds.getConnection(); 
   Statement stmt = conn.createStatement(); 
   ResultSet rs = stmt.executeQuery("SELECT last_name FROM employees"); 
   while ( rs.next() ) { 
   out.println( rs.getString("ename") + "<br>"); 
   } 
conn.close(); 

17.4.2.3 Sample Pre-2.0 JDBC Program

This source code registers an Oracle JDBC thin driver, connects to the database, creates a Statement object, runs a query, and processes the result set.

The SELECT statement retrieves and lists the contents of the last_name column of the hr.employees table.

import java.sql.*
import java.math.*
import java.io.*
import java.awt.*

class JdbcTest { 
  public static void main (String args []) throws SQLException { 
    // Load Oracle driver
    DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
     
    // Connect to the local database
    Connection conn = 
      DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:orcl", 
                                   "hr", "password");

    // Query the employee names 
    Statement stmt = conn.createStatement (); 
    ResultSet rset = stmt.executeQuery ("SELECT last_name FROM employees");

    // Print the name out 
    while (rset.next ())
      System.out.println (rset.getString (1));
    // Close the result set, statement, and the connection
    rset.close();
    stmt.close();
    conn.close();
  } 
} 

One Oracle Database extension to the JDBC drivers is a form of the getConnection() method that uses a Properties object. The Properties object lets you specify user, password, database information, row prefetching, and execution batching.

To use the OCI driver in this code, replace the Connection statement with this code, where MyHostString is an entry in the tnsnames.ora file:

Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@MyHostString",
    "hr", "password");

If you are creating an applet, then the getConnection() and registerDriver() strings are different.

17.4.3 Overview of Oracle SQLJ


Note:

In this guide, SQLJ refers to Oracle SQLJ and its extensions.

SQLJ is an ANSI SQL-1999 standard for embedding SQL statements in Java source code. SQLJ provides a simpler alternative to JDBC for both client-side and server-side SQL data access from Java.

A SQLJ source file contains Java source with embedded SQL statements. Oracle SQLJ supports dynamic and static SQL. Support for dynamic SQL is an Oracle extension to the SQLJ standard.

Oracle Database provides a translator and a runtime driver to support SQLJ. The SQLJ translator is 100% pure Java and is portable to any JVM that is compliant with JDK version 6 or higher.

The Oracle SQLJ translator performs these tasks:

  • Translates SQLJ source to Java code with calls to the SQLJ runtime driver. The SQLJ translator converts the source code to pure Java source code and can check the syntax and semantics of static SQL statements against a database schema and verify the type compatibility of host variables with SQL types.

  • Compiles the generated Java code with the Java compiler.

  • (Optional) Creates profiles for the target database. SQLJ generates "profile" files with customization specific to Oracle Database.

Oracle Database supports SQLJ stored subprograms and triggers that run in the Oracle JVM. SQLJ is integrated with JDeveloper. Source-level debugging support for SQLJ is available in JDeveloper.

This is an example of a simple SQLJ executable statement, which returns one value because employee_id is unique in the employee table:

String name;
#sql  { SELECT first_name INTO :name FROM employees WHERE employee_id=112 };
System.out.println("Name is " + name + ", employee number = " + employee_id);

Each host variable (or qualified name or complex Java host expression) included in a SQL expression is preceded by a colon (:). Other SQLJ statements declare Java types. For example, you can declare an iterator (a construct related to a database cursor) for queries that retrieve many values, as follows:

#sql iterator EmpIter (String EmpNam, int EmpNumb);

See Also:

Oracle Database SQLJ Developer's Guide for more examples and details about Oracle SQLJ syntax

Topics:


See Also:

Oracle Database Concepts for additional general information about SQLJ

17.4.3.1 Benefits of SQLJ

Oracle SQLJ extensions to Java enable rapid development and easy maintenance of applications that perform database operations through embedded SQL.

In particular, Oracle SQLJ does this:

  • Provides a concise, legible mechanism for database access from static SQL. Most SQL in applications is static. SQLJ provides more concise and less error-prone static SQL constructs than JDBC does.

  • Provides an SQL Checker module for verification of syntax and semantics at translate time.

  • Provides flexible deployment configurations, which makes it possible to implement SQLJ on the client, server, or middle tier.

  • Supports a software standard. SQLJ is an effort of a group of vendors and is supported by all of them. Applications can access multiple database vendors.

  • Provides source code portability. Executables can be used with all of the vendor DBMSs if the code does not rely on vendor-specific features.

  • Enforces a uniform programming style for the clients and the servers.

  • Integrates the SQLJ translator with Oracle JDeveloper, a graphical IDE that provides SQLJ translation, Java compilation, profile customizing, and debugging at the source code level, all in one step.

  • Includes Oracle Database type extensions.

17.4.3.2 SQLJ Stored Subprograms in the Server

SQLJ applications can be stored and executed in the server by using these techniques:

  • Translate, compile, and customize the SQLJ source code on a client and load the generated classes and resources into the server with the loadjava utility. The classes are typically stored in a Java archive (.jar) file.

  • Load the SQLJ source code into the server, also using loadjava, where it is translated and compiled by the server's embedded translator.

17.4.4 Comparing Oracle JDBC and Oracle SQLJ

JDBC code and SQLJ code interoperate, enabling dynamic SQL statements in JDBC to be used with both static and dynamic SQL statements in SQLJ. A SQLJ iterator class corresponds to the JDBC result set.

Some differences between JDBC and SQLJ are:

  • JDBC provides a complete dynamic SQL interface from Java to databases. It gives developers full control over database operations. SQLJ simplifies Java database programming to improve development productivity.

  • JDBC provides fine-grained control of the execution of dynamic SQL from Java, whereas SQLJ provides a higher-level binding to SQL operations in a specific database schema.

  • SQLJ source code is more concise than equivalent JDBC source code.

  • SQLJ uses database connections to type-check static SQL code. JDBC, being a completely dynamic API, does not.

  • SQLJ provides strong typing of query outputs and return parameters and provides type-checking on calls. JDBC passes values to and from SQL without compile-time type checking.

  • SQLJ programs enable direct embedding of Java bind expressions within SQL statements. JDBC requires a separate get or set statement for each bind variable and specifies the binding by position number.

  • SQLJ provides simplified rules for calling SQL stored subprograms.

    For example, the following four examples show, on successive lines, how to call a stored procedure or a stored function using either JDBC escape syntax or Oracle JDBC syntax:

    prepStmt.prepareCall("{call fun(?,?)}");       //stored proc. JDBC esc.
    prepStmt.prepareCall("{? = call fun(?,?)}");   //stored func. JDBC esc.
    prepStmt.prepareCall("begin fun(:1,:2);end;"); //stored proc. Oracle
    prepStmt.prepareCall("begin :1 := fun(:2,:3);end;"); //stored func. Oracle
    

    The SQLJ equivalent is:

    #sql {call fun(param_list) };  //Stored procedure
    // Declare x
    ...
    #sql x = {VALUES(fun(param_list)) };  // Stored function
    // where VALUES is the SQL construct
    

These benefits are common to SQLJ and JDBC:

  • SQLJ source files can contain JDBC calls. SQLJ and JDBC are interoperable.

  • Oracle JPublisher generates custom Java classes to be used in your SQLJ or JDBC application for mappings to Oracle Database ADTs and collections.

  • PL/SQL and Java stored subprograms can be used interchangeably.

17.4.5 Overview of Oracle JPublisher

Oracle JPublisher is a code generator that automates the process of creating database-centric Java classes by hand. Oracle JPublisher is a client-side utility and is built into the database system. You can run Oracle JPublisher from the command line or directly from the Oracle JDeveloper IDE.

Oracle JPublisher inspects PL/SQL packages and database object types such as ADTs, VARRAY types, and nested table types, and then generates a Java class that is a wrapper around the PL/SQL package with corresponding fields and methods.

The generated Java class can be incorporated and used by Java clients or J2EE components to exchange and transfer database object type instances to and from the database transparently.


See Also:


17.4.6 Overview of Java Stored Subprograms

Java stored subprograms enable you to implement programs that run in the database server and are independent of programs that run in the middle tier. Structuring applications in this way reduces complexity and increases reuse, security, performance, and scalability.

For example, you can create a Java stored subprogram that performs operations that require data persistence and a separate program to perform presentation or business logic operations.

Java stored subprograms interface with SQL using an execution model similar to that of PL/SQL.


See Also:


17.4.7 Overview of Oracle Database Web Services

Web services represent a distributed computing paradigm for Java application development that is an alternative to earlier Java protocols such as JDBC, and which enable applications to interact through the XML and web protocols. For example, an electronics parts vendor can provide a web-based programmatic interface to its suppliers for inventory management. The vendor can invoke a web service as part of a program and automatically order stock based on the data returned.

The key technologies used in web services are:

  • Web Services Description Language (WSDL), which is a standard format for creating an XML document. WSDL describes what a web service can do, where it resides, and how to invoke it. Specifically, it describes the operations and parameters, including parameter types, provided by a web service. Also, a WSDL document describes the location, the transport protocol, and the invocation style for the web service.

  • Simple Object Access Protocol (SOAP) messaging, which is an XML-based message protocol used by web services. SOAP does not prescribe a specific transport mechanism such as HTTP, FTP, SMTP, or JMS; however, most web services accept messages that use HTTP or HTTPS.

  • Universal Description, Discovery, and Integration (UDDI) business registry, which is a directory that lists web services on the internet. The UDDI registry is often compared to a telephone directory, listing unique identifiers (white pages), business categories (yellow pages), and instructions for binding to a service protocol (green pages).

Web services can use a variety of techniques and protocols. For example:

  • Dispatching can occur in a synchronous (typical) or asynchronous manner.

  • You can invoke a web service in an RPC-style operation in which arguments are sent and a response returned, or in a message style such as a one-way SOAP document exchange.

  • You can use different encoding rules: literal or encoded.

You can invoke a web service statically, when you might know everything about it beforehand, or dynamically, in which case you can discover its operations and transport endpoints while using it.

Oracle Database can function as either a web service provider or as a web service consumer. When used as a provider, the database enables sharing and disconnected access to stored subprograms, data, metadata, and other database resources such as the queuing and messaging systems.

As a web service provider, Oracle Database provides a disconnected and heterogeneous environment that:

  • Exposes stored subprograms independently of the language in which the subprograms are written

  • Exposes SQL Queries and XQuery


See Also:

Oracle Database Concepts for additional general information about Oracle Database as a web service provider

17.5 Choosing PL/SQL or Java

PL/SQL and Java interoperate in the server. You can run a PL/SQL package from Java or PL/SQL can be invoked from Java, so that either one can be invoked from distributed CORBA and Enterprise Java Beans clients.

Table 17-1 shows PL/SQL packages and their Java equivalents.

Table 17-1 PL/SQL Packages and Their Java Equivalents

PL/SQL PackageJava Equivalent

DBMS_ALERT

Call package with SQLJ or JDBC.

DBMS_DDL

JDBC has this functionality.

DBMS_JOB

Schedule a job that has a Java stored subprogram.

DBMS_LOCK

Call with SQLJ or JDBC.

DBMS_MAIL

Use JavaMail.

DBMS_OUTPUT

Use subclass oracle.aurora.rdbms.OracleDBMSOutputStream or Java stored subprogram DBMS_JAVA.SET_STREAMS.

DBMS_PIPE

Call with SQLJ or JDBC.

DBMS_SESSION

Use JDBC to run an ALTER SESSION statement.

DBMS_SNAPSHOT

Call with SQLJ or JDBC.

DBMS_SQL

Use JDBC.

DBMS_TRANSACTION

Use JDBC to run an ALTER SESSION statement.

DBMS_UTILITY

Call with SQLJ or JDBC.

UTL_FILE

Grant the JAVAUSERPRIV privilege and then use Java I/O entry points.


Topics:

17.5.1 Similarities of PL/SQL and Java

Both PL/SQL and Java provide packages and libraries.

Both PL/SQL and Java have object-oriented features:

  • Both have inheritance.

  • PL/SQL has type evolution, the ability to change methods and attributes of a type while preserving subtypes and table data that use the type.

  • Java has polymorphism and component models for developing distributed systems.

17.5.2 PL/SQL Advantages Over Java

As an extension of SQL, PL/SQL supports all SQL data types, data encapsulation, information hiding, overloading, and exception-handling. Therefore:

  • SQL data types are easier to use in PL/SQL than in Java.

  • SQL operations are faster with PL/SQL than with Java, especially when a large amount of data is involved, when mostly database access is done, or when bulk operations are used.

    Some advanced PL/SQL capabilities are unavailable for Java in Oracle9i (for example, autonomous transactions and the dblink facility for remote databases).

Code development is usually faster in PL/SQL than in Java; however, this really depends upon the development tool or development environment you are using.

PL/SQL is preferred when your data logic is SQL intensive. That is, the data processing or data validation requirements of your application are high.

Also, there is a large user base with Oracle-supplied packages and third party libraries that can draw upon for development.

17.5.3 Java Advantages Over PL/SQL

Java is used for open distributed applications, and many Java-based development tools are available throughout the industry. Java has native mechanisms that are unavailable in PL/SQL. For example, Java has built-in security mechanisms, an automatic Garbage Collector, type safety mechanisms, byte-code verifier, and Java 2 security. Also, Java provides built-in rapid development features, such as, built-in automatic bounds checking on arrays, built-in network access classes, and APIs that contain many useful and ready-to-use classes. Java has a vast set of class libraries, tools, and third-party class libraries that can be reused in the database. Java has a richer type system than PL/SQL. Java can use CORBA (which can have many different computer languages in its clients) and Enterprise Java Beans. PL/SQL packages can be invoked from CORBA or Enterprise Java Beans clients. You can run XML tools, the Internet File System, or JavaMail from Java.

If you application must interact with ERP systems, RMI servers, Java/J2EE, and web services, Java is preferred because none of these things can be accomplished with PL/SQL. Java is also preferred if you must develop part of your application in the middle-tier because your business logic is complex or compute intensive with little to moderate direct SQL access, you are implementing a middle-tier-driven presentation logic, your application requires transparent Java persistence, or your application requires container-managed infrastructure services. Thus, when needing to partition your application between the database tier and middle tier, migrate that part of your application as needed to the middle tier and use Java/J2EE.

17.6 Overview of Precompilers

Client/server programs are typically written using precompilers, which are programming tools that let you embed SQL statements in high-level programs written in languages such as C, C++, or COBOL. Because the client application hosts the SQL statements, it is called a host program, and the language in which it is written is called the host language.

A precompiler accepts the host program as input, translates the embedded SQL statements into standard database runtime library calls, and generates a source program that you can compile, link, and run in the usual way.

Topics:


See Also:

Oracle Database Concepts for additional general information about Oracle precompilers

17.6.1 Overview of the Pro*C/C++ Precompiler

For the Pro*C/C++ precompiler, the host language is either C or C++. Some features of the Pro*C/C++ precompiler are:

  • You can write multithreaded programs if your platform supports a threads package. Concurrent connections are supported in either single-threaded or multithreaded applications.

  • You can improve performance by embedding PL/SQL blocks. These blocks can invoke subprograms in Java or PL/SQL that are written by you or provided in Oracle Database packages.

  • Using precompiler options, you can check the syntax and semantics of your SQL or PL/SQL statements during precompilation, and at runtime.

  • You can invoke stored PL/SQL and Java subprograms. Modules written in COBOL or in C can be invoked from Pro*C/C++. External C subprograms in shared libraries can be invoked by your program.

  • You can conditionally precompile sections of your code so that they can run in different environments.

  • You can use arrays, or structures, or arrays of structures as host and indicator variables in your code to improve performance.

  • You can deal with errors and warnings so that data integrity is guaranteed. As a programmer, you control how errors are handled.

  • Your program can convert between internal data types and C language data types.

  • The Oracle Call Interface (OCI) and Oracle C++ Call Interface (OCCI), lower-level C and C++ interfaces, are available for use in your precompiler source.

  • Pro*C/C++ supports dynamic SQL, a technique that enables users to input variable values and statement syntax.

  • Pro*C/C++ can use special SQL statements to manipulate tables containing user-defined object types. An Object Type Translator (OTT) maps the ADTs and named collection types in your database to structures and headers that you include in your source.

  • Three kinds of collection types: associative arrays, nested tables and VARRAY, are supported with a set of SQL statements that give you a high degree of control over data.

  • Large Objects are accessed by another set of SQL statements.

  • A new ANSI SQL standard for dynamic SQL is supported for new applications, so that you can run SQL statements with a varying number of host variables. An older technique for dynamic SQL is still usable by pre-existing applications.

  • Globalization support lets you use multibyte characters and UCS2 Unicode data.

  • Using scrollable cursors, you can move backward and forward through a result set. For example, you can fetch the last row of the result set, or jump forward or backward to an absolute or relative position within the result set.

  • A connection pool is a group of physical connections to a database that can be shared by several named connections. Enabling the connection pool option can help optimize the performance of Pro*C/C++ application. The connection pool option is not enabled by default.


See Also:

Pro*C/C++ Precompiler Programmer's Guide for complete information about the Pro*C/C++ precompiler

Example 17-1 is a code fragment from a C source program that queries the table employees in the schema hr.

Example 17-1 Pro*C/C++ Application

...
#define  UNAME_LEN   10
...
int   emp_number;
/* Define a host structure for the output values of a SELECT statement. */
/* No declare section needed if precompiler option MODE=ORACLE          */
struct {
    VARCHAR  last_name[UNAME_LEN];
    float    salary;
    float    commission_pct;
} emprec;
/* Define an indicator structure to correspond to the host output structure. */
struct {
    short emp_name_ind;
    short sal_ind;
    short comm_ind;
} emprec_ind;
...
/* Select columns last_name, salary, and commission_pct given the user's input 
/* for employee_id. */
    EXEC SQL SELECT last_name, salary, commission_pct
        INTO :emprec INDICATOR :emprec_ind
        FROM employees
        WHERE employee_id = :emp_number;
...

The embedded SELECT statement differs slightly from the interactive (SQL*Plus) SELECT statement. Every embedded SQL statement begins with EXEC SQL. The colon (:) precedes every host (C) variable. The returned values of data and indicators (set when the data value is NULL or character columns were truncateVMd) can be stored in structs (such as in the preceding code fragment), in arrays, or in arrays of structs. Multiple result set values are handled very simply in a manner that resembles the case shown, where there is only one result, because of the unique employee number. Use the actual names of columns and tables in embedded SQL.

Either use the default precompiler option values or enter values that give you control over the use of resources, how errors are reported, the formatting of output, and how cursors (which correspond to a particular connection or SQL statement) are managed. Cursors are used when there are multiple result set values.

Enter the options either in a configuration file, on the command line, or inline inside your source code with a special statement that begins with EXEC ORACLE. If there are no errors found, you can compile, link, and run the output source file, like any other C program that you write.

Use the precompiler to create server database access from clients that can be on many different platforms. Pro*C/C++ gives you the freedom to design your own user interfaces and to add database access to existing applications.

Before writing your embedded SQL statements, you can test interactive versions of the SQL in SQL*Plus and then make minor changes to start testing your embedded SQL application.

17.6.2 Overview of the Pro*COBOL Precompiler

For the Pro*COBOL precompiler, the host language is COBOL. Some features of the Pro*COBOL precompiler are:

  • You can invoke stored PL/SQL or Java subprograms. You can improve performance by embedding PL/SQL blocks. These blocks can invoke PL/SQL subprograms written by you or provided in Oracle Database packages.

  • Precompiler options enable you to define how cursors, errors, syntax-checking, file formats, and so on, are handled.

  • Using precompiler options, you can check the syntax and semantics of your SQL or PL/SQL statements during precompilation, and at runtime.

  • You can conditionally precompile sections of your code so that they can run in different environments.

  • Use tables, or group items, or tables of group items as host and indicator variables in your code to improve performance.

  • You can program how errors and warnings are handled, so that data integrity is guaranteed.

  • Pro*COBOL supports dynamic SQL, a technique that enables users to input variable values and statement syntax.


    See Also:

    Pro*COBOL Programmer's Guide for complete information about the Pro*COBOL precompiler

Example 17-2 is a code fragment from a COBOL source program that queries the table employees in the schema hr.

Example 17-2 Pro*COBOL Application

...
 WORKING-STORAGE SECTION.
*
* DEFINE HOST INPUT AND OUTPUT HOST AND INDICATOR VARIABLES.
* NO DECLARE SECTION NEEDED IF MODE=ORACLE.
*
 01  EMP-REC-VARS.
     05  EMP-NAME    PIC X(10) VARYING.
     05  EMP-NUMBER  PIC S9(4) COMP VALUE ZERO.
     05  SALARY      PIC S9(5)V99 COMP-3 VALUE ZERO.
     05  COMMISSION  PIC S9(5)V99 COMP-3 VALUE ZERO.
     05  COMM-IND    PIC S9(4) COMP VALUE ZERO.
...
 PROCEDURE DIVISION.
...
     EXEC SQL
         SELECT last_name, salary, commission_pct
         INTO :EMP-NAME, :SALARY, :COMMISSION:COMM-IND
         FROM employees
         WHERE employee_id = :EMP-NUMBER
     END-EXEC.
...

The embedded SELECT statement is only slightly different from an interactive (SQL*Plus) SELECT statement. Every embedded SQL statement begins with EXEC SQL. The colon (:) precedes every host (COBOL) variable. The SQL statement is terminated by END-EXEC. The returned values of data and indicators (set when the data value is NULL or character columns were truncated) can be stored in group items (such as in the preceding code fragment), in tables, or in tables of group items. Multiple result set values are handled very simply in a manner that resembles the case shown, where there is only one result, given the unique employee number. Use the actual names of columns and tables in embedded SQL.

Use the default precompiler option values, or enter values that give you control over the use of resources, how errors are reported, the formatting of output, and how cursors are managed (cursors correspond to a particular connection or SQL statement).

Enter the options in a configuration file, on the command line, or inline inside your source code with a special statement that begins with EXEC ORACLE. If there are no errors found, you can compile, link, and run the output source file, like any other COBOL program that you write.

Use the precompiler to create server database access from clients that can be on many different platforms. Pro*COBOL gives you the freedom to design your own user interfaces and to add database access to existing COBOL applications.

The embedded SQL statements available conform to an ANSI standard, so that you can access data from many databases in a program, including remote servers networked through Oracle Net.

Before writing your embedded SQL statements, you can test interactive versions of the SQL in SQL*Plus and then make minor changes to start testing your embedded SQL application.

17.7 Overview of OCI and OCCI

The Oracle Call Interface (OCI) and Oracle C++ Call Interface (OCCI) are application programming interfaces (APIs) that enable you to create applications that use native subprogram invocations of a third-generation language to access Oracle Database and control all phases of SQL statement execution. These APIs provide:

  • Improved performance and scalability through the efficient use of system memory and network connectivity

  • Consistent interfaces for dynamic session and transaction management in a two-tier client/server or multitier environment

  • N-tiered authentication

  • Comprehensive support for application development using Oracle Database objects

  • Access to external databases

  • Ability to develop applications that service an increasing number of users and requests without additional hardware investments

OCI lets you manipulate data and schemas in a database using a host programming language, such as C. OCCI is an object-oriented interface suitable for use with C++. These APIs provide a library of standard database access and retrieval functions in the form of a dynamic runtime library that can be linked in an application at runtime. You need not embed SQL or PL/SQL within 3GL programs.

Topics:

17.7.1 Advantages of OCI and OCCI

OCI and OCCI provide significant advantages over other methods of accessing Oracle Database:

  • More fine-grained control over all aspects of the application design.

  • High degree of control over program execution.

  • Use of familiar 3GL programming techniques and application development tools such as browsers and debuggers.

  • Support of dynamic SQL, method 4.

  • Availability on the broadest range of platforms of all the Oracle Database programmatic interfaces.

  • Dynamic bind and define using callbacks.

  • Describe functionality to expose layers of server metadata.

  • Asynchronous event notification for registered client applications.

  • Enhanced array data manipulation language (DML) capability for arrays.

  • Ability to associate a commit request with a statement execution to reduce round trips.

  • Optimization for queries using transparent prefetch buffers to reduce round trips.

  • Thread safety, so you do not have to implement mutual exclusion (mutex) locks on OCI and OCCI handles.

  • The server connection in nonblocking mode means that control returns to the OCI code when a call is still running or cannot complete.

17.7.2 OCI and OCCI Functions

Both OCI and OCCI have four kinds of functions:

Kind of FunctionPurpose
RelationalTo manage database access and process SQL statements
NavigationalTo manipulate objects retrieved from the database
Database mapping and manipulationTo manipulate data attributes of Oracle Database types
External subprogramTo write C callbacks from PL/SQL (OCI only)

17.7.3 Procedural and Nonprocedural Elements of OCI and OCCI Applications

OCI and OCCI enable you to develop applications that combine the nonprocedural data access power of SQL with the procedural capabilities of most programming languages, including C and C++. Procedural and nonprocedural languages have these characteristics:

  • In a nonprocedural language program, the set of data to be operated on is specified, but what operations are performed and how the operations are to be carried out is not specified. The nonprocedural nature of SQL makes it an easy language to learn and to use to perform database transactions. It is also the standard language used to access and manipulate data in modern relational and object-relational database systems.

  • In a procedural language program, the execution of most statements depends on previous or subsequent statements and on control structures, such as loops or conditional branches, which are unavailable in SQL. The procedural nature of these languages makes them more complex than SQL, but it also makes them very flexible and powerful.

The combination of both nonprocedural and procedural language elements in an OCI or OCCI program provides easy access to Oracle Database in a structured programming environment.

OCI and OCCI support all SQL data definition, data manipulation, query, and transaction control facilities that are available through Oracle Database. For example, an OCI or OCCI program can run a query against Oracle Database. The queries can require the program to supply data to the database using input (bind) variables, as follows:

SELECT name FROM employees WHERE empno = :empnumber

In the preceding SQL statement, :empnumber is a placeholder for a value to be supplied by the application.

Alternatively, you can use PL/SQL, Oracle's procedural extension to SQL. The applications you develop can be more powerful and flexible than applications written in SQL alone. OCI and OCCI also provide facilities for accessing and manipulating objects in Oracle Database.

17.7.4 Building an OCI or OCCI Application

As Figure 17-1 shows, you compile and link an OCI or OCCI program in the same way that you compile and link a nondatabase application. There is no need for a separate preprocessing or precompilation step.

Figure 17-1 The OCI or OCCI Development Process

Description of Figure 17-1 follows


Note:

To properly link your OCI and OCCI programs, it might be necessary on some platforms to include other libraries, in addition to the OCI and OCCI libraries. Check your Oracle platform-specific documentation for further information about extra libraries that might be required.

17.8 Choosing a Precompiler or OCI

Precompiler applications typically contain less code than equivalent OCI applications, which can help productivity.

Some situations require detailed control of the database and are suited for OCI applications (either pure OCI or a precompiler application with embedded OCI calls):

  • OCI provides more detailed control over multiplexing and migrating sessions.

  • OCI provides dynamic bind and define using callbacks that can be used for any arbitrary structure, including lists.

  • OCI has many calls to handle metadata.

  • OCI enables asynchronous event notifications to be received by a client application. It provides a means for clients to generate notifications for propagation to other clients.

  • OCI enables DML statements to use arrays to complete as many iterations as possible before returning any error messages.

  • OCI calls for special purposes include Advanced Queuing, globalization support, Data Cartridges, and support of the date and time data types.

  • OCI calls can be embedded in a Pro*C/C++ application.

17.9 Overview of Oracle Data Provider for .NET (ODP.NET)

Oracle Data Provider for .NET (ODP.NET) is an implementation of a data provider for Oracle Database.

ODP.NET uses APIs native to Oracle Database to offer fast and reliable access from any .NET application to database features and data. It also uses and inherits classes and interfaces available in the Microsoft .NET Framework Class Library.

For programmers using Oracle Provider for OLE DB, ADO (ActiveX Data Objects) provides an automation layer that exposes an easy programming model. ADO.NET provides a similar programming model, but without the automation layer, for better performance. More importantly, the ADO.NET model enables native providers such as ODP.NET to expose specific features and data types specific to Oracle Database.

This is a simple C# application that connects to Oracle Database and displays its version number before disconnecting:

using System; 
using Oracle.DataAccess.Client; 

class Example 
{ 
  OracleConnection con; 

  void Connect() 
  { 
    con = new OracleConnection(); 
    con.ConnectionString = "User Id=hr;Password=password;Data Source=oracle"; 
    con.Open(); 
    Console.WriteLine("Connected to Oracle" + con.ServerVersion); 
  } 

  void Close() 
  { 
    con.Close(); 
    con.Dispose(); 
  } 
  
  static void Main() 
  { 
    Example example = new Example(); 
    example.Connect(); 
    example.Close(); 
  } 
}

Note:

Additional samples are provided in directory ORACLE_BASE\ORACLE_HOME\ODP.NET\Samples.

17.10 Overview of OraOLEDB

Oracle Provider for OLE DB (OraOLEDB) is an OLE DB data provider that offers high performance and efficient access to Oracle data by OLE DB consumers. In general, this developer's guide assumes that you are using OraOLEDB through OLE DB or ADO.

PKWJVVPKx|JOEBPS/adfns_regexp.htm Using Regular Expressions in Database Applications