Thumbnail image for NOCOPY Parameter Hint in PL/SQL

NOCOPY Parameter Hint in PL/SQL

Before we get to understanding the NOCOPY parameter hint in PL/SQL, it would help to first look at PL/SQL parameter modes and the ways in which they pass values in and out of a subprogram. PL/SQL subprograms have three parameter modes:

Read the Full Article →
Thumbnail image for 10 Important Lessons in Data Modeling

10 Important Lessons in Data Modeling

Are you building the database for a custom OLTP application starting from scratch, with the business requirements as only initial input? No matter what the size, domain or location of your project, the following lessons will help to ensure that your physical data modeling process is smooth and that you end up with a database […]

Read the Full Article →
Thumbnail image for The Difference between User and Schema in Oracle

The Difference between User and Schema in Oracle

As an Oracle developer, you have probably wondered at some point: do user and schema in Oracle really mean the same thing? And if they do, why does Oracle have two names for it? This post helps to clarify the sameness – or otherwise – of Oracle user and schema.

Read the Full Article →
Thumbnail image for PIVOT in Oracle 11G to Select Rows As Columns

PIVOT in Oracle 11G to Select Rows As Columns

A frequent requirement in SQL is to "pivot" a result set – that is, display rows as columns. Before 11G, the developer had to do the equivalent of jumping through hoops to pivot the data. In 11G, with the introduction of the new PIVOT syntax, the task of transposing rows to columns has become a […]

Read the Full Article →
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 →