Dev Corner

There are lots of ways to select a random record or row from Oracle database table. Here is an example SQL statement. Read the rest of this entry »

Problem:
Oracle tables are composed by extents. Extents are allocated from tablespace. You are given a table and you need to find in which tablespace it is stored. Read the rest of this entry »

Problem
You need to find when a PL/SQL procedure in an Oracle Database was updated. Read the rest of this entry »

Problem
You need to find information about all invalid database objects in an Oracle Database. Read the rest of this entry »

You can use the SQL implicit cursor to retrieve the number of rows, affected by SQL query: SQL%ROWCOUNT. Read the rest of this entry »

You declare PL/SQL variables, constants and types in declare block. The syntax is Read the rest of this entry »

Oracle Database 11g introduced new feature – virtual column. Instead storing data, the virtual column is calculated based on an expression stored in data dictionary. Let’s assume you have a table that stores customer information. The customer names are stored in two separate columns – first_name and last_name. The database queries often need to return the customer’s full name. Instead of storing the full name into the database or having to type it each time, you can define a new virtual column that is calculated, based on an expression: first_name || ' ' || last_name. Read the rest of this entry »

You can add special characters to an Oracle SQL query by using the CHR function. For example, to add a Tab character, you can use CHR(9). Where 9 is the ASCII code for the Tab character. Read the rest of this entry »

This example illustrates how you can create and use dynamic cursor in Oracle PL/SQL. The example is pretty simple, but I hope you can get the idea and apply it to more complicated cases. Read the rest of this entry »

If you need to remove duplicate rows from an Oracle Database Table, you can use different approaches. For example, you can use the DELETE command with a criteria that selects only duplicates. Usually it is faster to create a new table with distinct rows and replace the old table with the new one. Read the rest of this entry »