Feed aggregator

The Easiest way to get Chart of Accounts Segment Descriptions

Jo Davis - Wed, 2007-05-09 21:44
Such as account descriptions or cost centre descriptions as well as the value:


SELECT
gcc.code_combination_id
,gcc.segment1
,gcc.segment2
,gcc.segment3
,gcc.segment4
,gcc.segment5
,gcc.segment6
,gcc.segment7
,gcc.segment8
,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,1,gcc.segment1),1,40) segment1_desc
,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,2,gcc.segment2),1,40) segment2_desc
,DECODE(gcc.segment3,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,3,gcc.segment3),1,40)) segment3_desc
,DECODE(gcc.segment4,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,4,gcc.segment4),1,40)) segment4_desc
,DECODE(gcc.segment5,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,5,gcc.segment5),1,40)) segment5_desc
,DECODE(gcc.segment6,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,6,gcc.segment6),1,40)) segment6_desc
,DECODE(gcc.SEGMENT7,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,7,gcc.segment7),1,40)) segment7_desc
,DECODE(gcc.SEGMENT9,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,8,gcc.segment8),1,40)) segment8_desc
,gcc.chart_of_accounts_id chart_of_accounts_id
,gcc.account_type
FROM
gl_code_combinations gcc

Otherwise you have to do a hideous number of joins back to FND_FLEX_VALUES_TL and generally hard-code in value set ids :)

Have a fun day
Jo

The Falcon Has Returned

Peeyush Tugnawat - Wed, 2007-05-09 17:14

The Falcon Has Returned
This is a very interesting story about the endangered Falcon (Peregrine) that returned to Oracle for nesting.

Read more here:
Falcon Story at UC Santa Cruz

And the best part is the live webcam of Saddie and her four newborn chicks at Oracle HQ
Check it out here!

Insider for Oracle - funky GUI bits

Hampus Linden - Wed, 2007-05-09 15:47
We've been doing a lot of application benchmarking and tuning at work in the last couple of months as we are prepare for a new major release. I've been involved in the Oracle side of things and have spent a lot of time tracking down expensive (or plain weird) SQL-statements and trying to optimize the Oracle databases we use.

After spending about 6 - 7 hours a day running my normal sqlplus scripts over and over trying different things, monitoring benchmark runs I got a bit bored of text output and had a quick google for some sort of basic v$whats_up GUI tool and found this little cool little app called Insider for Oracle, it a great app for doing spot-on monitoring, sort of like Spot light from Quest, but without the hassle. Insider tries to be a more proactive tool with round the clock monitoring, but for that, it's no good.

The thing I find it useful for is when we start a benchmark I can trail what's going on with the system. See new SQL statements, wait events, SGA usage, PGA usage, hot segments etc. Nothing I can't do from sqlplus but it makes my tasks a bit easier.
Price wise it's quite a bargain for what I use it for, the license is for each database on the application "workspace" (i.e. how many databases you want to look at at once). And for what I use it for just looking at one DB at a time, that works out pretty cheap, 599USD or so. Cheap as chips.
I've found a couple of bugs (and submitted bug reports accordingly) and I've even submitted a couple of enhancement suggestions.
Check out their company blog.

Dave Chappell choins Oracle as Chief SOA Technologist

Clemens Utschig - Wed, 2007-05-09 11:28
Wow - what a blast, the so called "Pope of ESB" (according to one of our customers) joined Oracle last week - see here.

Being back from 4 weeks in Europe to give training, help customers, as well as speak at several conferences - I met Dave yesterday first time as oracle employee at java one - rocks!

Also, some weeks ago I have been interviewed by the theenterpriseedgeitarchitect.com on SOA, and what value it can bring into organization, when not viewed as "yet another IT pet project".

You can read the whole interview here

Date columns

Herod T - Tue, 2007-05-08 13:02

