Site search

form.antibot { display: none !important; } You must have JavaScript enabled to use this form.

Aggregator

[latest] [categories]
Please use the contact form to include your blog here, suggest a feed, or give feedback.

If your blog is aggregated on OraFAQ, you may want to display this image on your blog:

DBA Blogs

A Picture Is Worth A Thousand Words: Dashboards To The Rescue

Flavio Casetta - Mon, 2026-10-26 09:07

Building an application in the age of AI assisted development is only half the battle.

With an APEX application hosted on OCI, monitoring resource consumption is vital, because with the free tier you cannot exceed certain volumes of requests, whether they are REST requests or emails or notifications.

A well-crafted dashboard, beyond just eye-catching aesthetics, can help you spot problems that might otherwise go unnoticed until the application runs out of resources and stops working.

The position of the big blue circle shows there is something wrong with the embedding calls

Spotting anomalies at a glance:

A prime example of a dashboard's utility is how it highlights hidden inefficiencies and runaway processes.
In the API requests visualization, in the lower left portion of the image, a massive blue circle was dominating the chart, immediately drawing my attention.

The big blue bubble instantly signals an anomaly in the system's traffic pattern.
The position of its centre on the Y axis compared to the position of other types of requests over the week reveals that the application is currently executing a disproportionate number of embedding requests to the LLM.
Looking at the chart I could immediately start investigating the rogue document processing procedure.

SELECT 
  -- Chronological X: 6 = 6 days ago, 0 = Today
  to_char(w.request_date,'Day') as dy,
  (trunc(w.request_date) - trunc(sysdate)) as x_value,
  nvl(regexp_substr(w.url,'(http(s)?:/)(/([^/]+))+',1,1,'c',4), 'UNKNOWN') as series,
  count(*)                                     as requests,    -- Y-axis
  sum(elapsed_sec)                             as elapsed,     -- Bubble radius
  to_char(w.request_date,'Day') || '
Requests: ' || count(*) || '
Time: ' || sum(elapsed_sec) as custom_tooltip
FROM apex_webservice_log w
WHERE w.request_date >= trunc(sysdate) - 6 and w.request_date < trunc(sysdate) + 1
GROUP BY to_char(w.request_date,'Day'),
         (trunc(w.request_date) - trunc(sysdate)), 
         nvl(regexp_substr(w.url,'(http(s)?:/)(/([^/]+))+',1,1,'c',4), 'UNKNOWN')
ORDER BY x_value desc, series;

Dashboards can bridge the gap between hundreds of log entries in an APEX internal view and the need to extract and present the significant information in an efficient and concise way.

Last but not least, AI can suggest the critical resources to monitor and how to best represent the information with a chart if you are short of ideas.

Categories: DBA Blogs

Defending Your PL/SQL Code: Why Anchoring with %TYPE Saves the Day

Flavio Casetta - Mon, 2026-10-19 04:59

You are loading an associative array and you are getting the following error:

ORA-06502: PL/SQL: value or conversion error: 
associative array key violates its type constraints

image created by Google Gemini
When I asked Gemini for this error message it said it had to do with a null value in the key but this is false, if the key value is null you'll get a different error message:

ORA-06502: PL/SQL: value or conversion error: NULL index table key value

And there is also another interesting fact, if you explicitly assign an array element indexed by varchar2(n), in this fashion:

g_arr('') := 'something';

no error whatsoever, the whole array is wiped out, as if you executed g_arr.delete, and it's not clear to me if this is a bug or expected behavior.

So let's see the real reason for "associative array key violates its type constraints":

Create table config (config_key varchar2(50), config_value varchar2(500));

insert into config values('some_key', 'some value');
insert into config values('some_key_with_a_long_name', 'some other value');

declare
  type t_associative_array is table of varchar2(500) index by varchar2(10);
  g_arr      t_associative_array;
  k          varchar2(50);
