Oracle RMS Item Images & Extended Attributes

Item Images and Extended Attributes — Overview#

The core ITEM_MASTER table and its satellite tables — ITEM_DESC, ITEM_SUPPLIER, ITEM_LOC — capture what an item is, where it comes from, and where it goes. But a complete item record in a modern retail environment also needs images for e-commerce and digital channels, physical dimension data for warehouse management and logistics, printing attributes for shelf labels and price tickets, and whatever additional retailer-specific attributes the business requires.

Oracle RMS provides dedicated tables for images and dimensions, a ticketing attribute structure for label printing, and a formally documented extension model for anything beyond that. Understanding all four layers is essential for developers building integrations that need a complete item data picture — particularly those feeding Product Information Management (PIM) systems, e-commerce platforms, or warehouse management systems.

The ITEM_IMAGE Table#

ITEM_IMAGE stores image references for items. The composite primary key is ITEM + IMAGE_TYPE, allowing multiple images per item — each serving a different display purpose.

IMAGE_TYPE — Multiple Images Per Item#

The IMAGE_TYPE column controls what kind of image a given row represents. Values are retailer-configured but follow common conventions across Oracle Retail implementations. Each type corresponds to a different use case in the downstream systems that consume item images.

Image Storage Patterns — URLs, Paths and DAM Integration#

Because ITEM_IMAGE.IMAGE_ADDR is a plain VARCHAR2(400), it can hold any string — a relative file path, an absolute URL, a CDN endpoint, or a DAM system identifier. The pattern used has major implications for integration consumers.

ITEM_SUPP_COUNTRY_DIM — Physical Dimensions#

The ITEM_SUPP_COUNTRY_DIM table stores the physical measurement attributes of an item as specified by a particular supplier from a particular country of origin. Physical dimensions in Oracle RMS are modelled at the item-supplier-country level because the same item sourced from factories in different countries may have slightly different packaging dimensions or weights.

This table is consumed by warehouse management systems (WMS) for bin slotting, carton sizing, and pallet build calculations, and by logistics systems for freight cost estimation.

Ticketing Attributes — ITEM_TICKET#

Oracle RMS has a dedicated ticketing module that drives shelf label and price ticket printing. The ITEM_TICKET table holds the ticket printing configuration for each item — how many tickets to print, when to print them, and in what format.

The Oracle Retail Extension Model#

Retailers often need to capture attributes that are not in any Oracle RMS standard table — a proprietary sustainability score, a loyalty exclusion flag, an internal audit field, or a B2B-specific pricing tier. The temptation is to add custom columns directly to ITEM_MASTER or create a completely independent table with no relation to the RMS framework.

Oracle Retail formally documents an extension model — a specific pattern for adding custom attributes that does not break future upgrades and is supported by Oracle's patching and migration tools.

Adding Custom Columns — The Safe Approach#

Oracle Retail ships a set of pre-defined extension tables alongside the core schema. These tables are empty by design — they exist purely to receive custom columns added by the retailer or implementation partner. The most relevant for item data is ITEM_MASTER_EXT.

Custom Extension Tables — The Preferred Approach#

For larger sets of custom attributes, or for custom attributes that have their own relationships (e.g. an item can have multiple custom records, or the custom data joins to other reference tables), the better approach is a completely new custom table that references ITEM_MASTER via a foreign key.

-- Custom sustainability attributes table (retailer-defined)
-- References ITEM_MASTER.ITEM — not a core RMS table
CREATE TABLE xxrc_item_sustainability (
item                VARCHAR2(25)  NOT NULL,
carbon_score        NUMBER(5,2),
recycled_pct        NUMBER(5,2),
certifications      VARCHAR2(500),
last_assessed_date  DATE,
assessed_by         VARCHAR2(120),
CONSTRAINT xxrc_item_sust_pk
PRIMARY KEY (item),
CONSTRAINT xxrc_item_sust_fk
FOREIGN KEY (item) REFERENCES item_master(item)
);

Key Tables — Quick Reference#

Practical SQL Examples#

1. All images for an item with type descriptions

-- All image records for an item with human-readable type labels
-- Uses CODE_DETAIL to translate IMAGE_TYPE codes
SELECT ii.item,
ii.image_type,
cd.code_desc                    image_type_desc,
ii.image_addr,
ii.image_desc,
ii.image_indate
FROM   item_image   ii
LEFT JOIN code_detail cd ON  cd.code_type = 'IMTP'
AND cd.code      = ii.image_type
WHERE  ii.item = :v_item
ORDER BY cd.display_seq, ii.image_type;

2. Items missing a primary image — e-commerce readiness check

-- Active transaction-level items with no primary (type 'A') image
-- Run before a digital channel launch to find items that will have no image
SELECT im.item,
im.item_desc,
im.dept,
dp.dept_name,
im.status
FROM   item_master  im
JOIN   deps         dp ON dp.dept = im.dept
WHERE  im.status     = 'A'
AND  im.item_level = im.tran_level
AND  NOT EXISTS (
SELECT 1
FROM   item_image ii
WHERE  ii.item       = im.item
AND  ii.image_type = 'A'         -- primary image type
AND  ii.image_addr IS NOT NULL
AND  TRIM(ii.image_addr) != ' '
)
ORDER BY dp.dept_name, im.item_desc;

3. Physical dimensions for an item across all sourcing countries

-- Item dimensions at each packaging level per supplier and country
SELECT d.item,
s.supp_name,
d.origin_country_id,
d.dim_object,
d.weight,
d.weight_uom,
d.length,
d.width,
d.height,
d.lwhdim_uom,
-- Calculated volume in cubic units
ROUND(d.length * d.width * d.height, 4) cubic_volume
FROM   item_supp_country_dim  d
JOIN   sups                   s ON s.supplier = d.supplier
WHERE  d.item = :v_item
ORDER BY d.origin_country_id, d.supplier,
DECODE(d.dim_object, 'EA', 1, 'IN', 2, 'CA', 3);

