BMQL Tutorial: A Beginner's Guide to BigMachines Query Language

BMQL (BigMachines Query Language) is one of the most powerful features in Oracle CPQ Cloud. It allows you to query data from Commerce tables and Data Tables, enabling dynamic pricing, lookups, and complex business logic. This tutorial will get you started with BMQL fundamentals.

What is BMQL?

BMQL is a SQL-like query language specific to Oracle CPQ Cloud. It lets you:

Query Data Tables for pricing, product data, and business rules
Look up Commerce data from transactions and quotes
Filter, aggregate, and transform data within BML scripts
Create dynamic, data-driven configurations

BMQL Syntax Basics

Basic SELECT Query

javascript

// Query a Data Tableresults = bmql("SELECT price, description FROM ProductPricing WHERE sku = $productSku");

// Query a Data Tableresults = bmql("SELECT price, description FROM ProductPricing WHERE sku = $productSku");

Query with Variables

javascript

// Using BML variables in BMQLcustomerTier = "Gold";region = "US-West";discounts = bmql("SELECT discount_percent FROM TierDiscounts                   WHERE tier = $customerTier                   AND region = $region");

// Using BML variables in BMQLcustomerTier = "Gold";region = "US-West";discounts = bmql("SELECT discount_percent FROM TierDiscounts                   WHERE tier = $customerTier                   AND region = $region");

Multiple Conditions

javascript

// AND/OR conditionsproducts = bmql("SELECT * FROM Products                  WHERE category = 'Hardware'                  AND (status = 'Active' OR status = 'Featured')                 AND price > 100");

// AND/OR conditionsproducts = bmql("SELECT * FROM Products                  WHERE category = 'Hardware'                  AND (status = 'Active' OR status = 'Featured')                 AND price > 100");

Working with Results

BMQL returns a Record Set that you iterate through:

javascript

// Iterate through resultspriceTable = bmql("SELECT sku, price FROM Pricing WHERE category = $cat");for row in priceTable {    sku = row["sku"];    price = row["price"];    // Process each row}

// Iterate through resultspriceTable = bmql("SELECT sku, price FROM Pricing WHERE category = $cat");for row in priceTable {    sku = row["sku"];    price = row["price"];    // Process each row}

Checking for Empty Results

javascript

results = bmql("SELECT * FROM Discounts WHERE code = $promoCode");if (sizeofarray(results) == 0) {    return "Invalid promo code";}

results = bmql("SELECT * FROM Discounts WHERE code = $promoCode");if (sizeofarray(results) == 0) {    return "Invalid promo code";}

Common BMQL Operations

Aggregations

javascript

// SUM, COUNT, AVG, MIN, MAXtotals = bmql("SELECT SUM(quantity) as total_qty,                       AVG(price) as avg_price                FROM LineItems                WHERE quote_id = $quoteId");

// SUM, COUNT, AVG, MIN, MAXtotals = bmql("SELECT SUM(quantity) as total_qty,                       AVG(price) as avg_price                FROM LineItems                WHERE quote_id = $quoteId");

LIKE Operator for Pattern Matching

javascript

// Find products starting with "PRO-"products = bmql("SELECT * FROM Products WHERE sku LIKE 'PRO-%'");// Find products containing "Enterprise"enterprise = bmql("SELECT * FROM Products WHERE name LIKE '%Enterprise%'");

// Find products starting with "PRO-"products = bmql("SELECT * FROM Products WHERE sku LIKE 'PRO-%'");// Find products containing "Enterprise"enterprise = bmql("SELECT * FROM Products WHERE name LIKE '%Enterprise%'");

IN Operator

javascript

// Query multiple valuesregions = "('US', 'CA', 'MX')";prices = bmql("SELECT * FROM RegionalPricing WHERE region IN " + regions);

// Query multiple valuesregions = "('US', 'CA', 'MX')";prices = bmql("SELECT * FROM RegionalPricing WHERE region IN " + regions);

ORDER BY

javascript

// Sort resultsproducts = bmql("SELECT * FROM Products                  WHERE category = 'Software'                  ORDER BY price DESC");

// Sort resultsproducts = bmql("SELECT * FROM Products                  WHERE category = 'Software'                  ORDER BY price DESC");

Data Tables vs Commerce Tables

Data Tables

Data Tables store reference data that doesn't change per transaction:

Pricing matrices
Product catalogs
Discount rules
Regional settings

Commerce Tables

Commerce tables store transaction-specific data:

Quote headers
Quote lines
Configuration attributes

javascript

// Query Commerce datalines = bmql("SELECT * FROM _transaction_line WHERE _document_number = $docNum");

// Query Commerce datalines = bmql("SELECT * FROM _transaction_line WHERE _document_number = $docNum");

Best Practices

1. Use Indexes Wisely

Query on indexed columns for better performance:

javascript

// Good: Query on indexed columnresult = bmql("SELECT * FROM Products WHERE sku = $sku");// Slow: Full table scanresult = bmql("SELECT * FROM Products WHERE description LIKE '%widget%'");

