Feed aggregator

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.

Congrats to the Eclipse BPEL Designer Team!

Omar Tazi - Mon, 2007-04-30 18:40


I simply want to congratulate Michal and the BPEL Designer crew for winning the 4th prize at the popular JAX conference in Germany. Check out the news here if you can read German. Keep up the great work!

I also want to point out for those of you who don't know yet, there are other Oracle-led Eclipse plug-ins which we are really proud of:
- JSF Tools Project
- Project Dali JPA Tools

Finally we have announced last month project EclipseLink which will provide developers with world-class O/R mapping capabilities (via the TopLink product line donation).

Go ahead and try those Eclipse projects out and let me know what you think. We also welcome your participation ;)

How to do an "insert ignore" in Oracle

Hampus Linden - Mon, 2007-04-30 14:28
Ok, I should start off with a disclaimer. This is not a good idea to do.
Using the "insert ignore" statement is a way to let MySQL insert data from a dataset which may contain duplicate constraints to existing data, and simply skip the duplicate row.
Sounds like a great way to screw up data doesn't it? Should not be used unless you really know what is going on.

Ok, in MySQL we can do this.
mysql> insert ignore into a select * from b;
Query OK, 1 row affected (0.00 sec)
Records: 2 Duplicates: 1 Warnings: 0
But can this be done in Oracle without to much fuss? After a discussion on IRC a guy asked why can't we simply use the merge statement?
Well we can and it is probably a quite good suggestion if you *really* want to ditch those dupe rows.
-- So what do we got?
SQL> select * from a;

A B
---------- ----------
1 2
2 3

SQL> select * from b;

A B
---------- ----------
1 3
3 4

SQL> merge into a using b on (a.a=b.a)
when not matched then insert values (b.a,b.b);

1 row merged.

SQL> select * from a;

A B
---------- ----------
1 2
2 3
3 4

SQL>

-- Let's rollback that and have a look at the exeuction plan.

SQL> rollback;

Rollback complete.

SQL> set autotrace on
SQL> merge into a using b on (a.a=b.a)
when not matched then insert values (b.a,b.b);

1 row merged.


Execution Plan
----------------------------------------------------------
Plan hash value: 1973318225

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 2 | 64 | 7 (15)| 00:00:01 |
| 1 | MERGE | A | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 2 | 64 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| B | 2 | 52 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| A | 2 | 12 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

3 - access("A"."A"(+)="B"."A")

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
15 consistent gets
0 physical reads
0 redo size
819 bytes sent via SQL*Net to client
778 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
Looks ok.

Oracle 10g Installation on Vista

Dong Jiang - Sun, 2007-04-29 19:49

I wish I had found out this link earlier. Just follow the instructions and it’s hassle free to install 10g on vista.
I couldn’t agree more with the comments about M$ and Vista on the page too. The vista built-in zip/unzip capability would take over an hour to unzip the 10g base installation file. That’s RIDICULOUS!!!

Powered by ScribeFire.


Quick Thoughts on Flex and Open Source

Omar Tazi - Thu, 2007-04-26 18:38

Adobe plans to open source Flex, its development framework for building Flash-based web applications this should take effect when they go beta in June with the next Flex version code named Moxie (will be called Flex 3). Adobe is planning to use the Mozilla Public License or MPL. They are still planning to sell their Eclipse-based Flex Builder.

I read on CNet that "the move is also meant to appeal to open-source developers who shun closed-source and proprietary products. Adobe already offers the Flex software development kit for free and provides the source code." Also read on the same article that Jeff Whatcott, vice president for product marketing at Adobe's enterprise and developer business unit said "For some people, (open source) is a philosophical requirement, a sign of integrity and trust in a vendor, this will close that gap and address any lingering doubts they have about our openness and commitment to community."

