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...)
Some time ago I had some blog posts introducing some of the basic Statistical function available in Oracle. Here are the links to these.
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...)
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...)
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...)
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 […]
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...)
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:
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 […]
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.
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...)
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 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 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...)
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 […]
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 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).