5 things to know about open source

If you’re not familiar with open source software it can sound strange and even a bit scary.  However at the most basic level “open source” simply means you can read the source code.  Apart from that, open source software isn’t much different from the proprietary (closed source) software you may be familiar with.  There is lots of good and bad software out there both open source and proprietary .  Let’s look at a few things open source has in common with proprietary software.

Open source software is created by passionate, talented people.

Open source projects come in all sizes, but no matter if they are large or small, they are typically developed by people who are passionate about the technology they are developing.

Usually the core development team consists of people who personally had a need or a problem to solve and started the project to address it.  When someone has a personal stake in a project they tend to care more about it than they would about ‘the next thing they are assigned.’  As the project progresses other people will join, who also have a personal need for what it can do.

Open source software is as safe as any other software.

Of course there is always risk with any type of software.  Defective and malicious code can make its way into any product.

However, when the source is open for anyone to evaluate and review it becomes much harder to hide something bad.  When more people are involved in a project, at all levels; from reporting issues, to testing to developing the code, there are more eyes are on the entire project.  The more eyes, the better chance to find any type of problem, before it causes too much damage.

If anyone ever has a concern about the safety of an open source project, they can evaluate it and put themselves at ease.

Governments and large companies use lots of open source software.

It’s not just uber geeks that use open source.  Because open source software has proven to be safe and effective, more and more people are incorporating it into their organizations.  Large companies are issuing workstations to employees pre-loaded with software such as OpenOffice, governments are creating server farms using redhat and everyday more people are installing open source operating systems like Fedora and Linux Mint.

“Open” does not always mean “free.”

Just because an application is open source that doesn’t necessarily mean it’s free just like proprietary doesn’t mean it cant be free.  redhat for example is a leading open source linux operating system that charges for an enterprise license.

Some open source software may be free for personal or non profit use, while there is a charge for commercial use.  Others may be free to use, but if you incorporate them into an application you are building you would be required to open source your application.  And some are free to use as you wish with minimal restrictions.

Always evaluate the license of the software you want to use (open or closed source), and if you want to start and open source project choose a license that best fits how you want it to be used.

Open source software has long term sustainability.

Whenever you depend on a piece of software there’s always a concern that it may be deprecated, abandoned or changed in a way that you wont like.

With open source software you may (very important: read the license!) have the option to fork the code, support it yourself, and even add features you wish it had that may not be included otherwise.

When you have control of the software you depend on, you have the ability to control your own dependency on that software and you can make better long term decision for your own needs.

Followup to my session on the Oracle Database Development Web Series

Oracle Database Development  Web Series

Of course any demo must come with a bug

The git clone bug I had in SQL Developer was entirely my fault.

Prior to my session, I was cleaning up.  I had SQL Developer open and I deleted my project working directory using the command line.

After the session, restarting SQL Developer solved the problem and I was able to clone again.

I left out a part

Afterwards I had a question about using credentials when cloning from GitHub.  I had intended to briefly address this when cloning the repo with SQL Developer  but by the time I got past my mistake I forgot.  Here’s a link that will explain how to clone from GitHub better than I can.

Links used in the session

I hope the session was useful and, as always, let me know if you have any questions.

Lessons Learned Inside Oracle

Anyone who knows me know that if I form a strong opinion about something, it’s because I believe I have examined all the available facts.  Of course, I don’t always have all the facts; so even when I have a very strong opinion, I am open to opposing views and to changing my mind.

I love a lively debate on almost any topic.

When I’m wrong, I admit it, and I try to admit it to anyone who I may have influenced with my previous, wrong opinion.   It’s almost a compulsion for me.  The more wrong I am, the more public I try to be as I make things right.

If you know me well enough to already know the above, then you will understand why I have written this post.   If we haven’t had the chance to meet, you may read this and think it is just some kind of shill post from an Oracle employee.  That’s OK; I would probably have thought the same thing a few short months ago.

The vast majority of my career has involved working with Oracle databases as a developer for Oracle customers. I have always believed that Oracle makes the best database available (its commercial Oracle Database, I’m digging into  MySQL and Oracle’s NoSQL database and I’m sure we’ll talk more about those at another time.)

Bottom line: If you can afford Oracle Database, use it.

On the other hand, over the years I had formed some negative opinions about Oracle as a company. They didn’t disappear the day I started working for Oracle, but they have been fading away steadily, as I learned about Oracle from the inside.

A big, heartless corporation?

