Tag Archive: Oracle


PROBLEM
Using Coldfusion, you have run a query or procedure that returns a CLOB (Character Large Object) data type. You need to display this content in your web page or write it to a file. If you wrap your returned CLOB variable in a <cfoutput> you get an odd output like “[C@183eeb0″. If you try to <cfdump> the returned CLOB you get a giant array of objects. So how do you get your data?

SOLUTION

________________________________________

<cfset x = CreateObject(“java”,”java.lang.String”).init(myClobOutput)>

<cfoutput>#x#</cfoutput>

________________________________________

EXPLANATION
First, I can’t take full credit for this. One of the Java guys I work with helped me figure this out. A CLOB is a “collection of character data.” In my case, I am using Oracle, and it has its own “construct” for the CLOB. In other words it’s an object of sorts. When you put this object in the <cfoutput> tags, you get the string’s identifier, just like you would if you output a Java object. When you dump it, again just as with a Java object, you see an array of nested objects, one object per character.

While doing a <cfset> is creating a Java String under the hood, Coldfusion gets to decide how to create the string. To get our CLOB to display, you need to create an instance of a Java String using the CreateObject() method, and then call the init() function. The init() constructor function can take a variety of arguments and knows how to translate each into a string. Passing in the CLOB as an argument, the init() function treats it as a char[] (character array) and “allocates a new String so that it represents the sequence of characters currently contained in the character array argument.” So, in other words, it knows what the array is and loops through it, putting each character back together as a giant string.

Sometimes you need to know the column names for a specific table. I have needed this in the past when loading XML data into an empty table. For each node, I would check to see if a matching column named existed and if it didn’t I would update the table to add one, before loading the data. That way the query didn’t throw an error.

With Oracle PL/SQL, this is pretty simple. All you need to do is to get a list of the column names from the system table “USER_TAB_COLS”. There can be other versions of this table like “DBA_TAB_COLS” depending on how you set up your accounts and what you logged in as. But in general this all works the same. So to get a list of the columns you would do something like this:

SELECT column_name
FROM user_tab_cols
WHERE table_name = 'myTableName'
/

Play around with this and see what other kinds of uses you can come up with. Doing a “SELECT *” will show you that there are several other values in there like the column’s data type, data length, if its nullable, and several others.

:) Happy coding!

When running Oracle PL/SQL Queries, if you want to make a value “title case”, where the first letter of each word is capitalized, then use the INITCAP() function. The syntax for that looks like this:

INITCAP( myString );

For example:

INITCAP('JOHN DOE');
/* Retruns 'John Doe' */

INITCAP('This is the best blog ever');
/* Retruns 'This Is The Best Blog Ever' */

I use this a lot of prefixes of names like Mr and Mrs. Just note that it’s not full proof as “PhD” would get translated into “Phd” which tends to make the Phd not so happy!

:) Happy coding!