Thursday, January 29, 2009

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
AS
(
SELECT datefrom, datethru
FROM vw_periods
WHERE periodtypeid = 'WEEK'
AND TRUNC( SYSDATE - datefrom ) BETWEEN 0 AND 56
)
SELECT *
FROM periods;
(Thanks Tom!) Pretty simple right? That's what I thought.
  FROM vw_periods
*
ERROR at line 6:
ORA-32031: illegal reference of a query name in WITH clause
Hmmm...what's that? Since I can't get to the docs right now, I'll point you here.
Cause:
forward or recursive reference of a query name in WITH clause is not allowed.
Action:
Correct query statement, then retry.
That's not very helpful.

What's the definition of the view?
CREATE OR REPLACE
VIEW vw_periods
AS
SELECT
periodid,
periodtypeid,
datefrom,
datethru
FROM periods;
Nothing there...wait, what's the name of the table? Periods? Couldn't be that simple (and yet so obvious) could it?
SQL>WITH per
2 AS
3 (
4 SELECT datefrom, datethru
5 FROM vw_periods
6 WHERE periodtypeid = 'WEEK'
7 AND TRUNC( SYSDATE - datefrom ) BETWEEN 0 AND 56
8 )
9 SELECT *
10 FROM per;

DATEFROM DATETHRU
--------- ---------
08-DEC-08 14-DEC-08
15-DEC-08 21-DEC-08
22-DEC-08 28-DEC-08
29-DEC-08 04-JAN-09
05-JAN-09 11-JAN-09
12-JAN-09 18-JAN-09
19-JAN-09 25-JAN-09
26-JAN-09 01-FEB-09

8 rows selected.
Of course it can!

Lesson? Don't try to name your subquery with the same name as the base table.

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:
SELECT
COUNT( DISTINCT( col_1 ) ) col1_count,
COUNT( DISTINCT( col_2 ) ) col2_count,
COUNT( DISTINCT( col_3 ) ) col3_count,
COUNT(*) count_all
FROM my_table;
That 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?

Wednesday, January 28, 2009

Kate Update: Day 648

  1  SELECT 
TRUNC( SYSDATE - TO_DATE( '04/21/2007', 'MM/DD/YYYY' ) ) kate_counter
2* FROM DUAL
SQL>/

KATE_COUNTER
------------
648
Kris posted this on Facebook here, but just in case you can't access it I'm reproducing the note in full here:
well, as part of the new year, we (kate + me) began with follow-ups w/ her genetics DR. and neurologist.

we had a report sent from the radiologist that read "normal", for kate's MRI back in october. today her neurologist saw her and i brought the scans...we reviewed them together and while it does state "normal", it appears that the "GYRI" or the "wavy" parts of the brain structure are normal, but, they are not as "full" or "filled out" as a perfectly normal brain...it is still considered within normal parameters, but still smaller...her head is of normal size though...anyway, he feels this could be playing a part in her speech and motor delay issues...it is most probably caused by a lack of oxygenation at some point...

he explained that childrens' brains do recover and grow, but there is still this, for lack of a better word, immaturity in the fullness of the brain...her ventricles were also mildly enlarged, but within normal range...he showed me a scan of a "normally full" brain scan, and kate's is ever so slightly less full.

i wish i had a picture to show you, but pretend you dissect a cranium by lifting the top off and looking down, well, the brain doesn't entirely fill the skull....mind you, ever so slightly.

anyway, my heart kinda sank, but he was very impressed in her abilities to understand and comprehend complex tasks...she "acts" like a "normal" child by following directions...he wants to repeat a scan in october of this year to follow up on the growth...so yes, there is a very slight hint of brain damage, but one can not say for sure when this occurred...my best guess is 2.5 years of literally struggling to breathe...she can catch up, as kids brains are able to repair themselves, but it is a small hint as to what is going on...

secondly, her geneticist, is running a test for a disorder that does not allow a body to process cholesterol...( a key ingredient in brain growth), anyway, one of the hallmarks is small stature, and delays, but most prominently, syndactyly, most specifically, a mild syndactyly of the 2nd and 3rd toes...we will have results in about 3 weeks, as it has been sent to the Mayo Clinic...it is called Smith-Lemli-Optiz syndrome...it is rare and there are VERY severe forms, as well as very mild, barely perceptible forms....so, if you "google" it, don't fall out of your chair!

i guess i can't give up until i get the answers...deep inside of me i know there is something going on...i have always had a sense, even before she was born. anyway, there is the update...i know we will get this all figured out. BTW, i have taken up riding my bike to relax during the day when they are at school, unfortunately today, i can't get out to blow off some steam!

update
When I got home I was able to talk to Kris. The short of it, Kate has brain damage. Now, it's minimal as pointed out by the fact that the Radiologist didn't pick up it, but the Neurologist saw it right away. After seeing the MRI myself, you can see a small amount of space between her brain and her skull. Apparently that's indicative of brain damage.