I am sorry Jeff but this says ABSOLUTELY nothing about Adobe's openness. Flex is as proprietary and as far from open as it gets. Adobe still locks me in when I use Flex whether the source is open or not it doesn't really matter. Only one vendor defined Flex and only one vendor provides runtime for it. In my opinion, open source is not enough. We at Oracle continue to step up our open source contributions but we believe in a much more important source of openness and that is open standards to which we religiously adhere. That's what really gives you the freedom you are looking for as a user. The freedom to switch vendors down the road should you need to. So when we open sourced our reusable user interface components (Apache Trinidad), in addition to opening the source to which the community responded very positively, it was a 100% standards-based contribution (in this case the standard in JavaServer Faces).

I am not trying to take anything away from the success Flex/Flash enjoys it definitely helps build much more interactive web pages and seems to be very popular. I personally block Flash animations on my browser (Firefox) but advertisers like to use flash to make your pages look like fireworks which really annoys me. The way Oracle believes pages should be built is using JavaServer Faces (JSF) components (typically embedded in JSP pages) and if you need richer UI you do that by injecting some Ajax into it. We believe Ajax and JSF go very well together and hold such great promise that we have decided to donate our Rich Client Framework (RCF) to the OSS community. Stay tuned for a bunch of OSS-related announcements in a couple of weeks at JavaOne. Using JDeveloper (100% free), Apache Trinidad or ADF, one can build highly interactive standards-based user interfaces without knowing anything about JSF, JavaScript, XML DOM, CSS, DHTML, etc. Developers are shielded from the complexity of Ajax development… I would like to disclaim that we do support Flash as one of the rendering options for JSF components like charts. You build your JSF-based page and later you could potentially make the decision to render the chart in Flash. Because the JSF component definition is separate from the rendering you can still do that in an elegant way and we support that in ADF Faces as part of the Rich Client Framework.

To make the long story short my point in this entry is the following: Flash is not a bad thing, some people seem to like it and use it quite heavily. But open sourcing it or parts of it (especially the tooling) doesn’t make it open enough for me and the kind of people I talk to in the developer community. They understand very well that openness comes from standards not necessarily from opening the kimono and showing the code even under a friendly license like MPL.

Webcast Announcement: A Simple Fraud Detection Application using Oracle Data Mining, SQL Developer and Oracle BI EE

Marcos Campos - Tue, 2007-04-24 12:18
Tomorrow, April 25, 2007On April 25, 2007 at 11:45 AM EDT, the Oracle Business Intelligence, Warehouse and Analytics (BIWA) Special Interest Group (SIG) will host the following free webcast:A Simple Fraud Detection Application using ODM, BIEE, and SQL DeveloperSpeaker: Bob HaberstrohSession AbstractClassification is an often-used methodology in data mining that creates a predictive model Marcoshttp://www.blogger.com/profile/14756167848125664628noreply@blogger.com3
Categories: BI & Warehousing

The big leap

Herod T - Mon, 2007-04-23 10:03

In the last 8 months we have cycled through 4 MS SQL Server DBA's at one of our partner/remote sites, 11 servers, 11 databases. We are responsible for the IT services at that site and hiring SQL Server DBA's has become a 4 letter word. The original long term fellow had health problems and had to go on permanent medical at 40, I wish him luck. The first fellow we hired was a complete and utter useless twat who we fired after 2 months. The next fellow we hired was an OK fellow, a little too Microsoft branded, he had pretty much every MS certification known to the universe and made sure you knew it. He only lasted about 3 weeks when it was discovered he lied on his application and had a criminal background. We don't care if you do, just don't lie about it because the position requires being bonded. We also changed our company we use to check things like that because they were a little slow. This last lady, well, lets just say, she fell somewhere in between utterly useless and waste of skin and we let her go 2 weeks ago when 2 of the servers went down hard and it was discovered she hadn't been doing backup's since not long after she started. Her supervisor at the site was give a good dragging over the coals as well.