New York City, New York State, USA --- Modern skyscraper --- Image by © Ditto/Image Source/Corbis
New York City, New York State, USA — Modern skyscraper — Image by © Ditto/Image Source/Corbis

We’ve all seen the stories depicting Oracle as a monolithic company that only cares about the bottom line.  Before I joined Oracle, I rarely noticed any instances of Oracle giving back to the community or helping those in need.

Now that I have an @oracle.com email address, I get several emails a week looking for volunteers to help with one charity event or another.  It reminds me of how I never notice a certain type of car on the road until after I buy one myself. Now that I’m working at Oracle, I see Oracle in my news feeds all the time.  For example, I’ve started noticing projects such as the Raspberry Pi weather station or  Kids coding events when they pop up.

I guess it’s just a matter of my personal awareness.  If something isn’t in my  current focus (or reinforcing my current bias?), I see the attention-grabbing headlines, which (as we are used to in 21st century media) tends to the negative, rather than the positive.

Soul-crushing grind?

Oracle_OIC_Participant-Networking_63

I have to admit: when I accepted my new position, I felt a little twinge of dread: would I face a soul-crushing grind, with too many hours worked and a bare-bones benefits package?

My dread was completely mis-placed.

The insurance package, educational opportunities, and internal perks are some of the best I’ve ever had.

Sure, there’s some red tape and progress reports and all the typical things that come with a technical job at a company with over 130,000 employees. But I have never had this level of freedom to shape my own work. I am able to use and expand my strengths, while being supported and mentored to improve my weaknesses.

Stuck with “old” technology?

Over the last few years I’ve been using all sorts of new fun stuff, including NodeJS, AngularJS, and document databases. I’ve dabbled with graph databases, a bit of Java, and all kinds of cloud products.

When I applied for the Oracle Developer Advocate position I was concerned that I would be pigeonholed into a small set of “mature” technologies.  As I proceeded through the interview process, I learned that I would be covering many different languages; starting with Python then moving on to others like Ruby, JavaScript and PHP.  Still it was hard to shake the feeling that most other techies at Oracle would be focused on Java and PL/SQL – which might alienate me a bit.

Once again, I was so wrong.  I have seen (and am working on) many projects in all kinds of languages and technologies. I’ve learned about Oracle products using all of the technologies I’ve been interested in and many more that I had just assumed Oracle would not encourage.

My list of “Ooooh, I want to get involved in that” projects here at Oracle gets longer every day.

I feel like a kid in a candy store.

Open Source and Oracle – Really?

ic-OnlineStore-wht-on-red

As you may have noticed, I am the Oracle Developer Advocate for Open Source.  One of the reasons I applied for this position was that I’d always thought that Oracle was against open source and I would have a small, possibly quixotic chance to nudge Oracle towards open source.  I expected to have a lot of push back on anything open source.

What I found, instead, was that almost every one of my “brilliant ideas” for getting Oracle interested in open source was already in place or in the process of being implemented.

Much to my surprise and pleasure, I find myself playing catch up.

Here are just a couple of examples:

  • Oracle open source projects:  There are quite a few already and the open source resource are growing.
  • Oracle on Github: Already up and running.
  • Allow employees to contribute to open source projects:  An existing approval process was already in place and anyone interested just needs to apply. I have applied.
  • Support Oracle and non-Oracle owned drivers for languages more often used with open source projects:  Not only already existing, but one of my first assignments was to create an entry level tutorial and some examples for the cx_Oracle Python driver.

I still joke that Oracle hired its Open Source advocate on April Fools Day, but now it’s a great ice breaker when I’m at a Python or JavaScript meetup. They can hardly believe I am there, but there I am: eager to learn and eager to share what Oracle’s doing to support open source communities.

Can’t learn anything new if you think you know everything

ph-ind-edu-007-S

I often say that “I learn more from being wrong than I do from being right.”

In my first four months inside Oracle, I have learned an awful lot from my inaccurate perceptions about this company. I expect to be learning lots more in the years to come.

Does this mean I agree with everything everyone at Oracle says and does? No way (except for my manager, of course). But I’ve now learned that many of my beliefs were just that – things I believed, rather than things I know.

The more knowledge I acquire about Oracle, the more glad I am I took this job and the more optimistic I am that open source developers will “catch on” like I have.

Who knows what the future holds for me, I enjoy this position and I hope to stay here for a long time.  But, if something ever does draw me away from Oracle, I will be quite a bit smarter and hopefully I will be less quick to form negative opinions from limited data.

Intro to Git

