Oracle's NEXT_DAY function and How and Why to use it

I recently received an interesting request. How many transactions did we receive last year on a Friday, Saturday or Sunday? It is an interesting question depending on what kind of transactions you have and what reporting requirements may exist. So, I get this request and I'm all like...WTF? Bitches be trippin'. I have never received a question like this before. It is new ground for me. Ooh, I'm excited! Sorry Ma'am. You too sir.

I do a bit of googling and I don't really find much. Then I discovered NEXT_DAY in an Oracle Doc. So, NEXT_DAY takes a DATE argument and a CHAR argument. The CHAR argument we'll call DayOfWeek.   It is 'SUN'...'SATURDAY', meaning you use either the full name of the day of the week or the standard abbreviation and pass a date in and your results are the date of the next DayOfWeek that you supplied. For example:

select next_day('01-JAN-2013', 'SUNDAY') mydate from dual;

Gets:

MYDATE
1/6/2013

So, the first sunday after the first was the 6th. Ok. Now for the tricky part:

select next_day('06-JAN-2013', 'SUNDAY') mydate from dual;

Gets:

MYDATE
1/13/2013

At first, this threw me for a loop, but then it started to make more sense. It is called NEXT_DAY after all, so it would not return the same date. It would return the date of the next DayOfWeek. So, the way to short circuit that if you want to check the closest day is date - 1. So,

select next_day('05-JAN-2013', 'SUNDAY') mydate from dual;

Gets:

MYDATE
1/6/2013

So, now we come to the part where I slay the dragon, save the day and get the girl. Or keep struggling to restore the Thieve's Guild to its former glory. Anyway, I created a table to store a years worth of 3 day weekends, so to speak.


CREATE TABLE WEEKEND_DATES (
 
    WEEKEND_DATE DATE
   
);

I think looped through a year's worth of days (one for each week) and inserted the weekend dates for each week. Like so:

DECLARE

   l_i      NUMBER;

   mydate   DATE;

BEGIN

   l_i := 0;

   LOOP

      SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'FRIDAY') INTO MYDATE FROM DUAL;

      INSERT INTO WEEKEND_DATES

           VALUES (MYDATE);

      SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'SATURDAY') INTO MYDATE FROM DUAL;

      INSERT INTO WEEKEND_DATES

           VALUES (MYDATE);

      SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'SUNDAY') INTO MYDATE FROM DUAL;

      INSERT INTO WEEKEND_DATES

           VALUES (MYDATE);

      L_I := L_I + 7;

      EXIT WHEN L_I >= 373;

   END LOOP;

   COMMIT;

END;

So, now we have the table WEEKEND_DATES filled with the dates for all of the Fridays, Saturdays and Sundays over the last year. Now we can join that to the transaction table or use an In Clause and a sub-select or an exists with a sub-select to get our desired results. Here is a final query:

SELECT

       CASE timestamp

          WHEN NEXT_DAY (timestamp - 1, 'FRIDAY') THEN 'FRIDAY'

          WHEN NEXT_DAY (timestamp - 1, 'SATURDAY') THEN 'SATURDAY'

          WHEN NEXT_DAY (timestamp - 1, 'SUNDAY') THEN 'SUNDAY'

          ELSE 'NOT A WEEKEND AND SHOULD BE IMPOSSIBRU!!!'

       END

          DAY_OF_WEEK,

       T.* 

FROM TRANSACTIONS T

WHERE TRUNC(timestamp) IN (

SELECT WEEKEND_DATE FROM WEEKEND_DATES);