I was asked if I would like to learn to be a SQL Server DBA right around the timeline of the first replacement. I said "No thank you". When we let Mr. MS go, I was asked again. I said "Do not have time". When the 3rd guy was let go, I was asked again and I said "Starting to look that way isn't it". And now, that the last (and only) lady is gone, I was begged to learn SQL Server, so I said "OK, but only in a backup capacity", of course, if I believe that, I am sure the tooth fairy and Santa are going to come visiting bearing gifts of great winged pigs. I know enough about SQL server to get around, start, stop, run some scripts. Nothing to do with performance or optimization or anything like that.

So, this past weekend, I started to read the documentation for SQL Server, and immediately began to miss Oracle documentation. MS doc's suck. Nothing else to say, than they suck. I read what passes as the concepts guide, and started to wonder why does MS charge for SQL Server? it should be given away for free because it isn't worth any money to anybody at all.

I have put myself completely at the mercy of a company we use for training purposes, and they are coming up with a complete MS certified training course for SQL server with all of the prerequisites needed over the next year. I shudder to think of it. The admin at the site is also making me a VM of the test server so I can play with it on my laptop.




Way Cool: Linear Algebra in the Oracle Database 1

Marcos Campos - Fri, 2007-04-20 15:32
New to the Oracle Database 10g Release 2 is a hidden gem, the UTL_NLA package. This not very well known package (you don't get many hits for it in Google) brings linear algebra functionality to the Oracle Database. It makes the Oracle Database an even better platform for scientific and advanced analytics programming. Now it is possible to write performant matrix code in the database easily and Marcoshttp://www.blogger.com/profile/14756167848125664628noreply@blogger.com22
Categories: BI & Warehousing

Multi value selectors

Jornica - Wed, 2007-04-18 00:36

APEX had three multi value selectors: check box, multi select and list manager. APEX 3.0 introduces a new multi value selector: the shuttle. An shuttle consists of two text areas with buttons between the the area's to move items back and forth. At the right side, the selection, there are buttons to change the order of the items in the right text area. All selectors are based on a list of values. Setting or getting values of these selectors can be done with colon separated strings i.e. 'Violet:Indigo:Blue'.



The shuttle and list manager also preserve the select order as you can see in the picture. For each selector the same values in the same order are selected: first Violet and second Indigo. As you can see in the text boxes the shuttle and list manager preserve selection order in contrary with check box and multi select. The picture shown here is a modified version of on-line demo Item Types.

Conversion of 07 is done!

Herod T - Mon, 2007-04-16 10:54

Required a marathon 36 hours at work for 3 of us, but it is complete. The users have been using the new production system for almost 24 hours now. New system is magnitudes faster than the old one, the users are ecstatic.

Only issue we had was about 2 hours into the data transfer some chuckle head at the remote site accidental stepped on the power bar for 2 of the 4 new internet routers, but the error checking took care of it. Once the modem's were powered back on, the process realized it, and started the transfers again.


Transferring System Statistics to a TEST environment

Vidya Bala - Fri, 2007-04-13 15:15
Can we transfer system statistics from a Production environment to a Test environment? Most documentation talk about simulating a workload in your test environment and gather system statistics in test before implementing system statistics in Production. We are on Oracle 9i (9.2.0.6) in Production - we have not gathered system statsitics in Production so far. The decision has been made to start gathering System Statistics in Production. Testing this in a test environment prior to implementation seems like a challenge - given that our load test simulation may be very very different from Production.

Is it not possible to Gather System Stats in Production into a Staging Table , export the staging table to the Test environment and then import the Stats into the data dictionary in Test? Even if the above is possible , some key questions still remain while gathering System Stats into a Staging Table in Production -

a)is performance of Production impacted while System Stats is being gathered to a staging table?
b)SQL in the SGA invalidated ? - all documents that I have read so far tell me the answer is "No"
c)Since stats are gathered into a Staging Table , I am assuming no execution plans should change until stats are imported into the data dictionary in Production .

It will be great to know if any one has run into the same issue.
Categories: Development