During the lunch and learn at KScope15 we talked a little about using open source projects as a way to learn/teach programming concepts.  There was a little confusion about Git and GitHub so I thought I’d put up a short post with some resources.

I don’t claim to be a Git guru by any means, this is intended to be a high level introduction.  I have linked several resources at the end for those who want to dive deep.

Git, a Distributed Version Control System (VCS)

You may be familiar with the typical VCS which has a centralized shared repository.  You checkout a copy of the code, work on it, then check it back in.  Everyone works from the same central repo.

Git is different.  Think of it as having your own personal VCS built into your local work-space.  You still check code in and out, branch, merge and commit changes; but instead of locking files on a remote system somewhere, you merge your local VCS into a shared remote repo when ready.  You have the advantage of being able to make multiple frequent commits locally and only push to the central repo when you want your changes merged into the product.  Your work doesn’t impact others till you make the push.

There are many great features in Git, but for me it’s the distributed functionality that makes it awesome.

When your central repo is unreachable (Network/internet issues, server down or system patches;) using a traditional VCS your developers are not able to commit changes till the problem is resolved.  They can continue to work, but run the risk of not being able to roll back to a ‘working’ point in the code.

With Git your developers continue to work making frequent commits; they can rollback, branch, merge and shelf changes as needed.  When the issue is resolved they pull the current state down, merge their changes and push the merged changes back up.

As an added bonus, you can push your changes to multiple remote repos.  You can push and pull from an internal company repo as well as a remote hosting service such as GitHub or BitBucket.

Remote git hosting services

A remote git hosting service is basically a place where you host a Git repository.  Similar to how you would create a Git repository on an internal server, you can create the same repo on one of these services.  Once the repo is created at your chosen host, you pull and push to it the same as an other repo.

A common misconception is that GitHub is Git.  GitHub is probably the most popular of the many remote git hosting services but it is not Git.

The two I’ve used the most are GitHub and BitBucket.  Both offer a very comparable set of services; issue tracking, pull requests, team collaboration features and others.  GitHub tends to be more popular, especially with the open source projects; BitBucket gives you private repositories in the free level.

I would recommend either one, take your time to see which fits your needs better.  If you have a service you like better please leave a comment.

Want to learn more

There are a ton of resources to help you become an expert, here are a few I’ve found.  If you have a great one, leave a comment.

Git resources:

Remote git hosting services:

Check out the new Oracle Database Developer Choice Awards

The new Oracle Database Developer Choice Awards program is designed to recognize outstanding members of the Oracle Database development community.
You may already be familiar with the Oracle ACE program, but this program has a few differences.  Rather than winners being chosen by Oracle the process will be controlled by the developer community.
  • Community members will make the nominations.
  • Finalists will be chosen by panels of ACEs.
  • The winners will be chosen by community members votes.
The award categories for  2015 are:
  • SQL
  • PL/SQL
  • Oracle REST Data Services
  • Oracle Application Express
  • Database Design

devchoice

Feel free to submit comments for other categories to be considered for next year.

This program has been designed to use a more open community driven process.  We want to recognize those developers among us who have made the most positive contributions to our community.

Delete (cruD) using cx_Oracle

In this post we’re going to take a look at the D in CRUD: Delete.

We use the cx_Oracle driver to delete some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

My helper function get_all_rows() encapsulates a select statement used to verify that the deletes worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

Add this function to the top of your file.

Resetting the data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.py with the following code and run it whenever you would like to reset the data. (Notice this version adds people and pet data not included in other sections.)

Boilerplate template

The template we will be using is:

For each exercise, replace the “# Your code here” line with your code.

Reset the data

First let’s run reset_data.py to setup our data.

Simple delete

We will perform a simple delete that removes a single record in the cx_people table.  These are the steps performed in the code snippet below.

  • Get a cursor object from our connection.  We will use this cursor to perform our database operations.
  • Prepare a SQL DELETE statement, deleting the cx_pets record with an id of 1.
  • Execute the statement using bind variables.  (see the R part of this series for an explanation of bind variables)
  • Commit the transaction.
When I run this code in my Python session, I see:

Extra Fun 1

Delete all the birds .

Your results should be:

Answer

Reset the data

Now is a good time to run reset_data.py.

Boilerplate change

Change the boilerplate get_all_rows statements to get people and pet data.

Deleting records referenced by Foreign Keys

If you are using integrity constraints in your database (of course you are, because then you let the database do some heavy lifting for you), you will sometimes need to change the way you process your changes.

In our design, we have a Foreign Key constraint in cx_pets that ensures if a pet has an owner, that owner exists.

