Feed aggregator

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!

Safari on Solaris!!

Siva Doe - Sun, 2007-04-08 23:49

Well, got your attention didn't I. Not exactly Safari, but got GTK-Webcore ported and running in Solaris (Nevada). Obligatory screenshot here.

It is crashing on clicking any link, though. You can type in some  URL (SWAN), and it works okay.

I have to say, this is very fast. Takes about 2 seconds for the screen to show up from the moment I type in the command. Would be interesting to see the development.

Dial 1-800-GOOG-411

Omar Tazi - Sun, 2007-04-08 20:43

This might not be open source but it is FREE. If you are tired of being charged $1.5 by your cellular carrier for every 411 call, try Google's Voice Local Search. It is still experimental but we know what Google means by experimental or beta. It's usually pretty darn good. Although it was released earlier this month I just had a chance to try it today and I it worked just fine. About 75% of my attempts worked, when I'm not lucky I just say "back" and I get another chance. This service doesn't include residential requests but you can use it to find businesses. You can try "San Francisco California" and "Mas Sake Sushi" and it'll connect you or just say "text message" and it'll send you an sms (both free of charge).

This is a $7B/year market, more than 2.5 billion 411 calls are made every year in America. AT&T is toying with the free 411 calls but competition for this service comes from Jingle Networks which seems to have captured more than 5% of the 411 U.S. market in 2006. Another serious player is TellMe which was recently acquired by guess who... Microsoft of course.

DATE comparisons: the scenic route

Oracle WTF - Sun, 2007-04-08 07:37

Need to calculate the number of minutes between two dates? Yes, the dull way is to subtract one from the other and multiply by 1440. But why do that, when you can simply convert each date into Julian format by converting it into a string and then back to a date, and converting the resulting date into a Julian string, and (in a separate step) applying TO_NUMBER to the result to get an integer, so that you can simply subtract one from the other and multiply by 1440.

Except that rounds to the nearest day, so an additional step is to calculate the minutes since midnight for each of the two dates, which you can do by simply converting each date into an 'HH:MI AM' string, then back to a date, then back to an 'SSSSS' string, converting it to a number, dividing by 60 and adding it to the result of the first calculation. I think.

Anyway, consider the following library of handy date functions our Oracle WTF Easter gift to you, the online development community.

CREATE PACKAGE dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE )
        RETURN NUMBER;

    FUNCTION minutes_since_midnight
        ( timevalue DATE )
        RETURN NUMBER;

    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER;

END dates_pkg;
/

CREATE PACKAGE BODY dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE)
        RETURN NUMBER
    IS
        varch_value VARCHAR (10);
        num_value NUMBER (20);
    BEGIN
        SELECT TO_CHAR
               ( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/YYYY')
               , 'J')
        INTO   varch_value
        FROM   dual;

        SELECT TO_NUMBER (varch_value)
        INTO   num_value
        FROM   dual;

        RETURN (num_value);
    END julian_date;


    FUNCTION minutes_since_midnight (
        timevalue DATE)
        RETURN NUMBER
    IS
        secs_elapsed NUMBER (20);
        mins_elapsed NUMBER (20);
    BEGIN
        SELECT TO_NUMBER
               ( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
               , 'SSSSS') )
        INTO   secs_elapsed
        FROM   dual;

        SELECT (secs_elapsed / 60)
        INTO   mins_elapsed
        FROM   dual;

        RETURN (mins_elapsed);
    END minutes_since_midnight;


    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER
    IS
        final_number NUMBER (20);
        low_julian NUMBER (20);
        high_julian NUMBER (20);
        num_days NUMBER (20);
        num_minutes NUMBER (20);
        temp_mins NUMBER (20);
        min_low NUMBER (20);
        min_high NUMBER (20);
    BEGIN
        SELECT julian_date (lowdate)
        INTO   low_julian
        FROM   dual;

        SELECT julian_date (highdate)
        INTO   high_julian
        FROM   dual;

        SELECT (high_julian - low_julian)
        INTO   num_days
        FROM   dual;

        SELECT (num_days * 1440)
        INTO   num_minutes
        FROM   dual;

        SELECT minutes_since_midnight (lowdate)
        INTO   min_low
        FROM   dual;

        SELECT minutes_since_midnight (highdate)
        INTO   min_high
        FROM   dual;

        SELECT (min_high - min_low)
        INTO   temp_mins
        FROM   dual;

        SELECT (num_minutes + temp_mins)
        INTO   final_number
        FROM   dual;

        RETURN (final_number);

    END minutes_elapsed;
