How to Select a Random Row

I’ve seen similar questions in programming forums about how to select a random row from a table. Most RDBMS have functions to generate random numbers, so with a simple query we can select a random row(s):

The above command will sort the HR.EMPLOYEES table in random order, so we’ll be able to select a random. If you want to limit the number of returning rows, you can use subquery method:

How to Import Data from XML

One of my blog readers asked me how to import data from XML file. There are lots of ways to do it but I’ll show only a simple one by using DBMS_XMLSTORE.

We will need a simple XML file to import. So I created a folder as “/oracle/xmltest” and a text file named (sample.xml) in this folder. Oracle should be able to access this folder and file, so check OS permissions of them and be sure that they are readable by oracle user.

I entered the followed data into my sample xml file:

As you see, our employees have only 2 properties, “no” and “name”. So I’ll create a table to store these properties:

Oracle Table Functions

A table function is a PL/SQL function which will behave like a row source when queried. So you can perform transformations to the data before it is returned in the result set.

To write a table function, first we need to define an object as the row structure:

Because our function will return a table, we’ll also define a type:

How to Move a Datafile to Different Location

If the database is in archivelog mode, you can move the datafile while database is up. Take the datafile offline, copy (or move) the datafile to the new location:

Then issue the following commands to rename the datafile and make it online:

If the database is in no archivelog mode, you need to shutdown the database, open the database in mount mode and copy/move the datafile:

Then rename the file and open the database:

How to Prevent a User to Drop Own Objects

An Oracle user is always granted to drop their own objects. To prevent a user to drop their own objects, we can use DDL triggers.

Here’s sample trigger which will prevent HR user to drop EMPLOYEES table:

When HR user tries to drop employees table, he’ll see an ORA-20000 error.

For more information and samples about DDL triggers: http://psoug.org/reference/ddl_trigger.html