So, you may be looking at this and going why are you TRUNCing everything? Well, the NEXT_DAY function retains the time portion of the date supplied. So, the first time though this process, the only results received were the ones that matched on the full time as well as date. When you TRUNC a date, it sets the time portion to midnight. This allows you to compare based on any time during the day. TRUNC does have other options so google that if you are interested and I have touched on it in past posts. Also, know that the IN clause might not be the best performing of the options available, but because this was a one-time ad hoc query and results returned quickly enough, I did not dwell on performance. Now if you sick fucks are wondering about the CASE statement above, I simply had the common decency to provide the knowledge of which DayOfWeek the given record actually fell on. I'm cool like that. Now, since we are limiting to days that are Friday, Saturday or Sunday, no other days of the week should be possible. If the results had any IMPOSSIBRU in it, then I rawdogged the pooch like a motherfucker somewhere along the line. I'll need to circle the wagons, go back to the beginning and figure out the failure point, after I kick the pooch down the stairs (just in case).

Alright, I think that is it for today. Class dismissed.

Fuckers.

Function-Based indexes

Indexes in Oracle are like indexes in books and documents. It is simply a way of finding things quicker than looking through the whole thing. But, there can be some tricky little gotchas when it comes to Oracle data and real world scenarios.

I was recently tasked with adding an index to a column in a table. At first glance this is a stupid simple task. Think of the dumbest, most clueless fucktard you know. They could create an index. It is that simple. The confusion and trickiness and why I have a job is knowing when to use an index and how to use an index.

CREATE INDEX index_name ON table_name (column_name [, another_column_name...]);

Not heavy lifting is it? Bro, do you even index?

So, as any good DB dude should do, I ran some queries to get to know the data I was tasked with indexing. So, it turns out that the column I was supposed to index was largely null. This is a problem. Nulls are not included in indexes. How do you index the absence of something? Now, I could go ahead and create an index on the column and all of the non-null values would be indexed appropriately. But, would that be enough? I needed to find out how the column is referenced in the system. I ran a query to look through all of the database objects for references to the column and/or table. I might cover this in another post. I was able to tell that the column was not used in any WHERE clauses in the DB code. I went to an App guy to get perspective on the application and how it interacted with the column. So, it turns out that most of the calls from the App were checking on the NULL status of the column. Well, that's no good. All of those calls would not use our shiny new index and the Application would continue to be dog-ass slow.

Now we get to the meat of this post. Recent versions of Oracle are able to create indexes on function calls on columns. I believe it was added in 8i but wouldn't swear to it. No matter, we are on 11g. The statement I wrote is:

CREATE INDEX FBIDX_MYTAB_NVL_MYCOLUMN ON MYTABLE( NVL(MYCOLUMN, 'some value'));

I named the column FB to indicate Function-Based and IDX so I can know at a glance that it is an Index if I am looking at multiple objects in a list or something. Remember, meaningful names are never a bad idea. The interesting part of this is the NVL function on MYCOLUMN. NVL says, if MYCOLUMN is null, pretend it is 'some value' so I can compare it to other things. So, even when MYCOLUMN is null, NVL(MYCOLUMN, 'some value') is equal to 'some value'. And 'some value' can be indexed. Of course, to fully utilize the Funciton-based index, all of the calls that check the NULL status of the column have to be changed to NVL(MYCOLUMN, 'some value') = 'some value'. But, the bottom line is that the NULL values get indexed and interaction with the table is improved.

Letting the Data Dictionary do some of the leg work

Let's say that you have a column that is on several tables and you need to modify the length of that column. One method of doing that would be to figure out which tables have the column and manually write a shit pot load of alter statements to do what needs to be done. Now, let's suppose you are a shade or two brighter than Corky from Life Goes On. You are going to let the data dictionary do the work for you. Oracle comes with a plethora of tables, views, synonyms and what not that is chock fucking full of information. They are broken up based on item type and user access type. If you are curious, check public synonyms in your schema or if you have read access to SYS, check out the views that look like one of the following:

ALL_%
DBA_%
USER_%

Where the % is some string like TABLES, PROCEDURES, etc. Let's focus on USER_% first. You connect to Oracle as a user and the SYS views that begin with USER_ are the ones that you own. They exist in your schema - the one you logged in with anyway. So, USER_TABLES will list the tables you own, USER_PROCEDURES will list the procedures you own, and so on and so forth. Whereas, the SYS views with ALL_ are the ones that you have access to read, write, execute, etc. The DBA's and a lucky few with elevated privileges will have access to DBA_ views and can see everything in the database. Therefore, DBA_TABLES will list every stinking table in the entire goddamn database.

