Please note that you can always click on an image in my postings and it will render a clear full sized version in a separate browser page! Also please note that this blog is best viewed with Firefox and Chrome

Saturday, September 29, 2007

Some quotes I think are awesome.

"You're walking around blind without a cane pal. A fool and his money are lucky enough to get together in the first place."

" Guess you think you taught the teacher a lesson that the tail can wag the dog huh? "

"Ever wonder why fund managers can't beat the S&P 500? 'Cause they're sheep, and sheep get slaughtered."

"And where does your CEO put his million-dollar salary? Not in the company stock; he owns less than one percent. You, the shareholders, own the company. That's right, you! And you are all being royally screwed over by these, these bureaucrats, with their luncheons, their hunting and fishing trips, their corporate jets and golden parachutes. "

"Teldar Paper has 33 different vice presidents each earning over 200 thousand dollars a year. Now, I have spent the last two months analyzing what all these guys do, and I still can't figure it out. The new law of evolution in corporate America seems to be survival of the unfittest. Well, in my book you either do it right or you get eliminated."

"Confidence is contagious. So is lack of confidence."

"Dictionary is the only place that success comes before work. Hard work is the price we must pay for success. I think you can accomplish anything if you're willing to pay the price."

"If it doesn't matter who wins or loses, then why do they keep score? "

"Once you learn to quit, it becomes a habit."

"The difference between a successful person and others is not a lack of strength, not a lack of knowledge, but rather a lack of will. "

"I've never known a man worth his salt who in the long run, deep down in his heart, didn't appreciate the grind, the discipline. There is something in good men that really yearns for discipline and the harsh reality of head to head combat. "

"If you think it was luck, I'll do it again."

BI Publisher / XML Publisher and Barcoding

My client awaits my return next week with bated breath and part of my fun responsibilities will be to make sure I can get nice and working bar codes on their BI Publisher Reports feeding from EBS 11i. Fortunately I won't be alone in my effort, but from previous client experiences, people had to jump through hoops and hurt themselves to get this working. I just haven't been hurt yet :) In the coming weeks I will update this posting with my findings. As always, the most helpful blog I always refer to for all things BI Publisher is Tim Dexter's Blog. Is this guy trying to win an Oracle Ace or something? :)

OK, UPDATE! We got this working. It was not the more complicated 2-D bar coding that needs nice Java classes and so forth, but I will STILL show some nice screen shots and of course refer to good blogs where I dug up the info to do this. There are HUNDREDS of people out there that need help with this so I will show as many screen shots and steps as I can without compromising my client! Tune in very soon!

OK. Have an update with details. See the post titled 'BI Publisher Barcoding in Oracle Applications 11i'

Wednesday, September 26, 2007

The OWB Repository Upgrade Assistant!

Just what in the world is this little hidden Wizard tool doing? Let's find out more about the OWB Repository Upgrade Assistant, shall we?

First, what does it do, and why do we use it?

Next, how do we use it; exactly where do we find it?

Any issues we know of?

Oracle Warehouse Builder 10g R2 - OWB and Oracle RAC

Right now I am working hard on making sure we have all of our connections to sources, targets, the metadata repository, and any other loose ends tied up on a nice start to yet another Data Warehousing and BI project- using the Oracle suite of tools!

One thing I don't have much experience with that I wanted to share pitfalls about (and accept tips from readers as well) is using an Oracle RAC instance as our ultimate 'Target Schema' in our ETL using OWB. We have a multitude of data sources, of course, and we have our unified repository on the RAC instance. We also have our Target schema and tables on this same RAC instance. Nothing crazy just yet! Here is where the fun begins (note to self: it is always interesting to say its fun when it is actually a bit painful)....

