#DOAG2016

My last event this year was DOAG 2016 in Nuremberg. Since the event was in Germany it all started with a…beer…

beer

DOAG is one of the best organized user group events in this planet! And the venue is the best possible one with several floors each having its own restaurant for lunches. Also DOAG has a great number of attendees meaning you will meet plenty of friends. I started it by meeting (accidentally🙂 ) Christian Berg at the railway station before the event even started!

christianberg

I had breakfast with Tanel Poder…

tanel

And during the event I met many, many friends like Alex Nuijten:

alex

2*Martin, Neil, Mathias,…

martin

And some more friends at the dinner organized by Tirthankar and Sonja. Thank you! It was such a nice evening: great company, delicious food.

Dinner.JPG

Oh I did present too😀

Again two talks: Database designing with Data Modeler, where I gave one of by books away as usual:

heli1

and the favourite features of SQL Developer in a huuuuge room:

heli2

Great event! Amazing presentations and wonderful people. Thank you!

— Heli

EOUC Leaders’ meeting at #DOAG2016

Already the third time DOAG was helping the EMEA Oracle Usergroup Community (EOUC) leaders to meet face-to-face during the big event of DOAG (the German Oracle user group). Thank you DOAG for your amazing support for the community!

ug1

As always the meeting was a great success: 31 participants from 24 different user groups sharing their experience and collaborating. This time we also had two special guests from Mark Hurd’s office to hear our feedback on Oracle Cloud.