Now, here is an amazing little trick that will save you a Honey Boo Boo's mom sized chunk of work. You can write queries against those tables where the results are SQL commands. Let's say you have 3 procedures entitled MYPROC1, MYPROC2, and MYPROC3. Now let's suppose you want to compile all of them. You would write select 'ALTER PROCEDURE ' || procedure_name || ' COMPILE;'  as alterallmyprocs from USER_PROCEDURES; and execute that bad boy. Your results will look like:

ALTERALLMYPROCS
ALTER PROCEDURE MYPROC1 COMPILE;
ALTER PROCEDURE MYPROC2 COMPILE;
ALTER PROCEDURE MYPROC3 COMPILE;

So, you can paste those results into a sql window, execute as script and Oracle will compile all of your procedures. Pretty handy right? Well, imagine that you have 25 schemas with similar structures and there are 100 tables in each. Every one of those tables has a column called LAST_UPDATER that tracks via the application which user last updated the record. And now, let's suppose that the USERNAME column is getting enlarged so all of these LAST_UPDATER columns would need to expand to contain the enlarged USERNAME. Ignore for the moment that ideally you would be using the corresponding user id instead of the actual username. This is for instructional purposes, capisce? Would you really want to write the 2500 required statements by hand? Well, maybe you are a masochist. Or maybe you are a contractor. Or maybe you like to sandbag and appear industrious while actually slacking. In those cases, and for you Corky's out there, continue to work hard, not smart. I'll be using sql writing sql to get the same result in a fraction of the time. Just for fun, here is the sql writing sql statement to perform the above task:
select 'alter table ' || owner || '.' || table_name || ' modify ( ' || column_name || ' varchar2(256) );' it from all_tab_cols where column_name in ('USERNAME', 'LAST_UPDATER') and owner like '%MYAPP%';

I was recently tasked with some Year End reports. I needed to run a query in every production database for a specific app or set of apps and I used this technique to meet the narrow deadline. Of course the underlying requirement is that I am able to log into a schema created for me but given read access everywhere for purposes just like this.

Of course, with great power comes great responsibility. Use this for the good of all mankind. Do not fuck with people using this. Do not write something like select 'delete from ' || owner || '.' || table_name || ';' it from all_tables where owner = 'ThatDickThatAggravatesTheFuckOutOfMeAndFoolishlyGaveMePrivsOnHisTables';
Or even worse: select 'delete from ' || owner || '.' || table_name || ' where mod(' || column_name || ', 13) = 0;' from all_cons_columns where constraint_name like '%PK%' and column_name like '%ID%' and owner = 'ThatDickThatAggravatesTheFuckOutOfMeAndFoolishlyGaveMePrivsOnHisTables';
The former is going to delete everything from tables that do not get 'Child Record Found' errors but the latter is wicked nefarious. It will delete every row whose primary key id is evenly divisible by 13 and there are no child record conflicts. Sure, I could work out how to eliminate those conflicts but I don't want to make it that easy for you to really fuck with someone.

All kidding aside, properly used, this can save a ton of time and heartache. Start small, pay attention to syntax, and test, test, test!

Use Good Naming Conventions When Creating Oracle Objects

How many of you are familiar with K.I.S.S? Lick it up? God of Thunder? Cold gin? Any of these sound familiar? If so, I applaud  your good taste in bad music. But, I am talking about the Keep It Simple Stupid mantra many of those in IT have forgotten or eschew in favor of overly complex systems that they can gloat about designing and generally fuck up in one way or another. I am reminded of a guy I used to work with that loved to design and implement overly complex systems because he sounded smart. His white board designs invariably resembled His Holiness The Flying Spaghetti Monster. This is the guy that didn't trust the native windows management stuff so wrote his own which resulted in an application that docked in the upper left corner and could not be moved. Yeah, that is way better than a regular Windows window dude! Not.

