Back in January 2014, I wrote a script to cleanup an Oracle
student schema. It worked well until I started using APEX 4 in my
student schema. You create the following 75 objects when you create an APEX 4 schema.
OBJECT TYPE TOTAL
Here’s the modified script that ignores the objects created automatically by Oracle APEX when you create a
Sometimes trying to keep a post short and to the point raises other questions. Clearly, my Python-MySQL Program post over the weekend did raise a question. They were extending the query example and encountered this error:
TypeError: range() integer end argument expected, got tuple.
That should be a straight forward error message because of two things. First, the Python built-in
range() function manages a range of numbers. Second, the row returned (more...)
Configuring Perl to work with MySQL is the last part creating a complete Fedora Linux LAMP stack for my students. Perl is already installed on Fedora Linux.
I’ve also shown how to use PHP, Python, and Ruby languages to query a MySQL database on Linux. After installing this additional Perl DBI library, my students will have the opportunity to choose how they implement their LAMP solution.
You can find the Perl version with (more...)
Most folks know how important JSON is to web development but (more...)
After you install Ruby and build the Rails framework, you need to create the
mysql gem. This blog post shows you how to create the
mysql gem and how to write a simple Ruby program that queries the MySQL database.
The first step creates the
mysql gem for Ruby programming:
It should show you the following:
Fetching: mysql-2.9.1.gem (100%)
Building native extensions. This could take (more...)
P.O.I.S.E.D. is the acronym I invented for the performance tuning method described by Chris Lawson in The Art and Science of Oracle Performance Tuning; it stands for Problem, Observation, Interpretation, Solution, Execution, and Documentation. The Observation and Interpretation phases feed each other. The Interpretation phase concludes with a determination of root cause. If we are investigating poor performance a single SQL statement, the tool of choice for the observation phase (more...)
Just a small Sunday night anecdote with a wider point. I, or maybe a colleague, recently received an update statement from a developer. Now, this developer is long of tooth and is well versed in the ways of Oracle data manipulation.
The aforementioned update statement contained an interesting hint. BYPASS_UJVC. You may not have heard of this hint. It’s not commonly used, although it’s been around since Oracle 8.1.5. Mainly because it is (more...)
Since some time I have been adding
WHERE 1=1 to all my queries.
I get queries like this:
FROM emp e
AND e.ename LIKE 'A%'
AND e.deptno = 20
Lots of people ask me what’s the use of this
You know I like to type as little as possible but here I am typing a lot of extra characters. And yet, it makes my development life a (more...)
A recent question on the OTN SQL forum
asked how best to join two tables related by ID and date range, in order to insert one row per date into a data warehouse. One solution was to expand the data from each table, creating one row per date, then join on date. I think it's more efficient to join on date range, then expand.
If you run Oracle Standard Edition or haven’t licenced Diagnostics Pack for Enterprise Edition, then you don’t have AWR and ASH Data available. This is when Statspack, the predecessor of AWR, comes in handy to keep a history of database performance metrics. But although Oracle still deliver Statspack with their recent DB releases (yes, even in […]
Over the past couple of months Oracle has been releasing some EA (Early Adopter) versions of a new tool that is currently called SQL Command Line.
The team behind this new tool is the SQL Developer development team and they have been working on creating a new command line SQL tool that is based on some of the technology that is included in SQL Developer.
SQL Command Line in an stand alone tool and all (more...)
The mother of all SQL “anti-patterns” is the ad-hoc query. Here, the end-user of the application can specify any combination of parameters—for example, the job_id, the dept_id, or a combination of both—and the data is retrieved using a catchall query. This antipattern has two sisters who are almost as evil. These two sisters arise from the requirement to paginate through the records retrieved by a query.(read more
If a column or expression may be NULL, it is a pain to compare it to something else. Why? Because comparisons involving a NULL do not result in TRUE or FALSE: they result in NULL. I use the DECODE function to work around this problem: here's why - and how.
Moving through some observations of an ongoing investigation tackling the easiest ones first.
Previously I observed the slow execution of a recursive delete of statistics history but coming not from MMON but from a number of application processes which should have only been calculating stats for their own partitions.
Statement was sql id 9v9n97qj8z1dg:
delete /*+ dynamic_sampling(4) */
where savtime < :1
and rownum <= NVL(:2, rownum);
From the real time sql (more...)
Yesterday I mentioned issues with a recursive delete operation on statistics history.
This is a quick illustration of the last points I made on that post regarding the lack of a COUNT STOPKEY optimisation because of the use of NVL.
COUNT STOPKEY is an optimisation which allows processing to stop once the target number of rows has been reached.
create table t1 as select * from dba_objects;
alter session set statistics_level = all;
@thatjeffsmith recently recommended an article about making your blog more popular. The article said "lists of 10 things" were great ways to get more readers. Hey, if that's all it takes...
is a package that generates SQL to compare data or synchronize tables. Here are 10 good reasons to use it.
Steven Feuerstein runs a great site at PL/SQL Challenge
that is just another way for developers to stay up to date with their knowledge of PL/SQL, SQL and database design with a bit of fun.
PL/SQL championships are held quarterly, but the less frequent SQL and Database Design quizzes are currently held annually. Thanks to persistence and maybe a bit of experience, I was eligible to compete in both.
Unfortunately do to a timing
As you know, NaN is a “Not a Number”.
How do you think, what would be the result of the following query? (0f/0 == NaN)
select count(*) cnt from dual where rownum < 0f/0;
Ok, when you know the result, try to guess what will return this query:
select count(*) cnt (more...)