6 useful Oracle commands

Written on 26 March 2011, 12:43pm

Tagged with: , ,

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


2. How to paginate in Oracle using rownum

Method 1:

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

Method 2: (more…)

ColdFusion: cfdbinfo usage

Written on 17 March 2011, 11:40pm

Tagged with: , , ,

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 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">


The #dsn# data source has the following databases:<br />
<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


The #dsn# data source has the following tables:<br />
<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…)