Thumbnail image for How to Drop and Recreate All Foreign Keys that Reference a Table in Oracle

How to Drop and Recreate All Foreign Keys that Reference a Table in Oracle

In the last post you saw a workaround for the error "ORA-02449: unique/primary keys in table referenced by foreign keys", which blocks our attempt to drop an Oracle table if there are foreign keys from other tables referring to it. The caveat: if the dropped table has to be recreated, the dropped foreign keys must […]

Read the Full Article →
Thumbnail image for ORA-02449: Solving the DROP TABLE Dilemma

ORA-02449: Solving the DROP TABLE Dilemma

Sometimes you want to drop and recreate an Oracle table, and are in a fix because when you issue the DROP command Oracle responds with ORA-02449: Disabling constraints has no effect on ORA-02449, the only resolution is to drop constraints. Which presents another dilemma: the error does not specify which foreign keys are causing this […]

Read the Full Article →
Thumbnail image for INSERT ALL: Insert Multiple Rows with a Single INSERT Statement

INSERT ALL: Insert Multiple Rows with a Single INSERT Statement

A quick tip to get rid of the plodding way of inserting data into a table with multiple INSERT statements. Know what I mean? Say you have a table COLORS with this structure: And you want to create this data in the table COLORS: Hands up all who write insert statements this way:

Read the Full Article →
Thumbnail image for 15 Things You Should Know about the ORDER BY Clause

15 Things You Should Know about the ORDER BY Clause

When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right. The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default. ORDER BY ASC places NULL values […]

Read the Full Article →
Thumbnail image for MERGE: Insert New Rows, Update Existing Rows in One Shot

MERGE: Insert New Rows, Update Existing Rows in One Shot

MERGE in Oracle combines the power of INSERT and UPDATE into one power-packed statement

Read the Full Article →
Thumbnail image for How to Manage those Pesky NULLs when Sorting Data

How to Manage those Pesky NULLs when Sorting Data

How to position NULL values in the sort column – send them to the top or the bottom in the result set.

Read the Full Article →
Thumbnail image for DISTINCT and How NOT To Use It

DISTINCT and How NOT To Use It

The DISTICT keyword placed next to SELECT restricts the result to unique rows from a query. DISTINCT is also a much abused keyword, often used as a quick fix to bad queries. Take this example from Oracle Applications:

Read the Full Article →
Thumbnail image for How to Add Table Column at Specific Position

How to Add Table Column at Specific Position

In a perfect world, the database design is firm and final before development starts. But we do not live in a perfect world – changes can and do happen. You forgot a column in a table, or your client has a change request – for various reasons, you may find that you need to add […]

Read the Full Article →
Thumbnail image for Unlock User Account in Oracle Express Edition

Unlock User Account in Oracle Express Edition

The default user HR in Oracle 10G Express Edition is locked when you first try to login after database installation. This is apparently due to security reasons, and needs to be unlocked by the administrator. To unlock HR – or any other database user account in Oracle Database XE – follow the simple steps below:

Read the Full Article →
Thumbnail image for LTRIM, TO_CHAR, And A “Strange” Query

LTRIM, TO_CHAR, And A “Strange” Query

A query using LTRIM and TO_CHAR seemed to be behaving oddly, but soon a simple explanation presented itself. Check out the query below. Can you tell why it gives the answer it does?

Read the Full Article →