Wednesday, September 16, 2009

yes i do oracle

So, one of the things I have had to do recently is partially reverse engineer some data structures in a couple oracle databases, Now this would have been a trivial task if one of the following were available:

(if you just want code examples scroll down =)

1An ER diagram (document your shit people always document it)
2 Foreign Key structures (for those that don't know this involves telling oracle, or another standards compliant RDBMS, information about what is in the columns in the table, that for example Column A in table beta is the foreign key for table gama, and is contained withing column G this means that any data in column G must be found in column A and that there is a relationship between those two table.
a simplistic Banking example: lets say that data about who you are (birthday, name, address, all that good ol personal shit)is in one table that also contains your social security number and that your soc is used in the table that holds all the data about your accounts, account type, current balance, APR for the account, all stored neatly in one table. The soc in the you table is a foreign key to the accounts table, because you cant have an entry in the accounts table without there being a user in the you table to match a soc to. (I know there are holes in this, not the least being that there wouldn't be one table for any of these things and that you really wouldn't use a soc as a foreign key, but it is a unique number, and if you can see these holes what are you doing reading this section =P )

If your database has either then this probably isn't for you, instead use those resources, its faster, cheaper, and less prone to both pissing people off (explained later) and error then the method i am going to describe This is for those times when you are dealing with a vendor system that you need to extract data out of in a way that the system was never intended to give. A tenant of ALL RDBMS is that you have relations between the data, now this requires you to have some semblance of intelligence to use, and you need to understand the app you are going after, as random relations mean nothing a similar method (which I will detail in a later post) should be used to begin your search, finding some table that contains data you know (be it user names, a part number, a description etc, to give you your starting point, The following is a slightly obfuscated example for how you can check all the columns in one table (table A) against all the columns in another table (table B)

select distinct 'select '''|| trim(t1.table_name) || ':'|| trim(t1.column_name)||
':::'|| trim(t2.table_name) || ':'|| trim(t2.column_name)||
''' from dual;'|| chr(10)||'select count(*) '||
substr(trim(t1.column_name),1,15) ||'_'||
substr(trim(t2.column_name),1,14) ||' from ' || trim(t1.owner) ||'.'||
trim(t1.table_name)||' t1, '|| trim(t2.owner) ||
'.'||trim(t2.table_name)||' t2 where t1.'||trim(t1.column_name) ||
' =t2.'||trim(t2.column_name) ||' ;'
from all_tab_columns t1, all_tab_columns t2
where t1.owner = 'SCHEMATABLEA' and
t2.owner = 'SCHEMATABLEB' and
t1.table_name = 'NAMETABLEA' and
t2.table_name = 'NAMETABLEB' and
t1.data_type = 'NUMBER' and
t2.data_type = 'NUMBER'

The above assumes you can get access to the ALL_tab_columns table, which is a table detailing every column in every table for all tables you have been granted access to, this might vary in other distributions but i suspect you will find a similar table of information.

In the where section replace SCHEMATABLEA with the schema owner of the first table you want to compare, and SCHEMATABLEB with the owner of the second, nametablea and nametableb should be self explanitory. the last two restrict the columns looked at, in this case it assumes that the relationship columns in the tables will only be numeric, and not a text or other field.

this creates an output similar to this:

select count(*) FLAG_ACTIVE
where t1.FLAG =t2.ACTIVE ;
select count(*) FLAG_ERROR
where t1.FLAG =t2.ERROR ;
select count(*) FLAG_NAME
where t1.FLAG =t2.NAME ;

(NOTE* the sql here has had additional carriage returns inserted so that all will show on the page, I need to get better at this blogging stuff so i can insert scroll bars etc normal output would output 2 lines per table/column combination, each SQL statement on its own line)

save this output and run it against the database, (in this case I took 2 random oracle system tables and ran the query against them.
It will in turn generate output similar to this:


1 rows selected


1 rows selected

6 lines of data for each item you select, the upper output simply tells you the tablename:columnname for each table, separated by a triple ':'
the second set of output is a count of how many rows matched, in this case 0 you will want to grep for non 0 numbers in the output in some fashion.

This is a starter post for how to actually RE a database, the starting tools if you will, the above query can be expanded, simply leaving out one of the table names searches against all tables in that schema. This is the catch with people wanting to kill you, you can easily end up running 100k or even millions of queries against the database if it is large. this may seem like a good idea at first, but performance issues will creep in to other users, do this off hours and study the outputs during

adios for now, tomorrow.. hopefully a better explanation of this code and how it really works

Tuesday, September 15, 2009


This was origionally posted as a response to Rohan's issue with Larisa's view of faction changes,

It is really a matter of two different ideals and purposes. Rohan, your purpose is to allow friends who meet (probably in RL) that play different factions to play together without having to level another toon on another faction. In this case, the faction change works, and should work out of game, (though for maybe a bit more than 10$) Your desire is to play together, and have fun, in this case factions are a simple hinderance to this, For anyone who doesn't care about lore and rp this is fine, new paint and shrubs... good to go.

Then there is the other side Larisa is right too, There should be an in-game mechanic for those who for RP, or achievement reasons want to become the other faction as their current character. They truly want to play the traitorous imp, or the disillusioned hero jaded by their own leadership, For them the faction change isn't about fixing an out of game issue (hey we are two people but cant play together because we started on diff factions) its about exploring the world from a new perspective

To expand on the idea, Think about what the next expansion is set to bring to the world a cataclysmic sundering of the land and open war between the horde and alliance, I forget the esteemed blogger who talked about it, but His point was that essentially there is no more just war when you are the first to move. War in the past was purely an extension of politics (again famous quote that I don't have time to look up) Now, In our culture it is hard to view a war as being justified when we look at the people who started it. This presents the problem of however the war starts between the horde and alliance there will be problems that people have with their side's justification for war. This gives a perfect in game reason to want to change factions for us Rabid RP fans (ie We RP in raid people... its how we role). This is problematic however, because it makes no sense to "change factions" and suddenly be a whole different species, not to mention blood elves are prissy little things, and my dwarf pally would never become one.

The penalties for the change should be huge, becoming hated with all your old factions, and having a cap on how trusted you can become with the new, for a time, You should have to earn the right to be exalted among your new friends, through hard work, and crazy long quest lines. Now it might be possible to switch, or maybe even some day act as a bridge across the divide as champion of both, but that is a lengthy and hard process, Possibly flagging you for pvp for both factions until you prove yourself once again.

Just a thought

Tomorrow, interesting oracle scripts for reverse engineering database structures

Monday, September 14, 2009

Well, here i go again on my own

That being a shameless plug of a favorite song of mine......

So, what am I doing here? I am an oracle dba/coder/whatever else is needed tech kinda guy first

2 I am a Prot spec paladin in World of Warcraft, I raid prot, and holy, being one of those masochists, er i mean helpful people.... who didn't take a dps spec.

This is an attempt to lay down some of the information I have on both subjects, possibly at the same time If anyone is watching, I give no guarantees that this experiment lasts longer than this post,

Apparently you can also follow me on twitter, i have one fan atm... and have never posted anything =) cant even remember the address