LewisC’s An Expert’s Guide To Oracle Technology

Ok, I know you you’ve heard of them. Heck, in some cases, you’ve been beaten over the head with them. So why   aren’t you using them?

I’ve heard some people saying that it’s easier to code without bind variables. Really? Concatenating strings,   potentially with embedded quotes and/or varying formats, is easier than using bind variables?

I got an email the other day from a reader who asked to remain anonymous. He said that he read that he should use   bind variables and understood why but wasn’t sure he understood the concept of actually implementing code with   bind variables.

A job chain is a series of jobs chained together via dependencies. Today’s post walks through creating a 3 job chain.

The first step is to create our programs. I described creating dbms_scheduler programs in the previous entry, Oracle DBMS SCHEDULER Part 2 – The API.

These are very simple programs with no arguments. You can use SQL Developer to create these or just write the SQL yourself.

This is the SQL I used:

BEGIN
    sys.dbms_scheduler.create_program(
        program_name => 'SCOTT.PROGRAM1',
        program_action => 'SCOTT.PROCEDURE1',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'SCOTT.PROGRAM1');    

    sys.dbms_scheduler.create_program(
        program_name => 'SCOTT.PROGRAM2',
        program_action => 'SCOTT.PROCEDURE2',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'SCOTT.PROGRAM2');    

    sys.dbms_scheduler.create_program(
        program_name => 'SCOTT.PROGRAM3',
        program_action => 'SCOTT.PROCEDURE3',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'SCOTT.PROGRAM3');
END;
/

Once the programs are created, I create the chain itself.

In Part 1 of this series, I introduced you to the basic components and some information you need to know before using the scheduler. Today’s post will cover using the API to create some basic objects. I’ll show how to use SQL Developer to simplify some of these tasks.

Today’s post will cover creating programs and jobs.

Programs

As I said in my last scheduler post: it all starts with the program. A program is a PL/SQL procedure (can be packaged but not a function), PL/SQL block or external OS program.

You do not need to define a program if you don’t want to.

OCFS? That sure sounds familiar! OFS, OCFS? Where have a I heard that before? Oh yeah, Oracle Cluster File System! So, now Oracle has a cloud file system? Cool!

OCFS is based on ASM Cluster File System (ACFS) and ASM Dynamic Volume Manager (ADVM). It looks like the marketing material also refers to OCFS as CloudFS. The idea is to use ASM functionality with 3rd party file systems to provide simplify management and improve utilization of storage systems. More importantly, and here is where the Cloud comes in, it provides elastic provisioning.

DBMS_SCHEDULER is Oracle’s full featured scheduling solution that runs inside the database. Think of it as a complete replacement for DBMS_JOB (which I do still use occasionally but am using less and less over time). DBMS_SCHEDULER has a lot of benefits over DBMS_JOB – ability to stop a job, ability to reuse code, ability to reuse schedules, ability to chain jobs into a job streams, etc.

This post is the first of a series of posts that I will be doing on DBMS_SCHEDULER. Actually I am doing a lot of Streams work again and it was kind of a toss up as to which tool I’d blog about first.

I stopped by my blog today to upgrade the version of wordpress and some plug-ins. On the front page I got this hilarious ad and it seemed to really be appropriate to the very silly post it was shown with.

So a geek walks into a bar…..

I wrote a very silly post about a data architect and a biz guy (really silly geek humor) and the ad is “Why can’t I get a date? Maybe because you’re looking in the wrong place.” Ahahahahaha.

Sorry. That just cracked me up. When I refreshed, I got an ad for ETL software.

BG: What’s a data architect?

DA: Well, a company is like a big hill and the business people are at the bottom of the hill. Data starts at the top. It looks and smells a lot like poop. As a matter of a fact, I’ll call it poop.

BG: That’s kind of disgusting and probably irrelevant.

DA: No, stick with me (like poop). I’ll get to the point soon.

Parallel processing is using more resources, specifically CPU but also memory and I/O, to finish an operation faster. An operation might be doing an inventory in a warehouse. One person can do the inventory, walking row by row adding up the inventory. A faster way would be to hire a team of people. Each person would take one or two rows and when everyone is finished, the results can be tallied. The final answer will be the same either way (as long as everyone can count) but the team approach is faster. It also uses more resources (people).

