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;
/

Oracle Analytic Server 2024 is Available

Oracle Analytic Server new release is available. The internal name is OAS 7.6 and the formal is OAS 2024. 

If you are OBIEE or OAS owner, allow me to recommend you one thing (accept the Sunscreen part); Consider upgrading to this version. I didn't recommend anything of the kind in previous releases. This release is expected to have a mature version data visualization, beyond the classic BI.

It is available on https://edelivery.oracle.com/ as OAS 7.6 for Linux and Windows:

 

See the Oracle Blog here: https://blogs.oracle.com/analytics/post/announcing-the-general-availability-of-oracle-analytics-server-2024

The documentation here: https://docs.oracle.com/en/middleware/bi/analytics-server/books.html

And the What's new section is here.

Since the OAS release is usually mostly catching up of the OAC yearly changes, the OAC updates videos from last year might be a good resource as well: https://www.youtube.com/@OracleAnalytics/playlists.

In the Oracle blog there are links to few OAS specific YouTube playlists:

Data Visualization

Connect, model and data preparation

AI & ML

P.S. 

Gianni Ceresa created a Docker image for this version as well: https://github.com/gianniceresa/docker-images/tree/master/OracleAnalyticsServer  

Hope to see updates here as well: https://github.com/oracle/docker-images 

It seems that Gianni's installation guides for 2023 release on Linux are still relevant for the new 2024 release, with the proper file update:

Part 1

Part 2

Part 3

As well as my old Installing Windows OAS 5.5 post.

Or you can read the Oracle installation and Configuration Guide.

  

Implementing the post - Adding AI - Vision Service, Text Detection to APEX

 Following my post Adding AI - Vision Service, Text Detection to APEX I tried to implement it on our own system. Had few problems... Here is the summary. 

The process didn't work so I turned of the "Run in Background" and discovered, instead of the expected JSON content in Response column, the error 

{
"code" : 400,
"message" : "Input file type is not supported"
}

Fi

Fixing the BLOB column definition according to the one in Oracle Lab, solved the problem.

Type: File Upload

Storage definitions

Adding the columns FILE_MIME & FILE_NAME to the table.

Last but not least, the Analyze Image was the last Process I created. It is always a good idea to move it before the CloseDialog or any similar process.