Quick and Dirty Coding Hacks

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.

Drag and Drop

Next, I selected all of the columns and copied them: (Ctrl+C) …

copy and paste_spriden-image2

… then pasted them onto a new Excel worksheet, in the second column, B (Ctrl+v)

paste_excel-column2-image3

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).

remove the commas-image4

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”:

 

copy and paste whole column B to D-image6


paste to D-image7

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:

replace-all_columnd-imag8

So far I had:

two new columns-image9

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:

prepare your alter statements1-image10

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:

the little black square1-image11

Double click on the small black box in the right corner to fill the column of data to the last row of the adjacent column

12c1

Fill columns C  to”  and in E the semicolon

image13a


 

The alter statements have now been built.  Then I simply copied and pasted them right into SQL Developer and executed:

or right into sql developer-image16


 

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!

 

Advertisements

Tags: , , ,

About Helen J. Sanders

Assistant Director, Applications Development Services in the Student Collaboration Center at Temple University. Oracle database developer and enthusiast. Active in ODTUG. Serving on the Board of Directors for the Temple University Alumni Association, the Temple College of Education Alumni Association, and Vice President of the Network of Women with Careers in Technology.

7 responses to “Quick and Dirty Coding Hacks”

  1. Jon Wu says :

    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.

    Liked by 1 person

  2. chet says :

    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’ ) || ‘;’
    FROM user_tables
    WHERE table_name = ‘SWRIDEN’;

    Liked by 1 person

  3. thatjeffsmith says :

    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.

    Liked by 1 person

  4. kizi says :

    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
    lot!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: