database
GoLang: Inserting records into Oracle Database using goracle
In this blog post I’ll give some examples of how to process data for inserting into a table in an Oracle Database. I’ve had some previous blog posts on how to setup and connecting to an Oracle Database, and another on retrieving data from an Oracle Database and the importance of setting the Array Fetch Size.
When manipulating data the statements can be grouped (generally) into creating new data and updating existing data.
When working with this kind of processing we need to avoid the creation of the statements as a concatenation of strings. This opens the possibility of SQL injection, plus we are not allowing the optimizer in the database to do it’s thing. Prepared statements allows for the reuse of execution plans and this in turn can speed up our data processing and applications.
In a previous blog post I gave a simple example of a prepared statement for querying data and then using it to pass in different values as a parameter to this statement.
dbQuery, err := db.Prepare("select cust_first_name, cust_last_name, cust_city from sh.customers where cust_gender = :1") if err != nil { fmt.Println(err) return } defer dbQuery.Close() rows, err := dbQuery.Query('M') if err != nil { fmt.Println(".....Error processing query") fmt.Println(err) return } defer rows.Close() var CustFname, CustSname,CustCity string for rows.Next() { rows.Scan(&CustFname, &CustSname, &CustCity) fmt.Println(CustFname, CustSname, CustCity) }
For prepared statements for inserting data we can follow a similar structure. In the following example a table call LAST_CONTACT is used. This table has columns:
- CUST_ID
- CON_METHOD
- CON_MESSAGE
_, err := db.Exec("insert into LAST_CONTACT(cust_id, con_method, con_message) VALUES(:1, :2, :3)", 1, "Phone", "First contact with customer") if err != nil { fmt.Println(".....Error Inserting data") fmt.Println(err) return }
an alternative is the following and allows us to get some additional information about what was done and the result from it. In this example we can get the number records processed.
stmt, err := db.Prepare("insert into LAST_CONTACT(cust_id, con_method, con_message) VALUES(:1, :2, :3)") if err != nil { fmt.Println(err) return } res, err := dbQuery.Query(1, "Phone", "First contact with customer") if err != nil { fmt.Println(".....Error Inserting data") fmt.Println(err) return } rowCnt := res.RowsAffected() fmt.Println(rowCnt, " rows inserted.")
A similar approach can be taken for updating and deleting records
Managing Transactions
With transaction, a number of statements needs to be processed as a unit. For example, in double entry book keeping we have two inserts. One Credit insert and one debit insert. To do this we can define the start of a transaction using db.Begin() and the end of the transaction with a Commit(). Here is an example were we insert two contact details.
// start the transaction transx, err := db.Begin() if err != nil { fmt.Println(err) return } // Insert first record _, err := db.Exec("insert into LAST_CONTACT(cust_id, con_method, con_message) VALUES(:1, :2, :3)", 1, "Email", "First Email with customer") if err != nil { fmt.Println(".....Error Inserting data - first statement") fmt.Println(err) return } // Insert second record _, err := db.Exec("insert into LAST_CONTACT(cust_id, con_method, con_message) VALUES(:1, :2, :3)", 1, "In-Person", "First In-Person with customer") if err != nil { fmt.Println(".....Error Inserting data - second statement") fmt.Println(err) return } // complete the transaction err = transx.Commit() if err != nil { fmt.Println(".....Error Committing Transaction") fmt.Println(err) return }
GoLang: Querying records from Oracle Database using goracle
Continuing my series of blog posts on using Go Lang with Oracle, in this blog I’ll look at how to setup a query, run the query and parse the query results. I’ll give some examples that include setting up the query as a prepared statement and how to run a query and retrieve the first record returned. Another version of this last example is a query that returns one row.
Check out my previous post on how to create a connection to an Oracle Database.
Let’s start with a simple example. This is the same example from the blog I’ve linked to above, with the Database connection code omitted.
dbQuery := "select table_name from user_tables where table_name not like 'DM$%' and table_name not like 'ODMR$%'"
rows, err := db.Query(dbQuery)
if err != nil {
fmt.Println(".....Error processing query")
fmt.Println(err)
return
}
defer rows.Close()
fmt.Println("... Parsing query results")
var tableName string
for rows.Next() {
rows.Scan(&tableName)
fmt.Println(tableName)
}
Processing a query and it’s results involves a number of steps and these are:
- Using Query() function to send the query to the database. You could check for errors when processing each row
- Iterate over the rows using Next()
- Read the columns for each row into variables using Scan(). These need to be defined because Go is strongly typed.
- Close the query results using Close(). You might want to defer the use of this function but depends if the query will be reused. The result set will auto close the query after it reaches the last records (in the loop). The Close() is there just in case there is an error and cleanup is needed.
You should never use * as a wildcard in your queries. Always explicitly list the attributes you want returned and only list the attributes you want/need. Never list all attributes unless you are going to use all of them. There can be major query performance benefits with doing this.
Now let us have a look at using prepared statement. With these we can parameterize the query giving us greater flexibility and reuse of the statements. Additionally, these give use better query execution and performance when run the the database as the execution plans can be reused.
dbQuery, err := db.Prepare("select cust_first_name, cust_last_name, cust_city from sh.customers where cust_gender = :1") if err != nil { fmt.Println(err) return } defer dbQuery.Close() rows, err := dbQuery.Query('M') if err != nil { fmt.Println(".....Error processing query") fmt.Println(err) return } defer rows.Close() var CustFname, CustSname,CustCity string for rows.Next() { rows.Scan(&CustFname, &CustSname, &CustCity) fmt.Println(CustFname, CustSname, CustCity) }
Sometimes you may have queries that return only one row or you only want the first row returned by the query. In cases like this you can reduce the code to something like the following.
var CustFname, CustSname,CustCity string err := db.Prepare("select cust_first_name, cust_last_name, cust_city from sh.customers where cust_gender = ?").Scan(&CustFname, &CustSname, &CustCity) if err != nil { fmt.Println(err) return } fmt.Println(CustFname, CustSname, CustCity)
or an alternative to using Quer(), use QueryRow()
dbQuery, err := db.Prepare("select cust_first_name, cust_last_name, cust_city from sh.customers where cust_gender = ?") if err != nil { fmt.Println(err) return } defer dbQuery.Close() var CustFname, CustSname,CustCity string err := dbQuery.QueryRow('M').Scan(&CustFname, &CustSname, &CustCity) if err != nil { fmt.Println(".....Error processing query") fmt.Println(err) return } fmt.Println(CustFname, CustSname, CustCity)
18c is now available (but only on the Cloud)
On Friday afternoon (16th February) we started to see tweets and blog posts from people in Oracle saying that Oracle 18c was now available. But is only available on Oracle Cloud and Engineered Systems.
It looks like we will have to wait until the Autumn before we can install it ourselves on our own servers 😦
Here is the link to the official announcement for Oracle 18c.
Oracle 18c is really Oracle 12.2.0.2. The next full new release of the Oracle database is expected to be Oracle 19.
The new features and incremental enhancements in Oracle 18c are:
- Multitenant
- In-Memory
- Sharding
- Memory Optimized Fetches
- Exadata RAC Optimizations
- High Availability
- Security
- Online Partition Merge
- Improved Machine Learning (OAA)
- Polymorphic Table Functions
- Spatial and Graph
- More JSON improvements
- Private Temporary Tablespaces
- New mode for Connection Manager
And now the all important links to the documentation.
To give Oracle 18c a try you will need to go to cloud.oracle.com and select Database from the drop down list from the Platform menu. Yes you are going to need an Oracle Cloud account and some money or some free credit. Go and get some free cloud credits at the upcoming Oracle Code events.
If you want a ‘free’ way of trying out Oracle 18c, you can use Oracle Live SQL. They have setup some examples of the new features for you to try.
NOTE: Oracle 18c is not Autonomous. Check out Tim Hall’s blog posts about this. The Autonomous Oracle Database is something different, and we will be hearing more about this going forward.
12.2 DBaaS (Extreme Edition) possible bug/issue with the DB install/setup
A few weeks ago the 12.2 Oracle Database was released on the cloud. I immediately set an account and got my 12.2 DBaaS setup. This was a relatively painless process and quick.
For me I wanted to test out all the new Oracle Advanced Analytics new features and the new features in SQL Developer 4.2 that only become visible when you are using the 12.2 Database.
When you are go to use the Oracle Data Miner (GUI tool) in SQL Developer 4.2, it will check to see if the ODMr repository is installed in the database. If it isn’t then you will be promoted for the SYS password.
This is the problem. In previous version of the DBaaS (12.1, etc) this was not an issue.
When you go to create your DBaaS you are asked for a password that will be used for the admin accounts of the database.
But when I entered the password for SYS, I got an error saying invalid password.
After using ssh to create a terminal connection to the DBaaS I was able to to connect to the container using
sqlplus / as sysdba
and also using
sqlplus sys/ as sysdba
Those worked fine. But when I tried to connect to the PDB1 I got the invalid username and/or password error.
sqlplus sys/@pdb1 as sysdba
I reconnected as follows
sqlplus / as sysdba
and then changed the password for SYS with containers=all
This command completed without errors but when I tried using the new password to connect the the PDB1 I got the same error.
After 2 weeks working with Oracle Support they eventually pointed me to the issue of the password file for the PDB1 was missing. They claim this is due to an error when I was creating/installing the database.
But this was a DBaaS and I didn’t install the database. This is a problem with how Oracle have configured the installation.
The answer was to create a password file for the PDB1 using the following
Installing Oracle 12.1.0.2 on Windows 64bit
The following steps are what I did for installing 12.1.0.2 on Windows.
1. Download the Oracle installation ZIP files from the Oracle Downloads page.
2. Unzip the two 12c downloads files into the same directory.
3. Go to the newly created directory (it is probably called ‘database’) and you will find a file called setup.exe. Double click on this file.
After a couple of seconds you will see the Oracle Database 12c splash screen.
4. Step 1 : Configure Security Updates : Un-tick the tick-box and click the Next button. A warning message will appear. You can click on the Yes button to proceed.
5. Step 2 : Installation Options : select the Create and Configure a Database option and then click the Next button.
6. Step 3 : System Class : Select the Server Class option and then click the Next button.
7. Step 4 : Grid Installation Options : Select the Single Instance Database Installation option and then click the next button.
8. Step 5 : Select Install Type : Select the Typical install option and then click the Next button.
9. Step 6 : Oracle Home User Selection : Select the Use Windows Built-in Account option and then click the Next button. A warning message appears. Click the Yes button.
10. Step 7 : Typical Install Configuration : Set Global Database Name to cdb12c for the container database name. Set the Administrative password for the container database. Set the name of the pluggable database that will be created. Set this to pdb12c. Or you can accept the default names. Then click the Next button. If you get a warning message saying the password does not conform to the recommended standards, you can click the Yes button to ignore this warning and proceed.
11. Step 8 : Prerequisite Checks : the install will check to see that you have enough space and necessary permissions etc.
12. Step 9 : Summary : When the prerequisite checks (like checking you have enough space and privileges) are finished you will get a window like the following.
13. Step 10 : Install : You are now ready to start the install process. To do this click on the Install button in the Summary screen.
You can now sit back, relax and watch the installation of 12.1.0.2c (with the in-memory option) complete.
You may get some Windows Security Alert windows pop up. Just click on the Allow Access button.
Then the Database Configuration Assistant will start. This step might take a while to complete.
When everything is done you will get something like the following.
Congratulations you now have Oracle Database 12.1.0.2c installed.
But you are not finished yet!!!
14. Add entry to TNSNAMES.ORA : you will need to add an entry to your tnsnames.ora file for the pluggable database. There is be an entry for the container DB but not for the pluggable. Here is what I added to my tnsnames.ora.
The last step you need to do is to tell the container database to start up the pluggables when you reboot the server/laptop/PC/VM. To do this you will need to create the following trigger in the container DB.
sqlplus / as sysdba
CREATE or REPLACE trigger OPEN_ALL_PLUGGABLES
after startup
on database
BEGIN
execute immediate ‘alter pluggable database all open’;
END open_all_pdbs;
Restart your database or machine and you plug gage DB ‘pdb12c’ will no automatically start.
You are all finished now 🙂
Enjoy 🙂
Oracle Advanced Analytics and Oracle Fusion Apps
At a recent Oracle User Group conference, I was part of a round table discussion on Apps and BI. Unfortunately most of the questions were focused on Apps and the new Fusion Applications from Oracle. I mentioned that there was data mining functionality (using the Oracle Advanced Analytics Option) built into the Fusion Apps, it seems to come as a surprise to the Apps people. They were not aware of this built in functionality and capabilities. Well Oracle Data Mining and Oracle Advanced Analytics has been built into the following Oracle Fusion Applications.
- Oracle Fusion HCM Workforce Predictions
- Oracle Fusion CRM Sales Prediction Engine
- Oracle Spend Classification
- Oracle Sales Prospector
- Oracle Adaptive Access Manager
Oracle Data Mining and Oracle Advanced Applications are also being used in the following applications:
- Oracle Airline Data Model
- Oracle Communications Data Model
- Oracle Retail Data Model
- Oracle Security Governor for Healthcare
I intend to submit a presentation on this topic to future Oracle User Group conferences as a way of spreading the Advanced Analytics message within the Oracle user community. If you would like me to present on this topic at your conference or SIG drop me an email and we can make the necessary arrangement 🙂
Oracle 11.2g install on OLE 6.x
This notes are really just a reminder to myself of the typical “issues” that I encounter every time I do a new install of OEL 6.x and 11.2.0.4
These notes are in addition to the excellent installation instructions given by oracle-base.com: oel install, DB 11.2.0.x install
The notes listed below are just a reminder to myself of things that I seem to always have to look up. If you finish them useful then great.
1. Display issue & Installer not able to run
install says to do xhost +:0.0 this can give an error
instead do host +:0.0 and that should allow the installer to run
2. Now enough swap space when installer checks the pre-requisites
Need to add an addition 500M to the swap space
su (and then enter the password)
dd if=/dev/zero of=/tmp/swapfile bs=1M count=500
mkswap /tmp/swapfile
swapon /tmp/swapfile
exit (to return to the oracle user)
you can then turn off the extra space (if you really need to) after the install is finished
swapoff /tmp/swapfile
rm /tmp/swapfile
3. Post-Installation task
don’t forget the final step, to set to restart flag
as root
vi /etc/oratab
change the following line to have the Y at the end (instead of the N)
DB11G:/u01/app/oracle/product/11.2.0/db_1:Y
4. Set up the automated start and stop of the DB
Again Oracle-Base gives an excellent set of instructions for doing this. Click here.
Installing Oracle 12c on Windows 7 64bit
Here are the steps I when through to install Oracle 12.1c on Windows 7 64 bit.
- Unzip the two 12c downloads files into the same directory. I called this directory database
- Go down a couple of levels in the database directory until you come to the directory that contains setup.exe. Double click on this to start the installer.
- Step 1 – Configure Security Updates: Un-tick the tick-box and click the Next button. A warning message will appear. You can click on the Yes button to proceed.
- Step 2 – Software Update : select the Skip Software Updates option and then click the Next button.
- Step 3 – Installation Option : select the Create and Configure a Database option and then click the Next button.
- Step 4 – System Class: Select the Server Class option and then click the Next button
- Step 5 – Grid Installation Options : Select the Single Instance Database Installation option and then click the next button.
- Step 6 – Install Types : Select the Typical install option and then click the Next button.
- Step 7 – Installation Location : Select the Use Windows Built-in Account option and then click the Next button. An warning message appears. Click the Yes button.
- Step 8 – Typical Installation. Set Global Database Name to cdb12c for the container database name. Set the Administrative password for the container database. Set the name of the pluggable database that will be created. Set this to pdb12c. Or you can accept the default names. Then click the Next button. If you get a warning message saying the password does not conform to the recommended standards, you can click the Yes button to ignore this warning and proceed.
- Step 9 – Prerequisite Checks : the install will check to see that you have enough space and necessary permissions etc.
- Step 10 – Summary – You should now be ready to start the install. Click the Install button.
You can now sit back, relax and watch the installation of 12.1c complete.
You may get some Windows Security Alert windows pop up. Just click on the Allow Access button.
Then the Database Configuration Assistant will start. This step might take a while to complete.
When everything is done you will get something like the following
Now you are almost ready to start using your Pluggable 12c database on windows. The final two steps that you need to do is to add an entry to your tnsnames.ora file. You can manually do this if you know what you are doing or you can select Net Configuration Assistant under the Oracle –Ora12cDB Home 1 section of the windows menu. The second thing you need to do is to create a new user/schema.
Check out my previous blog post called ‘My first steps with 12c’ for how to do these last two steps. The ‘My fist steps with 12c’ post was based on installing 12c on Linux 6.
Oracle Magazine-Nov/Dec. 1998
The headline articles for the Nov/Dec 1998 edition of Oracle Magazine were on building web based applications and thin client computing. A large part of the magazine was dedicated to these topics. This was a bumper edition with a total of 152 pages of content.
Other articles included:
- There was a few articles on using Oracle 8i, including how to use Java in the Database, the Internet File System, Intermedia and Data Warehousing. Oracle 8i comes with over 150 new features
- There was a couple of articles on the Millennium Bug and how to approach such projects. There was also some advice for organisations who would have to look at how to deal with the introduction of the Euro currency in Europe.
- There was a section for articles on new product announcements from Oracle partners, including Quest, Nextek, Maxager, ObjectShare, Constellar (Warehouse Builder), Prism, DataMetrics, IQ Software, Eventus, DataMirror, Precise, Saville, DataShark, J-Database Exchange, Andataco, GeoMedia
- Oracle makes available Oracle 8i and the Application Server on a Linux platform for the first time.
- With Oracle 8i we have a number of ways of managing our constraints, including:
- Deferrable integrity constraints
- Non unique indexes for primary key and unique constraints
- Immediate constraint enabling
- Detecting lock and waiting transactions was always a task that consumed a lot of time for a DBA. A number of scripts was given to help you identify these and to resolve these problems.
- For allow of Oracle Certified DBAs out there. There was an article promoting the OCP DBA program and Exam. Some hints and tips about the exam were given, along with some practice questions.
- Plus there was 12 pages on adverts at the back of the magazine.
To view the cover page and the table of contents click on the image at the top of this post or click here.
My Oracle Magazine Collection can be found here. You will find links to my blog posts on previous editions and a PDF for the very first Oracle Magazine from June 1987.
BIWA Summit–9th & 10th January, 2013
The BIWA Summit will be on the 9th and 10th January, 2013. It is being held in the Sofitel Hotel beside the Oracle HQ at Redwood Shores, just outside of San Francisco.
The BIWA Summit looks to be leading event in 2013 focused on Analytics, Data Warehousing, Big Data and BI. If you are a data architect or a data scientist this is certainly one event that you should consider attending in 2013.
All the big names (in the Oracle world) will be there Tom Kyte, Mark Rittman, Maria Colgan, Balaji Yelmanchili, Vaishnavi Sashikanth, Charlie Berger, Mark Hornick, Karl Rexter, Tim and Dan Vlamis.
Oh and then there is me. I’ll be giving a presentation on the Oracle Data Scientist. This will be on the first day of the event (9th) at 11:20am.
For anyone interest in the Oracle Data Scientist World there are lots of presentations to help you get start and up to speed in this area. Here is a list of presentations and hands on labs that I can recommend.
As is typical with all good conferences there are many presentations on at the same time that I would like to attend. If only I could time travel.
This is a great event to start off the new year and for everyone who is thinking of moving into or commencing a project in the area. So get asking you manager to see if there is any training budget left for 2012 or get first dibs on the training budget for 2013.
Registration is open and at the moment the early bird discount still seems to be available. You can also book a room in the hotel using the registration page.
To view the full agenda – click here
Tom Kyte in Dublin 21st January 2013
Tom Kyte will be back in Dublin on the 21st January, 2013. He will be giving a number of presentations covering some of his popular Oracle Open World sessions and will also include a AskTom session
It will be a full day, kicking off at 9am and finishing around 3:30pm.
There is no better way to kick off the new year with a full day of FREE Oracle training and up skilling with Tom Kyte.
To register for the event send an email to marketing-ie_ie@oracle.com
As they say places are limited, so book early, I have 
so I’ll see you there.
Review Oracle Magazine- July/August 1998
The headline articles for the July/August1998 edition of Oracle Magazine were on designing, developing and delivering Data Marts using the Oracle Database and related tools. The main article looks at the different phases of developing a data mart in 90 days.
One of the key messages in these articles is to keep focused on the business problem and that the technology part is very minor in this. This message is still vey key to the analytics and big data world, keep focused on the business problem.
Other articles included:
- Oracle ships JDeveloper Suite. It included App Builder for Java, Oracle Application Server 4.0, Oracle 8 Database Server, Symantec’s Visual Page HTML editor, and a one year developer’s membership in the Oracle Technology Network. Yes there used to be a cost to be a member of OTN!!!.
- Oracle We Developer Suite wins the PC Magazine Editor Choice award. The suite comes with full development licences for Designer/2000 Release 2.1, including object extensions, Developer/2000 Release 2.1, Oracle App Builder for Java, Oracle Application Server 3.0, Oracle Database Server (releases 7 and 8) and the Oracle Web Development Kit
- Oracle Support announce plans to give read only access, via the web, to its Bug database.
- There was an advert for TOAD when it was still freeware and provided by ToadSoft.
- Security management for Oracle 8, has been increasing in importance over the past few years. For all those people who have some security responsibilities, here are some key elements for database security: System security, Data security, User security, Password management and System auditing. Security is more than just providing a Firewall.
- Building Message-based apps with Oracle 8’s Advanced Queuing, involves 5 main steps, including:
- Start the server’s AQ background process
- Create a database user account to manage queues
- Create a user-defined type for application messages
- Create a queue table and a corresponding queue of the user defined message type
- Build the application to enqueue and dequeue messages of the user defined message type
- For the DBAs there was an article on Fast Full Index Scan, how to enable it and gives a number of examples of the hints including the index_fss.
To view the cover page and the table of contents click on the image at the top of this post or click here.
My Oracle Magazine Collection can be found here. You will find links to my blog posts on previous editions and a PDF for the very first Oracle Magazine from June 1987.
- ← Previous
- 1
- 2
- 3
- …
- 6
- Next →
Oralytics 
You must be logged in to post a comment.