I have a good friend from a previous job who is an Oracle DBA, he is kind of shy and doesn't like a big presence on the net. I have blatantly stolen work from him in the past, including some excerpts from this particular document (always with his knowledge). But the poor bugger had the lack of foresight to send me this complete PDF document. So, I quickly decided I had better post it just to show him blogging isn't all evil and invitation to flaming.

That said, Jeremy's quick write up on oracle date columns that he did for his internal support staff. Absolutely nothing earth shattering or ground breaking, but I can't see any errors with it and it is good to give to people who can't speak "DBA". Yes, I have used this in the past elsewhere as well. But this is the complete PDF.


JDeveloper 11g Tech Preview is out!

Janusz Marchewa - Tue, 2007-05-08 02:41
Check out Shay Shmeltzer's blog for details: http://blogs.oracle.com/shay/2007/05/07#a365
Categories: Development

OpenSolaris SXDE upgrade time

Hampus Linden - Mon, 2007-05-07 16:04
It's been a while since I last played with Solaris Express Community Edition, so long that it actually changed names. There are now two versions, one for "random people" and one version for developers. SXCE is not released as often as SXDE but is more tested and stable, SXDE also comes with a lot more goodies, like Sun Studio 11 and Netbeans.
Anyway, the install of SXDE b63 under VMWare workstation 6 RC was as easy as ever.
However, at first boot I almost thought the VM was frozen for a minute or so, after grub it just sits there with a cursor in the corner of the screen for about 2 minutes before kicking off the kernel. All good after that though.
One thing I wanted to test was the new simplified resource capping in Solaris Zones. It was very straightforward and easy to use.
A couple of extra commands in zonecfg and the zone is capped to one core and 512Megs of memory. No more messing around with projects, except if you want really fine grained capping that is.
Cool!

Took a screenshoot:
b63

More APEX

Herod T - Mon, 2007-05-07 09:24

APEX is great, APEX is a life saver... OK, job saver?


As I mentioned before I like APEX, but not having the time to develop in it and learn it well, I get a little lost in the product. The APEX forums are a good place to pick up information, like most forums if you use them properly. I read Dimitri Gielis blog on a regular basis, imagine my surprise when he posts about answering one of my questions.

Thanks again Dimitri!



ODP.NET/ODAC for Vista now available

Christian Shay - Sun, 2007-05-06 23:04
Coming right on the heels of Friday's release of Oracle Database for Windows Vista, we now have released ODAC 10.2.0.2.21 which is certified for Vista. You can download it here. This release is key because the Oracle Database on Vista downloads announced on Friday only include ODP.NET for .NET 1.x. If you need ODP.NET for .NET 2.x on Windows Vista, or any other of the ODAC products, you will need to download this. It includes all of the ODAC products, including ODP.NET and Oracle Developer Tools for Visual Studio .NET packaged up with a new version of the Oracle installer that is Vista friendly. Only the installer has been upgraded, not the individual ODAC products, so if you are using ODAC 10.2.0.2.20 on another Windows platform, you don't need to upgrade.


The download includes:
Oracle Developer Tools for Visual Studio .NET 2003 10.2.0.2.20
Oracle Developer Tools for Visual Studio 2005 10.2.0.2.20
Oracle Data Provider for .NET 1.x 10.2.0.2.20
Oracle Data Provider for .NET 2.0 10.2.0.2.20
Oracle Database Extensions for .NET 2.0 10.2.0.2.20
Oracle Database Extensions for .NET 1.x 10.2.0.2.20
Oracle Provider for OLE DB 10.2.0.2.20
Oracle Objects for OLE 10.2.0.2.20
Oracle Services for Microsoft Transaction Server 10.2.0.1.0

Business Agility @ SOA

Peeyush Tugnawat - Sun, 2007-05-06 17:41

Bookmark and Share