Thought I would share a few links that I read this morning. I don’t do a lot of link posts but these three came off my RSS, one after the other, and all three are worth sharing.

The first is Will Oracle kill MySQL? Come on people! I can’t believe people are still stuck on this FUD. Ronald gives some good reasons why that is not likely. The only thing this kind of FUD really does is drive people to Postgres (and that postgres derivative that starts with an E and ends with a DB). I’m glad Ronald was able to set the record straight.

As developers, analysts, adminstrators, etc, I think most of us tend towards perfectionism. We want to do the job right and we want to do the job right the first time around.
Agility means being able to move forward with less than perfect information. Sometimes it means moving forward with information that is known to incomplete or even potentially inaccurate.
So what does that mean for analysis? Analysis, by definition, is the separation of individual components from the whole. The goal being, at least in my universe of activities, to be the understanding of a business proccess’s basic feature set and the relationships between those features.

• Got my OCP card in the mail today. http://twitpic.com/230ilr #
• DB Geek says: Twitter Weekly Updates for 2010-07-06:

  Blogged "ODTUG Kaleidoscope 2010 – The Journey So Far" htt… http://bit.ly/bVYwF2 #

• Cloud Comments: Comment on A quick overview of PuTTY and SSH for AWS Newbies by Installing a headless GeoServer on… http://bit.ly/d1jO66 #
• Interesting. Oracle Adds Support for Open-source R (the language) http://bit.ly/bpA7mh #
• I slept right through the bi 11g webcast. Didn't even notice it was AM until now. Bummer. #
• Blog Talk: Comment by Chris Barnhart on EDB Tip #4 Database Links in EDB http://bit.ly/9iyGHq #
• Blog Talk: Comment by Lewis Cunningham on EDB Tip #4 Database Links in EDB http://bit.ly/aSEngt #
• Interesting (dated) stat: oracle ebs 11i used hints on .3% of its 270000 queries.

• Blogged "ODTUG Kaleidoscope 2010 – The Journey So Far" http://it.toolbox.com/trd/46/2/39672/3 #
• #odtug #
• ODTUG Kaleidoscope 2010 – The Journey So Far http://it.toolbox.com/trd/73/2/39672/3/twitter #
• #odtug265 pigs, chickens and agile development with Maggie Tompkins. Maggie has made 14 consecutive kaleidoscopes. Wow! #
• I'm not a chicken or a pig. I'm more like a bacon flavored poultry product. #odtug295 #
• #odtug295 a scrum master can also be a pig. I didn't know that. #
• Maggie Tompkins #odtug295 http://twitpic.com/2111xd #
• 4 docs for scrum – func design doc, req tracability matrix, test design doc and db design doc. #odtug295 #
• #agile #odtug beware the ego maniac. The smartest guy.

• Blog Talk: Comment by Christopher Hamilton on Yahoo Sucks! Google wins. http://bit.ly/cb7OVe #
• @datachick I always thought waterboarding sounded like fun. Like dragging you around behind a boat on board. Wheeee! in reply to datachick #
• DB Geek says: Twitter Weekly Updates for 2010-06-22:

  Blogged "Stupid Proc Tricks -The Weird and Wonderful Overl… http://bit.ly/bdSH3i #

• Blog Talk: Comment by jonesr on Yahoo Sucks! Google wins. http://bit.ly/daOfcG #
• Oracle's iRecruitment.com gives me an error on the 3rd page. It's slow too. #
• Blog Talk: Comment by Lewis Cunningham on Yahoo Sucks! Google wins. http://bit.ly/byaoKO #
• Having the surrogate key vs natural key discussion. Again. Ever get tired of doing the same thing over and over?

• Blogged "Stupid Proc Tricks -The Weird and Wonderful Overload" http://it.toolbox.com/trd/46/2/39311/3 #
• My 2 year old turned off the refrigerator and unscrewed the light bulb. I fear what he will do when he discovers the screw driver. #
• DB Geek says: Stupid Proc Tricks -The Weird and Wonderful Overload: <!–
  google_ad_client = "pub-4372398370642959"… http://bit.ly/bPVMI2 #
