Tag Archive: SQL

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:

/* 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!