First, the Oracle RAC instance is using ASM. A bit abour ASM- ASM is a new feature in Oracle Database 10g that provides the services of a filesystem, logical volume manager, and software RAID in a platform-independent manner. Oracle ASM can stripe and mirror your disks, allow disks to be added or removed while the database is under load, and automatically balance I/O to remove "hot spots." It also supports direct and asynchronous I/O and implements the Oracle Data Manager API (simplified I/O system call interface) introduced in Oracle9i.

Oracle ASM is not a general-purpose filesystem and can be used only for Oracle data files, redo logs, control files, and the RMAN Flash Recovery Area. Files in ASM can be created and named automatically by the database (by use of the Oracle Managed Files feature) or manually by the DBA. Because the files stored in ASM are not accessible to the operating system, the only way to perform backup and recovery operations on databases that use ASM files is through Recovery Manager (RMAN).

ASM is implemented as a separate Oracle instance that must be up if other databases are to be able to access it. Memory requirements for ASM are light: only 64MB for most systems. In Oracle RAC environments, an ASM instance must be running on each cluster node.

The diagram below shows a very basic topology, and yes this RAC is using ASM.

Now that we have a few of the basic ideas out in the open...the real issues come into play for people like myself that don't know exactly how RAC is going to work with OWB services that are trying to deploy tables, data, and other things to a RAC target. Furthermore, what is going to happen with the OWB Repository, and the properties files that are created when a repository is created via OWB? Well we're learning.

First I'll mention a few details about installing OWB in a RAC environment, and in particular- the repository setup on the RAC cluster.

To install OWB in a RAC environment:
Create the RAC environment as described in the Oracle Clusterware and Oracle Real Application Clusters Installation Guide specific to your platform. If you have not already done so, it won't hurt to review the most recent Oracle Warehouse Builder Release Notes available for your version of the install!

For each computer to host Warehouse Builder components, configure the tnsnames.ora file located in the OWB_ORACLE_HOME\network\admin directory. Also be sure to set the initialization parameter MAX_COMMIT_PROPAGATION_DELAY to a value of zero.
Definitely configure tnsnames.ora for each Oracle Database server that will be a Warehouse Builder data source OR target. If you fail to configure tnsnames.ora for any host or database server, you may encounter a repository connection error such as "The connection to the repository was lost, because of the following database error: ORA-12154:TNS:could not resolve the connect identifier specified". This is a common error!

Then review any last-minute install requirements before launching the Universal Installer and installing OWB.

To install an OWB Repository in a RAC environment:
When OWB is installed, you'll then want to install an OWB repository. To launch the Repository Assistant on Windows, from the Windows Start menu, select Programs and navigate to the Oracle product group you installed in the previous step. Select Warehouse Builder, Administration, and then Repository Assistant. (To launch the Repository Assistant on UNIX, locate OWB_ORACLE_HOME/owb/bin/unix and execute reposinst.sh.)

The Repository Assistant prompts you to define users and an owner for the repository.

Now for each RAC node, make sure to register each one! So for each node, launch the Repository Assistant and select the Advanced Set up option. Connect to the node using the net service name. Select the option for registering the RAC instance. (see below)

If the software is installed on separate disks, copy rtrepos.properties to each node in the cluster. If you did not install to a shared disk, then you must manually copy the file /owb/bin/admin/rtrepos.properties from the primary node to each node in the cluster. <-- This is yet another point I forgot so please make a note of it! See metalink note below!

Then install the Warehouse Builder software on the client machines.

When complete the installation process, you can launch all the Warehouse Builder components.
When launching OWB components such as the Design Center, Control Center Manager, and Repository Assistant, select the log on option SQL*Net connection and specify the net service name you assigned in the tnsnames.ora file. Since you can connect to Warehouse Builder repositories using a net service name, you can embed RAC properties into the connect string to utilize RAC functionalities such as connect time failover, load balancing on server and load balancing of connections. Finally, the Control Center Service requires that service names for the individual nodes in the cluster be available. If these are not present after the RAC installation, you must manually ensure the availability.

