A quick reminder to my self:
When creating a parameter and want to use it in filter / calculation, there must be a default. For date default this is the default format:
DATE '2014-04-12'For example:
Val_Date = @parameter("P_DATE")(DATE '2014-04-12')
I need to find the last occurrence of a specific character in a string (and using only the part that follows that character). For example in a string 'abc-de-fghi-jklm' I want to locate the last '-' and get the sub-string of 'jklm'. In my cases there is unknown number of '-' characters.
The basic function to locate a sub-string in a string is "Locate".
While it seems Locate has only 2 parameters (the string we are searching and the full string), actually they are 3 parameters. The third one is optional: start position.
LOCATE(CharacterExpression, CharacterExpression [, StartPosition] )
The Start Position can accept negative values as well, meaning search from the end of the string.
So in my case Locate('-', 'abc-de-fghi-jklm', -1) is the solution.
To get the sub-string 'jklm' I will use
SUBSTRING( 'abc-de-fghi-jklm'
FROM Locate('-', 'abc-de-fghi-jklm', -1)+1)
The SUBSTRING function parameters are:
SUBSTRING(CharacterExpression FROM StartPosition [FOR length])
Once in a while I install OAS (Oracle Analytics Server) on my laptop. I do it for testing purposes mostly... I don't want anyone but myself to connect to this OAS. Sort of rich man's OA Desktop.
First of all, don't do it. Even if you want OAS on a laptop, you would better use a VM or Docker. This type of installation is not supported and usually is problematic.
This post is for myself, so I will stop reinventing the wheel each time I do it...
⛔
Problem:
Work around:
⛔
Once done remember to stop and start OAS on the same network.
Oracle has released the Oracle Analytics Server - OAS 2024 (7.6) October 2024 bundle patch, specifically 09-Oct-2024 patch. You can find it at the regular place: Doc ID 2832967.2 in the OAS tab.
The patch requires upgrade of OPatch, at least to version 13.9.4.2.13. For some reason I couldn't find the relevant OPatch version at the regular place (https://updates.oracle.com/download/6880880.html) where the FMW patch is described as Obsolete, (correct for 24 Oct. 2024).
You can visit this support note (Patch 28186730: OPATCH 13.9.4.2.17 FOR EM 13.5 AND FMW/WLS 12.2.1.4.0 AND 14.1.1.0.0) to download OPatch 13.9.4.2.17 that should do the trick.
I wrote about Oracle Spatial in the past. (Intro, data loading Points, Lines, Polygons). Now I had a new challenge.
We had a POC. Customer wanted to test performance of 7 million data points in 3200 polygons. In the POC I learned there is difference in performance, based on the way we create the query. I was lucky to gain some guidance from Daniel Geringer (who wrote this guide) of Oracle Spatial. Bellow is the summary of the process. It was done on Autonomous Database (ADW), but should be similar on any Oracle DB.
First part will cover the performance benefits from using the proper function. Second, some advisable data setting and cleaning steps we did.
Customer loaded ~7 million data points (lng/lat) of USA accidents from a similar data source and loaded to ADW. Loaded ~3200 US counties from this or similar source.
We could update the longitude and latitude (lng/lat) data and place them in a new spatial format column. For many reasons we preferred not to. We worked directly with the lng/lat points and the get_geometry function (see page 25 here or slightly longer version here). Next created index (see pages 26, 27 here)... and fixed some inconsistencies (see second part, bellow).
My default intention was to use the SDO_CONTAINS function. Instead of SDO_CONTAINS, Daniel suggested top use SDO_ANYINTERACT or SDO_INSIDE instead and to switch the order of the geometry parameter to the spatial operator.
SDO_ANYINTERACT can be used if it's ok to return points on the polygon boundary. If the requirement is only to return points inside the polygon, SDO_INSIDE should be used. In switching the geometry order, there is a huge difference between running 3200 times comparisons for 7 million points and running 7 million times comparisons for 3200 polygons. In our tests it was ~390 seconds vs. ~5 seconds.
select count(*) from accident a, counties c where SDO_... ='TRUE';
Let's compare the following:
In all cases we had level 8 of parallel execution:
This is 80 times faster!
Both SDO_INSIDE and SDO_CONTAINS return the same number (6,973,650) while both SDO_ANYINERACT return slightly higher number (6,973,846).
So we have 2 winners that can use the better parameters order.
I will focus on this option:
select count(*)I wanted to see how does the parallelism influence this query performance. I rerun it with various combinations using ADW High connection (bellow 8 ECPU explain plan):
What happens if we increase the amount of data?
I duplicated the original data 21 times, while moving each data points randomly by 10-100 meters each time towards the center of USA. I didn't want duplicated locations for the test.
The result was a table of over 146 million locations. I used similar query and tested it with various amounts of ECPU. Here are the results:
For better performance , before execution, it is advisable to:
We validated the Counties table
and received lots of errors of the type: 13367 [Element <1>] [Ring <1>]
Daniel explained that it is probably due to polygon directionality. It was solved by running the sdo_util.rectify_geometry like this:
Good code for similar problems is in the blog post by Hans Viehmann here.
See also the Spatial Data Validation chapter (slides 3-8) in the Oracle Spatial Best Practices and Tuning Tips by Daniel Geringer here.
Last part was the index creation. While I covered index creation for Points, Lines, Polygons earlier, I learned there is performance benefit in creating CBTREE organized index for streaming point data. See slides 29, 30 here.
Our code was:
One more interesting option to test is the SDO_POINTINPOLYGON function that was described Jean Ihm here and Karin Patange here.
Oracle published Critical Patch Update (CPU) Advisor For Oracle Analytics Server and Oracle Business Intelligence - Updated for April 2024 (Doc ID 2832967.2).
In the above described link there is a bundle patch available (currently, at Aug-2024, it's: Patch 36823847 July 2024 - "OAS Stack Patch Bundle 7.6.0.0.240710, or later" + a Java patch). Use the last bundle patch and not the ones described bellow. The remarks at the end of the post are still relevant.
This list is expected to be updated regularly.
Right now there 2 major parts there: OAS Weblogic & OAS FMw Infrastructure. The first OAS 2024 bundle patch is not available yet.
Under Weblogic we see
Few extra remarks:
In OAS 2024 there is a new option of Workbook export to Excel (on Tables and Pivots). To make it work you need to follow Installation Guide and install the NodeJS.
In OAS 2024 you can use the classic data modeler client for RPD or use the new Semantic Model web interface with similar functionality. If the Semantic Model is missing from the Menu after upgrade from previous versions, you have to grant missing permissions that might have been replaced by the import. See "After Importing A Snapshot into Oracle Analytics Server (OAS), The Semantic Modeler Feature Is No Longer Available (Doc ID 3011914.1)": Use Grant Permission Assignment and grant 'semanticmodeler.author' permission to your administrator role.
Thank You Itsik Hadar for finding the last 2 bullets.
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": []
}
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 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:
Connect, model and data preparation
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:
As well as my old Installing Windows OAS 5.5 post.
Or you can read the Oracle installation and Configuration Guide.
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"
}
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.
