Tokenizing a String : Using Regular Expressions

In my previous blog post I gave some PL/SQL that performed the tokenising of a string. Check out this blog post here.

Thanks also to the people who sent me links examples of how to tokenise a string using the MODEL clause. Yes there are lots of examples of this out there on the interest.

While performing the various searches on the internet I did come across some examples of using Regular Expressions to extract (more...)

BUCKET_WIDTH: Calculating the size of the bucket

Some time ago I had some blog posts introducing some of the basic Statistical function available in Oracle. Here are the links to these.

Tokenizing a String

Over the past while I've been working a lot with text strings. Some of these have been short in length like tweets from Twitter, or longer pieces of text like product reviews. Plus others of various lengths.

In all these scenarios I have to break up the data into individual works or Tokens.

The examples given below illustrate how you can take a string and break it into its individual tokens. In addition to tokenising (more...)

Order APEX column based on hidden data

An occasional question in the forums relates to issues ordering a particular column. It's one of those things that will probably keep coming up, so it's worth having another reference out here on the web.

The basic example stems from the need to order data that might contain characters.

with data as
(select '1' vc from dual union all
select '11' vc from dual union all
select '2' vc from dual union all
select 'a' (more...)

Returning BLOB file size

Occasionally I'll want some form of report noting file sizes of blobs in a database.

The solution is relatively simple, and I thought I'd write it up here for a place to copy syntax each time.

APEX users also have a handy table to verify this against (apex_application_files). Well, a synonym/view that ultimately maps to the core table wwv_flow_file_objects$.

It contains a doc_size column, which is no doubt evaluated at some point during upload of (more...)

Default-Werte für Datumsintervallendpunkte

Stew Ashton, der sich in seinem Blog in der Regel mit komplexen SQL-Queries beschäftigt, hält dort ein Plädoyer für den Verzicht auf NULL-Werte bei der Angabe der minimalen und maximalen Werte für Datumsintervalle. Stattdessen spricht er sich für die Verwendung der klassischen Minimal- und Maximalwerte aus, die durch den Oracle Datentyp DATE nahegelegt werden, also den 01.01.4712 B.C. (ich nehme an, das ist Oracles Version von der Erschaffung der Welt) und (more...)

Multi-Attribut-Vergleiche

Ein interessanter Hinweis von Jonathan Lewis, der auf eine Präsentation von Markus Winand bei den Trivadis CBO-Tagen zurückgeht (die ich mir auch gern angeschaut hätte, wenn ich mir dergleichen leisten könnte): in manchen RDBMS ist es möglich, mehrspaltige Vergleiche der folgenden Form durchzuführen:

where (col1, col2) < (const1, const2)

Die Logik dabei ist: das Prädikat liefert TRUE, wenn col1 < const1 ist, oder wenn gilt: col1 = const1, aber col2 < const2 (und NULL-Werte (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN: The story so far

Part 1—DON’T PANIC: Even experienced application developers may not understand EXPLAIN PLAN output. As the great Renaissance artist Leonardo da Vinci said in his dicourse on painting: “Those who are in love with practice without science are like the sailor who gets into a ship without rudder or compass, who is never certain where he […]

MEMBER OF comparison of PL/SQL and SQL

In the Kscope14 sunday symposium today, Steven Feuerstein explained that MEMBER OF syntax was slow in SQL and fast in PL/SQL. I challenged him that perhaps it was missing indexes on the nested table? My mistake - I got the task of testing it and see if that was the case... So I tested and was surprised at the answer.

I'm creating a nested table type and a table with a column of that type (more...)

Literally speaking

Reading Scott Wesley's blog from a days ago, and he made a remark about being unable to concatenate strings when using the ANSI date construct.

The construct date '1900-01-01' is an example of a literal, in the same way as '01-01' is string literal and 1900 is a numeric literal. We even have use some more exotic numeric literals such as 1e3 and 3d .

Oracle is pretty generous with implicit conversions from strings (more...)

External Table Access

I left to chance where students would attempt to place their external files in a Linux or Unix implementation. As frequently occurs, they choose a location in their student user’s home directory. Any attempt to read an external table based on a file in this type of directory fails because it’s not accessible by the Oracle user. You can’t simply chown the directory and files in the directory.

The failure returns the following result:

SELECT  (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN (Act II)

Over at ToadWorld … Part 5: SQL Sucks! Part 6: Trees Rule Part 7: Don’t pre-order your EXPLAIN PLAN Part 8: Tree Menagerie The story so far: A relational database is “a database in which: the data is perceived by the user as tables (and nothing but tables)  and the operators available to the user for (for […]

Sequence disallows order by

A call to a PRICE_S1 sequence in a query with an ORDER BY clause is disallowed. Any attempt raises the following exception:

SELECT   price_s1.NEXTVAL AS price_id
                  *
ERROR at line 1:
ORA-02287: SEQUENCE NUMBER NOT allowed here

You need to remove the ORDER BY clause to eliminate the error.

MySQL Insert from Query

While working with an error that my students surfaced in the Oracle Database 12c, I blogged about the limit of using a subquery in an Oracle INSERT statement, and I discovered something when retesting it in MySQL. It was a different limitation. I was also surprised when I didn’t find any mention of it through a Google search, but then I may just not have the right keywords.

The original test case (more...)

SQL Insert from Query

Sometimes my students find new errors that I’ve never seen. One student did that this week by including an ORDER BY clause in a subquery that feeds an INSERT statement. It raises an ORA-00907 exception, like:

ORA-00907: missing right parenthesis

You can’t include a subquery with an ORDER BY clause because it generates an error. The reason is simple. A subquery can’t perform a sort operation inside a subquery. Here’s a quick (more...)

SQL Analytics – Ranking with ordinal suffix

SQL Analytics provides a fairly simple mechanism for determining positional rank within a set of results.

Before I demonstrate that query - which is already found in many good libraries - I thought I'd show how we could take it a step further and add the ordinal suffix (st, nd, rd, th) to a result.

We can do this using date format masks

with placing as (select rownum rn from dual connect by level <  (more...)

SQL Analytics 101 – Break columns

SQL analytics can be used to generate break columns in your queries, without the need for break formatting attributes in APEX or the old fashioned break on option in SQL*Plus.

I came across an example recently where I wanted to apply the break formatting in my query to avoid extra sub-totals from being displayed after each break.
No sub-totals please
I could use jQuery to hide the rows instead of modifying them, but as (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN

On the Toad World site, I’m publishing a whole series of short blog posts on the subject of EXPLAIN PLAN. I’m actually using EXPLAIN PLAN as a central motif to teach not just SQL tuning but relational theory, logical database design, and physical database design. In a year’s time, I hope to have enough material for […]

Export data to excel

who never needed to export data to excel from sqlplus ? everybody had one day needed to do that. Sqlplus support the HTML markup which provides an excellent result once opened in excel. With the HTML markup, sqlplus create a table with all columns from your query including the name of your columns as table [...]

The post Export data to excel appeared first on Oracle DBA Scripts and Articles (Montreal).

Unindexed foreign keys

Unindexed foreign keys can lead to bad database performance due to lock contention and full table scans performed on the child table. Here is a diagram which illustrate the situation: In this exemple Oracle needs to lock the entire employees table when the primary key of the departments table is modified, in addition to that [...]

The post Unindexed foreign keys appeared first on Oracle DBA Scripts and Articles (Montreal).