For RAC, it is recommended that you install the Warehouse Builder components on each node of the cluster. The Control Center Service is required on to each node of the RAC cluster.You can achieve this in a single installation of the Warehouse Builder software if you install on a shared disk such as an OCFS or NTS shared disk.

Metalink Note On OWB + RAC:
Applies to:
Oracle Warehouse Builder - Version: problem can occur on any platform.

The Warehouse Builder Runtime Service fails to startup on a Real Application Cluster (RAC).The following can be found in the Runtime Service log file or in sqlplus depending on the way the Service is started.service startup failure reason ORA-29532: Java call terminated by uncaught Javaexception: oracle.jdbc.driver.OracleSQLException: ORA-28239: no key providedORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 40ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 153ORA-06512: at "OWB_REP_OWNER.WB_RT_SERVICE_CONTROL", line 286ORA-06512: at "OWB_REP_OWNER.WB_RT_SERVICE_CONTROL", line 314ORA-06512: at line 1

Switch from one RAC node to another RAC node.

The file rtrepos.properties created into the owb_home/owb/bin/admin during the Unified Repository creation has not been deployed (copy) into the owb_home/aow/bin/admin on the other node(s).

On the RAC node used to perform the Unified Repository creation, locate the file rtrepos.properties in owb_home/owb/bin/admin and copy it on the other node(s) into the owb_home/owb/bin/admin directory.

Sunday, September 16, 2007

Oracle PL/SQL Tokenizer and String Formatting

Several times in my past projects I needed to do some complex actions on Strings or Varchars.  Doing this in Java is a no-brainer with the Tokenizer.  Sadly, I either didn't look hard enough or didn't see it in the PL/SQL base packages, but I created a nice little tokenizer that solved my problems.  I've used this little nifty code bit several times now.


--This is the tokenizer proc that is called several times that breaks down the strings by

--using the input parameters of 1. where to start, 2. the delimiting character or string

--and 3. the string to be broken down. It returns the extracted piece, and also the next

--position in the string so if the tokenizer needs to be called again, it will automatically

--start at the next position to extract the subsequent piece.

--Gregory Partenach v1 October 2003

PROCEDURE tokenizer ( iStart IN NUMBER,

                                        sPattern IN VARCHAR2,

                                        sBuffer IN VARCHAR2,

                                        sResult OUT VARCHAR2,

                                        iNextPos OUT NUMBER )


    nPos1 number;

    nPos2 number;


   nPos1 := 0;

   nPos2 := 0;

   nPos1 := iStart;

   nPos2 := Instr (sBuffer, sPattern, iStart);


   nPos2 = 0 then

      sResult := Rtrim(Ltrim(Substr(sBuffer, nPos1)));

      iNextPos := nPos2;


   sResult := Substr(sBuffer, nPos1, nPos2 - nPos1);

   iNextPos := nPos2 +1;


END tokenizer; -----------------------------------------

Setting up Oracle Portal 10g to use Oracle Reports PART 1

I know many of you have been through this. I also know quite a few that haven't. Since I have had to integrate Reports with Portal in many different ways, I figured I would show one way I implemented this. I've had top-level consultants and co-workers tell me certain configurations don't work. Fortunately, in my experiences I have gotten the integration between Reports and Portal to work every single time. This will not go into detail about the Reports Server configurations...let's stick to the Portal side of things.

This procedure will set up a report server in Oracle Portal that will be used to call your Oracle Reports.

Log into Oracle Portal.

Click the "Administer" tab.

Click "Oracle Reports Security Settings".

Click "Create Reports Server Access".

Enter a name and display name for the Reports Server and select a provider.

Enter the Reports Server name and the web gateway URL for PDF reports. The Reports Server name can be looked up in Oracle Enterprise Manager. The current production gateway may look like "https://my.site.com/reports/rwservlet". The two steps after this screen are unnecessary, so you may click the "Finish" button on this screen when done.