Business Agility @ Service Oriented ArchitectureWe live in a constantly changing world. Technology and businesses are changing, emerging, and improving at a faster pace than ever
before. Business want the power to deliver products and services better and more
efficiently, to be able to excel, capture new opportunities, and
sustain competition. Technology innovation and improvements are striving to deliver on these requirements. From a business perspective, dynamics of such fast-changing world in terms of
technology can be daunting in terms of time to market cost, maintenance, training, and support. Replacing or re-writing entire systems
every time for new and improved functionalities is definitely a major concern.


Is IT just an unavoidable cost?Arguments about IT being a cost are not rare in business
world. There is no doubt in my mind that innovations in IT systems have
propelled business abilities and enriched business processes in past years.
Definitely, there is high business value gained from IT systems. At the same
time organizations also have to deal with the "cost" components that
come with IT systems such as maintenance, support, enhancements, training etc.


The argument from my perspective, should not be about the
classification of cost components, but how all the components and their
interactions can be simplified to provide more value to the business.


I think simplicity can help!Simplicity is highly desired in increasingly complex world
on IT. Direct incentives of infusing simplicity in IT architecture are
tremendous and do not require elaboration.


Direct derivative of simplicity in IT architecture approach
is Business Agility.


Simply put, "Simple is Powerful"

SOA translates to business agility. SOA is simple but very powerful architecture approach
for complicated systems and problems of IT world.

As I mentioned in my earlier post based on my experience,
SOA as it relates to software paradigm is an agile architecture approach that
is based on service-oriented principles of composition, abstraction, loose
coupling, discoverability, and amalgamation. SOA inherently empowers
scalability, evolution of services, interoperability, reusability, and
modularity.

Following diagram identifies high level concepts infused into SOA that help enhance business agility for an enterprise.



Inherent InteroperabilitySOA empowers Inherent Interoperability between systems, business processes, and people (organizations).

Re-usability Reusability factor in case of SOA is multi-fold because of standard based interfaces. Reusability is not new in software; what�s new is reusability across diverse systems, platform, and technologies. SOA components can be reused not only by the inherent system or technology but also by any system or process.

Modularity and CompositionSOA encourages Modularity that enhances the concept of Composition. Composite applications can be build based on changing business requirements and rolled out faster with less cost.

SOA Delivers
SOA enables faster development turn-around time,
faster realization of new inter and intra enterprise business processes, and
cost effective maintenance and upgrades.

All this translates to enhanced Business Agility. And
I would like to call it.....

Business Agility @ SOA


Bookmark and Share

Checking if passwords are equal in ADF

Janusz Marchewa - Sat, 2007-05-05 14:25
After spending some time trying to get Tomahawk's validateEqual working in ADF Faces (works perfectly in JSF, though) I've decided to implement a custom validation method. The second af:inputText wasn't connected to any ADF binding and this turned out to be the main troublemaker of the day. With the help of the Dev Guide validation started working quickly, but I've encountered a bunch of annoying little problems...

1. The error message would not display as expected. I expected something like "Repeat password - Passwords are not equal", but all I got was "com.sun.faces.el.ValueBindingImpl@9af3ee - Passwords are not equal". Fortunately, a quick forum search saved me this time (namely this thread and this one).

2. Both af:inputText components did not behave as expected after validation failure. JSF h:inputSecret has a redisplay attribute, but I didn't notice anything similar for af:inputText. When secret was set to true, something weird happened - after each validation failure the value of both components was initially set to ******, but it disappeared after pressing a key inside the component. After digging for a while I still don't know why the initial value is set to six stars, but I found the function that removes them:

function _clearPassword(a0,a1)
{
if(window.event!=(void 0))
a1=window.event;
if(a0.value!="******")
return true;
if((a1.keyCode==8)||
((a1.keyCode>=46)&&(a1.keyCode<112)))
a0.value="";
return true;
}


Now that's interesting... Anyway, I tried to get rid of those stars for a long time. The first component that was bound to the password attribute was easier, because binding the component to the backing bean that performs validation and setting the value to null did the trick.