should we change sql to meet ANSI 99 standards

Vidya Bala - Wed, 2007-04-11 18:02
I should say in all my years as both a Production and Development DBA I have very rarely seen my developers follow the most latest ANSI standard guide during sql development. Considering that most of us are on crunch time always with Application Development. Anyways I had the question posted to me " Shouldnt we change our sql to meet ANSI 99 compliance".

my 2 cents would be "No" - I really see the need to change the SQL if we were migrating say from Oracle to SQL Server for portability reasons. For the most bit Oracle version specific SQL development guides should be ANSI compliant and that should be enough - but then its just the way I have seen things work. Anyone run into the same issue - would be good to know.
Categories: Development

Roll-out

Robert Baillie - Wed, 2007-04-11 02:20
I'm pretty sure that most people that read this blog will also read The Daily WTF. But just in case you don't, there's a nice entry on 'soft-coding'. Overall the article makes sound sense, but there's a line right at the end that resonates with me, especially since I read it the day after someone told me that they needed a developer for a whole day (9 hours) to roll out their system... With the myriad of tools available today, there is no reason that your deployment process need be any more complicated than a simple, automated script that retrieves the code from source control, compiles it, copies/installs the executables, and then runs the relevant database scripts. It makes me feel like I'm not alone.

Roll-out

Rob Baillie - Wed, 2007-04-11 02:12
I'm pretty sure that most people that read this blog will also read The Daily WTF.

But just in case you don't, there's a nice entry on 'soft-coding'.

Overall the article makes sound sense, but there's a line right at the end that resonates with me, especially since I read it the day after someone told me that they needed a developer for a whole day (9 hours) to roll out their system...

With the myriad of tools available today, there is no reason that your deployment process need be any more complicated than a simple, automated script that retrieves the code from source control, compiles it, copies/installs the executables, and then runs the relevant database scripts.


It makes me feel like I'm not alone.

Oracle Develop hits the road

Clemens Utschig - Tue, 2007-04-10 23:33

Oracle Develop: The Premier Conference for Developers

This spring, Oracle Develop—the premier conference for developers—is coming to a city near you. Don't miss it. You'll experience two days of expert-led, in-depth technical sessions, hands-on labs, advanced how-tos, and detailed tutorials. Work alongside the world's leading experts to expand your knowledge of popular technologies like Enterprise Java, SOA, .NET, Databases and PL/SQL, as well as Ajax, PHP, Spring, and more. This is your opportunity to network with local technologist while learning from the world's best.

More information can be found here

Oracle Develop is coming to Europe and Asia! Sign up now!

Christian Shay - Mon, 2007-04-09 13:53
Following up on the tremendous success of our San Francisco Oracle Develop conference, we are bringing Oracle Develop on the road to Europe and Asia in May and June. We will have in-depth technical sessions as well as hands on labs where you can get your hands dirty with the latest and greatest Oracle products.

Click here to go to the main Oracle Develop web page to read more and to sign up.

Here's are some of the planned .NET sessions:
Getting Started with Oracle and .NET
New Features in Oracle Database 11g for .NET Developers
PL/SQL Programming for .NET Developers: Tips, Tricks, and Tools
Best Practices for .NET Developers
Hands-on Lab: Building .NET Applications with Oracle


There will also be many sessions on Enterprise Java, SOA, Databases and PL/SQL, as well as Ajax, PHP, Spring, and more.

Dates and locations:
May 14-15 Seoul, Lotte Jamsil
May 17-18 Bangalore, The Grand Ashok
May 22-23 Beijing, China World Hotel
June 18-19 Munich, Arabella Sheraton
June 21-22 Prague, Prague Conference Center
June 26-27 London, ExCeL


Here's a couple photos I took at the San Francisco Oracle Develop .NET Hands on Lab, showing some pretty intense learning going on!





See you there!

Pages

Subscribe to Oracle FAQ aggregator