The new systems we write and the old systems we maintain and improve grow in complexity naturally. Some of them are needfully complex from day one, but some can start quite simple and grow complex over time as features are added and new systems come up that need to be utilized. For the love of everything holy, do not artificially create complexity. Make stuff as stupid simple as you can from the beginning. In all that you do, this will pay dividends in the future.

I was tasked recently with comparing databases for an application we have with a couple dozen separate instances. Ideally, each of these instances would be identical in structure (or nearly so) and differ on data mainly (Customer 1 in Database A is not the same as Customer 1 in Database B). Unfortunately, we have only recently gotten into structured deployments using Hudson, not to mention a rogue DB Developer we had on staff back in the day (holla!). Over the years, there has been divergence. Some of them are easy to identify as useless or no longer useful items that we can clean up, but some, oh, some are hard to identify as such. In mature systems in mature organizations, the keys to the databases are heavily guarded and formal processes are in place to keep most people out. Some start ups even into their adolescent phase have thrown the doors wide open. How this starts is small businesses or business units that have either no formal DBA or only a couple of coders and a DBA that is wearing a few different hats. In strange cases you may have a boss that is making decisions that are poorly thought out. Whichever happens, what you end up with is objects that you have no idea who created, why they were created, if they are still in use, or what other objects might depend on those. What you end up with is a crazy Jenga game where the wrong table drop can mean long night spent bailing the system out, grumbles and finger pointing from your peers and maybe even the loss of gainful employment. Tread carefully.

This little statement can be a lifesaver in this situation:


SELECT name, line, text
  FROM dba_source
 WHERE upper(text) like upper('%<Object name to search>%');

Replace <Object name to search> with the table or procedure name you want to check. If you do this and get a table or view does not exist error, you need to get your DBA involved to grant you the appropriate data dictionary privileges. This statement will search stored procedures, functions, packages and the like for a reference to the searched text. If the table is one-off for a specific reason, probably due diligence was not used during its creation so the dependencies tab in a DB browsing tool or the dependency data dictionary table would not have any information to assist you in making a decision regarding the object's worth. You may be able to deduce after some inspection whether it is a useful and still actively used object. But you may not, and individual inspection of items doesn't scale. What if there are 50 objects that do not match? Individual inspection would be a month long task if not longer.

Now, consider table constraints for a moment. Let's say when we built our recipe table from earlier posts we made sure the recipe id could never be null. One way, and a very popular way of doing this is directly in the table definition:
Create Table RECIPE (
RECIPE_ID NUMBER NOT NULL
.
.
.
);

Now, let's say you want to monetize your recipe application. Every new subscriber gets their own database. A year down the road you have 50 subscribers so you have 50 database schemas with roughly identical copies of the recipe database structure. Now, you hire a neighborhood kid to do some light tech work that is beneath you as the King of the new Recipe Database Empire FaceCookBook or CookFaceBook or CookBook? You are too busy with discussions of your impending IPO and talks with Bono and what not to compare your databases. Your new hire compares everything, including constraints. What would you say if I told you that your 50 databases have 50 different constraints for each original constraint? Surprised? When you think about it, it makes sense but I never took the time to think about it until it was too late. My comparison of 27 databases have 120,000+ collisions on constraints. Every table that had an in line NOT NULL declaration on a column or a DEFAULT value declaration had a different name in each database. Oracle has an automatic sequence that is behind the scenes and when you declare a column to be NOT NULL or DEFAULT 0 or whatever, the way Oracle implements and enforces that is by creating a system named constraint in the form of SYS_C00000000001 or something like that where the 1 is the value from the system constraint sequence. Since you are letting each individual system name the constraints whatever it wants, there is no guarantee that SCHEMA1.SYS_C0000000001 is identical to SCHEMA2.SYS_C0000000001. Basically, all hell breaks loose. You get these false positives or probable false positives that muddy the waters so you cannot see immediately that TABLE C in SCHEMA 48 is missing a constraint on COLUMN X.