• Blog Talk: Comment by harishkumar on Learn Oracle – What is Oracle? http://bit.ly/8ZSrvx #
• Blog Talk: Comment by "Jeffrey Kemp" on Stupid Proc Tricks -The Weird and Wonderful Overload http://bit.ly/dp1Qsi #
• Blog Talk: Comment by "Jeffrey Kemp" on Stupid Proc Tricks -The Weird and Wonderful Overload http://bit.ly/dp1Qsi #
• Blog Talk: Comment by Nini on Oracle and XML In Action – A Real World Example http://bit.ly/dlZBXL #
• A dilbert example of "trust, but verify" http://bit.ly/cSD4Sq #
• @pioro Congrats on the acehood!

Yea, I know. Quite the dramatic title. Work with me for a sec.

I’m pissed. Really pissed. Advance warning: I will rant now.

I forget the exact year but in the early 90s (I think 1993 or 94), I signed up for a novel service. Online email. Remember this is the early 90s. I was an early adopter even back then.

The internet was mostly character based and mostly used by universities, some larger companies and government. People using the internet were using email, usenet and ftp (and some crazies were using veronica, jughead, archie and gopher).

So what can I say? One of the phrases I hate most is “We’ve had problems with that.” I have seen that, or some form of that, question used as an excuse to not try new features, to not use best practices, keep a head in the sand, stick to Oracle7 features, etc.

As an example, in my early days with data modeling, I once created a new schema. It wasn’t a very big schema; probably 10 tables or less. I spent a lot of time doing the analysis to make sure I got the data structures correct. The ERD was sent around and got approval from everyone.

• Blogged "Stupid Proc Tricks -The Weird and Wonderful Overload" http://it.toolbox.com/trd/46/2/39311/3 #
• My 2 year old turned off the refrigerator and unscrewed the light bulb. I fear what he will do when he discovers the screw driver. #
• DB Geek says: Stupid Proc Tricks -The Weird and Wonderful Overload: <!–
  google_ad_client = "pub-4372398370642959"… http://bit.ly/bPVMI2 #

Powered by Twitter Tools

When overloading a procedure or function it is the calling parameters that make the difference. The return type does not make a difference as far as the ability to overload. Generally, when talking about overloaded procedures, you will hear or read something like “You must change at least one of the parameter data types to overload it.” But that’s not really true.