What does that mean? Nothing really. We sort of knew all along. For both of us (her hearing, me reading) it hit pretty hard. I can't remember that last time I felt that way...and I can't explain why. Nothing has changed. We'll still love Kate, we'll still work with her (well, Kris will, I get to play!), she's just going to be slower than everyone else.

Which conveniently brings me to the theory I have about Kate.

Kate is a glimpse of the evolution of the human race. Her lifespan is double, perhaps triple that of you or I. The only downside is that development is slower (like a fine wine right?), which means she won't leave home until she's about 40. As a colleague pointed out though, that's not so unusual these days. ;)

Tuesday, January 27, 2009

Developing a J2EE based SOA Architecture

No, I haven't gone to the dark side.

I just figured it would be best to know my enemy. ;)

Tonight I went to the Tampa JUG (no, it's not a code word for the local strip joint so stop laughing), or, the Tampa Java Users Group. The guy that runs is the the not so infamous Mr. V, from Application Developers vs. Database Developers Part II. I also got to meet/see some former WellCare colleagues as well (hi everyone!).

The presentation was done by Kevin Barfield from JBoss.

Interesting tool which incorporates (I think I'll get this right) the JBoss server, BPM and just about anything else you can imagine. It also included an overview of the Enterprise Service Bus (ESB). That's also a new file type that you can load (as opposed to .ear, .war, etc).

Overall pretty cool stuff, but I really can't put it into context. I'd have to monkey around with it for awhile before I could truly understand what to do with it.

Anyway, that was my trip on the dark side. Not a whole lot of fun but an opportunity to expand my horizons.

Dowload Source Code to Disk

I wanted to be able to download all the source to file system. The problem was that I couldn't figure out how to separate the Package Specification and the Package Body.

I sent out a tweet asking for help. @neilkod answered my plea. Try using DBMS_METADATA he said. I did I replied. He suggested using a filter. Hadn't tried that.

OK, I won't rehash the entire (slow) conversation here, suffice it to say, he pointed me in the right direction.

Here's the code I ended up with:
  FUNCTION get_ddl
( p_owner IN VARCHAR2,
p_object_name IN VARCHAR2,
p_object_type IN VARCHAR2 ) RETURN CLOB
IS
l_handle NUMBER;
l_clob CLOB;
l_transform_number NUMBER;
l_object_type VARCHAR2(30);
BEGIN

l_object_type := get_object_type( p_object_type );
First, a call to an internal (my internal) routine that will determine the object type value for the OPEN function. I found that table here.

l_handle := dbms_metadata.open( object_type => l_object_type );

dbms_metadata.set_filter
( handle => l_handle,
name => 'SCHEMA',
value => p_owner );

dbms_metadata.set_filter
( handle => l_handle,
name => 'NAME',
value => p_object_name );
The SET_FILTER value values can be found here.

l_transform_number := dbms_metadata.add_transform
( handle => l_handle,
name => 'DDL' );
If you don't set this you'll get a bunch of XML and I really wasn't in the mood for XML.

l_clob := dbms_metadata.fetch_clob
( handle => l_handle,
cache_lob => TRUE,
lob_duration => DBMS_LOB.SESSION );
Fetch the CLOB...

dbms_lob.write
( lob_loc => l_clob,
amount => 1,
offset => dbms_lob.getlength( l_clob ),
buffer => '/' );
Append a run "/" sign at the end of the file...

dbms_metadata.close( handle => l_handle );

RETURN l_clob;
EXCEPTION
WHEN others THEN
dbms_metadata.close( handle => l_handle );
RAISE;
END get_ddl;
Voila!

All done. Now you just need a routine to write it to file and you're done.

I'm also putting this in the googlecode repository here: http://code.google.com/p/plsqlsourcedownload/

You should be able to view all the code there. If not, let me know. Not sure how that works yet but I'm giving it a go.

Monday, January 26, 2009

What's My Purpose?

No, not as a person. I kind of know that one; father, husband, son, etc.

I'm talking about my chosen career.

Software Developer, Database Developer, DBA, however it is that I am defined professionally.

What is my purpose as one of those?

I bring this up because I had a great conversation with a friend over the weekend. In discussing all things geeky, he said, "My job is to eliminate the need for me."

That's a paraphrase of course because I believe beer was involved during this discussion.

I about jumped out of my seat though. I had never heard anyone (to my knowledge or memory anyway, though my old boss may jump in and remind me) utter those words. I have uttered those words (but not to my wife apparently, as I called her almost immediately) before. I have said them in interviews, in discussions...but never here.

"My job is to eliminate the need for me."

Indeed.

I strive for that...to build a system that requires virtually no support. I am nowhere close, but I strive for it.

What other role do we serve but a support role (unless we sell software)? IT in just about every instance is a cost center right? We are there to support the business side of the house do their job more effectively and more efficiently. Nothing more, nothing less.

We aren't there to build systems that will require constant care. We aren't there to ensure we have a job in the future. We're hired to build the best application possible and get out of the way.

Of course, there are many factors in our way for doing so. Poor business requirements is probably the number 1 reason. If the business can't describe how can we build it?

The upside of eliminating the need for us is we can move on after a couple of years. We can demand more money up front as they won't need us for 5 or 10 years (still don't know how to sell that one). The idea remains the same though...

So then, how do we build software that will obviate the need for us? I still don't know...but if you do, feel free to comment.

Sunday, January 25, 2009

UTL_FILE: Writing a File to the File System

This is more for me that it is for you as I always seem to forget how to do this. Next time I'll simply search my site for the example.

Nothing fancy here, I just want to be able to write a file to a directory on the file system. This directory must be a database object (until java is used anyway).

So...
CREATE DIRECTORY test_dir AS '/temp';
No, I'm not doing this on Linux (yet), this is my c:\temp directory.

Here's the procedure:
CREATE OR REPLACE
PROCEDURE write_to_file
( p_dir IN VARCHAR2,
p_filename IN VARCHAR2,
p_file IN CLOB )
IS
l_file_handle UTL_FILE.FILE_TYPE;
l_loops INTEGER := 1;
l_max_linesize NUMBER := 32767;
l_start_position NUMBER := 1;
l_source VARCHAR2(32767);
BEGIN
l_file_handle := utl_file.fopen
( location => p_dir,
filename => p_filename,
open_mode => 'w',
max_linesize => l_max_linesize );

LOOP
l_source := SUBSTR( p_file, l_start_position, l_max_linesize );
EXIT WHEN l_source IS NULL;

utl_file.put_line
( file => l_file_handle,
buffer => l_source );

l_start_position := l_loops + ( l_max_linesize * l_loops );
l_loops := l_loops + 1;

END LOOP;
utl_file.fclose( l_file_handle );

EXCEPTION
WHEN others THEN
utl_file.fclose( l_file_handle );
RAISE;
END write_to_file;
/
show errors
Simple enough right? Running it is easy from SQL*Plus.
EXEC write_to_file( 'TEST_DIR', 'testing.sql', 'HELLO WORLD!' );
I've done it 20 or 30 times, but each time I have to relearn it...next time I won't!

Wednesday, January 21, 2009

Oracle XE on Ubuntu Intrepid Ibex - Part II

Last night I tried to install Oracle XE on my new Ubuntu Intrepid Ibex installation (Machine 3), but it failed due to lack of swap memory. Thankfully, cdman was reading and offered up this page in the Ubuntu documentation.

I followed the instructions there, only adding 1024 instead of 512 Mb. Reboot. Rerun the package installer (still not command line...) and voila!

From oraclenerd

Following those instructions, opened up a terminal:
chet@ubuntu:/etc$ sudo init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:8080

Specify a port that will be used for the database listener [1521]:1521

Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:n

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"
That took about 10 minutes because it's an old underpowered machine, but I'm not worried about that now.

Open up SQL*Plus (via GUI...like I said, I'll get there) and login as sys:
SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges
Wait, I'm not logged in as root. OK, try again:
SQL> conn sys/testing as sysdba
Connected.
Nice. Once I can figure out how to launch SQL*Plus (cdman, no hints!) from the command line I should be able to use the "conn / as sysdba" syntax by using sudo. So I'm in.
SQL> SELECT * FROM DUAL;

DUM
---
X

SQL> CREATE USER cjustice IDENTIFIED BY testing
2 DEFAULT TABLESPACE users
3 QUOTA UNLIMITED ON users;

User created.

SQL> GRANT DBA TO cjustice;

Grant succeeded.

SQL> conn cjustice/testing
Connected.
SQL> CREATE TABLE t
2 AS
3 SELECT 'BOLLOCKS' t, rownum id
4 FROM dual
5 CONNECT BY LEVEL <= 1000;

Table created.
A couple of quick tasks and all seems to be well.

Let's check out the website:

From oraclenerd
WTF?

Probably something in the etc/hosts file that I need to update. I'll leave that for Part III.

Tuesday, January 20, 2009

Oracle XE on Ubuntu Intrepid Ibex - Part I

No, I didn't read any guides on how to do this or anything, I just downloaded the .deb file and tried to install via the GUI tools (I'll get to the command line stuff eventually). I just wanted to see if it was possible out of the box with no intervention whatsoever.

From oraclenerd

I have no doubt I can do this on Machine 4, I'll try that as soon as I can figure out how to remote into that computer (It's a laptop and I prefer the 20 inch monitor...and I'm lazy).

Monday, January 19, 2009

Ubuntu Installation Update

I finally managed to seriously try a non-windows OS recently.

To update, I have installed it on 4 machines at home. They are all still in dual-boot mode as I haven't completely figured out how to get rid of Windows without screwing everything up, but 2 of the 4 now default to Ubuntu. It's progress right?

Machine 1:
Dual Xeon Pentium 3 Processor with a speed of 1.6 Mhz (is that right?), 512 MB Ram. It's an old Dell Dimension 530 workstation that I bought on EBay. After installation I quickly realized that the graphics card (Diamond FireGL2) was incompatible. Joy. So I'm currently debating whether to get a newer, compatible graphics card.

Machine 2:
IBM Thinkpad (can't retrieve the specs at the moment). 256 MB Ram. This is my son's computer (he's 6) and the initial inspiration. It was old, had the remnants of corporate security on it, and was a perfect test subject for my trials.

I still haven't been able to get the internal wireless card working (Intersil Prism 2.5), but I did have an old Linksys PCI card lying around, so there is internet access. Tonight I was trying to diagnose the reason for no sound and apparently messed something else up in the process (hopefully I'll learn something from all of this). The latest error on restart was of the GDM variety, though I can't recall it specifically.

Machine 3:
Dell Inspiron 4300. P3, 1.8 Mhz. 512 MB Ram.
This has been my work-horse for the past 7 years. Over the last 2 or 3 though, I've used it mainly to work remotely. In the beginning, I had JDeveloper (9 I believe) and Oracle 9i Server running on it at the same time. That was painful. Overall it seems to run just fine with Ubuntu. Though I probably won't be doing any performance testing (Oracle) with it soon.

From oraclenerd

Machine 4:
Dell XPS, T5350 (I think) Dual Core Processor. 3 GB Ram.
This is my main "work" computer and has Windows Vista installed on it. I installed Ubuntu (dual, default is still Windows as it's shared) last week. Downloaded JDeveloper 11i (.bin file) and managed to install it successfully (w00t!). Next up I want to install Oracle 11g Server using this post as a guide (thanks Mr. Norris).

Performance on this machine rocks. Vista out of the box took up 1 GB of Ram. Ubuntu is running at about 400 MB. I have had no problems on this machine and look forward to learning more here...

From oraclenerd

And if you've got any resources you can link up for me, please do.

Sunday, January 18, 2009

What a Life...

Saturday afternoon I had scheduled to go golfing (I'm not a regular golfer) for a friend's bachelor party (believe it or not, no strippers). Saturday morning wifey was sick so I decided to cancel the day of golfing (drinking). For weeks I had also planned on attending the Museum of Science and Industry (MOSI) Tweetup Saturday night. Since wife was sick and there was a second event, dinner, I opted to go see my friend and celebrate with him.

My friend is a former teammate from UF (Go Gators!). Of the 8 people who attended, I was the only one not a high school (or further back) chum.

Anyway, one of his high school friends (and teammate) was a guy who played 12 years in the Big Leagues (MLB). He retired 2 years ago.

Me:
"So, what do you do now?"

Him:
"I chase my kids."

Me:
"Wow"

35 years old and his job is to chase his kids. How sweet is that? He never has to work a day for the rest of his life. I wonder if I had been fortunate (read: talented) enough to play MLB if I could handle doing "nothing" for the rest of my life? I couldn't imagine it now...I work almost non-stop. Either for work or just learning something new. Doing "nothing" would probably drive me nuts.

What about you? Could you retire at 35 or whatever your current age and do nothing?

Friday, January 16, 2009

Database Views: Love 'em or Leave 'em?

A view in Oracle is essentially a query you can store in the database.

I use them quite heavily in my own systems. There are a few reasons that I like them and use them and in no particular order, here they are:

Complex Joins

Say you want to flatten out a customer's data. Typically a customer is comprised of 1 or more phone numbers, 1 or more addresses, 1 or more email addresses and their demographic information (Gender, DOB, etc). If you use something like the Party Model, that will require at least 5 tables:

PERSON
ENTITY
ADDRESS
PHONE
EMAIL

Which in turn requires 4 joins. I typically make a view on those tables and then reference the view in all my code (naturally the exception is where there are INSERTs/UPDATEs/DELETEs).

I don't necessarily consider this a complex join, but it's a lot of typing going on and with that the potential for errors. My personal preference is to create the view and then grant access to the view, not the underlying tables (security).

Maintenance and Support

These 5 tables would probably be at the core of just about any system that requires user interaction. Let's say you had 50 packages referencing these tables and you need to make a change...either add a column, alter a column or just drop a column. You potentially have 50 packages that are now invalid because of this because they all reference one or more of these tables. You would then have to go through each package to determine whether or not a change was actually required, or you could just recompile the schema (DBMS_UTILITY.COMPILE_SCHEMA). After recompile though you still had 42 packages in an invalid state.

If you used the view, after it's update and recompile, you may (hopefully) have only 1 or 2 packages that require updating. Far and away a better prospect than 42!
Logic
What if you web application needed the address in a specific format? In the view you can create that one time
SELECT 
name,
address1,
address2,
city,
state,
zip,
city || ', ' || state || ' ' || zip AS web_city_state_zip
FROM vw_person;
Of course that can be done on the table itself now with Virtual Columns. Let everyone know about it and they don't have to rewrite it (support and maintenance). Done!

That's my haphazard list for using database views.

What about you? Do you use them or prefer not to? If not, why not?

Thursday, January 15, 2009

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.

Tuesday, January 13, 2009

OBIEE: Direct Database Request

Of course a couple of our leads asked last week if they could get access to OBIEE to do some reports. Naturally, they didn't want the point and click variety, they had the SQL (our OLTP is replicated to our DW as well).

I had no idea how to do it of course. Click click clicking around I couldn't find anything. Wait, BI Administrator. That's how you add users. OK, good enough.

How do you give them access to write Direct Database Request SQL? I'm still not sure but the answer probably lies in the "Answer" section. From there, you can either select a subject area or select a "Direct Database Request." With the Direct Database Request all you need is the Connection Pool information (still not positive on how this is formatted) and your SQL statement.

The Connection Pool entry looks something like this:
"DEV_BI"."Connection Pool"

You do not need to put the relevant tables into the Physical Layer of the Administration tool. That makes life a little easier.

Monday, January 12, 2009

OBIEE: Copy Reports From Prod to Dev

Yes, I said from Prod to Dev.

As I recently reacquired my job via the DW guy resigning, I'm in charge of all his stuff. Which basically means I get to learn the easy way, fire.

This means that I haven't had any time to "learn." I've just had to do. Fortunately so far, I haven't been asked to do a whole lot with the tool. Until now.

We have a large migration in the pipe right now...massive changes to the core of our OLTP system which require massive retooling of our DW system. Naturally I've been working on the database side of things. I started out with some 150 invalid objects and I've whittled it down to 3.

What about the reports though? That SQL is not stored in the database (at least as I understand it). Last week I began looking for a tool in OBIEE that would allow me to see database dependencies, kind of like Application Express' tool. Alas I couldn't find one, but I didn't have a whole lot of time to search. And I'm still learning the OBIEE lingo so I don't really know what to search for.

Anyway, I chatted with my former colleague and he told me I could just update the catalog in the Dev environment. Talked to the DBA and the Linux admin about getting access but it never materialized (it was Friday I think and I was out sick today). I still don't know what all is involved other than there's some sort of file I can just swap out...which scares me. Chances are I'll write over something.

So, off to Mr. Rittman's site.

First, Migrating OBIEE Projects Between DEV and PROD Environments, which led me to this one, Migrating OBIEE Reports Between Web Catalogs.

I hadn't seen (or heard) about the BI Catalog Manager, so I opened it up. It want's a directory or URL. Hmmm...I have no idea. I'll have to get the admin guys to help me out with this one. Let me just try the login URL and see if that works:

http://bi_server_prod:7777/analytics/

Nope. But there's a hint, URL must be in the form of...

http://bi_server_prod:7777/analytics/saw.dll

Got it. Sweet. No outside intervention necessary. Log in to dev:

http://bi_server_dev:7777/analytics/saw.dll

Voila!

Copy and paste the reports over and I'm done. Now the arduous task of going through each report to make sure it's not broken. Should be fun.

Fun With Linux

I write this from my first successful Linux installation, Ubuntu Intrepid Ibex. It started innocently enough after reading this tweet.
Is a bad that I uninstalled Internet Explorer from all my family members pcs in the last 2 weeks and replace d it with firefox.
It's fairly innocuous, but it got me thinking about installing some flavor of Linux on my son's old laptop running XP Pro. Maybe it was the sneaky factor?

So last Saturday I went to Wal-Mart and bought an 8 GB San Cruzer flash drive, downloaded the ISO image from ubuntu.com, unzipped it and put the files on the flash drive. Plugged in the flash drive to my son's computer and waited like 20 minutes for it to boot up. It's an old laptop with 256 MB Ram, and I'm not sure what the processor is...but it's slow.

When it finally did come up, I tried clicking on the Install icon on the desktop and it just sat there...for hours...and hours. Finally coming up with some gnome applet error. By the way, the scenery is quite nice:

From oraclenerd


So I sat there for the next 24 hours trying to figure this thing out. Finally, I just installed it from the wubi.exe file supplied in the ISO via Windows. Weird thing about that is it went out to the 'tubes to get the files when it had them right there. Oh well.

Installation went swimmingly. Had Ubuntu running just fine, a bit better than Windows ran on that machine. The update manager told me I needed had updates so I started that process. As I sat around I started to look around (very dangerous). I'm not much of an OS guru, I know enough to get by and that's it. So I'm looking at the files that I left on the system and decided to start removing them. Windows folder? Bah, don't need that. Trash. NTDETECT.com? Trash. boot.ini? Trash. The only folder I did leave was Ubuntu and Documents and Settings (for the saved files). After the update manager completed I needed to restart, so I did.

Can't Load Operating System or some such nonsense. What? I just installed Ubuntu! Effing Linux! Can't these people get it straight? No wonder no one uses it!

Naturally, I'm an idiot.

Had it running on Sunday night and Monday when it came time for my son to get on the computer (after school), it was broken. I received a few irate IMs from the wifey in that regard. Ugh.

Found the XP recovery disk that night and tried to update the boot.ini and NTDETECT files (among others) to no avail. Began re-installation of Windows. Joy. Maybe I learned my lesson this time?

I decided to install Ubuntu on my old machine at home too. That was cut short because I had a very old Graphics card (Diamond FireGL2) that didn't work with Ubuntu. Perhaps I should get a newer graphics card?

In the meantime, I decided to install it on a 3rd machine, my other old box. This has been my mainstay for the last 7 years (yikes). Dell 4300, Pentium IV, 512 MB Ram. There are two disks on this machine, the first of which has 2 partitions, one for the OS and one for Data.

I successfully install it, reboot and am then prompted with a GRUB prompt? WTF? I can't remember the details as it all seemed to happen so quickly. Messing around there I played with the HIDE/UNHIDE commands, MAKEACTIVE, etc. Reboot. Error Loading Operating System. WTF? Come on!

XP Recovery Disk to the rescue. Getting to the command line I quickly noticed F:\WINDOWS. F? How'd that happen? Oh wait...HIDE...MAKEACTIVE...great. Spent the next few hours trying to replace the boot.ini file, but it wasn't recognizing the partition so it didn't matter. I finally just decided to delete the OS partition and re-install Windows.

Finally, finally, here I am. Ubuntu installed on son's computer, check. Ubuntu installed on my (first) old computer, check (still need graphics card). Ubuntu installed on my (second) old computer, check.

I decided not to delete any windows system files this time.

Next up, I need to figure out how, on restart, to have Ubuntu the default in OS selection.

Monday, January 5, 2009

Learning Oracle Business Intelligence (OBIEE)

I learned how to edit a user in OBIEE without breaking anything (thus no need for a new counter Jake).

At first I thought it would be done through the web application only to find no edit button associated with the user. I had to go through the online repository, update the user, and commit those changes. Not really sure what all that means yet, but it's a start.

I had a crash tutorial on this last week as the person I replaced was off to start his own company. OBIEE is a vast, vast world.

Where does one go to learn OBIEE from the bottom? OTN naturally. The Oracle By Example series is excellent so that's where I started.

First up, Creating a Repository Using the Oracle Business Intelligence Administration Tool . I've made it through the Physical Mappings and the Business Mappings so far, one more section to go. I can't say I completely understand all the lingo, but I'll catch on sooner or later. I'm more concerned with the basics of support at this point.

General
- Instrumentation

Official Oracle Documentation
- 11.1.1.5
- 11.1.1.3
- 10.1.3.4.1 and prior
- OBIA 7.9.6.3
- OBIA 7.9.6.2

Update 1/13/2011
I have noticed quite a bit of traffic comes to this page so I am going to continue to update it in regards to OBIEE 11g. I'll keep the 10g stuff here as well as many of the principles are the same, I'll just point to specific posts in this section.

Samples
Sample Applications for both 10g and 11g provided by Oracle are here. Good way to learn as you get to see how others do certain tasks.
11.1.1.5 Sample App (v107) is here.
OBIEE 11.1.1.6.2 BP1 - Sample Application (V207)

OBIEE 11g
- Oracle BI EE 11g – Upgrading from BI EE 10g – Repository & Web Catalog
- An Oracle Fusion Middleware 11g Primer for EBS Sysadmins - Don't let the EBS part fool you. Fusion Middleware is now the underpinning of OBIEE so understanding it is key.
- OBIEE 11g - Demystifying the directory structure - Great overview of the new directory structure. I still get lost trying to figure out what is what. Doesn't help that there are multiple folders under different roots with the same name...
--> Here's my take, Fun With Folders!

Startup/Shutdown Stuff

- Startup/Shutdown Script for Linux Installations
- Starting and Stopping Services on Linux (mine)
- OBIEE: Start/Stop Individual Components (Enterprise Manager)
- OBIEE: Start/Stop Individual Components (Manually)

Customization

- Customizing Oracle Business Intelligence Enterprise Edition 11g
- OBE: Creating and Deploying Custom Styles, Skin, and Messages in Oracle BI EE 11g

Architecture Series
Mr. Rittman.

- So Just What Does WebLogic Server Do Within OBIEE 11g?
- So How Does Enterprise Manager work, Within OBIEE 11g?

Bob Ertl - Bob is the first person I have seen from the BI team to actually blog. What better place to get information than straight from the source?
- Oracle BI Server Modeling, Part 1- Designing a Query Factory
- Oracle BI Server Modeling 2- Dimensional Schema Shapes
- Oracle BI Server Modeling, Part 3- Conformed Logical Dimensions

OBIEE 10g 10.1.3.4
Blogs
- The RittmanMead blog is the premier online resource for all things OBI EE. It can be found here.
- John Minkjan over at OBIEE 101 has quite a bit of information. He posts often and detail posts. I've already been able to reference individual posts a number of times.
- gerardnico There are some great resources here.
- Christian Berg - who has helped me quite a bit writes over here.
- rnm1978 - same goes for this guy. Very helpful articles.
- OTN Forums

BI Architecture Series
This just started recently by Mark Rittman, I'm looking forward to future posts in this regard:

Inside the Oracle BI Server Part 1 : The BI Server Architecture
Inside the Oracle BI Server Part 2 : How Is A Query Processed?
Inside the Oracle BI Server Part 3 : BI Server In-Memory Joins

How ToInstallation
Miscellaneous
My articles on said subject can be found here.
I'm sure as time goes on my tastes will ebb and flow. I'll update as appropriate.

Saturday, January 3, 2009

2008 Blog Metadata

Since I know of, or am too lazy to look for, any tool to give me metadata on my blogging last year, I did it all by hand. Lots of fun.

Posts - 113
JAN  7
FEB 5
MAR 5
APR 12
MAY 15
JUN 9
JUL 19
AUG 7
SEP 11
OCT 5
NOV 7
DEC 11
Comments - 376

According to Google Analytics, March was my lowest month in terms of volume, 676 visits. December was the best at 2,100. Total visits for the year, 18,247.

My highest weeks in terms of visitors were those that I either got fired, laid off or furloughed which would lead me to believe people like those sorts of things. ;) Actually, I think it's a show of support. I'm grateful to be able to share the tougher times here and receive tons of encouragement.

The application developers vs. database developer series (other than my losing my job series) was very popular. You can read part I here and part II here.

The two highest days in volume were the day I got fired, 682 (because the post that got me fired came through Google Alerts about an hour after posting so everyone in the company got to read it), and the day I posted about the passing of Carl Backstrom.

As we move on with our lives it's easy to put things aside like Carl's death. In his role as Mr. Javascript/CSS on the Application Express team, he had the opportunity to interact with a lot of people. I had nothing but positive experiences with him and so did many others. He is sorely missed by many.

Nested Tables

One concept of Oracle that I've always been intrigued by is Nested Tables. Nested tables are, basically, a table (dataset) stored in a single column. They are part of the object-relational features of Oracle. Now, I've never found a practical use for them as it's still relational data in reality.

Anyway, I've been playing around with them in regards to the test harness that I've mulling over.

I decided to try them out to store the arguments for each procedure/function. I can certainly string this together at runtime, but again, just trying to learn something new.

I start off with some SQL Objects:
CREATE TYPE r_arguments AS OBJECT
(
argument_name VARCHAR2(30),
variable_name VARCHAR2(30),
data_type VARCHAR2(30),
defaulted VARCHAR2(1),
position INTEGER,
sequence INTEGER,
in_out VARCHAR2(9)
);
/
show errors

CREATE OR REPLACE
TYPE t_arguments AS TABLE OF R_ARGUMENTS
/
I'll use T_ARGUMENTS as a data type for my table definition:
CREATE TABLE procedures
(
owner VARCHAR2(30)
CONSTRAINT nn_owner_procedures NOT NULL,
package_name VARCHAR2(30),
procedure_name VARCHAR2(30)
CONSTRAINT nn_procedurename_procedure NOT NULL,
function_or_procedure VARCHAR2(1)
CONSTRAINT forp_forp_procedures CHECK ( function_or_procedure IN ( 'F', 'P' ) )
CONSTRAINT nn_forp_procedures NOT NULL,
overload VARCHAR2(40),
arguments T_ARGUMENTS
)
NESTED TABLE arguments STORE AS args;
Thanks to Mr. Morgan and his always helpful library I was able to finally figure out the syntax. Specically, I was ommitting the NESTED TABLE clause there at the end.

When you describe the table and set the describe dept to all it looks like this:
CJUSTICE@ELEVEN>set describe depth all

@DESC PROCEDURES
Name Null? Type
----------------------------------------------------- -------- ----------------
OWNER NOT NULL VARCHAR2(30)
PACKAGE_NAME VARCHAR2(30)
PROCEDURE_NAME NOT NULL VARCHAR2(30)
FUNCTION_OR_PROCEDURE NOT NULL VARCHAR2(1)
OVERLOAD VARCHAR2(40)
ARGUMENTS T_ARGUMENTS
ARGUMENT_NAME VARCHAR2(30)
VARIABLE_NAME VARCHAR2(30)
DATA_TYPE VARCHAR2(30)
DEFAULTED VARCHAR2(1)
POSITION NUMBER(38)
SEQUENCE NUMBER(38)
IN_OUT VARCHAR2(9)
I got that wrapped up, so how to INSERT?
DECLARE
l_arguments T_ARGUMENTS;
BEGIN
l_arguments := T_ARGUMENTS();
l_arguments.EXTEND(1);
l_arguments(1) := R_ARGUMENTS
( argument_name => 'TESTING',
variable_name => 'L_TESTING',
data_type => 'NUMBER',
defaulted => 'Y',
position => 1,
sequence => 1,
in_out => 'IN' );

INSERT INTO procedures
( owner,
package_name,
procedure_name,
function_or_procedure,
overload,
arguments )
VALUES
( 'TESTING',
'TESTING',
'TESTING',
'F',
NULL,
l_arguments );
END;
/
And it looks like this:
OWNER   PACKAGE PROCEDU F OVER ARGUMENTS(ARGUMENT_NAME, VARIABLE_NAME, DATA_TYPE, DEFAULTED, POSITION, SEQUENCE
------- ------- ------- - ---- --------------------------------------------------------------------------------
TESTING TESTING TESTING F T_ARGUMENTS(R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 1, 'IN'))
What if you want to be able to select a given value from inside the nested table? I swear in an earlier version of Oracle you just had to qualify, or maybe I'm just crazy, but here's how you do it now:
SELECT variable_name
FROM
procedures p,
TABLE( p.arguments );

VARIABLE_NAME
------------------------------
L_TESTING
You need to CAST your nested table using the TABLE clause. I still haven't figured out how (or if) you ever need to use ARGS (from the NESTED TABLE clause above).

Alright, now I'm going to add 10 records into the nested table. In the relational world, you'd have 2 tables to do this. One for the procedures and one for the arguments. I'll do it in "1."
DECLARE
l_arguments T_ARGUMENTS;
BEGIN
l_arguments := T_ARGUMENTS();

FOR i IN 1..10 LOOP
l_arguments.EXTEND(1);
l_arguments(i) := R_ARGUMENTS
( argument_name => 'TESTING',
variable_name => 'L_TESTING',
data_type => 'NUMBER',
defaulted => 'Y',
position => 1,
sequence => i,
in_out => 'IN' );
END LOOP;

INSERT INTO procedures
( owner,
package_name,
procedure_name,
function_or_procedure,
overload,
arguments )
VALUES
( 'TESTING',
'TESTING',
'TESTING',
'F',
NULL,
l_arguments );
END;
/
The result of a SQL statements looks like this:
OWNER   PACKAGE PROCEDU F OVER ARGUMENTS(ARGUMENT_NAME, VARIABLE_NAME, DATA_TYPE, DEFAULTED, POSITION, SEQUENCE
------- ------- ------- - ---- --------------------------------------------------------------------------------
TESTING TESTING TESTING F T_ARGUMENTS(R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 1, 'IN'), R_AR
GUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 2, 'IN'), R_ARGUMENTS('TESTING
', 'L_TESTING', 'NUMBER', 'Y', 1, 3, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING',
'NUMBER', 'Y', 1, 4, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1
, 5, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 6, 'IN'), R_AR
GUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 7, 'IN'), R_ARGUMENTS('TESTING
', 'L_TESTING', 'NUMBER', 'Y', 1, 8, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING',
'NUMBER', 'Y', 1, 9, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1
, 10, 'IN'))
Not too pretty.

I now want to know how many arguments (yes, I already know, it's 10, but humor me):
SELECT 
owner,
package_name,
procedure_name,
overload,
COUNT(*) c
FROM
procedures p,
TABLE( p.arguments )
GROUP BY
owner,
package_name,
procedure_name,
overload;

OWNER PACKAGE PROCEDU OVER C
------- ------- ------- ---- ----------
TESTING TESTING TESTING 10
Voila!

I still don't have a practical application for this as this can be done on the fly using PL/SQL collections. Perhaps this in combination with your Middle Tier application (returning UDTs back to the calling application)...

Friday, January 2, 2009

2009 New Year's Resolutions

I was tagged by @crisatunity here, so I'll do this. Hopefully The Curmudgeon isn't reading...

These are professional resolutions, not personal (I'm fat, so guess what #1 is on that list).

1. No brainer here. Go the entire year without losing my job. If I leave voluntarily, that doesn't count. I'm just shooting for no firings, no layoffs and no furloughs.
2. Become proficient in OBIEE. I've been given a second chance so to speak. Our DW guy decided now would be a good time to start his own consulting company and I am the lucky recipient of his job. Bonus: Datawarehousing is all the rage. OBIEE is a great tool to have in the tool box so I consider myself lucky on more than one front.
3. Build a PL/SQL only test harness. I don't really know why, but I want to give this a go. I've used SQLUnit (and like it) and a little bit of utPLSQL, but I think there's a lot to learn here. Specifically, how to generate and run code. I've never really done that, except for a small DROP <OBJECT> script now and again.
4. Contribute something tangible to the Oracle community. So I blog, cool. I've done an ApEx presentation, sweet. I would love to be able to contribute an application (a test harness perhaps?) or some sort of library. I don't know exactly what it is yet...

Four is good enough for me this year. One will be the one I focus on most...I just hope it doesn't become a self-fulfilling prophecy. ;)

So, who shall I ask to join us? Jake has already done like 33 end-of-the-year posts...I'm sure one of them included some form of resolutions (predictions actually, it qualifies though). Here we go:

1. Bradd Piontek || (twitter, blog)
2. Don Seiler || (twitter, blog)

I've paired those 2 guys together because I'm pretty sure they know each other. Watching them on Facebook and Twitter go at it keeps me laughing all day. For instance, tonight on Facebook Bradd posted a picture of him drinking something out of a brown paper bag and asking the community to guess what he was drinking. Mr. Seiler said something along the lines of "shame and disappointment?"

3. Dan McGhan || (twitter (loser), blog)

Dan was my "co"-presenter in September on ApEx. I made him do the whole thing, mostly because he heckled me (i.e. answered all the questions) in my first presentation.

*No drinking went on during the writing of this post.