Let's consider an alternative. Try this instead:


CREATE TABLE recipe
(
   recipe_id   NUMBER CONSTRAINT recipe_recipe_id_nn NOT NULL
.
.
.
);

Now, the naming convention lacks something but IMHO it is far superior to SYS_C0001869645. If you ever tried to insert a recipe with a recipe id, instead of an error message that says SYS_C000689345 violated you would see RECIPE_RECIPE_ID_NN violated. You immediately know that the recipe id column in the recipe table should be not null. And, when the neighborhood kid does the compare for you, he doesn't interrupt your IPO meeting with Jon Bon Jovi and Curt Schilling to tell you that there are 120,000+ discrepancies between databases and WTF is going on!?!!?! It could get ugly.

For more on constraints, check out docs.oracle.com, www.oraclefaq.com, http://jonathanlewis.wordpress.com (I like this guy), http://asktom.oracle.com, or Burleson at http://www.dba-oracle.com/. If you find a page with Cowboy Burleson on it you have to take a shot but if you find a page with Uncle Touchy Pedo Burleson on it you have to put yourself on a list and seek therapy - extra points if you take three shots, do the tuck and exclaim, "It puts the lotion on its skin, or it gets the hose again!"

The Decode function in Oracle

     Oracle has a function called decode that allows you to decode coded values or even encode values depending on your needs. The function takes a column, a series of if-then pairs and a default value. An example is in order. Let's think back to our Recipe database example from earlier posts. You have a Table called Recipe and it has a column called Type to show if the recipe is an Appetizer or a Soup or a Dessert for example. And let's say that in Version 1 of our recipe database we just made RECIPE.TYPE a CHAR(1) column. So, we store 'A' for Appetizer, 'S' for Soup and 'D' for Dessert, etc. Now, let's say your Mom/SO/Daughter/Client is confused by the 'S' on the screen and why Salads show an 'L' (for obvious reasons we cannot have both Soup and Salad designated with an S unless we want to combine them so the 'S' designates a Soup or a Salad. For this example, we want to keep them separate, and 'A' was taken for Appetizer so the next available letter in Salad is the 'L', but you don't want to explain every one of them. You can write a SQL statement with a Decode and list out all of them one time and use that where you need it. Our statement would look something like:

SELECT NAME, DECODE(TYPE, 'A', 'Appetizer', 'S', 'Soup', 'D', 'Dessert', 'L', 'Salad', 'Other') AS Decoded_Type FROM RECIPE;

This is pretty handy. Now, let's say you want to run a report and see how many different type recipes you have. One possible query, using multiple decodes within an aggregate would be:

SELECT
  SUM(DECODE(TYPE, 'A', 1, 0)) as NumberOfAppetizers,

  SUM(DECODE(TYPE, 'S', 1, 0)) as NumberOfSoups,

  SUM(DECODE(TYPE, 'D', 1, 0)) as NumberOfDesserts,

  SUM(DECODE(TYPE, 'L', 1, 0)) as NumberOfSalads,

  SUM(DECODE(TYPE, 'A', 0, 'S', 0, 'D', 0, 'L', 0, 1)) as NumberOfOthers
FROM RECIPE;

Now, the way SUM(DECODE(TYPE, 'A', 1, 0)) as NumberOfAppetizers, reads is: for every record, I want to select 1 if the TYPE = 'A' and 0 Otherwise, Oh, and, please sum up the total of that for me and call it NumberOfAppetizers. This might not be the best way to do this for our example, but I ran across a real world case where someone had written a huge SQL statement where the calculated those values in sub-selects, and then joined all of those sub-selects together to display the desired information. Although the above is a much simplified version it could be expanded upon to utilize DECODE, alleviate the multiple sub-select join, and improve readability and performance.