The day was full of interesting topics starting from improvement ideas on communication between user groups to our appearance at the OOW2017. We spoke about our new magazine (feel free to sign up, it’s free and extremely interesting http://www.oraworld.org/home/ ).

Thank you for attending the meeting and being so active! And thank you DOAG for making this possible!

ug2

And the mandatory WIT in user groups photo🙂

ug3

 

 

 

 

#SANGAM16

In November I also attended the Sangam the first time. It all actually started in Norway at the OUGN event earlier this year. Sai from AIOUG was there and attended my session. Afterwards he came to me and said: “Heli, we need you in India!”. Because of Sai I was willing to skip one of my favourite events, BGOUG, that was at the same time with Sangam and decided to visit India.

I am glad I did. I really, really liked India! And Sangam was an amazing event. And I learned a lot about different cultures! In Delhi (I changed the plane there) I learned that you must have a tag (for stamping) in every hand luggage and in Bangalore I learned that in Finland we do not have any rush hours…

trafic

The venue for the event was a lovely hotel in Bengaluru:

hotel

Sai was extremely kind to us and organized us a wonderful sightseeing tour:

sightseeing

After the sightseeing tour we were ready for Sangam16!

speakerswaiting

One of the keynotes during the event was a guru explaining us the technology of happiness.

keynote

It was an interesting presentation. We also tried meditation and I am pretty sure I fell asleep🙂

I gave again two sessions: my favourite SQL Developer features and database designing with Data Modeler.

heli.JPG

It was a great event!

We enjoyed the ACE dinner:

acedinner

and plenty of quality time with great people:

team

Thank you very much Sai and the AIOUG team, OTN and the ACE program for letting me to experience this! I really hope I can visit Sangam one day again!

— Heli

DDLs for Table synonyms

In my company we always define synonyms for all the tables. I do not want to manually define them and actually I do not need to see them in my Browser. Is there a way to get the DDLs generated for those synonyms automatically, without defining anything?

Yes, use Table DDL Transformations.

syn1

Define the name for the Table DDL Transformation Script (in this example PrivateSynonym), define when it will be triggered (in this example After Create, meaning it will be the last DDL for the table), type the script:

syn2

Press Save. Then you can test the script by selecting one table from the Table to Test list and pressing Test.

This is what I got:

syn3

NOTE: the synonym is only created for the DDL not the design, you will not see any synonyms in the Browser.
To get the DDL generated select this script in DDL generation:
syn4
Select the tables you want to generate the synonyms and press OK.
P.S. If the DDL File Editor shows blank you might have hit a known bug.
Workaround: Go back to Table DDL Transformations and select the script. Select After Create, type a space, remove it and save the script again.
You can use this script written by Marko Helskyaho, or write your own. This one creates a private synonym for all the tables selected for DDL generation.

/*
variable ddlStatementsList should be used to return the list with DDL statements
that are created by script – as shown below:
ddlStatementsList.add(new java.lang.String(ddl));
other available variables:
– model – relational model instance
– pModel – physical model instance
– table – the table in relational model
– tableProxy – table definition in physical model
*/
var ddl;
var lname;
var sname;
prompt = model.getAppView().getSettings().isIncludePromptInDDL();
useSchema = model.getAppView().getSettings().isIncludeSchemaInDDL();
if(model.getStorageDesign().isOpen()){
if(useSchema){
lname = tableProxy.getLongName();
sname = tableProxy.getName();
}else{
lname = tableProxy.getName();
sname = tableProxy.getName();
}
}else{
if(useSchema){
lname = table.getLongName();
sname = tableProxy.getName();
}else{
lname = table.getName();
sname = tableProxy.getName();
}
}

if(prompt){
ddl= “\nPROMPT Creating Synonym for ‘”+lname+”‘;\n”;
}else{
ddl = “\n”;
}
ddl = ddl +
“CREATE OR REPLACE SYNONYM Heli.”+sname +”\n”+
“FOR “+lname +”\n” +
“;”

ddlStatementsList.add(new java.lang.String(ddl));

#OTNTOUR China, Beijing

In the beginning of November I attended my first OTN APAC Tour and was speaking in Beijing. It was a busy event:

beijing1

And the first ever I could not read the agenda: I needed to ask the local people where and when do I speak😀

beijing2

And I did speak! My first session was about my favourite features of SQL Developer and the second one was about database designing using Data Modeler:

beijing_heli

The Data Modeler session was the last one for the whole event and for some reason the previous session overrun about 45 minutes😦 So my session started at the time I was supposed to be finishing…but no worries people stayed and I had a great audience for the session.

beijing_heli2

This was my first visit in China and I want to thank Tony Chen (user group liaison for APAC) for the great hospitality and taking the time (from his extremely busy schedule) to show me a little bit of China. And of course Jennifer for being such a fun company to travel with!

tonyjen

Thank you all my fellow speakers, it was a joy to spend time with you and to learn.

And thank you to the organizers for the great event and the amazing speakers’ dinner! When I was thinking we are done with the eating more food was brought to the table🙂

beijingspeakersdinner

And the same food fest was also with the breakfast. I travel a lot and I see many different kinds of breakfasts in hotels but I must say the one in Beijing was one of the best ever: I cannot imagine any food that was not served at the breakfast🙂 Just some examples:

beijing_breakfast1

beijing_breakfast2

Travelling is always good but I think this one was really eye-opening: before my trip I did not understand that I should be extremely happy to live in the country that has clean air. In Beijing I though the smog was bad when I arrived:

beijing_smog1

But I learned it can be even worse:

beijing_smog2

I really enjoyed my visit to China but due to the pollution I do not think I could live there. But from now on I will never take our fresh and clean air for granted.

Thank you OTN, the ACE Program and ACOUG!

— Heli

Comments in RDBMS

I have entered the descriptions in Data Modeler (Comments in RDBMS), for tables and columns, to be implemented in the database  but when I generate the DDL the comments are not there. What am I doing wrong?

Remember the Preferences? That’s the answer again.

Go to Tools, Preferences.

Select Data Modeler, DDL and enable Generate Comments in RDBMS.

commentsddl

Now that you generate the DDLs they will include the Comments in RDBMS you have entered in Data Modeler:

commentsddl2

 

 

 

How to define a prefix for table names?

I want to define a prefix for my table names. Can that be done easily? Yes🙂

Right-click the name of the relational model in Browser and select Change Object Names Prefix:

prefix1

Select Prefix replacement, type the prefix wanted to the New Prefix field and select Add new prefix. Select Tables. Then press Apply.

prefix2

Now all the tables in selected relational model will have the new prefix in the table name:

prefix3

You can use this also to add prefixes to the names of Views, FK Constraints, Columns, Indexes, or PK&UK Constraints.

Story of my iPhone(s)

If you follow me on twitter or see me in day-to-day life you know I had “some” problems with my iPhone5. But I did not want to upgrade to iPhone6, I rather waited for iPhone7. Just to give you an idea briefly: the battery needed to be recharged 4-5 times per day (42% meant that no point trying to use Sports Tracker or see your emails too often, the phone will die in less than an hour), the phone gets hot every now and then, the Wi-Fi was not working well and those problems forced me to reboot the phone at least 2-3 times a day and so on. I am sure you already understand how frustrated I was…

A bit more than a month ago one evening my iPhone stopped working, totally. I was just about to go to the gym but… I need my phone in my work so an option not having a phone tomorrow was not an option and going to gym without being able to use Sports Tracker…no… So I really had no other option but to try to recover the phone…

I was really lucky: I had a backup from that morning. The backup was in our office and I was at home so the first thing was to drive to the office and find the backup. Then the recovery started. It was reaaaally slow. By the estimation of iTunes I knew it will take hours… I decided to go for a run while it is recovering. I was wearing my iWatch for the run. It took me 7 hours to complete the recovery (not the run😀 ). It was early in the morning when I finally had my iPhone working again (thank you, thank you, I was so happy about it) and then the phone decided to reset my watch and update the data. What I learned during this exercise was that the phone is the master and the watch is the slave. What does that mean? My running was gone! Like I never run. The watch never had a chance to tell the phone about my run before the phone erased my watch.

But never mind, everything else was recovered and I had my phone and watch working again. I was really tired in the office, but happy🙂

I was in San Francisco when the new iPhone came out but immediately heard they run out from the stock. I did not want to wait anymore so I told my elder son to order me one to home. It took several weeks (4?) to arrive and of course it arrived while I was travelling in Poland. When I returned home I was too busy to start setting up the new phone, especially since I already learned it is not a job I can do in a minute… Last evening (during a meeting) I decided it is the time to start using the new phone…

Just a note: I had three top level IT experts in the room (one dba and two developers) plus my elder son who is very smart with technology so please do not even think for a second “there you see when a woman is trying to be technical”…

First I plugged the phone to my computer (which had the backup of my previous phone). The iTunes told me the iTunes version I have is too old for my new phone and must be updated. Ok, let’s update. After a while it tells me the signature for iTunes is faulty and therefore it cannot be updated. What next? Google. The advice I found was simply install the new version, do not update. Ok, I did that. Now the iTunes is saying “cannot connect to Heli’s iPhone5”. What? Why do you want to connect to iPhone5? The sim is already on the new one and the new phone is connected to iTunes, the backup should be in this computer. Think! OK, got it, the screen of the iPhone7 is locked (insert the pin) and iTunes calls it Heli’s iPhone5 because of the backup. Here we go, backup restored in not too long. But what about my iWatch? It looks happy and working but no messages showing up on the screen. Yeah right you need to pair it with the new phone. But how to do it?

We all try but we have no idea how to do it. Google. Then I understand I must reset the watch (that is scary, it tells me that all the data will be gone but I already learned it last time that the data on my watch is useless and the phone always wins so ok) and do the pairing. Next thing (surprise) is that the phone tells me the software on my watch is too old and must be updated. Ok, do it. I wait, I wait, I wait… I finally go to bed.

Now it’s the next morning and it is still not ready. I started the pairing again…

This morning I was luckier than Mark Rittman (https://medium.com/mark-rittman/the-story-behind-the-ikettle-the-eleven-hour-struggle-to-make-a-cup-of-tea-and-why-it-was-all-769144d12d7#.vme8k1ybp): I was able to have my coffee, oatmeal and rye-bread while waiting for my “iThings” to start working. I was able to read my emails and follow social media with my laptop. So I really cannot complain, except that I do not have an extra 2h in a morning for this. I remember the times when the phones just worked and starting to use a new phone was easy. I miss those days! I solve technical problems as part of my work but I DO NOT WANT to solve problems with my phone or my watch. I simply want them to work…

Now, 2h later, my phone says it is not able to find updates for the watch and asks me to start over… Great! Have a wonderful day! I might continue in the evening or I might stop using my iWatch…let’s see…

Cheers,

Heli

P.S. I do not give up easily…trying one more time. The error message was not an error message but saying all is good now and you can start pairing your phone and watch. At least I hope so… Dear Apple, please work on your wording, you make our life very hard now. Missleading error messages are a pain! I left home too late because of this mess but with both the phone and the watch, and both working.

Comparing, Part 1: A database to a database

I am writing a serie of posts about different compares: comparing databases and comparing database designs. The possibility to automatically compare different versions of the database or a design is very crutial to have good quality designs and databases.

This first post is about comparing two databases. Maybe the production and the test?

If I want to compare two databases to each other, I use SQL Developer and the Database Diff functionality you can find under Tools menu.

comp1

First select the Source Connection and the Target Connection and select the parameters the way you want.

comp2

Press Next (In Finnish that is Seuraava🙂 , see SQL Developer even speaks Finnish :-D) )

Select the database object to be compared and press Next:

comp3

You can specify objects:

comp4

Or select all by just pressing Next.

You can see what will be compared and what will not:

comp5

If you are happy with it just press OK, otherwice press Back and change your parameters.

In the Diff Report you can see in green all the new objects and in yellow all the objects that has been changes. If you select an object from the list you can see the exact change:

comp6

And if you select the Script tab you can see the DDL script to make that change to your target database:

comp7

Do you think this is a feature you should be using?😀

OTN Appreciation Day: Constraints

My favourite feature in a database is constraints: Primary Key, Foreign Key, Unique Key, Not NULL, etc.

Why? Because I like to have good quality data in my databases! The only way to have a good quality data is to design the databases and use constraints whenever possible.

To avoid duplicate data I use Primary Key and Unique Key Constraints, to avoid orphan records in child tables I use Foreign Key Constraints and to avoid data nobody can understand (NULLs!) I use Not NULL constraints. I can also define CHECK constraints to define a condition for each row in the table, the data is not accepted to the table unless it meets the criteria defined in a constraint. A simple example of a CHECK Constraint would be a list of values for a column, for example each row must have either “Yes” or “No” as a value for the column. A CHECK constraint can only be defined for columns in one table, not columns in several tables nor all the data in a table. What we do not have yet is a “CHECK constraint for several tables at a time”, “CHECK constraint for a whole table” or “CHECK constraint for the whole database”. This kind of constraint is called an assertion. If you want to vote for this important feature to be implemented in Oracle database, just go and vote: https://community.oracle.com/ideas/13028 !

These are just examples of a great feature. The database will look after the data quality for me, while I do more interesting things…

— Heli