When you've created an Oracle Report and are ready to access it in Portal, you will need to upload it to the server. The current path on the server where the reports can be placed (for example) may look like: /u01/app/oracle/product/10.1.2/OAS/as_biportal/reports/samples/demo.

Setting up the Report for use in Portal- You should first have your Oracle report created and know the name of all the parameters that will be passed into the report. Click "Create Reports Definition File Access".

Enter the name and display name for the report and select the provider. Typical names can look like "RPT_ORDER_STATUS" and display names like "Report - Order Status", etc.

Select the report server to use and the file name of the report (the report server was created in the prior step above). The file name is the actual name of the Oracle Reports .rdf file for the report.

Select the destination type and format. For PDF reports, the type should be "Cache" and the format should be "PDF".

Enter the parameters for the report. The "Name" field should match exactly the parameter names you defined within the Oracle Report. The two steps after this screen aren't always necessary, so you may click the "Finish" button on this screen when done.

The report definition is now created and you will be returned to the below screen. Click the "Access" tab.

Select the "Publish as Portlet" checkbox and click the "Apply" button. The screen will refresh.
Then click the close button.

You will be returned to the below screen. Click the "Customize" link to expose the report parameters to Portal.

A popup window will be displayed listing the custom parameters you defined for the report, as well as several system parameters. For each custom parameter you defined, check the box labeled "Visible to user". This will expose the parameter to Portal screens that the report is placed on so that data can be passed from the screen and into the report.

In the "Additional User Parameters" field you can enter the following code:
&userid=[username]/[password]@[host string]
Note that we don't have the 'Visible to user' option checked here. This is because this parameter is being passed to the Report as a security and data filtering action. Depending on who is logged into Portal, the user's name and password will be passed to the Report and the appropriate data will be retrieved to display on the Report. We don't want this parameter to be visible to anyone, the username and password are passed transparently.

You can then click the "Save Parameters" button when done and then close the popup window manually. The report should now be ready for use in portal screens. You can also click the "Run Report" button after you've saved the parameters for the report. Depending on your report and the parameters that it requires, the report may not display correctly, but it should give you an idea if the report is at least being accessed and called correctly from Portal.

Next time we will continue by showing how to create a Portal page for your Oracle Report.

First post on this blog

Blogging is not new to me, but I am creating yet another one. This Blog will focus on my professional areas of interest. Namely, Oracle and other technical subject areas.

A bit about me without giving you an explicit resume- (if you want my resume, want to see where I worked, or more- please contact me through email partench@gmail.com or look over a briefing at http://www.linkedin.com/in/cali97 ). I've been doing technical work in some fashion since 1996. I've been to a few good schools, racked up a couple of higher degrees, and even considered going into healthcare/medicine until I realized it was almost as corrupt as politics.

I've been lucky enough to work on projects and software that made it to film (Pixar's "Finding Nemo"), lucky to work 1 on 1 with Shaun O'Brien on many projects, as he was my direct mentor at my previous employer and is also employed by my current employer now too! I can't shake that guy?! Now, saldy he was hired away to be a Principal Product Manager at ORACLE for JDeveloper and the ADF Framework. Congrats to him. Unfortunately, 90% of my work is buried and hidden deep in my client's vaults never to be seen by outside eyes.

My areas of specialization and interest are in Oracle Database Development, Data Warehousing, Business Intelligence, Informatics, Tuning, Motion Picture Visual Effects, Graphical Rendering Engines, ...I could go on!

On a lighter note, the real person side of me is enjoying city life in Chicago. I live just a block away from the Lincoln Park zoo. Its quite a change from San Francisco! I enjoy trying new restaurants, taking in art museums as well as creating a bit of my own art, and of course seeing the Chicago Bears play on Sundays. I'd like to get into rock climbing when time permits, and I'm already planning a trip or 2 to check out some vineyards in Michigan and maybe Napa Valley as well. Again....I could go on!