This is more of a reference post to store some useful Oracle features. Or a cheat sheet if you wish 🙂

1. How to find the maximum column size in a table

select
       max(vsize(mycol))
from
       mytable;

2. How to paginate in Oracle using rownum

Method 1:

select * from 
( 
       select rownum r, a.* from 
       (
             YOUR_QUERY
       ) a 
       where rownum <= 10
) 
where r >= 1;

Method 2: (more…)

The cfdbinfo tag, introduced back in ColdFusion 8 provides a lot of information about your database environment. The documentation page shows 7 distinct values for the ‘type’ attribute of cfdbinfo:

type=”dbnames|tables|columns|version|procedures|foreignkeys|index”
Type of information to get:
* dbnames: database name and type
* tables: name, type, and remarks
* columns: name, SQL data type, size, decimal precision, default value, maximum length in bytes of a character or integer data type column, whether nulls are allowed, ordinal position, remarks, whether the column is a primary key, whether the column is a foreign key, the table that the foreign key refers to, the key name the foreign key refers to
* version: database product name and version, driver name and version, JDBC major and minor version
* procedures: name, type, and remarks
* foreignkeys: foreign key name and table, primary key name, delete, and update rules
* index: name, column on which the index is applied, ordinal position, cardinality, whether the row represents a table statistic or an index, number of pages used by the table or index, whether the index values are unique

This post shows some simple examples for the 7 cases described above to give you a better grip on this tag.

1. Get the databases

<cfset dsn = "YOURDSN">

<cfdbinfo
    type="dbnames"
    datasource="#dsn#"
    name="dbdata">

<cfoutput>
The #dsn# data source has the following databases:<br />
</cfoutput>
<cfdump var="#dbdata#">

It’s important to note that the cfdbinfo returns in this case all the databases, not just the one defined in the data source.

2. Get the tables of a database

<cfdbinfo
    type="tables"
    datasource="#dsn#"
    name="dbdata">

<cfoutput>
The #dsn# data source has the following tables:<br />
</cfoutput>
<cfdump var="#dbdata#">

In MySQL – the cfdbinfo above returns only the tables of the database defined in the data source.
In Oracle – the cfdbinfo above returns all the tables in all the databases returned at point 1.
To filter the returned tables, you can use the following two attributes: (more…)