The second component kept setting its value to ****** despite setting it to null in the backing bean. After many unsuccessful attempts I removed those stars with a small JavaScript workaround inside the afh:body tag:

onload="_getElementById(document, 'repeatedUserPassword').value = '';"

Categories: Development

Fire Fighting

Herod T - Fri, 2007-05-04 22:47

With the conversion of '07, staffing issues, hardware failures, fires, and the day to day grind, my job satisfaction over the last year has well, been at an all time low. I can never seem to get anything done properly, everything is patched together and done in haste to move onto the next fire. One of the fine ladies that works with me actually bought everybody in the group a little fireman's hat and a tiny little working fire hydrant and hose squirt gun, because it seems to be how we are viewed by the company as of late, simply running around putting out fires. A necessary, expensive evil entity who never has time to talk, and is only around when there is an emergency, or a fresh pot of coffee.

Well, today was a little different. 4 of us IT people had taken a group trek to the lunch room for a refill, we bumped into a few of our sales staff, once we all realized we were on hallowed neutral ground and no marking of territory was needed we started to chit chat, the weather around here has been record breaking and that is what we were talking about, all relaxing nicely. Then one of the sales guys pipes up, completely out of the blue and says "So, any of you ever look at our order processing and shipping system?". Well the system is a bolt on system to our oracle e-business suite and is relatively new. Development was outsourced and then the application support was laid in the lap of our haggard sales support team. None us IT people at that time were part of the sales support team, so the answer was a resounding "No". I monitor the database, but it is quiet and never complains about anything.

The sales guy goes on to talk about how over the last 2 months, it takes longer and longer to process an order, to the point of the sales guys queue up their orders and process let the system process them over night, because if they process the order manually, it locks up the application for as long as the order takes to process. The sales guy, lets call him Henry, goes on to say when the system was first in place, an order would process in about 1-2 seconds, now an order he processed this morning while a customer was on the other end of the phone took 7 minutes. I know his time is valid because the screen actually tells the sales person how long the processing took.

Henry continues on saying all of the sales folks have raised the issue with their manager, who was supposed to pass this along to the IT group, but it appears never has. I decide to return to my cube and take a look. I do the normal thing of right off the bat of starting a snapshot every 10 minutes and dig up some of the baseline snapshots so I have something to compare too. I call Henry and ask him to process an order so I can trace his session. I find his session and start a trace, he processes an order, and his session goes inactive after only a couple of seconds. I have Henry on the other end of the phone, and he says the process is still running. I can plainly see, that according to the DB, the process is over.

The app is a Delphi fat client, so I can rule out any connection pooling or anything like that. I go up to Henry's desk, the process is still going. I wait until it finishes, it takes almost 7 minutes, and he reviews the PDF that pop's up of the order and then he clicks the forward button and lets the work flow take over and push the order off to the next people in line. Those people happen to be in the cube right next to Henry, so I check with them. They say yes, they see they have an alert that the order has come in, but the lady is not wanting to open the order because at this time of day, it can take almost 10 minutes for the form to open., she says, first thing in the morning she can process a hundred forms in just a few minutes and I should come back tomorrow. I push the issue and she opens the form, and yes it takes - get this, 7 minutes.

Speculating to myself on what the problem might be, I go down to the sales support group and talk to their team lead, they know about the problem and it has been a ticket in the help desk for awhile but they are short staffed by 3 and do not have time to deal with it any time soon. I ask if I can, and of course am promptly handed the keys to the system and told to "have fun, don't change anything". I take a look and I find the PL/SQL that generates the PDF, it is a simply external call to a java program via the client that reads the DB and generates the PDF. I can run it in test no problem, generates a PDF in a couple of seconds. I ask Henry to try test, he does, takes just a few seconds.