This is the statement that creates the constraint in the Creating the Database Objects section of the Initial Setup post.

If we attempt to delete a record in cx_people that is referenced in cx_pets (Person has a pet,) we get an error.

When I run this code in my Python session, I see:

Before deleting the person you have to handle the pet (watch out for claws and teeth).

There are a few options here, depending on your database design:

  • If: pets are not required to have an owner and you only want to delete the person, not the pets.  Then: you can update the pets and set their owner to null.
  • If: pets are required to have an owner.  Then: you can delete the pets for the owner.

In either of the above scenarios you can update the pets and set their owner to another person.

Bob is moving out of our area and his new apartment doesn’t allow pets, so he’s giving them to Kim.  Let’s use that last option here.

  • Get a cursor object from our connection.  We will use this cursor to perform our database operations.
  • Prepare a SQL UPDATE statement, changing owner to 2 (Kim) for the records with an owner of 1 (Bob).  Updating is covered in the U part of this series.
  • Execute the statement using bind variables.
  • Prepare a SQL DELETE statement, deleting records with an id of 1 (Bob).
  • Execute the statement using bind variables. (see the R part of this series for an explanation of bind variables)
  • Commit the transaction.
When I run this code in my Python session, I see:

When you change data it’s a good idea to verify the number of affected rows.  This is covered in the R part of this series.

Extra Fun 2

Due to a zoning change, snakes are no longer allowed in our area.  Stacey has decided to move and take Sneaky with her.

Lets fix our data.

Your results should be:

Answer

Some other things you could try
  • Change the database constraints to delete or Null the child record on delete (a cascading delete).  Delete a person and let the database handle the children.
  • Remove the people who don’t have any pets.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Update (crUd) using cx_Oracle

In this post we’re going to take a look at the U in CRUD: Update.

We use the cx_Oracle driver to update some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

My helper function get_all_rows() encapsulates a select statement used to verify that the updates worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

Add this function to the top of your file.

Resetting the data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.py with the following code and then run it whenever you would like to reset the data. (Notice this version adds pet data not included in other sections.)

Boilerplate template

The template we will be using is:

For each exercise, replace the “# Your code here” line with your code.

Simple update

We will perform a simple update that modifies a single record in the cx_people table.  These are the steps performed in the code snippet below.

  • Get a cursor object from our connection.  We will use this cursor to perform our database operations.
  • Prepare a SQL UPDATE statement, changing age to 31 for the record with an id of 1.
  • Execute the statement using bind variables.  (See the R part of this series for an explanation of bind variables.)
  • Commit the transaction.
When I run this code in my Python session, I see:

Extra Fun 1

Update Bob’s notes to ‘I like cats’ .

Your results should be:

Answer

Reset the data

Now is a good time to run reset_data.py.

Boilerplate change

Change the boilerplate get_all_rows statements to get pet data.

Make sure your where clause is specific

In the above example, notice that we used the id column in our where clause.  For our data set, id is the primary key.  You do not always have to use a primary key, but you should make sure you only update the rows you intend to.

Next let’s look at updating multiple rows.   We’ll have Bob give his dog Duke to Kim.

  • Get a cursor object from our connection.  We will use this cursor to perform our database operations.
  • Prepare a SQL UPDATE statement, changing owner to 2 (Kim) for the records with an owner of 1 (Bob) and a type of ‘dog’.
  • Execute the statement using bind variables.  (See the R part of this series for an explanation of bind variables.)
  • Commit the transaction.
When I run this code in my Python session, I see:

In our example we only used owner and type, assuming that Bob only had one dog, Duke, as it is in our original data.  With the new reset data function we added a second dog Buster.  This example is intended to demonstrate what may happen when multiple users are working with the same data set.

In our data, the only unique identifier for cx_pets is id.  Bob may have two dogs, or even two dogs named Duke.  Make sure if you intend to change a specific row you use a unique identifier.

It also helps to…

Verify the number of affected rows

Now lets give Buster back to Bob.  This time we will use the unique id column and we will print out the number of rows affected using Cursor.rowcount.

  • Get a cursor object from our connection.  We will use this cursor to perform our database operations.
  • Prepare a SQL UPDATE statement, changing owner to 1 (Bob) for the records with an id of 6 (Buster).
  • Execute the statement using bind variables.  (See the R part of this series for an explanation of bind variables.)
  • Commit the transaction.
When I run this code in my Python session, I see:

Cursor.rowcount will show you the number of rows affected for insert, update and delete statements and the number of rows returned in a select statement.