begin
    FOR rec in (select config_key, config_value from config)
    LOOP
        g_arr(rec.config_key) := rec.config_value;
    END LOOP;

    k := g_arr.first;
    WHILE k IS NOT NULL LOOP
    DBMS_Output.PUT_LINE ('''' || k || ''':''' || g_arr(k)||'''');
    k := g_arr.NEXT(k);
  END LOOP;
 
end;
/

And bum!

ORA-06502: PL/SQL: value or conversion error: 
associative array key violates its type constraints

The problem is that you declared the associative array key to be INDEX BY VARCHAR2(10), but the value coming from table is longer.

This type of problems can easily happen when you add new values over time whose length can be longer than original ones and you were a bit lazy when you wrote the code, declaring some variables with explicit data types to save some typing.

To avoid surprises you can rewrite the script as follows:

declare
  type t_associative_array is table of config.config_value%type
                                       index by config.config_key%type;
  g_arr      t_associative_array;
  k          config.config_key%type;
begin
    FOR rec in (select config_key, config_value from config)
    LOOP
        g_arr(rec.config_key) := rec.config_value;
    END LOOP;

    k := g_arr.first;
    WHILE k IS NOT NULL LOOP
    DBMS_Output.PUT_LINE ('''' || k || ''':''' || g_arr(k)||'''');
    k := g_arr.NEXT(k);
  END LOOP;
 
end;
/

This time it will output the values without errors.

'some_key':'some value'
'some_key_with_a_long_name':'some other value'

Statement processed.

Is this error specific for associative array key ?
Yes it is, if the problem is in the length of the value, you will get this error instead:

ORA-06502: PL/SQL: value or conversion error: character string buffer too small

This distinction is good because it pinpoints the root cause of the problem.

Last but not least: watch out for the column and variable length semantics!

If you store the keys containing some variable length characters and you defined the associative array using byte semantics using explicit data types, you could incur in the same errors and be confused as the length of the key or the value seem to be within the valid range.

The best solution, and best practice by the way, is to define the array and the variables as shown above, referring to the table columns definition.

Categories: DBA Blogs

Striim MDR on PostgreSQL: Fixing striimsecuritytable does not exist

DBASolved - Fri, 2026-10-16 16:31

Striim won’t start on a new PostgreSQL MDR? If postgres owns the striim schema, here’s how to find it and fix the issue.

The post Striim MDR on PostgreSQL: Fixing striimsecuritytable does not exist appeared first on DBASolved.

Categories: DBA Blogs

A Practical Guide to Oracle Regex

Pakistan's First Oracle Blog - Wed, 2026-10-14 20:29

icon), then paste everything below this comment.-->

If you've ever pulled thousands of rows into an application just to check whether a product code looks right, this post is for you. Oracle's built-in regular expression functions let you validate, search and tidy text right where the data lives, so you don't need extra string-handling code in every app that touches the table.

The toolkit in one glance

Oracle gives you one condition and four functions:

Name What you get back REGEXP_LIKEA true/false test, used in WHERE clauses and constraints REGEXP_COUNTHow many times a pattern shows up REGEXP_INSTRThe position where a match starts (or ends) REGEXP_SUBSTRThe matching text itself REGEXP_REPLACEThe string with matches swapped for something else

Each accepts an optional match-parameter string. The useful flags are i (ignore case), c (respect case), n (let the dot match a newline), m (treat each line as its own line for anchors) and x (ignore spaces in the pattern).

Setting up the sample data

CREATE TABLE stock_items (
  sku            VARCHAR2(20),
  item_desc      VARCHAR2(100),
  supplier_email VARCHAR2(80),
  delivery_date  VARCHAR2(10),
  handling_note  VARCHAR2(200)
);

INSERT INTO stock_items VALUES
 ('BRK-0215',  '  Heavy duty   brake rotor 330mm ', 'accounts@brakeworks.example.com.au', '07/03/2026',
  '<note>fragile</note><note>keep dry</note>');
INSERT INTO stock_items VALUES
 ('ROT-1180B', 'Rotor rotor bolt kit',  'sales@boltbarn.example.com.au', '19/11/2026',
  '<note>pallet only</note>');
COMMIT;

1. Guarding the door with a CHECK constraint

Say every SKU must be three capital letters, a hyphen, four digits, and optionally one trailing capital letter. One constraint handles it for every application:

ALTER TABLE stock_items ADD CONSTRAINT ck_sku_shape
  CHECK (REGEXP_LIKE(sku, '^[A-Z]{3}-[0-9]{4}[A-Z]?$', 'c'));

The anchors ^ and $ force the whole value to fit the pattern, and the c flag keeps lowercase letters out. Results:

BRK-0215    accepted
ROT-1180B   accepted
brk-0215    rejected (lowercase)
BRK0215     rejected (no hyphen)
BRK-215     rejected (only three digits)
BRK-0215BB  rejected (two trailing letters)

2. Counting words

SELECT item_desc,
       REGEXP_COUNT(item_desc, '\w+') AS word_count
FROM   stock_items;

For 'Rotor rotor bolt kit' this returns 4. It's a quick way to flag descriptions that are too short to be useful.

3. Pulling out a piece of text

To get each supplier's domain, capture it in a group and ask for that group with the sixth argument:

SELECT REGEXP_SUBSTR(supplier_email, '@(\S+)$', 1, 1, NULL, 1) AS supplier_domain
FROM   stock_items;

brakeworks.example.com.au
boltbarn.example.com.au

The @ is matched but left out of the result, because only the bracketed group is returned.

4. Finding where something sits

SELECT REGEXP_INSTR('Rotor kit 330mm', '\d')           AS first_digit,
       REGEXP_INSTR('Rotor kit 330mm', '\d+', 1, 1, 1) AS just_after_number
FROM   dual;

FIRST_DIGIT  JUST_AFTER_NUMBER
-----------  -----------------
         11                 14

The fifth argument, 1, asks for the position immediately after the match instead of its start.

5. Cleaning up messy whitespace

Our first row has leading, trailing and doubled spaces:

SELECT REGEXP_REPLACE(TRIM(item_desc), '\s{2,}', ' ') AS tidy_desc
FROM   stock_items;

' Heavy duty brake rotor 330mm ' becomes 'Heavy duty brake rotor 330mm'. Any run of two or more whitespace characters collapses into one space.

6. Back references: reshuffling and spotting repeats

A back reference lets you reuse what a group captured. Here we convert Australian-style dates (DD/MM/YYYY) to ISO format:

SELECT delivery_date,
       REGEXP_REPLACE(delivery_date,
                      '^(\d{2})/(\d{2})/(\d{4})$',
                      '\3-\2-\1') AS iso_date
FROM   stock_items;

07/03/2026   2026-03-07
19/11/2026   2026-11-19

Rows that don't fit the pattern pass through unchanged, so test for that before trusting the output.

Back references also catch accidental repeats, such as "Rotor rotor":

SELECT sku
FROM   stock_items
WHERE  REGEXP_LIKE(item_desc, '(^| )(\w+) \2( |$)', 'i');

Group 2 captures a word, and \2 demands the same word straight after it. This flags ROT-1180B.

7. Greedy versus lazy matching

By default, quantifiers grab as much as they can. Adding ? makes them take as little as possible:

SELECT REGEXP_SUBSTR(handling_note, '<note>.*</note>')  AS greedy,
       REGEXP_SUBSTR(handling_note, '<note>.*?</note>') AS lazy
FROM   stock_items
WHERE  sku = 'BRK-0215';

GREEDY                                          LAZY
----------------------------------------------  ---------------------
<note>fragile</note><note>keep dry</note>      <note>fragile</note>

Greedy runs to the last closing tag, while lazy stops at the first.

Gotchas worth remembering

Character classes need two sets of brackets. Write [[:upper:]]+, not [:upper:]+. The inner pair names the class and the outer pair makes it a list.
Don't assume Perl, Python or JavaScript syntax carries over. Oracle supports shortcuts like \d, \w and \s, but not word-boundary markers (\b) or lookaheads.
Escape literal metacharacters. To match a real dot or bracket, use \. or \(.
Mind performance. A regex condition on a large column usually can't use an ordinary index. If a pattern query is hot, consider a function-based index or a cheaper pre-filter such as LIKE.
Multibyte text works, but write characters directly. Oracle matches on encoded bytes rather than \uXXXX escapes.

Wrapping up

Start with a constraint on your messiest column, since that's where the payoff is quickest. Then add REGEXP_SUBSTR and REGEXP_REPLACE to your reporting queries. Once you've written a few patterns, you'll find a lot of application-side string handling can simply be deleted.

Categories: DBA Blogs

RAG Time with APEX for #JoelKallmanDay

Flavio Casetta - Wed, 2026-10-14 03:16

What is the best approach when it comes to execute a semantic search for RAG (Retrieval Augmented Generation) in OllAPEX ?

OllAPEX is an Oracle APEX workbench designed for local LLM integration via Ollama, with semantic search over documents (such as Oracle skills repositories or excerpts from Oracle documentation) amongst other functions.

The main purpose of semantic search report applied to the Oracle Skills or other Oracle documents is to test a search before implementing a RAG mechanism inside OllAPEX itself.

The Real-World Context: Semantic Search in OllAPEX

In OllAPEX, semantic search allows users to query vector-embedded markdown files directly from an APEX interface.

               [ User Input Topic / Query ]
                           │
                           ▼
          ollapex.semantic_search(:TOPIC, ...)
                           │
                           ▼
          JSON_TABLE (Extract Top N Chunks)
                           │
                           ▼
    Join with OLLAPI_DOCUMENT_CHUNKS & UPLOADS
                           │
                           ▼
           [ Render Results in a report  ]

The underlying SQL query calls a PL/SQL vector search function ollapex.semantic_search(), parses the returning JSON payload via JSON_TABLE, and joins the parsed rows with document chunk metadata tables (ollapi_document_chunks and ollapi_uploads_for_embedding). Here is the user-facing application displaying semantic search results for queries over chunks of Oracle skills in markdown format:

picture #1, semantic search report

The natural execution:

The initial version of the query behind the report looked like this:

picture 2, semantic search query
And its execution plan:

picture 3, execution plan for the initial semantic search query
Then I thought: how can I ensure that the CTE is executed only once as I don't want that the prompt embedding function is called more times than necessary ?

The wannabe "optimized" query: forcing MATERIALIZE

To ensure the vector search function in the CTE executes only once, I added a /*+ MATERIALIZE */ hint:

picture 4, query with MATERIALIZE hint added
And this is the execution plan that I got:

picture 5, execution plan for the semantic search query with MATERIALIZE

As you see the cost is slightly increased and now there is a FULL scan on the temporary table created by the MATERIALIZE hint. No big deal but it doesn't look like an improvement to me.

But wait, is this the best way to tackle this problem?

The good old mantra: keep it easy!

In the end if I want to be sure that the embedding function is executed only once and there are no dependencies from what follows, then I am better off separating the embedding phase from the search phase:

dynamic action doing the embedding before running the report

So when the user clicks on the search button, a dynamic action performs the embedding on user's prompt and returns the results as a JSON array (CLOB).

The final query eventually uses the vector to perform the semantic search:

final semantic search query

Since there are no vector indexes defined (yet), the execution plan remains identical to that of picture 3, thus performing a so-called "exact" similarity search but now I am 100% sure that the embedding request will be performed just once.

Have a nice #JoelKallmanDay, remembering Joel, Scott and Carl.

Categories: DBA Blogs

Database Space Usage

Tom Kyte - Sun, 2026-10-11 23:04

We are in the process of purging/deleting data from some large tables ex. Our F42199 table had 63 million rows from which we deleted 10 million rows. Since deleting the 10 million rows we have inserted 4 millions new rows Prior to purging the 10 million rows the size of the F42199 table was 158GB derived by querying the dba_extents view. Today the table has grown to 260 GB, much to our surprise as we thought the empty blocks from the deleted rows would be reused. So the question is why are the empty blocks not being reused or why is the table size increasing?

Categories: DBA Blogs

Oracle Spatial Studio

Tom Kyte - Sun, 2026-10-11 23:04

Can an end user create spatial data in Oracle Spatial Studio by digitizing as line or polygon on an apex created front- end map screen and if so, can the user digitized feature be stored as sdo geometry in the Oracle database? For example, a technician entering a road project text data would be able to digitize the road within the Oracle cloud infrastructure using oracle spatial studio like you can with qgis and postgresql.

Categories: DBA Blogs

Striim 5.4.2 on macOS: Using Oracle Instant Client 26ai Drivers

DBASolved - Sun, 2026-10-11 16:08

Running Striim 5.4.2 on macOS against Oracle 26ai? Swap the bundled 21 drivers for Instant Client jars and set NATIVE_LIBS.

The post Striim 5.4.2 on macOS: Using Oracle Instant Client 26ai Drivers appeared first on DBASolved.

Categories: DBA Blogs

Before the Ice Cream Melts: Catching Refrigeration Trouble Early with Oracle AI Data Platform

Pakistan's First Oracle Blog - Sat, 2026-10-10 00:14

A refrigerated truck rarely fails with a bang. It drifts. The trick is noticing the drift while the cargo is still safe.

A Tuesday Night on the Highway

Picture a fleet of refrigerated trucks carrying vaccines, dairy, and frozen food overnight. Each truck produces a steady stream of readings: cargo temperature, compressor current, door-open time, vibration, and fuel use. Those readings usually sit in separate places, such as telematics portals, maintenance systems, and spreadsheets.

When someone has to hunt across all of them, problems get found late. This post sketches how a fleet operator could use Oracle Cloud Infrastructure (OCI) and Oracle AI Data Platform to connect those signals and move from reacting to predicting. It is an adapted scenario based on a published Oracle reference approach for industrial IoT, not a customer case.

The Cast of Oracle Services Oracle AI Data Platform

The backbone. It brings ingestion, processing, storage, analytics, and AI into one connected foundation, so raw truck telemetry becomes curated data that other teams can reuse.

OCI Streaming

The intake pipe. Spark publishes sensor events to it through its Apache Kafka-compatible endpoint, which moves high-volume readings toward processing.

Spark on OCI

The cleanup crew. It validates and enriches incoming data and standardizes timestamps, units, and measurements, so a Fahrenheit reading from one truck and a Celsius reading from another do not get mixed up.

Oracle Analytics Cloud

The control room screen. Dispatchers and maintenance planners see trends, anomaly flags, and forecasts in shared dashboards.

Oracle Fusion Data Intelligence

The memory. It can combine maintenance history and work-order data with the operational picture, so a strange reading can be compared with what was last repaired on that unit.

Following One Reading from Truck to Technician

Sense. Sensors on the trailer capture temperature, compressor behavior, and door activity.
Stream. Events flow into OCI Streaming through the Kafka-compatible endpoint.
Prepare. Spark checks, enriches, and standardizes the data.
Store. Object Storage and data-lake layers keep both current and historical readings.
Learn. Models flag unusual behavior and forecast where signals are heading.
Show and alert. Dashboards and notifications reach the people who can act.

Good predictions depend on good data first. The machine learning only works if the pipeline underneath it is reliable and consistent.

Smarter Than a Fixed Alarm

A simple threshold alarm only fires after the temperature crosses a line. Anomaly detection looks at patterns across signals, such as a compressor drawing more current while the cargo temperature creeps upward. That pattern can deserve attention even though no single reading has crossed a limit yet.

Forecasting adds time. If a signal is expected to keep moving the wrong way, planners get a chance to schedule a check at the next depot instead of dealing with a spoiled load on the road.

What a Good Alert Looks Like

An alert can name the affected signal, the truck or trailer, and the time of the event. The people receiving it should treat it as a prompt to look closer, not as proof that something has broken. They confirm against the dashboard trend, decide whether to inspect, and record what they decided.

Two Screens That Do the Heavy Lifting Fleet health view in Oracle Analytics Cloud

Dashboards can combine sensor trends, anomaly indicators, forecasts, maintenance dates, and work-order readiness. One page answers the question: which trailers need eyes on them first?

Maintenance context in Fusion Data Intelligence

Teams can identify units with higher anomaly rates, compare predicted maintenance dates with scheduled ones, and review asset trends. That helps with prioritizing work and with efforts to limit unplanned downtime.

Ask Questions in Plain Language

The same curated data can feed AI-assisted reporting. A manager could ask which routes showed the most door-open anomalies this week and explore possible causes without waiting for a separate report. Oracle also notes that Oracle Analytics Cloud data models can be reached through supported MCP clients for natural-language and developer workflows. The MCP tools are described as a preview feature, so review the documentation before testing.

Five Things to Settle Before Launch

Data rules. Agree on expected ranges, units, timestamp conventions, and what happens when a sensor goes quiet.
Who owns each alert. Name the responsible team, severity levels, notification channel, and escalation path.
Model upkeep. Track false alarms and misses, watch for drift, and decide when to retrain or roll back.
Access control. Consider least-privilege access, OCI IAM policies, compartment boundaries, encryption, network controls, and audit logging.
A human in the loop. Decide how alerts get verified, how decisions are recorded, and how outcomes are reviewed.

Where to Go Next

Start by reading the documentation for OCI Streaming, Oracle AI Data Platform, Oracle Analytics Cloud, and Oracle Fusion Data Intelligence. Then test the pipeline, access controls, model monitoring, and response workflow against your own fleet and your own cargo rules.

The Takeaway

The value is not in collecting more data. It is in connecting what you already collect so the right person sees the right signal early enough to act. Done well, that points toward fewer unplanned breakdowns, steadier cargo quality, and calmer nights for dispatch.

Categories: DBA Blogs