4. Items missing case dimensions — WMS integration readiness check

-- Active items with a primary supplier but no outer case (CA) dimensions
-- WMS cannot slot or plan freight without case dimensions
SELECT im.item,
im.item_desc,
is2.supplier,
s.supp_name,
is2.primary_country_id
FROM   item_master   im
JOIN   item_supplier is2 ON  is2.item             = im.item
AND is2.primary_supp_ind  = 'Y'
JOIN   sups          s   ON  s.supplier            = is2.supplier
WHERE  im.status     = 'A'
AND  im.item_level = im.tran_level
AND  NOT EXISTS (
SELECT 1
FROM   item_supp_country_dim d
WHERE  d.item              = im.item
AND  d.supplier          = is2.supplier
AND  d.origin_country_id = is2.primary_country_id
AND  d.dim_object        = 'CA'
AND  d.weight            > 0
)
ORDER BY im.dept, im.item_desc;

5. Full item data export — master, images, dimensions and custom attributes

-- Combined item export across all attribute layers
-- Joins ITEM_MASTER, ITEM_IMAGE (primary), ITEM_SUPP_COUNTRY_DIM (case),
-- ITEM_MASTER_EXT (custom), and ITEM_DESC — one row per item
SELECT im.item,
id.item_desc,
id.short_desc,
im.dept,
dp.dept_name,
im.status,
im.unit_of_measure,
-- Primary image URL
img.image_addr                  primary_image_url,
-- Case weight for logistics
dim.weight                      case_weight_kg,
dim.length                      case_length_cm,
dim.width                       case_width_cm,
dim.height                      case_height_cm,
-- Custom extension attributes (add columns as configured)
ext.sustainability_score,
ext.b2b_excl_ind
FROM   item_master          im
JOIN   item_desc            id   ON  id.item = im.item
AND id.lang = (SELECT lang FROM comphead)
JOIN   deps                 dp   ON  dp.dept = im.dept
-- Primary image — LEFT JOIN: items without images still appear
LEFT JOIN item_image        img  ON  img.item       = im.item
AND img.image_type  = 'A'
-- Case dimensions from primary supplier-country — LEFT JOIN
LEFT JOIN item_supplier     is2  ON  is2.item             = im.item
AND is2.primary_supp_ind  = 'Y'
LEFT JOIN item_supp_country_dim dim ON  dim.item              = im.item
AND dim.supplier          = is2.supplier
AND dim.origin_country_id = is2.primary_country_id
AND dim.dim_object        = 'CA'
-- Custom extension table — LEFT JOIN
LEFT JOIN item_master_ext   ext  ON ext.item = im.item
WHERE  im.status     = 'A'
AND  im.item_level = im.tran_level
ORDER BY dp.dept_name, im.item_desc;

Common Gotchas for Developers#

• !

  Hardcoding IMAGE_TYPE values like 'A' or 'B' in integration scripts. The IMAGE_TYPE codes are retailer-configured values in CODE_DETAIL. One implementation uses 'A' for the primary image; another uses 'PRIMARY'; another uses 'F'. Scripts that hardcode these values will silently return no rows in any environment that uses different codes. Always look up the valid codes from CODE_DETAIL WHERE code_type = 'IMTP' and parameterise them.

• !

  Treating a non-NULL IMAGE_ADDR as a valid, reachable image.

  ITEM_IMAGE.IMAGE_ADDR is a free-text field. A row can exist with a value in IMAGE_ADDR that is a broken URL, a path to a file that was deleted, or a placeholder string entered during data migration. The presence of a row in ITEM_IMAGE does not guarantee the image is live. Always validate image reachability in integration pipelines that feed e-commerce or customer-facing systems.

• !

  Adding custom columns directly to ITEM_MASTER instead of ITEM_MASTER_EXT. This is the most damaging implementation mistake in Oracle Retail projects. Custom columns on core tables survive until the next Oracle patch is applied. Any patch that modifies the column structure of ITEM_MASTER will conflict with custom columns and fail. Oracle Support will not assist with upgrade issues on modified core tables. Custom attributes always go into ITEM_MASTER_EXT or a custom XXRC_* table.

• !

  Not creating ITEM_MASTER_EXT rows when items are created. If the extension table pattern is in use, every new item must have a corresponding row in ITEM_MASTER_EXT. Item creation scripts or staging batch processes that do not include this INSERT will leave items without extension records. Queries that INNER JOIN to ITEM_MASTER_EXT will silently exclude those items from all results.

• !

  Querying ITEM_SUPP_COUNTRY_DIM without filtering on DIM_OBJECT.

  A single item can have up to three ITEM_SUPP_COUNTRY_DIM rows per supplier-country combination — one for each DIM_OBJECT (EA, IN, CA). Querying without a DIM_OBJECT filter returns up to three rows per item. Any query that selects dimension values into scalar variables will throw TOO_MANY_ROWS. Always filter by the specific DIM_OBJECT you need for your use case.

• !

  Using ITEM_IMAGE for full-resolution binary storage via LONG RAW or BLOB workarounds.

  Some older implementations attempted to store binary image data in the database using workaround columns on custom extension tables. This makes the RMS database grow very large, slows down all operations on those tables, and creates backup and recovery challenges. Oracle Retail's design intent is clear: ITEM_IMAGE stores references, not data. Binary image storage belongs in a dedicated file system, object store, or DAM system.

Key Takeaways#