Extra Fun 2

Give all birds to Kim that she doesn’t already have and print the number of affected rows .

Your results should be:

Answer

Some other things you could try
  • Change multiple column values
  • Preform an update that changes all rows, if the rowcount is greater than 2, rollback the change

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Kscope15 The Other Days

I covered Monday in another post.  After that, I got really wrapped up in everything going on and didn’t get any more posts up.  Now I’m home and I can try to remember and summarize.  Forgive me if I forget anything, it’s a whirlwind of activity.

I’ve been exploring other technologies for the past couple years so some of my Oracle skills got rusty and I missed some cool stuff.  Fortunately there were plenty of opportunities to do a little catch up.

I really enjoyed every single session I attended.  The speakers were very knowledgeable and eager to answer questions.

Just a quick summary:

  • Mark Doran taught me some interesting Analytic functions.
  • Maria Colgan showed how to configure your system to get the most out of Oracle Database In-Memory.
  • Noel Potugal and Jeremy Ashely made me come home and dig out my Raspberry Pi and Arduino boards.
  • Craig Shallahamer helped me understand Queuing much better, (and even a little bit about how hamburgers cook.)
  • Melanie Caffrey explained how to set up EBR for simplified Pl/SQL releases, and even got me thinking about some alternate uses that I need to go experiment with.
  • Kris Rice demonstrated Oracle REST Data Services and answered everything I could think of just as I was thinking of new questions.
  • Attending the Women In Technology session opened my eyes to some issues I had not thought of.
  • In a database developer session, speaking with people in the halls and even discussing developer environments on the bus to Nikki Beach, I learned there are plenty of misconceptions about the available tools, such as git.

I met so many interesting people from all over the world that I’m still trying to place conversations with faces.

To be honest, I was mostly excited to go to Kscope15 because it was the first chance to meet a couple people on our Developer Advocates team.  I knew some of the sessions would be interesting but probably not related to what I’m doing.  That is the kind of being wrong that I like!

Kscope has become one of my top picks.  I need to get some decent material ready for the next one so I can make a contribution.  It’s going to be difficult to measure up.

Thanks for a great time and I hope to see everyone again soon.

 

Insert (Crud) using cx_Oracle

In this post, we’re going to take a look at the C in CRUD: Create.

We will be using the cx_Oracle driver to create some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

I will be using a helper function get_all_rows(). This is a select statement used to verify that the inserts worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

Add this function to the top of your file.

Resetting the data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.py with the following code and then run it whenever you would like to reset the data.

Boilerplate template

The template we will be using is:

For each exercise, replace the “# Your code here” line with your code.

Simple insert

We will perform a simple insert that adds a single record into the cx_people table.  These are the steps performed in the code snippet below.

  • Get a cursor object from our connection.  We will use this cursor to perform our database operations.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the data.
  • Execute the statement using bind variables.  (see the R part of this series for an explanation of bind variables)
  • Commit the transaction.
When I run this code in my Python session, I see:

What is a transaction?

You’ll notice in the bullet points above, I said to commit the transaction.

When you make execute Data Manipulation Language or DML statements, such as the insert I use in this post, those changes are only visible to your current connection or session.

Those changes will not be visible to other sessions (even another session connected to the same schema in which the changes were made) until you commit your changes. That step makes it “permanent” in the database, and available for everyone else to see (and possibly change in a future transaction).

Extra Fun 1 & 2

1.  Insert more than 1 row .

Using data for ‘Rob’, 37, ‘I like snakes’ and ‘Cheryl’, 41, ‘I like monkey’ Your results should be:

Insert-02

Answer

This method will work for inserting many rows at once, but there is a better way.  I cover that below.

2.  Verify that a second connection cannot see your changes till after the commit.

Using data for ‘Suzy’, 31, ‘I like rabbits’ and assuming that you did the previous exercise your results should be:

Insert-03

Notice that after the insert, the connection that made the insert can see Suzy but the second connection can’t.

After the commit, both connections see Suzy.

Answer

I modified the helper function and the get_all_rows calls in the template code to make it a little easier.  If you chose to do this, please revert the template code after this exercise.

Reset the data

Now is a good time to run reset_data.py.

Using Identity Columns

You may have noticed that the id column is not passed in, but is automatically set sequentially.  Prior to Oracle Database 12c, this was accomplished using a sequence and a trigger.

In 12c, this can be accomplished by using an Identity Column.

 You can find more information on identity columns here(pdf).