• DB Geek says: Twitter Weekly Updates for 2010-05-31: <!–
  google_ad_client = "pub-4372398370642959";
  /* 468×60, cr… http://bit.ly/975olU #
• Gergen: Mr. President, take command http://www.cnn.com/2010/OPINION/05/30/gergen.oil.spill/index.html?hpt=C2 #
• Blogged "My PHP Online References" http://it.toolbox.com/trd/46/2/39030/3 #
• Deal Breakers: 10 Traits & Practices Ruin Your Promotability & Success! http://bit.ly/8Z7iOW #
• Training workers to leave. Interesting concept. http://money.cnn.com/2010/06/01/smallbusiness/staff_training/index.htm?hpt=T3 #
• Updated the links on my PHP for PL/SQL Developers presentation. http://bit.ly/aRtkY5 #
• Anyone successful getting hprof working in sqldev 2.1.1.64? It works for me in 1.5.5 but no love in 2.1. #
• @oraclenerd Hierarchical Profiler. in reply to oraclenerd #
• @oraclenerd It's easy to use and sqldev will even set it up. Setup is working in 2.1 but it's not displaying my runs. 1.5 works fine.

• DB Geek says: Twitter Weekly Updates for 2010-05-24: <!–
  google_ad_client = "pub-4372398370642959";
  /* 468×60, cr… http://bit.ly/cjrA0b #
• Blogged "PHP for PL/SQL Developers: More Include Files, Inserting Data and Must See Sites" http://it.toolbox.com/trd/46/2/38892/3 #
• I've been Tweeting since July 28, 2007 (1031 days). How about you? – http://HowLongOnTwitter.com #
• My life writ dilbertian http://bit.ly/cw6qfo #
• @dtseiler @dtseiler I still double space. I'm oldskool, yo. in reply to dtseiler #
• Interesting webinar coming on June 3, "Urban Myths" about SQL presented by Mike Stonebraker http://bit.ly/9Os9XD  #
• What we need to do about BP's oil spill. http://www.cnn.com/2010/OPINION/05/25/honore.oil.spill/index.html?hpt=T1 #
• RittmanMead's Stewart Bryson speaking at SOUG tonight. http://soug.acomp.usf.edu/nextmeetingdetailed.php #
• Going to usergroup tonite. Hungry now. Wonder what the soug feedage will be.

• DB Geek says: Twitter Weekly Updates for 2010-05-17: <!–
  google_ad_client = "pub-4372398370642959";
  /* 468×60, cr… http://bit.ly/aXWadD #
• Blog Comments: Comment on uCertify Oracle Certification Software Review by LewisC: Just a year late but Congrats o… http://bit.ly/d3E1Mf #
• Blogged "PHP for PL/SQL Developers: Include Files, Connecting to Oracle and Selecting Data" http://it.toolbox.com/trd/46/2/38739/3 #
• Updated my PHP for PL/SQL Developers post with links to all the posts so far: http://bit.ly/php_ora #
• RT @orana Suncoast Oracle Users Group, May 27, 2010 http://ff.im/-ktELE The OLTP DBA’s Guide to Delivering a Dimensional Model. #
• @datachick I got my email about voting today too. Guess I'll go get it done.

• DB Geek says: Twitter Weekly Updates for 2010-05-09: <!–
  google_ad_client = "pub-4372398370642959";
  /* 468×60, cr… http://bit.ly/cpeb6i #
• Blogged "PHP for PL/SQL Developers: Finally, some real code" http://it.toolbox.com/trd/46/2/38612/3 #
• Blogged "MySQL Track at ODTUG Kaleidoscope" http://it.toolbox.com/trd/46/2/38656/3 #
• SAP buys Sybase for 5.8 billion http://bit.ly/chxieC #
• rt @oracletechnet A full Oracle Database App Development stack in a VirtualBox appliance: Can I haz? Yes I can http://bit.ly/dACvjB #
• Blogged "Know Thy Audience – Lessons on Public Presentations" http://it.toolbox.com/trd/46/2/38684/3 #
• Blog Comments: Comment on 3 Cloud Operating Systems You Can Use Right Now by ben: G.ho.st has shutdown to pursue o… http://bit.ly/dvl2uD #
• Wow. This sucks.

• DB Geek says: Twitter Weekly Updates for 2010-05-02: <!–
  google_ad_client = "pub-4372398370642959";
  /* 468×60, cr… http://bit.ly/buvM60 #
• Blogged "PHP for PL/SQL Developers: A First Look" http://it.toolbox.com/trd/46/2/38432/3 #
• "PHP for PL/SQL Developers: A First Look" http://it.toolbox.com/trd/73/2/38432/3/twitter #
• Finally tossed the dlink 615. Fast router but the dropped connections were driving me nuts. Don't buy it. #
• Blogged "ProjectManager.com – A Review" http://it.toolbox.com/trd/46/2/38443/3 #
• Blog Talk: Comment by Balvant on Oracle Collections: A Definition in Plain English Part 1 http://bit.ly/bA9oX6 #

Powered by Twitter Tools

• DB Geek says: Twitter Weekly Updates for 2010-04-25: <!–
  google_ad_client = "pub-4372398370642959";
  /* 468×60, cr… http://bit.ly/9lhaI4 #
• Blogged "PHP for PL/SQL Developers, The Beginning" http://it.toolbox.com/trd/46/2/38272/3 #
• Blogged "My ODTUG Kaleidoscope 2010 Schedule" http://it.toolbox.com/trd/46/2/38347/3 #
• dilbert wins again. http://dilbert.com/strips/comic/2010-04-29/ #
• hp + plam = me likee. cool os and great consumer hardware. hp web os netbook and/or tablet, anyone? #
• plam = plam. doi. #
• ack. stupid phone is killing me. palm, palm, palm. thank you. #

Powered by Twitter Tools

• DB Geek says: Twitter Weekly Updates for 2010-04-18: <!–
  google_ad_client = "pub-4372398370642959";
  /* 468×60, cr… http://bit.ly/axT2vM #
• Blogged "PHP for PL/SQL Developers, The Introduction" http://it.toolbox.com/trd/46/2/38205/3 #
• Blog Comments: Comment on Designing the Data Mart – Part 2 by Lazalo: Very good article. Thanks! http://bit.ly/dBCxr4 #

Powered by Twitter Tools

• DB Geek says: Twitter Weekly Updates for 2010-04-11: <!–
  google_ad_client = "pub-4372398370642959";
  /* 468×60, cr… http://bit.ly/blFgRo #
• Blogged "Why projects fail. Reason #113" http://it.toolbox.com/trd/46/2/37942/3 #
• Trying out the officalt blackberry twitter client. Soso #
• Twitter for bb makes my red light blink when ANYone I follow tweets. That means my red light never stops. Annoying. #

Powered by Twitter Tools

• DB Geek says: Twitter Weekly Updates for 2010-04-04: <!–
  google_ad_client = "pub-4372398370642959";
  /* 468×60, cr… http://bit.ly/9P2t5N #
• @craigmullins just reads your march dbta column. Good stuff. Agree with osdb move from easy to complex comment. in reply to craigmullins #
• Cloud Comments: Comment on Defining Cloud Computing: Part 6 – IaaS by Basant Singh: Rackspace is also a leading Ia… http://bit.ly/9z9LD8 #
• Cloud Comments: Comment on About by Neha Sharma: Hi,
  I am very impressed with your blog which has some amazing pos… http://bit.ly/9pbAVM #
• Cloud Comments: Comment on A quick overview of PuTTY and SSH for AWS Newbies by Daminda: Hi bro,
  Thank you for thi… http://bit.ly/8Yflzh #
• Michael Stonebraker?s take on NoSQL. Good read.

• DB Geek says: Twitter Weekly Updates for 2010-03-28: <!–
  google_ad_client = "pub-4372398370642959";
  /* 468×60, cr… http://bit.ly/aAGEHw #
• DB Geek says: Twitter Weekly Updates for 2010-03-21: Got a reply email from a recruiter. Started off with"sorry fo… http://bit.ly/9xkyD6 #
• back from the cruise. Mixed emotions. #
• Muhahahahaha. RT @fmanjoo: Teabonics. Flickr set of misspelled Tea Party signs. This made my morning. http://bit.ly/dy9DR3 #
• This is hilarious – RethinkDB is switching over to Lisp http://bit.ly/9bx2l7 #
• Oracle ace director and java champion teleconference just getting started. Please mute your phones people. #
• @chriscmuir viki is recording it now. in reply to chriscmuir #
• 100ish ace directors, 300ish aces, 110ish java champions. Cool. My first knowledge of java champions.

• Blogged "Give Evil a Break" http://it.toolbox.com/trd/46/2/37597/3 #
• Sitting in a sprint planning meeting. Screw pigs and chickens. I wanna be a scrumlord! #
• Coughing. Breathing rough. Chest hurts. What does pneumonia feel like? I don't have fever or nausea. #
• @datachick heh. Xml fixes everything. Like the cloud. Xml cloud = nerdvanna! in reply to datachick #
• ?Friendship, love, health, energy, enthusiasm, and joy are the things that make life worth living and exploring.? ? Denise Austin #
• @fmanjoo I'm going with entropy. in reply to fmanjoo #
• @peglegpug I got the perfect pullup. Works great. Pull ups turned out to be much harder than I remembered.

• Got a reply email from a recruiter. Started off with"sorry for the delay in responding." My message was from 2007. #
• Cool! Watch instantly! RT @Techmeme: Netflix announced for Windows Phone 7 Series (Chris Ziegler/Engadget) http://techme.me/=IVb #
• DB Geek says: Twitter Weekly Updates for 2010-03-14: <!–
  google_ad_client = "pub-4372398370642959";
  /* 468×60, cr… http://bit.ly/awLyuk #
• Blogged "Anti-Standards" http://it.toolbox.com/trd/46/2/37481/3 #
• From Dr Dobbs. Hilarious! Larry Ellison on chatroulette talking about cloud computing. http://bit.ly/aO0dhU #
• Dilbert speaking in words even management can understand. http://dilbert.com/strips/comic/2010-03-17/ #
• I decided I want to become a certified scrumbastard. Then someone pointed out that it is scrummaster. Well then, nevermind. #
• ODTUG Kaleidoscope 2010 early bird registration ends Mar 24.