END dates_pkg;

Just for fun, let's test it:

CREATE TABLE wtf_test (start_date NOT NULL, end_date NOT NULL) AS
SELECT DATE '2006-12-25' + DBMS_RANDOM.VALUE(1,365)
     , DATE '2007-12-25' + DBMS_RANDOM.VALUE(1,365)
FROM   dual CONNECT BY LEVEL <= 1000;

-- ...several runs here to allow for caching etc, last set of results shown...

SQL> set timing on autotrace traceonly stat

SQL> SELECT dates_pkg.minutes_elapsed(start_date,end_date) FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:03.96

Statistics
----------------------------------------------------------
  16000  recursive calls
      0  db block gets
     74  consistent gets
      0  physical reads
      0  redo size
   9330  bytes sent via SQL*Net to client
    809  bytes received via SQL*Net from client
     68  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   1000  rows processed

SQL> SELECT (end_date - start_date) * 1440 FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:00.16

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
     74  consistent gets
      0  physical reads
      0  redo size
  25485  bytes sent via SQL*Net to client
    809  bytes received via SQL*Net from client
     68  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   1000  rows processed

So the handy package version takes 25 times as long as the 1-line SQL version.

And in the interests of fairness, in case you're thinking perhaps that is just the normal overhead of calling PL/SQL functions in SQL, let's try our own function:

CREATE FUNCTION minutes_elapsed
    ( lowdate DATE
    , highdate DATE )
    RETURN NUMBER
AS
BEGIN
    RETURN (highdate - lowdate) * 1440;
END minutes_elapsed;
/

SQL> SELECT minutes_elapsed(start_date,end_date) FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:00.26

Statistics
----------------------------------------------------------
     21  recursive calls
      0  db block gets
    107  consistent gets
      0  physical reads
      0  redo size
  25496  bytes sent via SQL*Net to client
    809  bytes received via SQL*Net from client
     68  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   1000  rows processed

Still 15 times faster.

Many thanks to Padders for sharing this one.

Oracle Beefs

Robert Vollman - Thu, 2007-04-05 15:20
I've got very few beefs with Oracle. It is extremely complicated and tough to learn compared to other relational databases, but that's partially offset by the tremendous documentation, and the huge Oracle community.Don't get me wrong, I love Oracle. I'm regularly impressed by the sophisticated and often clever ways it handles the requirements of an RDBMS, and quick to recommend its use. But Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com5

Learn about .NET and Oracle at Collaborate07 in Las Vegas

Christian Shay - Thu, 2007-04-05 09:27
The International Oracle Users Group (IOUG) will hold its Collaborate 07 conference in Las Vegas (April 15-17). If you attend, be sure to check out the .NET session and demonstration kiosk to hear more about new .NET features for Oracle Database 11g.

Session: Oracle Database 11g: What's New for Java, JDBC, .NET, PHP, and OCI

This session will be hosted by myself and Kuassi Mensah.

We will also have a "Application Development Tools" booth in the exhibit hall where you can see demos of Oracle Developer Tools for Visual Studio .NET, ODP.NET and .NET Stored Procedures in Oracle, SQL Developer, Application Express, and JDBC.

Exhibit Hall: Oracle Database 11g - Application Development Tools booth (DB-007)

See you there!

Pages

Subscribe to Oracle FAQ aggregator