Returning data after an insert
 Sometimes we need to perform additional operations after an insert using data generated by the database, such as the identity column above.  For example, let’s add a person and a pet for them.

We could run an insert then select the value back using the name.  But if the name is not unique we’ll have a problem.  This is where the RETURNING clause is helpful.

We will perform an insert that adds a single record into the cx_people table. Then using the returned id we will add a pet.  These are the steps performed in the code snippet below.

  • Get a cursor object from our connection.  We will use this cursor to perform our database operations.
  • Create a variable associated with the cursor to receive the returned value.  Set its type to cx_Oracle.NUMBER.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the people data.
  • Execute the statement using bind variables returning the id into new_id.
  • Get the value from new_id and assign it to sandy_id.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the pet data.
  • Execute the statement using bind variables including the sandy_id value.
  • Commit the transaction.
  • Print the sandy_id value. (It’s a float so we use .rstrip(‘.0’) to make it pretty)
  • Prepare a SQL statement using a bind variable
  • Execute the statement using sandy_id for the bind variable.
  • Fetch the results from the cursor into a variable.
  • Print the results with a little decoration text.
When I run this code in my Python session, I see:

Notice the new value, the owner in Sandy’s pets and Sandy’s id in the New Data are all 3 .

Extra Fun 3

3.  Insert Sandy again but return her id and name.

Your results should be:

Notice that (3, ‘Sandy’..) is still there along with our new (4, ‘Sandy’..) but the returned id is 4.  It should return the new id each time you run it.

Answer
 Reset the data

Now is a good time to run reset_data.py.

Insert more than 1 row

As mentioned above, when you want to insert multiple rows, running multiple insert statements is inefficient and makes multiple trips to the database so instead, we will use executemany.

We will perform an insert that adds two records into the cx_people table.  These are the steps performed in the code snippet below.

  • Create an array populated with our data
  • Get a cursor object from our connection.  We will use this cursor to perform our database operations.
  • Set the cursor’s bindarraysize to the number of records in our array.
  • Set the cursor’s setinputsizes.  This tells the cursor what to expect from our data items.  The first and third items are strings so we define the max length, the second is an int so we just use int.  This allows the cx_Oracle driver to pre-define the memory needed.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the data.
  • Execute the statement using bind variables.
  • Commit the transaction.
When I run this code in my Python session, I see:

There may be an easy way to use the returning option with executemany, but after searching the web for a while, the methods I found were complicated enough that I won’t go over them here.  It seems to be easier to just use a PL/SQL function, which is also a topic for another time.

Some things you could try
  • Loop through an array of people and insert each one returning its id.  Using that id add multiple pets with executemany()
  • Create a large array of people.  Time the difference between looping through single inserts and using executemany()

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

KScope15 Monday

The first two sessions I attended were from Tom Kyte (ask Tom) titled, All (Well, a Lot) about SQL.

Over the years it seems like I get busy working on this critical project or that show stopper bug. New stuff comes out and I skim the titles and decide “I’ll come back and dig in in a few days.” Somehow I rarely go back and dig in. The new features I pick up mostly come from things others put in the code or internet searches to fix an immediate issue.

Watching Tom go through feature after feature it seems like he was reading straight off of my “I’ll get to it” list. Of course, his explanations of the examples are so good that I came away with a better understanding of the things I still don’t know, but at least now I know more about what I don’t know.

Next, I went to Team Development in the Cloud: Introduction to Oracle Developer Cloud Service, by Brian Fry.

Brian gave a great overview of the tools available in the Development Cloud offering. I’ve been using some of them for about a month but like I mentioned above, there were a few that I plan to get too soon. Now, thanks to Brian, I’ll have a head start in figuring them out. But it looks like there’s enough in there, that my list just grew a bit more.

One thing I would like to see is an option to use Gradle, I’m not a big Maven fan, but given everything in the suite I’m sure I can make do with it.

It is nice to just pop an entire development environment up as a service, I can see this saving me so much time.

I’m really looking forward to the IDE in the cloud feature when it comes out. Maybe I can scale down what I require in a laptop.

For my last session of the day I attended Galo Balda’s presentation, Getting Started with Row Pattern Matching in Oracle 12c.

Another thing on my list. I really need to take some time an work on that list.

Galo had several great examples and use cases that really showed me how MATCH_RECOGNIZE can be much more useful than I previously thought. He set a good pace throughout the presentation; fast enough to keep it interesting but it allowed enough time to absorb each example.

I look forward to more from Galo.

I floated in and out of each community event in the evening.  Met quite a few people and had a great time.