Well, this is turning into a really long story, but I needed to lay the groundwork, sorry about that. Continuing the story, I decide to check out the DB server. lots of free space, minimal CPU usage, our reporting software shows that the machine only averages about 70% CPU usage over the course of a few days. Well that isn't it. I then cd into the temporary holding location for the PDF files and do an ls. Then I wait, and wait, and wait, and wait. I open another session and check the server. No CPU usage, no nothing. Being the server is a Linux box I run top and take a look, nothing out of the ordinary, I run iostat, nothing out of the ordinary. Very confused I check the other session and it is completed, I run "time ls" and wait. I am sure you can guess it, time comes back to just under 7 minutes. I check the scroll back on my terminal, it goes back to the max of the buffer, 2000 lines. I do a ls | wc -l. I wait the 7 minutes and get the outstanding number of 532,932. Wow, I didn't think that was possible, that is a lot of files. Then I realize with the file name sizes, ls is 4 columns wide. That is 2,131,728 files in that one directory assuming they are all the same file name length. I go back up one directory, do a ls -lrt and the directory is only 2 bytes in size. That doesn't seem right, with that many files in there, the directory header should be massive.

I call the SA group and have them check. They come back, surprisingly about 10 minutes later saying "Yeah, the directory is f*cked" it is a mount point on the NAS and we can't even see it from console. The NAS is mounted to the DB server and as a share that the sales people can access. A few phone calls later, and we confirm that the PDF's are temporary and not needed, we down the DB with the user's consent, the SA's unmount the drive and remount a new empty one immediately. Once we bring it back, we call Henry and he tries it and an order processes in under 2 seconds. We look at the directory and there is a single PDF in there. We know we haven't written 2+ million orders in 2 months (we wish!!), so the SA's work on getting some files out of the directory so we can take a look. While waiting on the SA's I monitor the directory, there are now 3 files, but Henry is the only user in the system still, we haven't released it to the public yet. I call Henry, he processed 1 other order, not 2 more - he swears by it. I asked Henry to process another, he does. now 7 files, and another one, now 15 files. I tell Henry to stop and call the support team and tell them about the problem.

The sales support group immediately drop everything and dig into the issue. Takes the team lead about 10 minutes to find the problem. The Delphi, just in case, backs up all files submitted by that user before it processes the current order. It assigns a sequence number and copies the files, effectively doubling the number of orders issued by the sales person before the new order is generated. The user's PC does all the work copying the files. The code to remove the backup files is instead a simple comment saying something to the effect of "delete was taking too long, investigate using a cron task to delete, the directory can fill up fast", made by one of the sales team support people who left awhile back. A quick change, and bingo, the code will delete order's older than 24 hours and not make any backups. Since the app is ran from a network drive, the sales group compiles a new version and pushes it out. After letting a few more users in, we verify the problem is fixed and go home for the day.

This made me feel good. Nothing to do with problems in an oracle database, no meetings, no hours of planning and discussion. Just suit up, get to work, and put the fire out...


sigh.




Oracle Database on Windows Vista now available!

Christian Shay - Fri, 2007-05-04 14:27
The 32-bit Oracle Database 10g Release 2 for Windows Vista (v. 10.2.0.3) is now available for download! It supports Vista Business, Ultimate and Enterprise Edition.

Developers who just need the 32-bit Oracle Client for Vista can download it at that same link. This client release is much easier to install than the previous "start at 10.2.0.1 and patch up to 10.2.0.3" method that I blogged about earlier, and it doesn't require that you have access to a Metalink account (to get the patches) if you just want to evaluate the software.

And as I mentioned in that prior blog entry, Oracle Database Express Edition has been certified on Vista for some time now.

Some information about Oracle on 64-bit Windows Vista can be found at the Oracle Database on Windows Vista: Statement of Direction.

Happy installing! :)

ANSI Joins

