OraNA :: Oracle News Aggregator

Front and center again...I just woke up from a nap, I'm grumpy, so I must write. Besides, I haven't had a good rant in quite some time.

Friend of mine asked me last week for some advice, specifically asking if there was a tool to convert Oracle SQL Syntax to the ANSI SQL syntax. (A quick search turned up this (it was the first result), if you're interested).

I had to ask why.

Client is switching to an open source database, i.e. "free." Oracle licensing is way too pricey.

I'm sure Oracle costs a lot of money, it's pretty darn good software. Quite possibly the best in the world especially in the database realm. I've written about the incredibly feature rich goodness that is the Oracle database here here...actually, just trust me. It's in my name.

Why is there even a comparison?

Could it be that everyone out there believes that the sole purpose of a database is to store data? That it can't do anything else? The storage and retrieval of data...that's all it does of course.

It's like saying the Democrats and Republicans are the same...at face value, perhaps, but the devil is in the details.

This, this "Bit Bucket" mentality is what is so incredibly frustrating.

I am no position to argue the differences between the various flavors of database, I lack the experience. But if I were using SQL Server, I would leverage the shit out of it's capabilities. If I were using MySQL, I would leverage the shit out of it's capabilities. If I were using Firebird, I would leverage the shit out of it's capabilities. Same goes for every single flavor out there. Get my point here?

The database is NOT a bit bucket!

Do I need to use more 4-letter words?

I know that Oracle is feature rich and that 99% percent of your code can live in the database...think APEX and PL/SQL. You could probably put ALL of your code inside the database if you wanted to put the javascript in BLOBs as well.

Please, please please quit telling me they are the same...they are not.

Follow up rant by Mr. O'Neill can be found on this following post Everything is a Bit Bucket

By: Michael O'Neill
@oraclenude
oraclenude.crisatunity.com

In response to Chet's frustration over yet another encounter with a database agnostic, I wanted to contribute my first article to the oraclenerd franchise. My thoughts seemed too long for the comment stream.

I ascribe the kernel of thought behind "the database is a bit bucket" primarily to each and every database vendor that ever existed. Every database vendor, in an effort to persuade users of competitive products to adopt their product, has participated willingly in espousing some core aspect of how "same as the other guy" their product is in addition to whatever differentiation pitch they have.

Now, the generally weak-minded and lazy developer (yes, I think the majority of developers are in fact weak-minded and lazy) latches on to the vendor's selective "sameness" claims for professional and personal reasons. (full disclosure: I am both an Oracle DBA and .NET developer)

Professionally, because they are financially invested in writing third-party code not database code. To them, the less they spend learning and understanding the particulars of things like databases, operating systems, networks, human beings, etc. the better. Personally, because there is a dominate thread in the culture of developers to dismiss the database as interesting or meaningful. It is a form of heresy to show affection towards any platform in any specificity.

This is why Java's Big Lie of "write once, run any where" swoons so many. Java's Big Lie is analogous to "the database is a bit bucket" by declaring that even the language of software code should be as absolutely interchangeable as possible - even at the expense of being cost-effective or useful. There is an unquestioned faith that decoupling everything from everything is a good thing. This faith gives us code that is as far from the simplest thing that could work from the first moment writing the code is undertaken. It is a faith I reject. That's why I'm an ORACLENERD.

P.S. I know oraclenude and oraclenerd is confusing. It's supposed to be.

It's been awhile since I've been able to work with APEX extensively, so I am rusty.

A question came up today whether we could get multiple values into a single variable (Item in APEX).

Yes we can!

APEX_UTILSNeed some data first:

CREATE TABLE t ( some_text VARCHAR2(10) );

INSERT INTO t ( some_text )
SELECT dbms_random.string( 'a', 10 ) some_text
FROM dual
  CONNECT BY LEVEL <= 5;

CJUSTICE@TESTING>SELECT * FROM t;

SOME_TEXT
----------
thrFXviVWJ
kpfGRRwctv
EVxNrcmBHC
gcBlHaKrLa
irYduOZfkS

I want that table data to be in a single item. TABLE_TO_STRING is your function.

VAR C VARCHAR2(100);

DECLARE
  l_table APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  SELECT some_text
  BULK COLLECT INTO l_table
  FROM t;

  :c := apex_util.table_to_string( p_table => l_table );
END;
/

PL/SQL procedure successfully completed.


C
-----------------------------------------------------------
thrFXviVWJ:kpfGRRwctv:EVxNrcmBHC:gcBlHaKrLa:irYduOZfkS

Easy enough. How about converting it back to a table? STRING_TO_TABLE is your answer.

DECLARE
  l_table APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  l_table := apex_util.string_to_table( p_string => :c );
  
  FOR i IN 1..l_table.COUNT LOOP
    d( 'value ' || i || ':  ' || l_table(i) );
  END LOOP;
END;
/

value 1:  thrFXviVWJ
value 2:  kpfGRRwctv
value 3:  EVxNrcmBHC
value 4:  gcBlHaKrLa
value 5:  irYduOZfkS

PL/SQL procedure successfully completed.

Done.

I got called into a discussion about an existing APEX application. The custom LDAP functionality wasn't working as they expected.

I knew APEX had an LDAP authentication scheme (and don't know the full history of the project so I can't (won't) comment on why it wasn't used). So I fired up my local sandbox just to see how easy or hard it was. Admittedly, I have always avoided anything to do with LDAP...not sure why (plate is full?). I used this as a guide.

Anyway, it was remarkably easy.

Setup
APEX: 3.2.1
Web Server: Apache (OHS)
Database:

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

First I fired up the web server:

C:\oracle\http\opmn\bin>opmnctl start
opmnctl: opmn started

C:\oracle\http\opmn\bin>opmnctl startproc process-type=HTTP_Server
opmnctl: starting opmn managed processes...

Opened up APEX, and created a new application. For authentication schemes I chose "No Authentication."

After I had created the application, I went into Shared Components --> Authentication Schemes --> Create

Select the default and click Next



Select "Show Login Page and Use LDAP Directory Credentials" and click Next



I've already done this so I'm selecting my current Login page, 11, click Next



Enter your LDAP Host and your DN:



Your DN String should look something like this (from article above):

cn=%LDAP_USER%,l=amer,dc=oracle,dc=com

Make sure you use the %LDAP_USER% after the cn= portion of the string.

Name it ldap_test, click Create Scheme:



You will then be redirected back to the list of Authentication Schemes, ldap_test should now be current



To test it just run your application and login using your LDAP (AD) credentials



Success!

Found this in a snippet today:

-- ********************************
-- End of Package Body

END package_pkg ;
/

Seriously? Was that necessary? Could I possibly be under the illusion that it is not the end of the package?

Stop it.

Now.

As the name implies, it's the Top 10 for this past month. Probably the biggest reason I am doing this is the EBS Install series has become a runaway success. I've never had something become so popular so quickly...which of course tells me I'm no good and John Piwowar is the best ever. :)

John deserves quite a bit of recognition so the more I can provide, the better.

Title	Pageviews	Unique Pageviews
EBS Install Guide - Part I	541	394
Learn Oracle Business Intelligency (OBIEE)	444	337
EBS Install Guide - Part 2	347	262
DBMS_CRYPTO: Example	339	295
APEX: Install 3.2.1 on 11gR2	263	225
EBS Install Guide - Part 3	253	202
OBIEE Posts	243	215
OBIEE: How to Migrate Your rpd	234	183
BULK COLLECT and FORALL	187	166

Get the latest SQL Developer release here.

I'm not a big tools guy, I prefer SQL*Plus to anything else. I especially don't like paying for tools (yes, the database is a tool and costs a lot of money...I do realize the hypocrisy).

After Syme Kutz's presentation at SOUG, I've been looking more closely at SQL Developer. I've been using it (and JDeveloper) since they were both made freely available a few years ago. Mostly for the schema browsing, looking around, importing and exporting data. I do use it (SQL Developer) to write reports that I can share with the Business folks as well.

Syme's presentation was primarily on Unit Testing (which I begged for). First step to using Unit Testing is to install the repository, a set of tables the application uses to build and store tests and their results.

You need to have version 2.1 or greater.

First up, go to Tools --> Unit Test --> Select Current Repository



You'll be prompted to select a connection (i.e. database) to use



Would you like to create one now? Select Yes.



You're then told the the required roles do not exist, select OK.



Confirm running SQL



Running...will take just a few seconds



Success!



That's it. Easy right? Future posts will detail managing users and creating tests.

After trying out the lazyweb method of search (aka Twitter) and not getting much help, I resorted to help at the OTN OBIEE Forum. It's not Twitter's fault, I think this problem was a bit too complex to describe in 140 characters.

Here's the post on OTN. I started to get nervous too, I posted on Friday and hadn't had a response...until today. 3 days? Man...that's way too long!

Here's the short of it.

Our reports were coming out funny. Dimension column headings had one style and the Fact table column headings had another.



Using Firebug, I could easily isolate the sections.

On the Dimension column, the definition looked like this:

<th  
  class="ColumnHdg" 
  style="background-color: rgb(231, 231, 247); font-size: 9px; color: rgb(0, 51, 102);" 
  scope="col" 
  dir="ltr">Product Desc
</th>

The Fact table column was defined as:

<th 
  class="ColumnHdg" 
  scope="col" 
  dir="ltr">Basis Amount
</th>

Note the style attribute...that overrides any class settings. Very annoying.

I thought it would be relatively simple to fix. I worked with custom messages before, this had to be similar. So I began to "grep" the messages directory

c:\oraclebi\web\msgdb\>findstr /i /m /s /c:"background-color" *.*

Nothing.

How about looking for the name of the class, ColumnHdg?

c:\oraclebi\web\msgdb\>findstr /i /m /s /c:"columnhdg" *.*
messages\criteriatemplates.xml
messages\formattemplates.xml
messages\mktgadminuitemplates.xml
messages\mktgcommontemplates.xml
messages\mktglistformattemplates.xml
messages\mktgsegmenttemplates.xml

So I start with criteriatemplates.xml and find the reference to columnHdg (just now realizing that the case doesn't match...oh well). That was part of the WebMessage kuiColumnFormulaEditor. So I searched for that...

You see where I'm going.

That lead me to the javascript files (of which there are tons). Nothing...not a single thing that could possibly be adding this style attribute.

That's when I mapped the dev server drive to my local computer and opened up WinMerge

I then began to compare every single file in both the msgdb and res (javascript) folders. I would then compare the files that were different to see if that could be the cause. Still...nothing.

I had looked in the webcat before, but couldn't find anything of global significance. I was headed back there though since I had lost hope with custom messages and/or javascript.

Then I got a message from the OTN Forum Administrator...could it be? Looked at the name of the person who answered it first, John Minkjan...sweet!

Looks like you forgot to reset the OOB settings when installing OBIEE:
have a look here to reset them:
http://obiee101.blogspot.com/2009/02/obiee-editing-system-wide-defaults.html

Click through, follow his instructions, bounce the server and voila!



As of this writing, I don't know what OOB stands for; I could make something up, but it probably wouldn't be appropriate for this family site.

Thanks John!

I received a question in the form of a comment on using XML files here. Fittingly, we were discussing this exact topic yesterday and I decided I would try it out.

So here goes.

You'll need 2 files: a XML file and a XSL stylesheet (which defines the XML file). I found said files here. I'll also provide them here (cdcatalog.xml) and here (cdcatalog.xls).

In the Administration tool, go to File --> Import --> from Database



When you prompted to select a datasource, select XML and the window should look like this



For URL, browse to the location you saved cdcatalog.xml, do the same for XSLT



Hit OK and you'll be prompted with the Import dialog



Import the "table" and you're done.

I've found myself at a certain disadvantage lately, specifically in regards to understanding tuning. I don't like being at a disadvantage...I'm competitive that way.

I understand, from a very high level of what needs to be done, but I could not prove my theories. Well, I could, sort of, but it wasn't necessarily a reasoned or logical approach. I just tried all possible permutations. There's a certain time constraint with that method and when you're talking about huge volumes of data (i.e. datawarehouses), time is short.

I've decided it's time to change that, so I'll begin to peck away ever so slowly.

In Usage Tracking, there is a table called S_ETL_TIME_DAY. It's a sister table to S_ETL_DAY which is your everyday time dimension. S_ETL_TIME_DAY breaks down a single day into hours and minutes which means there are 1440 records (24*60*60).

Somewhere I saw the following SQL:

SELECT DISTINCT hours
FROM s_etl_time_day

which just returns the hours in the day (24). I've pondered on whether DISTINCT is a bug, but it seems fairly innocuous here.

What about this though?

SELECT rownum - 1 hours
FROM dual
  CONNECT BY LEVEL <= 24;

Which one is faster?

Let's try an explain plan

Query 1

S_NQ_SCHED@TESTING>EXPLAIN PLAN FOR
  2  SELECT rownum - 1 hours
  3  FROM dual
  4    CONNECT BY LEVEL <= 24;

Explained.

Elapsed: 00:00:00.05
S_NQ_SCHED@TESTING>@EXPLAIN

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1731520519

------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
|   1 |  COUNT                        |      |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(LEVEL<=24)

15 rows selected.

Query 2

S_NQ_SCHED@TESTING>EXPLAIN PLAN FOR
  2  SELECT DISTINCT hours
  3  FROM s_etl_time_day;

Explained.

Elapsed: 00:00:00.04
S_NQ_SCHED@TESTING>@explain

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 878743397

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |    24 |    72 |     5  (20)| 00:00:01 |
|   1 |  HASH UNIQUE       |                |    24 |    72 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| S_ETL_TIME_DAY |  1440 |  4320 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

9 rows selected.

Sadly, about the only thing I can usually see in an explain plan is whether an index is being picked up or if there is a nested loop. I'm not going to worry about all of it now...this is just a start. More to get me in the habit.

Next I set up tracing since "reading" the file is so much easier now.

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HOURS';

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Here's the output:

Query 1	Query 2

What does it all mean? I am sure you know or understand better than I. For me, I just need to create the habit. I'll read (more) about the specifics of the explain plan and the output from tkprof. If you want to explain, feel free. If you want to do it in your own post, link it up or you can write it here. I'd be happy to host it.

Today I got notice that I would be granted a blogger pass for COLLABORATE 10 in Las Vegas, NV. Vegas baby, Vegas! What's more, all three groups, IOUG, OAUG and Quest approved.

If you weren't aware, I had the same deal last year through OAUG. I realized, very quickly, that living up to the obligation was very tough. I wrote 13 articles over that short span of time and it was completely exhausting.

So of course I want to do it again...I'm a glutton for punishment I guess.

CJUSTICE@TESTING>SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Last night I was installing the Unit Testing repository for SQL Developer for a fun little post. After installing the repository, I just did a brief sanity check.

CJUSTICE@TESTING>SELECT owner, COUNT(*) c
  2  FROM dba_objects
  3  GROUP BY owner
  4  ORDER BY 1;
  
  OWNER                                   C
------------------------------ ----------
...snip
SI_INFORMTN_SCHEMA                      8
SYS                                 22970
SYSMAN                               1341
SYSTEM                                454
S_NQ_SCHED                              3
TSMSYS                                  3
WMSYS                                 242
XDB                                   682

27 rows selected.

Strange.

I created a user, UNIT_TESTING, to house the data and fed it to SQL Developer. Did I create the user?

CJUSTICE@TESTING>SELECT COUNT(*) 
FROM dba_users 
WHERE username = 'UNIT_TESTING';

  COUNT(*)
----------
         1

Yeah.

I check DBA_OBJECTS using UNIT_TESTING as the predicate:

CJUSTICE@TESTING>SELECT * FROM dba_objects WHERE owner = 'UNIT_TESTING';

no rows selected

Really?

CJUSTICE@TESTING>SELECT table_name
  2  FROM dba_tables
  3  WHERE owner = 'UNIT_TESTING';

TABLE_NAME
------------------------------
UT_LIB_TEARDOWNS
UT_LOOKUP_CATEGORIES
UT_LOOKUP_DATATYPES
UT_LOOKUP_VALUES
UT_METADATA
UT_TEST
UT_TEST_ARGUMENTS
UT_TEST_IMPL
UT_VALIDATIONS
UT_TEST_IMPL_ARGUMENTS
UT_LIB_STARTUPS
UT_LIB_VALIDATIONS
UT_LIB_DYN_QUERIES
UT_SUITE
UT_SUITE_TEST
UT_TEST_IMPL_VAL_RESULTS
UT_TEST_IMPL_ARG_RESULTS
UT_TEST_IMPL_RESULTS
UT_TEST_COVERAGE_STATS
UT_TEST_RESULTS
UT_SUITE_RESULTS
UT_SUITE_TEST_RESULTS

22 rows selected.

WTF?

Does this mean that my data dictionary is corrupted? This is a sandbox so it is very well possible...just never seen this kind of thing before.

Apparently this is a 3 part series, Part I, OBIEE: Text File as Data Source and Part II, OBIEE: Create Repository Init Block.

Finally, to the ultimate goal, dynamic session variables. Ideally, these would be repository variables, but since I'm using the row-wise variety, that is not possible (as of 10.1.3.4). Once a day, or some other relatively long time frame would be sufficient.

Anyway, there's an occasion where dynamically creating variables might be needed or necessary.

To recap the problem

Due to certain restrictions in our environment, the tnsnames entries in our separate environments (Dev/QA/Stage/Prod) are exactly the same. I would prefer to have one entry in each environment that points to that environments database, but alas, I cannot. What this does is forces us to change the password in the connection pools when migrating. I know it's not ideal, but it is what it is.

The proof of concept I am embarking on will allow us to lookup the tnsname entry and the associated password for that environment, theoretically, requiring no changes between environments.

In that respect, it was decided that we would have a single service account. From that service account, we could look up the values we need for each environment. Here's the other fun problem...what comes first? The chicken or the egg? This service account password will be stored in a simple text file that will be locked down by the system administrators. Don't ask what happens when the password changes either. At this point, it's just a proof of concept. So be gentle. If you have an established way to manage passwords...please share.

A few people did chime in, Pete Scott suggesting using the a command line utility detailed here. Christian chimed in that I should use it at my own risk and rnm1978 said this problem has not been truly solved yet.

The whole issue of migration OBIEE deployments through the environments is a bit of a pig's ear with no foolproof method that I've seen

I need to look up that phrase as I'm certain it's peculiar to my brethren across the pond.

Finally, the meat of the post. Here's my table:

OBI@TESTING>@desc obi_variables
 Name                           Null?    Type
 ------------------------------ -------- -------------
 VARIABLE_NAME                  NOT NULL VARCHAR2(50)
 VARIABLE_VALUE                 NOT NULL VARCHAR2(250)
 VARIABLE_TYPE_CODE             NOT NULL VARCHAR2(30)

I'll throw a few records in there for fun (and to obviously demonstrate).

INSERT INTO obi_variables
  ( variable_name,
    variable_value,
    variable_type_code )
VALUES
  ( 'TNS_TESTING',
    'TESTING',
    'TNSNAME' );

INSERT INTO obi_variables
  ( variable_name,
    variable_value,
    variable_type_code )
VALUES
  ( 'PW_TESTING',
    'TESTING',
    'PASSWORD' );

INSERT INTO obi_variables
  ( variable_name,
    variable_value,
    variable_type_code )
VALUES
  ( 'URL_TESTING',
    'http://localhost:7777/pls/apex/f?p=101:1',
    'URL' );

TYPE       VARIABLE_NAM VARIABLE_VALUE
---------- ------------ ------------------------------------------
PASSWORD   PW_TESTING   TESTING
URL        URL_TESTING  http://localhost:7777/pls/apex/f?p=101:1
TNSNAME    TNS_TESTING  TESTING

For demonstration purposes, I am going to create a new database in the Physical layer and add a connection pool called obi variables pool. From the previous post, I'll be using the values that I brought in through the text file. The only difference is that I added username...just in case. To sum that up, I'm bringing in the TNSNAME entry, the USERNAME and the PASSWORD.

Your connection pool should look like this:



The password column is the same format as the others, VALUEOF( INIT_PASSWORD )

You'll have to re-enter that when you save.

Now I'll be creating an init block using this connection pool. I won't drag you through that entire process again (your welcome). This is a Session Init Block as opposed to the Repository Init Block in the previous example.

The only difference is that I will be using a row-wise variable. I'll get to that in a second.

After selecting the obi variable pool connection pool, entering the following SQL statement into the area provided:

SELECT variable_name, variable_value
FROM obi_variables

.In the Edit Data Target area, select the row-wise initialization variable. Select OK.

Test it and voila! You now have dynamic variables in OBIEE.

Recently, I've been searching for a Single Row/Detail type report in Answers. Sadly, there isn't one so named.

Here's what I could find in Answers:



Nothing jumps out at me, so, Twitter to the rescue again



Within minutes...I had an answer from Mr. Christian Berg. What was that answer? The Narrative View.

Here's the screen for the Narrative View:



According to Mr. Berg, you reference the columns using the @1 (@2, @3, etc) syntax, the number representing the column number. So I tried that



Run it...



That's not what I want...

OK, so let's see if I can add some HTML to it. First I check the Contains HTML Markup box and then I enter in the following in the Narrative Text Area:

@1<br>

@2<br>

@3<br>

@4<br>

@5<br>

@6<br>

@7<br>

@8<br>

@9<br>

Here's what it looks like:



Better, but not exactly what I want. Now that I know I can use HTML though, it should be very easy.



Voila!

EBS Challenge
John Piwowar wrote the first part of the EBS Installation Guide back in December, since then, it's become a runaway success. I hadn't realized how so until recently when I was looking at Google Analytics and it came it at number 19, for all time. Now it's up to 13 and with the addition of it to the front page, it should only go up faster.



I've never had a post that was this popular so I thought it fitting that John should get the recognition that he deserves. If you decide to take part and then write it up, let me know and I'll link you up to The EBS Challengepage. I think there are 4 people that have completed it so far. I'll even give you space here if you don't have your own blog.

Travel
Got the best color car ever this week.



OBI EE
For some reason my original, Learning Oracle Business Intelligence (OBIEE) post, has been near the top of the charts for multiple search phrases.So I did what any sane person would do, I updated it with some new information. Not really sure if it's new, but it's got more. If people are finding it, I might as well update it as often as possible.