|
PRIMARY KEY and NOT NULL
I've seen this far too often. A table with a primary key (good) and a check constraint (NOT NULL) on the same column.
Stop doing it. Watch. CREATE TABLE tAs HillbillyToad said,  It is better than no constraint, that's for sure. The heart was in the right place... Labels: constraints, development, sql, wtf ¶ 2/03/2010 07:46:00 PM 15 Comments Links to this post 
Database Table Size
I've always wondered how big a table is...up until recently I depended on the DBAs to retrieve such information for me.
Thanks to my good, and very helpful, friend, Mr. Thomas Roach, I no longer have to wait or bother the DBAs. %_SEGMENTS contains a column called BYTES. Use this column to determine the size of your table, with just a little math. SELECT segment_name, SUM( bytes ) / 1024 / 1024 mbThere's a plethora of these scripts out in the wild...but I was originally inspired by helping Mr. Neil Kodner out back in November (which I refer to as the "missing" month). Read his take on it here. ¶ 2/01/2010 11:47:00 AM 6 Comments Links to this post
Dynamic Single Row Query
Once in awhile doing analysis, I'd like to see the count from each table, just to get an idea of how much data I am working with. Of course this doesn't measure width, but it is a metric.
Of course I could go through and write a SELECT COUNT(*) from every table, that works if there are like 4 tables. Anything more and...well it sucks. Yes, I could gather stats on the schema and then reference NUM_ROWS, but this is an occasion where I don't have the necessary privileges. I've been trying to do this for years, with no luck, until yesterday. Typically I would do something like this: DECLAREWhich would of course would give me this: DECLAREOnly now did I realize a fatal flaw with that...I was trying to bind the variable into the string, which would obviously never work. For whatever reason, this time I decided to research it. I came across Flavio Cassetta's site and this post, SQL Error: ORA-01745: invalid host/bind variable namI'll skip a few iterations and get to the final product (because I went through this exercise on Friday night and quite honestly, don't remember all the permutations). SET SERVEROUTPUT ONDone. ¶ 1/10/2010 09:36:00 PM 0 Comments Links to this post
SQL: Calculate Wasted Time
Inspired by this today:
 I answered with this:  I started to do it in SQL, but then I got impatient. I finished it in Excel just to get it done. Some time later, I decided to do it in SQL...just because it's fairly easy. I know there are better/different ways to do it, so share please. Those minutes are precious...so don't waste them. Labels: sql ¶ 9/17/2009 10:07:00 PM 0 Comments Links to this post
Database Tutoring
Last week a friend of mine sent over a craigslist posting, someone looking for a tutor. Here's the ad:
Looking for an experienced SQL Database Systems analyst to help with homework assignments for a graduate level database course. Would like to meet 2 times per week (for 2 hours each session) over the next four months. Evening, weekends or Wednesdays preferred. The candidate must be able to explain the technical to the non-technical. Please reply with resume and availablility.I replied immediately and heard back the next day. I sent my resume but I thought the blog would be more appropriate. Apparently it was enough. We spoke on Saturday for about an hour and I received all the materials necessary to start doing research including a sample database (in Access). I have to say I'm pretty excited about it. I thoroughly enjoy trying to explain database concepts so that others (non-techies) can understand. It's a Masters level class filled with students from Computer Science and from an Education Technology tract. Bet you can guess which side my "student" falls in. Seems a little odd that the Educational Technology folks are in the class, but I think it's a good thing. When they need an application in the future, they'll have a much better grasp of what to ask for and hopefully they'll be more involved in the process. I'll use this space both for reporting on progress and helping to explain things. Wish us luck! ¶ 9/13/2009 09:15:00 PM 3 Comments Links to this post
SQL Developer: Drill Down Reports
Finally, finally I've figured this out. I've googled "SQL Developer Drillable Reports" to no avail. The solution kept alluding me.
The first result you should get back is one from a fellow Tampan (Tampon?), Lewis Cunningham, from July 2006. OK, it's a bit old (I think it was still called Raptor back then), but I'll give it a try. In it, Lewis talks about creating additional "pseudo" columns, SDEV_LINK_NAME, SDEV_LINK_OWNER, SDEV_LINK_OBJECT which appear to map to the corresponding columns in DBA_OBJECTS.  I tried that, and got...nothing. I tried changing the alias(es) to match the column I was using, again, to no avail. Let me back up just a tad, I'm trying to create some reports based on the PLSQL_PROFILER_% tables: * PLSQL_PROFILER_RUNS * PLSQL_PROFILER_UNITS * PLSQL_PROFILER_DATA It's annoying to have to rewrite the SQL everytime. I did create a @profile script, but I had to pass the RUNID; so first, I had to know the RUNID. So I took to Twitter as I know Kris Rice hangs out there sometimes.    That was last week, and I have been unable to get this to work. I could have sworn Kris had a good tutorial on it, but I think I confused it with the extensions you can create. Anyway, I'm at it again tonight and I end up back at the link Kris originally pointed me to. For some reason (cough) I missed this crucial little nugget this first time (the bind variable is case-sensitive)Really? Could it be that easy? I UPPERed RUNID and voila! It worked! To recap, go to the Reports tab, right click on a folder (I have one named "profiler") and select Add Report.  I fill out the Name, Description and Tooltip (optional)  Hit Apply which saves my report. Now I want a report that on PLSQL_PROFILER_UNITS that accepts the RUNID as an IN parameter. First, create the report:  Go to the Binds tab and fill in the fields  Go to the Advanced tab and fill in the name of the report  Now, select your first report, right click, go to Reports and select the report you just created   Perfect! Just a small reminder, the bind parameters are CASE SENSITIVE! Labels: reports, sql, sql developer, tools ¶ 9/09/2009 09:49:00 PM 4 Comments Links to this post
PL/SQL: Pipelined Function
As with Friday's post, pipelined functions are something I use rarely, thus end up looking up how to do it.
So with a little help from Tim Hall [ blog | twitter ], I shall do a quick example. I'm trying to integrate these 2 posts, PL/SQL: Split URL Parameters and PL/SQL: Split Key-Value Pairs. I don't want to store the parsed response or request string just yet, just the string. But in the off chance I want it parsed, I don't want to load it up into excel or something and begin the arduous task of breaking it down. I will (just remembered) have to parse the response string when I receive it though to figure out whether or not the transactions was Approved or Declined. Let's start with the types: CREATE OR REPLACE TYPE r_key_value_record IS OBJECTThe simple function, just for demonstration purposes: CREATE OR REPLACEThis will definitely be expanded in the days to come to include those previous posts mentioned above. I will post the final result and link it up here as well. ¶ 8/09/2009 06:27:00 PM 3 Comments Links to this post
REGEXP_REPLACE - Credit Card (CC) Numbers
I'm starting to rewrite some of our payment processing stuff right now. One thing I want to get a handle on is what is being sent to the payment gateway (we're capturing the response, but the request isn't easily accessible).
So I created a table and I'm writing the REQUEST URL to it. Ran my first test, hey look, there is the credit card number. That won't work. I then started to think about how to mask just the credit card number...I could look for the key(word) in each URL string, but not all gateways are the same. Regular Expressions! A nice 5 year old example from Alice Rischert can be found here. Following that it was fairly easy to figure out, specifically the example (REGEXP_INSTR) on finding the Zip code. [[:digit:]]{13,16}That's it. That's all there is too it and it's applicable to each and every gateway. You could have some false positives if you're passing through 13 to 16 digit numbers, but this is essentially a logging table, so who cares. If I need to see the exact credit card number, I can look it up through the usual process (access, decrypting, etc).I ran a bunch of tests to see how it would work: VAR C VARCHAR2(4000);I'm starting with the easy cases, numbers, 13 to 16 digits long: CJUSTICE@TESTING>EXEC :C := 1234123412341234;Now some of the negative cases, still just numbers: CJUSTICE@TESTING>--17So any number over 16 is not masked. For my purposes, I don't much care. Numbers are easy right? Let's start adding some strings in with the Credit Card number embedded in there. CJUSTICE@TESTING>SET DEFINE OFFThis is more for me than it is for you (another reason you should blog). I always forget how to use Regular Expressions since I encounter the opportunity to use them so rarely. Labels: regexp_replace, sql, testing ¶ 8/07/2009 05:25:00 PM 0 Comments Links to this post
SQL: SYS_CONTEXT
In my experience so far, I've seen very few places where SYS_CONTEXT is used. It is typically used in conjunction with CONTEXTs and also Virtual Private Database (VPD).
Oracle has a built in namespace called USERENV and their is a wealth of information you can retrieve from there. I wrote up a quick view to demonstrate the use of SYS_CONTEXT: SELECTWhich yields the following: ACTION: NULLBasically, these are global variables that Oracle stores on the current session/user. You can create a context for just about anything you want. I've used them in the past along with table based security in a stateless environment. In essence, I create a record in a table with a SESSION_ID, I then store that in the context (global variable) and I use that to reconnect to the database each time. I promise to have an example of using it in the near future. Labels: database, oracle, plsql, security, sql ¶ 7/05/2009 11:02:00 PM 0 Comments Links to this post
Oracle's New INSERT Syntax
Not really Oracle's...just mine.
I'm busy debugging VALUESI manually count the columns and they're the same. Run it again. VALUESI then cut and paste both "halves" of the INSERT statement into excel so I can get a row-by-row compare.
Everything looks good, go! VALUESWTF? I still haven't figured it out, but that excel picture gave me an idea... I tend to put one column on each line, for large tables, this takes up quite a bit of vertical space. I've seen others put columns (and values) on the same line. Just looks ugly to me. Here's what this table looks like: INSERT INTO my_tableWouldn't it be cool if you could do something like this though? INSERT INTO my_table1. You'd save space. 2. It would be easier to read and 3. It would be easier to debug Thoughts? update: I did solve my problem, I was missing the opening parenthesis in the VALUES clause. update 2: I created an "Idea" over at Oracle Mix, check it out and vote for it here. ¶ 6/16/2009 05:00:00 PM 13 Comments Links to this post
How To Populate Your TIME Dimension
Yesterday I wrote about the Datawarehousing TIMES table.
 Today I populated it. I modified it for my owner purposes naturally, specifically removing the fiscal components (thankfully calendar year equals fiscal year). I think that saved me days of figuring out how to calculate certain fields.I remember the first time trying to do this took about 20 different SQL statements. I generated the key (date, time_id) and then calculate that either loop or perform another SQL statement using specific dates out of the table (last day of the month for instance). This time I managed to do it in a single SQL statement. Again, I left out some of the more complicated calculations since I did not have to worry about fiscal requirements. I also got a short refresher course in the date format models which always come in handy and learned a new function, NEXT_DAY. NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE, regardless of the datatype of date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.Here's the SQL to populate your TIME Dimension: SELECTYou can find the table definition and the INSERT statement here as well. Any feedback good, bad or ugly is welcome. Labels: datawarehouse, howto, sql ¶ 3/12/2009 06:15:00 PM 6 Comments Links to this post
ORA-32031: illegal reference of a query name in WITH clause
I was trying to use the subquery factoring clause, WITH, in a query as it was a fairly small subset but called a number of times...with the same predicates.
I ran into the above mentioned error. Here's my query: WITH periods(Thanks Tom!) Pretty simple right? That's what I thought. FROM vw_periodsHmmm...what's that? Since I can't get to the docs right now, I'll point you here. Cause:That's not very helpful. What's the definition of the view? CREATE OR REPLACENothing there...wait, what's the name of the table? Periods? Couldn't be that simple (and yet so obvious) could it? SQL>WITH perOf course it can! Lesson? Don't try to name your subquery with the same name as the base table. ¶ 1/29/2009 11:28:00 PM 1 Comments Links to this post
Is DISTINCT a Bug?
In your application or data model that is.
On a well designed system, I've rarely seen the need to use DISTINCT. If anything, I use it to do analysis on table data or maybe a summary report. Something like this: SELECTThat will tell me the basic distribution of certain data elements in a specific table. Good for possibly determining whether a column needs a Bitmap Index. In an OLTP system however, the liberal use of DISTINCT signals (to me) a problem in the underlying model. Obviously not all instances are without merit, but I'd be willing to bet that the majority are an indicator. How about you? What are you thoughts on DISTINCT? Good? Bad? Indifferent? Labels: database, design, rant, sql ¶ 1/29/2009 09:35:00 PM 8 Comments Links to this post
OBIEE: error : Odbc driver returned an error (SQLExecDirectW)
My method so far to make changes to our existing reports has been to
1. Open the report 2. Modify (Most, if not all, are Direct Database Requests) 3. Cut and Paste the SQL into my editor (JDeveloper) 4. Fix any issues 5. Run in SQL*Plus 6. Cut and paste back into OBIEE On occasion, I'll have to use the Catalog Manager, select report, properties, Edit XML and get the SQL from there. Obviously not the most efficient way, but it works. Others, like Mr. Berg, have suggested turning the log level to level 2 and capturing the SQL being passed to the database. I'm not that industrious yet. Anyway, I've run across this error a couple of times so far. When I do number 6 and click on Validate SQL and Retrieve Columns, I get the above mentioned error. Hmmm...I've just run this thing in SQL*Plus and it works just fine. The queries are really too big to simply eyeball either. So I went simple, in the text box I entered SELECT * FROM DUAL and clicked on Validate SQL and Retrieve Columns: error : Odbc driver returned an error (SQLExecDirectW). error : State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 17001] Oracle Error code: 933, message: ORA-00933: SQL command not properly ended at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000) error : SQL Issued: {call NQSGetQueryColumnInfo('EXECUTE PHYSICAL CONNECTION POOL "BIDEV"."Connection Pool" SELECT * FROM DUAL when mailing_group = ''EMPLOYEE'' then ''Other'' when mailing_group = ''TEST'' then ''Other'' when mailing_group = ''VIP-EXTERNAL'' then ''Other'' when mailing_group = ''SITEUSERS'' then ''Other'' else mailing_group end )')} It appears the query that previously occupied that box is still there...partially. Mr. Berg suggested clearing the cache, so I went into the Administration tool, Manage, but cache was grayed out. I asked the DBA and Linux guy to pass me the file, but they were being difficult today, so I didn't get it. In the short term, I just created a new report appended with " R" (refactored) and moved on to the next one. Hopefully tomorrow I'll be able to get some answers. ¶ 1/15/2009 12:07:00 AM 2 Comments Links to this post
SQL Objects vs. PL/SQL Tables
So I was writing a small procedure to loop through a procedure's arguments (all_arguments).
After getting it working, I then began to port it to packaged code, which is where I ran into a little problem. Here's the initial statement: DECLARERuns fine. My first step to refactor was to use the TYPE declaration in the package header. But you can't do that (little rusty on pl/sql tables). So I created SQL Objects or User Defined Types (UDT). CREATE TYPE r_procedure_arguments AS OBJECTThen I rewrote the anonymous block to use the UDT. CJUSTICE@ELEVEN>DECLAREnot enough values? OK, let's go through it. There are 8 "columns" in the defined object and 8 in the SELECT clause. Hmmm...I tried adding some extra values at the end: DECLARESame result, not enough values. I spent the next hour toggling between gmail, facebook and meebo thinking about the problem. Wait! You have to "cast" the values from the SELECT clause! CJUSTICE@ELEVEN>DECLAREDuh. So I post it here so next time I don't waste an hour trying to remember why I can't get it to work. Labels: development, plsql, sql, udt ¶ 12/30/2008 01:32:00 AM 9 Comments Links to this post
Index those Foreign Keys
I've been reading about this phenomenon for years over on asktom, but I had never actually encountered the problem, until today that is.
I'm helping out doing a small piece on another project. Trying to get back into the habit of "good" unit-testing, I have created some test data. One build script that runs the whole thing, a few user scripts and finally a teardown script that deletes all the data I have created. Naturally, I run it via SQL*Plus; turning feedback and echo off and using a liberal number of PROMPT directives (very similar to how the ApEx install goes coincidentally). This is what my teardown script reports: ...deleting from child table 1Nothing fancy. Then I realized on the final DELETE it was hanging up. Any locked objects? Nope. Maybe it's the trigger? I checked, only saw ON INSERT OR UPDATE, confirmed that with another developer. He suggested unindexed foreign keys. Huh? Really? That's impossible you say. It wasn't impossible. It was true. So I ran my copy of the Tom's "find unindexed foreign keys." Quite a few "****" which is not good. So I went searching for them myself and came up with this little query (for my specifc table): SELECTThat gave me a list of all the columns referencing the primary key (some 37 different tables). If index_name was NULL, then I knew I had to add one. Since I have this obsession lately with generating code, I just used this query and added the following: ( CASENow I have my CREATE INDEX statements and all is well. I run the teardown script again and it finishes like it should. Labels: constraints, indexes, sql, testing ¶ 12/02/2008 07:15:00 PM 1 Comments Links to this post
Things I've Learned This Week
You can in fact DELETE from DBA_JOBS. Not that I had ever tried before this weekend, I just assumed it was a view (still might be with an INSTEAD OF trigger).
I also learned how to reset a sequence without dropping and recreating it. This was courtesy of my crazy DBA, oraclue. Example: SQL> CREATE SEQUENCE TEST_SEQ Labels: oracle, sequences, sql ¶ 11/20/2008 09:35:00 PM 1 Comments Links to this post
Fun with SQL: Analytics and Heirarchical
I've had this problem since yesterday and I believe I finally solved it.
Given this data: ID DIFF_ID START_DAT END_DATE AMOUNTFor each consecutive time period (month) that there is an amount, count how many buckets, up to six. First thought was definitely Analytics. I toiled away on what became a very unwieldy query (took more than one page anyway). A whole bunch of LAGs with the same number of ever increasing CASE statements. My first obstacle overcome was to filter out those that had a 0 for amount. That left me with: ID DIFF_ID START_DAT END_DATE AMOUNTIt took a good while to figure that out for some reason. Once I had that figured, I needed to figure out which were consecutive. Frank Zhou is always solving puzzles with SQL and I remembered I had responded to one of his about a year ago. If you get a chance, please take a look at his site...he solves some pretty cool puzzles with SQL using the MODEL clause and analytics. Anyway, his post, How to find the earliest start date and the latest end date for consecutive transactions in SQL was similar (and my response similar), so I found it to revisit my thinking at the time. First, I use the LAG function to get the previous row's ID (unique) and call it PREV_ID. I use DIFF_ID in the PARTITION clause (window) and order by END_DATE; then add one to see if the months are consecutive. If that value matches the START_DATE of the current row, it's consecutive and I use LAG again to get the previous row's ID. SELECTThat produces the following output: DIFF_ID ID START_DAT END_DATE PREV_ID AMOUNTAs you can see, I have 3 records with the PREV_ID populated. As I am building it, I realize I keep nesting the queries, so in comes the WITH clause (when I first learned of that it was terribly difficult to search for, I didn't know it was also called subquery factoring clause...). WITH subMuch better. Note the START WITH and CONNECT BY PRIOR, I created my own heirarchical table to determine another window to PARTITION on (MIN_START_DATE of the consecutive records). DIFF_ID ID START_DAT END_DATE MIN_STARTNow all I have to do is PIVOT the table (I chose not to use the new PIVOT feature) on DIFF_ID and add an analytic COUNT on my new window (MIN_START_DATE). WITH subAnd voila! DIFF_ID B1 B2 B3 B4 B5 B6Problem solved! Table creation and data can be found here. Labels: sql ¶ 9/09/2008 11:55:00 PM 5 Comments Links to this post
Calculate Total Possible Score for Wii Bowling, Power Throws
Needless to say I have too much time on my hands. I've become obsessed with two Wii games, Bowling and Tanks. I play over and over. I had a perfect game in bowling the other day which wasn't quite as exciting as I thought it would be.
I've also been playing the "Training" game of Power Throws (bowling). You are given 10 shots with another row added each frame. You start out with 10, then 15, then 21, etc. If you get all the pins, you get a bonus (total pins * 2). So far, my highest score has been 651. I've never bothered to figure out what the total possible score is though. SQL to the rescue. Using analytics and the CONNECT BY LEVEL option (10g and higher), it's fairly easy: I had to set the filter on the outer query because you start with 4 rows (10 pins). And the results: Voila! 890 is the best possible score if I strike every frame. I did manage to pass my high score by 9 pins tonight as well. It's a great day! So how come no one wants to hire a guy that can do this kind of fun stuff with SQL? ;) ¶ 8/20/2008 10:04:00 PM 3 Comments Links to this post
Oracle Mix: Two Ideas
I've added two ideas at Oracle Mix. The first one is UTL_FILE has the ability to read the contents of a directory and the second one is Add a pseudo-column that stores the date the record was INSERTed or UPDATEd.
I mentioned the second one before. The first one I've seen on the iloveplsqland.net site. So go and vote (or don't, but leave your comments). Labels: development, ideas, oracle, oracle mix, sql ¶ 7/31/2008 11:15:00 PM 0 Comments Links to this post
11g New Feature: PIVOT
I do have an interview tomorrow. Woohoo!
So my go at the PIVOT operator, "new" in 11g. pivot_clause Datawarehouse Guide SQL Reference Examples Arup Nanda's Example My example. Let's create some data first: So what's the big deal with PIVOT? I'm not sure yet other than it's something new and new is cool. Basically, PIVOT allows you to pivot rows into columns. We often do this for reports we generate. Here's the old way: Really, not that bad. But if I want to do COUNT and AVG, I have to create more CASE statements like the ones above. My query will go from 14 lines to 30 in a hurry. Here's the new cool way: It happens that is 14 rows as well. Now I'll add AVG and COUNT: SELECT *Nice! Sixteen lines of SQL...not bad at all. That should make code a bit more readable (it'll fit on a single page)...I like it! Labels: 11g, development, sql ¶ 7/31/2008 05:15:00 PM 0 Comments Links to this post
Commas: Before or After the Line?
I'm in a new group again which means I have to learn (and accept) other people's style. No one at WellCare put commas before the line (thankfully), but I've found a few here. I've finally come to accept that this is just style and doesn't really matter, as long as the code does what it's intended to do. Yes, it's silly, but we all have our little quirks right? ¶ 7/14/2008 10:36:00 PM 11 Comments Links to this post
Count The K's
I have this really annoying co-worker who happens to be the DBA. Everytime he walks by my desk he pounces on my keyboard. I've learned to Windows Key + L to lock the computer when I hear him approaching, but occasionally I forget.
As I'm standing near my unlocked computer he starts typing furiously in my sql*plus session (shouldn't he know better?). I should know better... Walking away he asks, "How many K's are in there?" I ignored him, but then wondered myself...what's the best solution to this problem? So, here it goes: DECLAREEasy enough, 45. Then I started thinkinhg...can I do this in pure SQL? Of course! SELECTI'm sure many of you can do better than that. So let's see 'em. Prodlife, this isn't a complicators test either. ;) ¶ 7/08/2008 09:00:00 PM 9 Comments Links to this post
SQL for Buying a New Car
Two weeks ago I purchased a new car. The main reason behind that was that I drove a 1996 Nissan P.O.S. (not point of sale either). I now had a 30+ mile commute each way and I (well, my wife and mother) needed a reliable car.
A big factor was gas mileage. Two brands stood out, Honda and Toyota. Honda has the Civic Hybrid and Toyota has the Prius (even though I don't care much for the design). The Corolla and Civic were the top two cars. So after visiting those two dealers to look at cars, I stopped by the Mazda dealer to see if they had anything with MPG greater than 30. I drove the Mazda3 and it was nice (everything would be nice compared to my POS). Salesman offered to run the numbers and I said OK. The point of this rambling, is that I want to know what it will cost based on mileage. Of course I didn't have my laptop, so I couldn't run the numbers (and I hadn't previously). I did it in Excel, but wanted to try it out in SQL. Here's the result: Obviously this isn't terribly difficult. You can do lots with the results as well. Three or four years ago I would have created a table and a function to do this. Now I can do it "virtually." I love SQL... *Key: car_1 = miles for car 1 car_2 = miles for car 2 car_1_gpy = gallons per year car 1 car_2_gpy = gallons per year car 2 car_1_cpy = cost per year car 1 (gas) car_2_cpy = cost per year car 2 (gas) car_1_cpm = cost per mile car 1 car_2_cpm = cost per mile car 2 ¶ 6/11/2008 07:58:00 PM 3 Comments Links to this post
BULK COLLECT and FORALL
I have a task to tune a little bit of SQL. It's very ETL like, but the target is not (yet) a star schema, it's pretty much a table flattened out for reporting purposes.
As I reviewed it, I noticed it went row by row, with a COMMIT inside the LOOP. That's gotta go. Can I do this in one SQL statement? No, there's other processing that needs to be done (UPDATE two other tables before and after). Hmmm...could I just return the appropriate records into a collection? I'll have to look at that to see if it's possible. For now though, I am going to try and use BULK COLLECT with the LIMIT clause and FORALL for the processes that occur before and after. Let's create some data: OK, now let's create an anonymous block, BULK COLLECTing the data from T into a PL/SQL table and then populated another table with that data: And then I run it and I get the following: A quick google search and I end up here . So I can bulk bind, but I have to INSERT into the table as a whole. I can't be selective. I updated my code to this: I run it and it completes successfully. This is all on XE, so I wonder (hope) that 11g will allow me to do what I want (I'll be working on an 11g RAC system). I scroll down the list of google results and I find this one which then takes me to AskTom. The first post demonstrates that my first attempt will work on 11g. BULK COLLECT and FORALL are great tools if you can't do it in a single SQL statement and if you want to avoid the row by row processing. Labels: development, howto, oracle, plsql, sql ¶ 6/05/2008 10:14:00 PM 8 Comments Links to this post
Multi Table INSERTs
I learned something new today! Multi Table INSERTs.
I was trying to capture the new records into a staging table using RETURNING BULK COLLECT INTO, but that doesn't work (yet). I remembered reading about Multi Table INSERTs, but never had the need for using them, so never bothered to learn it.
Awesome! Labels: database, datawarehouse, sql ¶ 5/21/2008 05:17:00 PM 1 Comments Links to this post
Validating a Process Part II
Continued from my previous post.
While discussing external tables with my feisty colleague some time back, I explained that I liked using them but I couldn't figure out how to change the file name to match that of what was defined in the table definition. Colleage to the rescue: Cool! As I mentioned before, I had 4 files types I had to read: 820 and 835, both of the x12 format and two custom file layouts (flat files essentially). Since no one in the group knows Java yet, I wanted to keep the Java portion of the application as small as possible. So with the two custom files, I decided to use external tables. I could then put into practice the above ALTER TABLE statement. As I looped through the list of files to be processed, I would issue an EXECUTE IMMEDIATE so that I could then SELECT from the table in the next step. It worked like a charm. As I was doing some testing, I would issue the ROLLBACK statement to clear the tables for the next run. When I verified, there was still data there. WTF? Oh wait, there's an EXECUTE IMMEDIATE...which runs DDL...which COMMITs...barnacles! So I couldn't use that new thing I learned, oh well. Fortunately UTL_FILE does have the ability to rename files so I picked a name like 'external_table_file_name.txt' and rename the incoming file to that, then SELECT. Works like a charm. Labels: ddl, java, sql, utl_file ¶ 5/01/2008 11:40:00 PM 5 Comments Links to this post
It's a Matter of Time
I've been thinking a lot lately about my recent failed deployments.
How did I get so sloppy? I'm not one to make excuses, but I would say there are some mitigating circumstances at least. Time being one of them. So I got out my trusty calculator (SQL*Plus), and ran the numbers. From August 26, 2007 through March 30, 2008, I've worked 1802 hours. Of that, 118 were PTO or holiday, which brings the total down to 1784. For perspective, a work year of 8 hours per day comes out to 2080 hours a year.
Cool! Only 86% of my hours in a little over 6 months! Obviously, this is not a good thing. Further breaking the numbers down:
DB = Days Between BD = Business Days HPD = Hours Per Day HPDW = Hours Per Day/Week HPWD = Hours Per Work Day HPWW = Hours Per Work Week The scary part is that I am not very diligent about entering my time. It's probably short anywhere between 5 and 10%. I'm not the only one working these kinds of hours either. I know for a fact there are others. Do you think this plays a role in my failed deployments? Labels: discipline, sql, work ¶ 4/21/2008 08:11:00 PM 4 Comments Links to this post
DBMS_CRYPTO: Example
Updated 03/08/2010 Original source can be found here. Based on comments, specifically from caringaboutsecurity down below, I've (finally) updated the source code. Another point, he is correct that there is no key used in this code. Salt is used, but no key. A key would need to be passed in along with the SSN and stored in some location. This code is not concerned with the overall security aspects, just to encrypt the value. I am not qualified to speak towards the over-arching domain of security. The package specification is also provided in this updated version. In the comments from my previous post, Tyler and Tom both mentioned the fact that you should never store sensitive data as plain-text in the database. I used a bad example unfortunately, but Tom did mention the DBMS_CRYPTO package which I have used before. Since I have used it and published a working example to the OTN forums, I figured I'd put it here as well. The goal is the encrypt a SSN and store the encrypted string (RAW) in the database. In conjunction with VPD, you should be able to easily control who can see the plain-text version of the SSN. To do that you will need to create another function which decrypts the encrypted SSN. This is a pretty basic example, but it should help to get you started. You can read more about the DBMS_CRYPTO package in the docs. Specification CREATE OR REPLACEBody CREATE OR REPLACEI mention it in the comments of the code, but do not forget to wrap your PL/SQL before you load it, otherwise someone will be able to easily see your salt/key. Labels: dbms_crypto, howto, oracle, plsql, sql ¶ 4/17/2008 12:24:00 AM 8 Comments Links to this post
Failed Deployment...
236 Days
20 Hours 48 Minutes 30 Seconds I hate making mistakes but I've made another one. My streak ends almost 237 days from my previous one. Something so silly too. In our source system, data was been double loaded somehow. So we decided on a surgical delete. A total of 7 DELETE statements needed to be run; 4 on the source system and 3 on the target system. The source system went off without a hitch. I babysat the re-load of the source tables and was ready to have our load jobs run in our target system. What's this? It ran in half the time?! How's that possible? I pulled up our logs to find that zero rows were loaded into one of our tables. There should have been 45 Million plus. I started to run down the possible causes: 1. Did the job we have in the scheduler that TRUNCATEs our persistent staging tables run? Nope. 2. Did I fail to instruct the DBAs correctly in the critical CR? Nope. Instructions look good. 3. Next to the logs, it ran fine on Saturday morning but not Sunday morning. What happened yesterday? 4. Ah yes, my CR. Open up the script...nothing out of the ordinary...and then I saw it. On our target system, we use work tables to pre-generate a keys. It makes things a heck of a lot faster and removes the need for PL/SQL lookups in SQL (no, we don't have incremental builds yet). So the work table needs to be DELETEd from first based on the keys from the first:
OK, no funny business there. Then I DELETE from the main table:
As I look at it I wonder WTH I was thinking using an EXISTS clause on the main table. That's the source. But do you see what I missed? See it yet? OK, I left out the "AND my_key = a.my_key" from the inner query. Obviously a stupid approach, but it would have worked. The best way to do it is to just get rid of the EXISTS clause:
Live and learn, live and learn... Labels: funny, humility, sql, work ¶ 3/30/2008 10:21:00 PM 0 Comments Links to this post
Is It Arrogance?
I wrote on Friday night about my experiences that day.
I am a very opinionated person. I believe, whole-heartedly, that the database is severely under-utilized, especially at my current employer. I believe that one of the big draws of MySQL is that it's easy for web/application people to pick up. I also believe, in our situation, that's it's a way for application developers to skirt the whole "data" problem. They'll just pawn it off on the Production DBAs to keep the database running. Amusingly, some of our application developers brought down one of our Oracle instances, more than once. Pretty tough thing to do I always thought. I've read articles on bind variables since the beginning, but since it had been drilled into me, I found it quaint. Who would do that? From a C# app someone passed in hundreds of thousands of un-bound INSERT statements. It flooded the shared pool (is that right?) and brought it to a screeching halt. Anyway, back to the point. I've been very vocal lately about MySQL. A few of my friends have begun to warn me that I may be crossing the line towards arrogance. That I will come off as someone resistant to change. I don't see it. But sometimes we're the last to see our own reflection. I don't believe that I am resistant to change. I like change. I just want it to be proven, that's all. I embraced ApEx because it made my life easier. That's all I want. Does this make me arrogant? Labels: database, mysql, oracle, sql, work ¶ 2/04/2008 10:57:00 PM 3 Comments Links to this post
MySQL Friday
Each month we have an IT All-Hands meeting.
Last month I was promoted to Senior Vice President (SVP), because of my superior management techniques. Today I was promoted to CEO! Unfortunately it only lasted for a few minutes. I happen to resemble our new CEO (and I'm always pining for a promotion) and they thought it would be funny (again) to bring me up. I hugged the guy behind me, shook hands with people next to me and ran up to the front. I wanted to shriek, like the people do on The Price is Right, but I didn't have it in me. You gotta have fun at work right? Well, after that it got serious. Our new Director (at WellCare, Directors are executives, one step up from managers and one below VPs) who heads our architecture team (and release management) got up to discuss where he would be taking us. Slide one: From 3 database engines to 1. From 4 programming languages to 2. From 3 OSs to 1. Wanna guess what question I had? "So, what database engine are we going to use?" I knew the answer, but I take every single opportunity I get to make my point. "MySQL." Being on the datawarehouse team, I was confident that Oracle was not going away. He went on to explain: "Legacy applications would be maintained but everything going forward would be done in MySQL." A flurry of questions came from the crowd so I was unable to followup immediately. I could feel the room come alive...it was weird (I think I'm still hopped up from the events that took place today). Our CIO asked if there were any more questions or comments. I spoke up. I have two points. 1. If it's about cost, move all of the one-off applications into just a few Oracle instances. From what I can tell, we have somewhere in the neighborhood of 100. Let's say 5 databases, datawarehouse, our production OLTP and one for others. All you need to do is assign them different schemas, voila! Cost is much lower and there is a very big chance to reuse code. 2. Actually, I can't remember what my other point was. I think it had something to do with putting the logic in the database, that Java was the fad a few years ago, Ruby was the big thing now, what would it be in 5 years? Will we have to rewrite all of the logic then? (I guess I do sorta remember). After that, someone asked about the two programming languages. Not a great answer from the crowd's reaction. Then someone asked about the OS. The crowd was riotous (if that's a word). The CIO had to calm us all down. I made a remark that he hadn't danced yet (one of our former hazing techniques for new employees) because I didn't want it to be completely personal, or just to ease something that I started. After the meeting, I spoke with the Director. Oracle will be gone in 20 years because of the open source databases, it's being commoditized (not sure what that means). SOA is the wave of the future. It was a polite conversation. I told him I look forward to learning from him but that I will probably never be sold on that idea. Fewer moving parts, simplicity, that's what I want. I then spoke with the CIO, told him that once the decision was made, I would support it and keep my mouth shut (or find a new job). I sent an email to the VP of the Director's group (after a couple of beers...idiot!) explaining my rationale. One of the biggest reasons we chose to come to Tampa, to WellCare specifically, was because it was so young and immature. I would have the opportunity, if I could prove myself, to shape the future of IT here. It's nice to have a voice. Anyway, it's Friday, I'm prepped to spend all weekend at work to get this project delivered that was due in November. Have a good weekend! Labels: cio, database, funny, mysql, oracle, sql, work ¶ 2/01/2008 10:52:00 PM 2 Comments Links to this post
SAS: Create SAS Dataset from Oracle
SAS is very prevalent in our environment. When testing, I need to create some datasets for the business users so they they can do their UAT (yes, I shouldn't be involved at this point...but I am).
Here's the syntax to do that (I use SAS EG):
You can also use the following syntax:
If you want to use Oracle SQL (SAS guys and gals call it passthrough SQL), here's the syntax for that: ¶ 12/11/2007 02:14:00 PM 0 Comments Links to this post
Instrumentation: DBMS_APPLICATION_INFO
Instrumentation has something that I have come to rely on fairly heavily. I believe I first read about it on asktom, but the one that really spurred me on was this post on instrumentation on his personal blog.
Initially, I couldn't really wrap my head around instrumentation. I don't know why it was so difficult; I had a similar problem with sessions when I first started my career. I look back now and it just seems so obvious. Now that I am doing datawarehouse work, nothing is fast. Fast to me is now one hour to load 30 or 40 million records. No more split second queries for me. We currently use no tools. It's straight PL/SQL. Instrumentation of the code is ideal. Actually, it's more instrumentation to aid monitoring. The tool most easily used is provided by Oracle in the DBMS_APPLICATION_INFO package. There are three subprograms that I use most, SET_MODULE, SET_ACTION and most importantly SET_SESSION_LONGOPS. I hadn't started using it until this year, I mainly stuck to the first two. SET_SESSION_LONGOPS is now part of my procedure/function template I've created in JDeveloper. What it allows you to do is set a row in the v$session_longops view (I know it's not actually putting the row in the view...it's the underlying table, but I digress). You can then monitor how your job is doing. Here's an example:
g_index and g_slno are global variables in the package. l_table is a PL/SQL TABLE OF VARCHAR2. Now you can monitor the progress of your job in v$session_longops! Here's the query I use:
Now you too can sit for hours and watch your job move incrementally forward! But seriously, it does help tremendously to know where a job is at. You can further use the SET_MODULE and SET_ACTION calls to see a specific point in the processing (inside a loop). Here's the code in context:
Labels: dbms_application_info, development, instrumentation, plsql, sql, work ¶ 12/02/2007 10:04:00 PM 0 Comments Links to this post
|
Guest Authors
How To
EBS Install Guide
Learn OBIEE Parallel Processing: DBMS_JOB Write File to Disk Populate Time Dimension DBMS_CRYPTO PL/SQL: Split URL Parameters Instrumentation: DBMS_APPLICATION_INFO OBIEE: Migrate Your rpd Application Express: A Guide
Popular
Why You Should Blog
AppDev vs DataDev Coding is Easy Fun With Linux Code Style Index Better than Tom Kyte? Previous Posts
The Case for the Bit Bucket
OBIEE: Variable Rendering Afraid to COMMIT; Everything is a Bit Bucket APEX: Create and Parse Arrays The "Database is a Bucket" Mentality Code Comment WTF? Part 209 APEX: LDAP Authentication February Top 10 SQL Developer: Install Unit Testing Repository Code Projects
Archives
August 2007 /
September 2007 /
October 2007 /
November 2007 /
December 2007 /
January 2008 /
February 2008 /
March 2008 /
April 2008 /
May 2008 /
June 2008 /
July 2008 /
August 2008 /
September 2008 /
October 2008 /
November 2008 /
December 2008 /
January 2009 /
February 2009 /
March 2009 /
April 2009 /
May 2009 /
June 2009 /
July 2009 /
August 2009 /
September 2009 /
October 2009 /
November 2009 /
December 2009 /
January 2010 /
February 2010 /
March 2010 /
|
