Adding AI - Vision Service, Object Detection to APEX

There is a great site of labs by Oracle - LiveLabs. They are using OCI (Oracle Cloud Infrastructure).  One of the labs, named "Build AI-Powered Image Search into your Oracle APEX App" is combining APEX with  AI - Vision Service and uses the IMAGE_CLASSIFICATION option of the service.

I already wrote a post about replacing the default Image Classification option of the Vision service with Text Detection here (and did a little journey to understand better the Lab.)

This time I needed to use the Object Detection option of Vision Service. 

So I will explain the small changes needed to be done in the original Lab and next talk a bit about the difference between Object Detection and Image Classification.

In the picture there are the various options of AI - Vision Service:

All we have to do are 2 small changes:

In LAB 3, Task 2 - Invoke the OCI Vision REST Data Source through a Page Process, step 10  change to:

Click FEATURE_TYPE and enter the following (we replace the IMAGE_CLASSIFICATION value):

Under Value :

• Type: Static Value

• Value: OBJECT_DETECTION

 

In LAB 3, Task 2 - Invoke the OCI Vision REST Data Source through a Page Process, step 14 change the PL/SQL Code to (in the "Parse the Response" part):

UPDATE SM_POSTS
SET
AI_OUTPUT = (
    SELECT
        LISTAGG(obj_name, ',') WITHIN GROUP(
        ORDER BY
            obj_name
        )
    FROM
        JSON_TABLE ( :P1_RESPONSE, '$.imageObjects[*]'
            COLUMNS
                obj_name VARCHAR2 ( 100 ) PATH '$.name[*]'
        )
  )
 WHERE
 ID = :P1_ID;

 

Instead of the original:

UPDATE SM_POSTS
SET
AI_OUTPUT = (
    SELECT
        LISTAGG(obj_name, ',') WITHIN GROUP(
        ORDER BY
            obj_name
        )
    FROM
        JSON_TABLE ( :P1_RESPONSE, '$.labels[*]'
            COLUMNS
                obj_name VARCHAR2 ( 100 ) PATH '$.name[*]'
        )
  )
 WHERE
 ID = :P1_ID;

 

What is the difference?

The original feature of the lab -Image Classification,  Assigns classes and confidence scores based on the scene and contents of an image.

This post option - Object Detection, Identifies objects and their location within an image along with a confidence score.

 

Let's see an example. For the following, very simple picture we will get 2 responses:

Image Classification returns: Human arm,Human face,Human hand,Human head,Human nose

Object Detection returns: Helmet,Human face,Person

Here are the 2 JSON files returned:

Image Classification:

 {
  "labels": [
    {
      "name": "Human head",
      "confidence": 0.9931826
    },
    {
      "name": "Human hand",
      "confidence": 0.99312717
    },
    {
      "name": "Human arm",
      "confidence": 0.9930545
    },
    {
      "name": "Human nose",
      "confidence": 0.99297017
    },
    {
      "name": "Human face",
      "confidence": 0.9926239
    }
  ],
  "ontologyClasses": [
    {
      "name": "Human head",
      "parentNames": [],
      "synonymNames": []
    },
    {
      "name": "Human nose",
      "parentNames": [],
      "synonymNames": []
    },
    {
      "name": "Human hand",
      "parentNames": [],
      "synonymNames": []
    },
    {
      "name": "Human face",
      "parentNames": [],
      "synonymNames": []
    },
    {
      "name": "Human arm",
      "parentNames": [],
      "synonymNames": []
    }
  ],
  "imageClassificationModelVersion": "1.5.97",
  "errors": []
}

Object Detection:

{
  "imageObjects": [
    {
      "name": "Helmet",
      "confidence": 0.9530024,
      "boundingPolygon": {
        "normalizedVertices": [
          {
            "x": 0.332,
            "y": 0.1619190404797601
          },
          {
            "x": 0.531,
            "y": 0.1619190404797601
          },
          {
            "x": 0.531,
            "y": 0.39580209895052476
          },
          {
            "x": 0.332,
            "y": 0.39580209895052476
          }
        ]
      }
    },
    {
      "name": "Person",
      "confidence": 0.8978557,
      "boundingPolygon": {
        "normalizedVertices": [
          {
            "x": 0.169,
            "y": 0.17541229385307347
          },
          {
            "x": 0.742,
            "y": 0.17541229385307347
          },
          {
            "x": 0.742,
            "y": 0.9940029985007496
          },
          {
            "x": 0.169,
            "y": 0.9940029985007496
          }
        ]
      }
    },
    {
      "name": "Human face",
      "confidence": 0.8083062,
      "boundingPolygon": {
        "normalizedVertices": [
          {
            "x": 0.363,
            "y": 0.34182908545727136
          },
          {
            "x": 0.507,
            "y": 0.34182908545727136
          },
          {
            "x": 0.507,
            "y": 0.5847076461769115
          },
          {
            "x": 0.363,
            "y": 0.5847076461769115
          }
        ]
      }
    }
  ],
  "ontologyClasses": [
    {
      "name": "Human face",
      "parentNames": [],
      "synonymNames": []
    },
    {
      "name": "Helmet",
      "parentNames": [
        "Sports equipment",
        "Clothing"
      ],
      "synonymNames": []
    },
    {
      "name": "Person",
      "parentNames": [],
      "synonymNames": []
    },
    {
      "name": "Sports equipment",
      "parentNames": [],
      "synonymNames": []
    },
    {
      "name": "Clothing",
      "parentNames": [],
      "synonymNames": []
    }
  ],
  "objectDetectionModelVersion": "1.3.557",
  "errors": []
}

 

 

Oracle Function Exception - No_DATA_FOUND

 I wanted to write a function that accepts 2 parameters. An Item_ID and Manufacturer and returns the support time.

In most cases the first parameter (Item_ID) is sufficient. There are few cases we need the Manufacturer as well.

Unfortunately sometimes customers don't enter the second parameter correctly or I get a NULL there instead of the Manufacturer. In those cases I still want to return a value based on the first parameter (Item_ID) only.

At first I tried using few selects into variables in the function and doing if then else on the result... But it broke each time I didn't have value for the second parameter. I could write a second function with exception and call it from the main function. I preferred this solution, though there are some extreme cases it might not cover.

So I used the Exception option in the function code here:

create or replace function f_SUPPORT (P_ITEM IN NUMBER, P_MANUFACTURER IN VARCHAR2)
RETURN NUMBER is SUP NUMBER;
BEGIN
select Support_mnth into SUP from warranty where item=p_item and MANUFACTURER=P_MANUFACTURER;
return(SUP);
EXCEPTION
   WHEN NO_DATA_FOUND THEN  
select Support_mnth into SUP from warranty where item=p_item;
return(SUP);
end;
/