Robert Vollman - Fri, 2007-05-04 12:29
Like most of us, I still join tables in my SQL queries the old-school way. Simply put:SELECT whateverFROM table1 t1, table2 t2WHERE t1.id = t2.idAND t1.value > 10;But increasingly often I run into people who use ANSI joins instead. They were either introduced to SQL with Oracle 9 (or Sybase 12, etc), and were taught to use the SQL standard way, or else they made the conversion at some point in Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com7

One liner

Oracle WTF - Wed, 2007-05-02 19:38

I was untangling a query when I came across eight variations of this code where only the literals 'a' to 'e' changed. Two of these were nested within an additional NVL so that the second would execute if the first returned null.
nvl(decode(2, 1, 'a', 2, 'b', 3, 'c', 4, 'd', 5, 'e', ' '), 'na')

Migration from Access

Herod T - Wed, 2007-05-02 12:45

A few months ago, we hired a consultant to work with a group of users and start to migrate from the numerous access database applications and excel spreadsheet "applications" to APEX. At the time it was APEX 2.0. We created them a database on a little used server and gave the consultant about 100 gig of storage and a list of items the consultant must do and must not do written into the contract.

We have heard very little about the project, the manager in charge pretty much left them on their own. Today we had a meeting which turned out to be the "wrap-up" meeting for the project. The consultant and the users, in under 4 months converted 16 MS Access databases and 44 excel spreadsheets to APEX applications. LDAP authentication and centrally managed user list as all the apps are in a single APEX workspace. The project is magnificent! The users are happy, all the rules have been adhered to and the consultant came in under the time limit getting the bonus money that was part of the contract. Everybody is happy all around. I looked into the DB, proper referential integrity, very few bad SQL, the schema is organized well with non shared tables prefixed with a TLA that they came up for that particular MS access or excel application. Applications talk to each other when necessary and the users are reporting much better productivity and no missing transactions or data.

Every application is snappy and efficient. The consultant and the project user lead also signed off on retiring the 155 MS Access licenses, which will be a nice thing to stop paying for when we renew.

All and all, a good day!

APEX is amazing... I still struggle with some basics on it... but I am a DBA, not a developer. We already have another meeting with the consultant to put them onto another APEX project we have waiting in the wings.



Quick SQL Server Training for Oracle DBAs

Andrew Fraser - Wed, 2007-05-02 10:12

This page has been moved to http://andrewfraserdba.com/?p=41


Categories: DBA Blogs

Webcast Announcement: Oracle's In-Database Statistics

Marcos Campos - Wed, 2007-05-02 00:11
Today (Wednesday), May 2, 2007 at 12:00 PM EST, the Oracle Business Intelligence, Warehouse and Analytics (BIWA) Special Interest Group (SIG) will host another interesting free webcast:Oracle's In-Database StatisticsSpeaker: Charlie BergerSession AbstractOracle Database 10g embeds a range of SQL-based basic statistical functions including: summary statistics, hypothesis testing, correlation Marcoshttp://www.blogger.com/profile/14756167848125664628noreply@blogger.com2
Categories: BI & Warehousing

Apache Trinidad Graduates!

Omar Tazi - Mon, 2007-04-30 19:02
About a week ago and after 12 months hanging out in the Apache incubator, project Apache Trinidad received all the votes necessary from the Incubator PMC to graduate. Trinidad got 12 binding +1 votes by the Apache Incubator PMC, and two more non-binding by the Incubator community. It's been an exciting time in which we've seen more and more people from Oracle and of course outside Oracle join the community as users and even as committers. The traffic on the Trinidad mailing lists is really encouraging and the community is growing rapidly. In addition to Oracle which relies heavily on Trinidad for its own development, several companies are using including consulting shops who find the Trinidad components very useful and mature enough to build highly interactive web applications for their clients.

Trinidad is now going to be an important part of Apache MyFaces. You know where to go, we're working on the logistics to migrate the project from the incubation infrastructure to the MyFaces side of the house. Try it and let us know what you think.

Pages

Subscribe to Oracle FAQ aggregator