// Good: Query on indexed columnresult = bmql("SELECT * FROM Products WHERE sku = $sku");// Slow: Full table scanresult = bmql("SELECT * FROM Products WHERE description LIKE '%widget%'");

2. Limit Result Sets

Only select columns you need:

javascript

// Good: Select specific columnsprices = bmql("SELECT sku, price FROM Products WHERE category = $cat");// Bad: Select all columnsprices = bmql("SELECT * FROM Products WHERE category = $cat");

// Good: Select specific columnsprices = bmql("SELECT sku, price FROM Products WHERE category = $cat");// Bad: Select all columnsprices = bmql("SELECT * FROM Products WHERE category = $cat");

3. Avoid BMQL in Loops

Execute BMQL outside loops when possible:

javascript

// Good: Single BMQL callallPrices = bmql("SELECT sku, price FROM Pricing");for sku in skuList {    // Look up from already-fetched data}// Bad: BMQL in loopfor sku in skuList {    price = bmql("SELECT price FROM Pricing WHERE sku = $sku");}

// Good: Single BMQL callallPrices = bmql("SELECT sku, price FROM Pricing");for sku in skuList {    // Look up from already-fetched data}// Bad: BMQL in loopfor sku in skuList {    price = bmql("SELECT price FROM Pricing WHERE sku = $sku");}

4. Use String Escaping

Prevent BMQL injection by escaping user input:

javascript

// Use cleanalialialialialialiString() or validate inputsearchTerm = cleanString(userInput);results = bmql("SELECT * FROM Products WHERE name = $searchTerm");

// Use cleanalialialialialialiString() or validate inputsearchTerm = cleanString(userInput);results = bmql("SELECT * FROM Products WHERE name = $searchTerm");

Common Errors and Solutions

Error: "Column not found"

Cause: Typo in column name or querying wrong table
Solution: Verify column names in Data Table definition

Error: "Invalid BMQL syntax"

Cause: Missing quotes, parentheses, or invalid operators
Solution: Check SQL syntax, especially string literals

Error: "Query timeout"

Cause: Unindexed query or too much data
Solution: Add indexes, limit results, optimize query

Example: Dynamic Pricing Lookup

Here's a complete example of using BMQL for tiered pricing:

javascript

// Get customer tier from transactioncustomerTier = tier_attr;productSku = sku_attr;quantity = qty_attr;// Look up base pricepriceResult = bmql("SELECT base_price FROM ProductPricing                     WHERE sku = $productSku");if (sizeofarray(priceResult) > 0) {    basePrice = priceResult[0]["base_price"];} else {    return "Price not found";}// Look up tier discountdiscountResult = bmql("SELECT discount_percent FROM TierDiscounts                        WHERE tier = $customerTier                        AND min_qty <= $quantity                        ORDER BY min_qty DESC");if (sizeofarray(discountResult) > 0) {    discountPercent = discountResult[0]["discount_percent"];} else {    discountPercent = 0;}// Calculate final pricefinalPrice = basePrice * (1 - discountPercent / 100);return finalPrice;

// Get customer tier from transactioncustomerTier = tier_attr;productSku = sku_attr;quantity = qty_attr;// Look up base pricepriceResult = bmql("SELECT base_price FROM ProductPricing                     WHERE sku = $productSku");if (sizeofarray(priceResult) > 0) {    basePrice = priceResult[0]["base_price"];} else {    return "Price not found";}// Look up tier discountdiscountResult = bmql("SELECT discount_percent FROM TierDiscounts                        WHERE tier = $customerTier                        AND min_qty <= $quantity                        ORDER BY min_qty DESC");if (sizeofarray(discountResult) > 0) {    discountPercent = discountResult[0]["discount_percent"];} else {    discountPercent = 0;}// Calculate final pricefinalPrice = basePrice * (1 - discountPercent / 100);return finalPrice;

Next Steps

Now that you understand BMQL basics:

Practice with simple queries on Data Tables
Explore Commerce table schemas in your environment
Build reusable BML functions for common queries
Optimize queries for production performance

For more Oracle CPQ tutorials, check out our Oracle CPQ training services.

BMQL Tutorial: A Beginner's Guide to BigMachines Query Language

BMQL Tutorial: A Beginner's Guide to BigMachines Query Language

What is BMQL?

BMQL Syntax Basics

Basic SELECT Query

Query with Variables

Multiple Conditions

Working with Results

Checking for Empty Results

Common BMQL Operations

Aggregations

LIKE Operator for Pattern Matching

IN Operator

ORDER BY

Data Tables vs Commerce Tables

Data Tables

Commerce Tables

Best Practices

1. Use Indexes Wisely

2. Limit Result Sets

3. Avoid BMQL in Loops

4. Use String Escaping

Common Errors and Solutions

Error: "Column not found"

Error: "Invalid BMQL syntax"

Error: "Query timeout"

Example: Dynamic Pricing Lookup

Next Steps

Related Articles

Storing Data as Delimited Strings in Oracle CPQ Cloud :: Anti-Pattern

Naming Conventions are Important in Oracle CPQ Cloud

Need Expert CPQ Help?