A couple of other things we hit upon this time that might not have seen before is the nested function calls and the column aliasing. Functions, whether SQL Native or After-Market, depending on what they do and your use-case or needs, can be nested where the inner function executes and returns result to the outer function which then executes. We see that with the decode statements and the sum functions. The decode converts the type to either a 1 or 0 for each row and then the results are summed for the entire table. Pretty neat stuff. I have used this a lot with dates - ADD_MONTHS(TRUNC(SYSDATE), 1) or something like that. The column aliasing lets you rename columns in a select statement. SELECT TYPE AS MY_TYPE, SUM(AMOUNT) AS PROFIT FROM RECIPE GROUP BY TYPE; etc.

I feel this might be a slippery slope here because my explanations keep introducing new things. ADD_MONTHS is a function that takes a date and returns the passed value moved n months into the future or past depending on sign. Yes, ADD_MONTHS('01-DEC-2012', -12) will return '01-DEC-2011'. Sysdate, I believe we have covered before but just in case we haven't that is the system date from the oracle server. TRUNC is a function that truncates part of the date value that you pass depending on what you pass. TRUNC(SYSDATE) drops the time portion and returns the current day month and year. Other options exist, like TRUNC(SYSDATE, 'MM') returns the first day of the current month, etc. Another new item is the GROUP BY statement. If you notice the absence of GROUP BY in the earlier example, that is because I did not select anything that was not being aggregated. That is the rule with GROUP BY in Oracle. You must group by everything that you are not aggregating. If you are aggregating everything you do not need the GROUP BY. So, in the PROFIT example, I am selecting TYPE and the SUM of AMOUNT so I must GROUP BY TYPE or Oracle tells me TYPE is not a group by expression.

Check out the Oracle Function Docs for more information.

I'll dig into more interesting Oracle SQL statements, problems and/or solutions in the future. And, I might even lose my shit and go off on a rant. Toodles.

A Really Interesting Real World Data Issue

     I was approached this week by a colleague that had a data issue. A column was showing duplicates but visually the values had to be different. How to figure out what was different? We would eventually want to consolidate all of the values to the same value but finding out the differences would be interesting and important - we wouldn't want to propagate the incorrect values.

     Think about a table with a column and when you select distinct or select unique() or group by the values look alike but are different. How do you figure out what is different? The standard functions for strings in Oracle are okay but stripped down. There isn't a Left, Right or Mid like there might be in SQL Server or Access. There is substr(value, initial position, length). You could loop through and compare each character and exit the loop when something is different and that might give you where they are different. Then you could compare the ascii values of the characters at those positions to see what is different and decide upon a a good value and then update that character. But that is pretty complex and it takes a lot of manual intervention and you would not see an interesting issue this way.

     I did some research and discovered a function called DUMP. Now, Oracle has taken a dump on my life on a daily basis for 12 years now. I finally get to take a DUMP on Oracle. Ah, revenge is sweet! Taking a dump of a column will give the type, length and ascii value of each character. This is brilliant! And native! I have obfuscated the data for an example that I can provide to you all. Imagine a table called dump_example:


create table dump_example (
dump_column varchar2(4000));

     After adding in the values and we get this:

select * from dump_example;


"DUMP_COLUMN"
"20121130�EXAMPLE�TEXT�VALUE"
"20121130 EXAMPLE TEXT VALUE"
"20121130 EXAMPLE TEXT VALUE"

 

    An interesting issue I ran into is the Length function and Dump diverging on the length for the given column:

If we select the length of the dump column from the dump example table 

select dump_column, length(dump_column) len_dump_col from dump_example;

"DUMP_COLUMN" "LEN_DUMP_COL"

"20121130�EXAMPLE�TEXT�VALUE" 27

"20121130 EXAMPLE TEXT VALUE" 27

"20121130 EXAMPLE TEXT VALUE" 27

...we see that the length of all three values is 27. Now let's take a dump! Once done with that, we wipe and wash our hands with soap and hot water for a few minutes. And then, we do this:

select dump_column, dump(dump_column) len_dump_col from dump_example;

"DUMP_COLUMN" "DUMP_DUMP_COL"

