I have been working with Oracle technology since 2010, and know my way around a database well enough, and do an okay job supporting our E.R.P. system at my job. However, I’m no PL/SQL nor SQL expert. Far from it. And some days I just don’t have the time nor the inclination to “Ask Tom”, so I resort to the tools I am familiar with, no matter how elementary/rudimentary they may be, to get me through the day.
For example: I recently was in a situation where I had to copy three of our prod tables down to a non-prod schema, renaming the tables and each column in the table, for training purposes for a new staff member, so he could follow along with the way the tables were structured in a training manual. I work at Temple University (Go Owls!) which is an Ellucian Banner school, and Banner’s table-naming standards are such that the column headings each have the table name in them. For example, on the SPRIDEN table, every column is named SPRIDEN_[something], as in SPRIDEN_ID, SPRIDEN_FIRST_NAME … you get the picture. But the training manuals referred to tables in a training schema I no longer had access to, the SWRIDEN table, and all column names SWRIDEN_[something]”. Some of these tables have hundreds of columns and I was not inclined to type hundreds of ALTER TABLE” statements into my SQL Developer editor window. Maybe I’m just lazy.
So after creating the new tables by copying the prod versions, via SQL Developer, I could not find a way to globally rename the column headings for the newly copied/renamed tables. If there is such a way to do this, and anyone who may be reading this article knows how, I’d greatly appreciate a comment telling me how to do so. In other words, is there a command where I can say: rename all columns on a table from __ to __?
Instead, I handled this with the tools at hand that I’m familiar with. My Methodology may make you cringe. But remember- this is “quick and dirty” hack # 1.
First thing: I set my SQL Developer Database preferences for Drag and Drop to format select statements for me, so that I would not have to type out each field name on these very large tables.
Next, I selected all of the columns and copied them: (Ctrl+C) …
… then pasted them onto a new Excel worksheet, in the second column, B (Ctrl+v)
Since each column name is followed by a comma, I globally removed them on the worksheet by selecting the column, and doing a “find & select” then going to the “replace” tab. I just replaced the commas with a space (Literally a hit of the space bar).
Next, I copied that column, with its removed commas, on to another column, skipping one in between. So I went from column “B” to “D”, making sure I pasted as “values”:
Now I had identical data in columns “B” and “D”, and needed to replace the old column names with the new. So just like I used “find & select” to replace the commas in column “B” with spaces, I did the same thing in column “D”, replacing the old table name with the new, or in my case, replace SPRIDEN with SWRIDEN:
So far I had:
I was then ready to build the “ALTER TABLE” statements.
In column A1 of the spreadsheet, I entered my alter statement: Alter table SWRIDEN rename column. In C1 put to and in E1, the semicolon to end the SQL statements I’m creating:
To copy the alter statements down to the last row in A that is adjacent to B, don’t click and drag! The easiest way to fill a column in Excel is by hovering over the bottom right corner of the cell you want to copy down, until the small square black box appears in that corner, then double click on the small black box:
Fill columns C “to” and in E the semicolon
The alter statements have now been built. Then I simply copied and pasted them right into SQL Developer and executed:
I hope this little hack will be useful to others out there who may be new to SQL, and just need a fast way to get something done, with older, more familiar tools. Look for more quick and dirty hacks to come. I’d love to hear some of yours as well!
7 thoughts on “Quick and Dirty Coding Hacks”
Wow. I was so proud that I figured out I could exactly copy a long column of formulas in Excel using the Notepad as an intermediary, but now I feel less impressed with myself.
LikeLiked by 1 person
Optionally, “=concatenate( “alter table “, , ” rename column “,, ” to “, , “;” )
I use excel often to do this kind of stuff, especially in regards to debugging long INSERT statements. http://www.oraclenerd.com/2009/06/oracles-new-insert-syntax.html
Actually, why not just use SQL and the data dictionary to solve this one?
SELECT ‘ALTER TABLE ‘ || table_name || ‘ RENAME COLUMN ‘ || column_name || ‘ TO ‘ || REGEXPREPLACE( column_name, ‘your_requirements_here’ ) || ‘;’
WHERE table_name = ‘SWRIDEN’;
LikeLiked by 1 person
Regex replace! Hmm. Great idea actually.
So a few things…
What Chet said 🙂
Or create the table using something like our “use as template’ feature, copy the DDL out to the worksheet and use search/replace to fix up the col names…
or build views to ‘fix’ the column names or
why are the col names so import? let the new guy use his imagination 🙂 Or even better, have HIM figure out a way to attack this puzzle.
LikeLiked by 1 person
Another great suggestion. Thanks, Jeff. Easing the new guy along… Didn’t want him playing with our actual tables – even in non-prod where we develop – during month 1.
After I initially left a comment I seem to have clicked on the -Notify me when new comments are added- checkbox and now every time a comment is added I recieve four emails with the same comment.
Perhaps there is a way you can remove me from that service?Thanks a
I don’t see a way for me to do this in Word Press. You may have to go back in and click..something to unsubscribe from the comments. I’m sorry!