"20121130�EXAMPLE�TEXT�VALUE" "Typ=1 Len=33: 50,48,49,50,49,49,51,48,239,191,189,69,88,65,77,80,76,69,239,191,189,84,69,88,84,239,191,189,86,65,76,85,69"

"20121130 EXAMPLE TEXT VALUE" "Typ=1 Len=27: 50,48,49,50,49,49,51,48,32,69,88,65,77,80,76,69,32,84,69,88,84,32,86,65,76,85,69"

"20121130 EXAMPLE TEXT VALUE" "Typ=1 Len=30: 50,48,49,50,49,49,51,48,194,160,69,88,65,77,80,76,69,194,160,84,69,88,84,194,160,86,65,76,85,69"

Notice the divergent length values. So, something in the selecting and displaying of the values suppresses the multiple white space and somehow the Length function ignores those extras as well. It is the ninth character that begins the divergence in actual value so we need to figure out what the character is related to 32, 194, and 239. If we go to http://www.asciitable.com/ we can see that 32 is the space and the others are on the extended character map and we should probably go with the space as the good character. We can update the other values to consolidate the table but that doesn't explain how two native functions in Oracle can get divergent results like they did. I still haven't quite figured out how or why that is happening. I've been considering posting this out on some expert sites and see if I get any information. 

I was really pleased that this issue popped up this week. I was intrigued by the issue and enjoyed the investigation process and the new function that I learned about. Has anything happened recently in your daily work load that caused  you to learn something new and exciting?

I plan on getting back to the introductory stuff in the next blog. 

How to add, delete, and change data in a database.

I recently went into a little bit of detail about databases and the SELECT statement. That is useful when you want to see what is in your database and do reporting, etc. But, this presumes that the data already exists. What if you have a new database with tables and things but have no data? What if you want to change some data? What if you want to remove data from your table(s)? Where did your mom learn that little trick with her tongue? These are all compelling questions.

There are utilities out there that can load data in bulk into a table but let us focus on the native SQL statements that achieve our desired results. INSERT, UPDATE, DELETE and Tijuana, I'm guessing. Let's say we have a table called RECIPE and this table has these columns:
RECIPE_ID, NAME, TYPE, SOURCE, DATE_CREATED, DATE_MODIFIED
We want to add a record to this table. There are some intricacies here but let's keep it simple.
Your insert statement might look something like this:
INSERT INTO RECIPE VALUES (1, 'Chocolate Chip Cookies', 'Dessert', 'Grandma Betty', sysdate, sysdate);
NOTE: Sysdate is a mechanism to get the current date from the Oracle database. Many systems use it to keep track of when something happens by selecting it and storing it as we have done.
If you run this and commit it, you will have one record in your table. If you have auto commit turned on you are a maniac and an unsavory sort and I want nothing to do with you. Auto commit is a feature that, you guessed it, commits everything you run automatically. You might be thinking this saves you from forgetting to commit or it saves you a step. In my experience, it adds several steps when you have to unfuck whatever you just fucked up. Use with extreme caution.

Now, let's say you are on the phone with Grandma Betty and tell her that you added her cookie recipe and she says, "Well, you know, Dear, that is your Great Great Grandmother Ester's recipe." Oh Noes! What do? Calm the fuck down! I got this. You need to update your record.
UPDATE RECIPE SET SOURCE = 'Great Great Grandma Ester', DATE_MODIFIED = sysdate WHERE NAME = 'Chocolate Chip Cookies';
Commit;
You may actually use the RECIPE_ID in the where clause in place of the NAME but I wanted to keep it understandable. Hopefully this is understandable...

Now, let's say you try these cookies and they taste like Satan's taint. Great Great Grandma Ester went full retard and her recipe is awful and must be exterminated with extreme prejudice. No worries, Brah.
DELETE FROM RECIPE WHERE NAME = 'Chocolate Chip Cookies' AND SOURCE = 'Great Great Grandma Ester';
Commit that shit, yo.

With these tools, you can add, remove and modify data from your database as you desire. Now, I'm off to your mom's house. Toodles.