Test your SQLs online

Nowadays I am trying to answer questions about SQL, PL/SQL and Oracle on StackOverflow . You can see my profile card below :

Recently I am seeing a new web site link in answers, while I am navigating through as much questions as possible. That site called SQLFiddle where you can create an online database and try your codes.

As you can see above it is pretty simple, and it is really easy to use. There is different products and different versions for some products you can choose between. While I am writing this post, exact list was like below:

MySQL 5.5.30
MySQL 5.6.6 m9
MySQL 5.1.61
Oracle 11gR2
PostgreSQL 9.1.8
PostgreSQL 9.2.1
PostgreSQL 8.4.12
PostgreSQL 8.3.20
SQLite (WebSQL)
SQLite (SQL.js)
MS SQL Server 2008
MS SQL Server 2012

Jake Feasel, creator of SQLFiddle, explains why he built the site as :

I found JS Fiddle to be a great tool for answering javascript / jQuery questions, but I also found that there was nothing available that offered similar functionality for the SQL questions. So, that was my inspiration to build this site. Basically, I built this site as a tool for developers like me to be more effective in assisting other developers.

How to use

So let’s talk about how to use SQLFiddle,

First of all, you select the product you want to use from combo box on top-left corner, next to site logo. Then you should create your schema, in left editor you can create tables and insert data to your tables. When you are done with coding click Build Schema button to run your code. Now you have your tables and data. You can write your queries in the right editor and run them. Results will appear below editors.

Limitation

There is a limitation with MySQL, that Jake Feasel explains in About page. On the right editor you can only use select if you are using MySQL, with other products feel free to use all DML operations.

Login

There are around 12 different log-in options like G+ and OpenID. When you login to site, you can see your Fiddle history, and your favorite fiddles. There is no other advantages of logging in yet.

That’s all for today. Do not hesitate to comment.

P.S. I am preparing new ODI post but I am really busy with my work these days. So follow my blog for updates.

Online Tools for Productivity

Hello all,

Today I am going to write a non-technical post. The online tools I use for productivity in my college and work life, which make my life simpler and easier. All the tools I will mention below have free plans or demo versions as well as they have premium, or paid services.

Mailing

For mailing, my first choice was GMail, which has been developed and served by Google. It was a huge thing to have 2GB inbox space when GMail first published. I remember around eight years ago when I first heard of it. There was only one way tou subscribe to GMail, which is getting an invitation. Since these days, it became an public service, Inbox space enlargened to 10GBs and still growing.

GMail then had more abilities, and integrated to new services like calendar, docs and etc.

Outlook.com is the new e-mail service of Microsoft. And I am pretty happy with this account and web mail interface. It is easy to use, has a clear interface, and nice features. Microsoft is going to turnover all hotmail accounts to outlook until the end of June 2013, says rumors on the web. Also it is nice that nowadays there are lots of options for e-mail addresses you can take. For example your_name.surname@gmail.com may be taken in gmail but it is quite possible to find your_name.surname@outlook.com address available. Also as Microsoft Office Outlook is a well known software, this e-mail addresses seems more professional.

Meetings And Collaboration

For meeting appointments I use Doodle.com. You create a survey including times, send invitation to attendees, then they check their available time. So you can find a suitable time for all to make an appointment for your meeting or other event.

Scriblink is an online whiteboard, where all the attendees can add/drop something to the white board. Attendees can also chat in the chat pane.

Google Docs is like an online Office tool with Spreadsheets, Presentations, Word Processor. You can view, edit and share documents via Google Docs. It recognizes and opens all common file extensions we use in daily life.

Skype is the software I use most for video-calls. It has chat, voice call and video call abilities, with conference support. It is easy to use and has ability to call phones also.

Dropbox is a well-known file sharing tool. I also use it for sharing files between our project group and also publishing files through internet for public download.

Facebook groups, we use Facebook Groups for really small project groups for innovative discussions, sharing videos and photos and ideas between our group. As everybody uses facebook on a daily basis nowadays, it is an easy and secure way to update whole group via Facebook. Also you can share documents and files, which is a great plus. And the most useful ability is to make your group hidden and closed.

Other Utilities

Evernote.com It is the most used utility for me, I use it on my Android phone and my iPod to write notes on the way. And use its web interface to check and edit my notes. I have all kinds of information on my evernote like new blog posts, project topics, my home’s utilization bills, etc.

Creately.com This is the website I use to draw. Drawing ER diagrams, workflows, GANTT charts etc. is very easy with using creately. So it is useful and light-weight.

TeamViewer Remote control is a need in IT environment, using my computer at home form work or from my mobile device is easy with TeamViewer. Some times you need something from your personal computer, or you need a connection without proxy servers, then TeamViewer works for me.

Google Calendar I am an android user. So I have an integrated phone with my Google account, which leads me to use Google Calendar for my appointments, it is nice to have a synchronized agenda in my pocket all the time.

Hootsuite.com This is a social media center for you. In free plan you can add up to five accounts, and control all accounts through online dashboard. It is nice to have all social media in one screen, control all your virtual presence, develop your personal brand and image.

That’s all, I am using these tools to get my productivity and collaboration boosted. Try them and comment me, or comment the online tools you like most.

ODI 11g: Step by Step Creating a User

In the last post here: http://www.canburaktumer.com/blog/odi-11g-step-by-step-master-and-work-repository-creation/ , we have created our master and work repository. As we only had one user, which is SUPERVISOR, we need other users to have a safe environment. For a safe environment give every user privileges, only what they need.

For example, an OPERATION ADMIN role should not be able to use designer tab to edit interfaces, packages and procedures. A DEVELOPER role should not have privileges to create or alter users in security tab.

Creating a Typical DEVELOPER

So now let’s create a DEVELOPER user now, who will have the rights to Designer , Operator and Topology tabs. First connect to your master or work repositories with SUPERVISOR.

Expand the Users accordion to see user list, in our case there is only SUPERVISOR in list. Click on the little man with plus on it, a pop-up menu will appear with only one option; New User. Click that option.

So this is the main screeen where you enter information of a new user. There is some little details I like to tell about. You can create an account with an expiration date. So when the day comes, user will become an invalid user. And its icon will be red. (See second picture below. I created DEVELOPER user with expiration date, and let it expire to have a screenshot.)

If you check Supervisor checkbox than this user will have SUPERVISOR privileges, and actually you don’t have to assign any other privileges to this user as it has all of them.

Last detail is the Password tex box, which you can not edit. There is a button below text box, which says Enter a Password. When you click it you’ll be able to create a password for user. Password also has an expiration option. You can create passwords that expire to increase level of security. Also there is other password policies you can set, but we will be talking about it in next post. By default ODI has only one password policy, which is ‘Passwords should have six or more characters.’

After selecting a password, we save and close and user appears in our user list.

Assigning Privileges to a User

Now it’s time for our user to have privileges a developer will need. We will assign privileges by using predefined roles, there is also a longer way to do it; giving privileges object by object like giving ‘view interface’, ‘edit interface’, ‘new interface’ privileges. But it is a longer method also it requires more attention and knowledge, so in this post we are going to use roles, as we are learning about the basics.

If you have used ODI before, but not assign a role to a user, it may become confusing in the ways of ODI’s user experience perspective. In ODI, when you are doing something in accordions, then usually you click, right-click or double click and open the edit window and do whatever you want. However, in security tab while you are assigning a profile to a user, you simply drag profile on to the user. It is a simple way to do it, on the other hand it is not the obvious way in terms of ODI’s user experience.

So I will drag and drop CONNECT, DESIGNER, METADATA ADMIN, OPERATOR and TOPOLOGY ADMIN privileges to our DEVELOPER user. Final view should be like below:

If I should briefly explain these privileges:

CONNECT : The basic profile to connect an ODI repository, it is like CREATE SESSION privilege on Oracle Databases. It has some more rights like viewing some objects. You can see objects if you click + next to CONNECT.

DESIGNER : This is where our codes are. There are interfaces, procedures, scenarios, packages, projects, variables in designer. And designer profile gives privileges to create, edit, delete ability for these objects.

METADATA ADMIN : This is actually about Model accordion. Model is the place where data stores’ metadata are being held. Like tables, files, web services and other data stores. METADATA ADMIN gives you ability of create, edit, view and delete of Model objects or Model folders.

OPERATOR : Operator has sessions’ information, load plans, scenarios … etc. Actually running codes, and their sessions are being held in this tab. By using operator you can see errors, see successful runs, re-run scenarios…

TOPOLOGY ADMIN : Topology is where you have connection information to data stores, TNS info for a table’s database or path of a file or URL of a web service. All connections are stored here. We will see topology tab in detail in upcoming posts.

So now we learned how we create a user, how to assign privileges to it. But how can we connect to repository with our new user. OK, nice question, let’s create a connection for our new user.

Creating a Connection

So we have already created connections, so I will make a quick review of it.

Click on connect to repository, you should see login window, click on green plus to create a new connection. Now enter your DEVELOPER user’s information like you see in figure below.

Our connection is ready.

Next post will be about security tab in detail. Before we begin to set up our environment for development, we should secure it.

Questions in the comment area please.

ODI 10g: Installation Errors

Installing ODI 10 on Windows 7

When you are trying to install ODI 10 on Windows 7, it will check Windows version, and raise an error like below:

“This version of operating system is not supported. Supported versions : 4.0,5.0,5.1,5.2,6.0. Your version: 6.1″

Here is the solution:

Open file %ODISETUP%setupWindowsDisk1installoraparam.ini

Find the lines below and add “,6.1″ at the end. (without quotation)

[Certified Versions]
#You can customise error message shown for failure, provide value for CERTIFIED_VERSION_FAILURE_MESSAGE
Windows=4.0,5.0,5.1,5.2,6.0

After modification line should be like this:
```
Windows=4.0,5.0,5.1,5.2,6.0,6.1
```
You are ready to install ODI 10g on Windows 7 now.

Source : http://gurcanorhan.wordpress.com/2011/02/07/installing-odi-to-windows-7/

ORA-01455 Error

Hello again,

Recently a colleague asked me about an ORA error he encountered. It was first time that I saw this error. But thanks to Google and OTN, I found a solution.

ORA-01455 : converting column overflows integer datatype

in our case the source of this error is the version difference between database and the database client. He was trying to export database using TOAD, which had an Oracle Client 10.2.0 in background. But database was Oracle11gR2. And this caused the ORA-01455 error. Updating the client solved the problem.

Also there is a known bug report about this error on OTN forums. Which has been fixed in Oracle DB 10.2.0 version. So if you have a newer version, but you get the error please check version compatibility between database and client.

There was another solution which is using ‘no audit export’. But we could not set this parameter in TOAD environment. If you are exporting your database directly using SQL, then this can be a solution for your case.

Thanks for reading, questions on comment section please.

ODI 11g : Step by Step Master and Work Repository Creation

Hello all,

So first post, first steps. We are going to create master and work repository for your Oracle Data Integrator. I assume that you have already have an ODI installation up and running and also an Oracle Database.

First I am going to create schemas for my repositories, you can put your repositories in one schema but it is common to have them in separate schemas. If you run the script below you will have two schemas for your repositories with all privileges.

create user ODIM_BLOG identified by ODIM_BLOG;
create user ODIW_BLOG identified by ODIW_BLOG;
grant all privileges to ODIM_BLOG;
grant all privileges to ODIW_BLOG;

Creating Master Repository

So if your schemas are ready, we can continue with the master repository. In the main ODI screen, go to File > New.

Master-1 — Master Repository Creation Step 1

Now you’ll see three options, we’ll continue with Master Repository Creation Wizard, select it and click OK to continue.

Master-2 — Master Repository Creation Step 2

Now it is time to setup our connection to database, you will need DBA connection to setup your repository. Fill in the required fields, you can see my blog environment’s information below. A little caution here: if you are going to use SYS user as DBA account, you have to fill DBA User area like ‘SYS as sysdba’ otherwise it will not connect. If you are using another DBA account then you don’t need to declare ‘as sysdba’. Hit the Test Connection button to see if you can connect.

There is another text box which asks for the repository ID, be sure to remember this ID, as you’ll also be asked for another ID while creating work repository. And it is important that all repositories have different IDs. As ODI does every operation with ID’s, it’s crucial not to have duplicated IDs.

Master-3 — Master Repository Creation Step 3

Next step is the generating a password for SUPERVISOR user, this user will be our ODI admin until you will create another user with SUPERVISOR privileges. It is an old tradition to set this user’s password as SUNOPSIS, it is also a tribute like behavior for ODI’s creator Sunopsis. I set it as SUPERVISOR to remember it easily.

Master-4 — Master Repository Creation Step 4

Here it goes, now we will wait until ODI finishes its job. If you check your master repository schema you can see it is being populated by SNP_% tables. SNP prefix is also a heritage from Sunopsis.

Master-5 — Master Repository Creation Step 5

And here we have created our master repository. Now it is time to setup a connection and create a work repository.

Master-6 — Master Repository Creation Step 6

Creating a Master Repository Connection

So for connecting master repository, click Connect to a Repository in ODI Home Screen. A little window will pop-up. This is the window where you can select between your predefined connection, edit them or create new connection. As we do not have any predefined connection, we will create one from scratch. Click on +(plus) sign to add a new connection.

MasterConnect-1 — Master Repository Connection Step 1

Now this is the connection details window. First blank will be the alias for this connection so it should be something to remind you to which repository you are connecting. First login information is for ODI user we are going to use, which is the SUPERVISOR in our case. And the second login information is for the Master Repository’s database schema, this is ODIM_BLOG in our case.Make sure Master Repository only radio button is checked before testing the connection. Because we do not have any other repositories yet. Test your connection then connect your master repository with SUPERVISOR. It’s time to move a step further.

MasterConnect-2 — Master Repository Connection Step 2

Creating Work Repository

When you are connected to aster repository go into Topology view and extend the repositories accordion. Right click on Work Repositories and click on the New Work Repository on pop-up menu.

Work-1 — Work Repository Creation Step 1

Enter your schema information, test your connection and move on. In our case we are going to use ODIW_BLOG schema as we want our repositories to be separated.

Work-2 — Work Repository Creation Step 2

Give a name and ID to your work repository. DO NOT forget to give a different ID from your master repository’s ID. Select your work repository type in this step.

There is two types of work repositories: Development and Execution. Execution repository does not have a development ability and does not have an active Designer tab. It only holds, scenarios, load plans, sessions and topology information. Development repository has an active Designer tab, which adds this repository to hold, create, edit, delete ability of interfaces, packages, variables, scenarios, knowledge modules, model objects like tables and views.

We will get into deeper about development and execution repository in another post. So I will make it short here.

Work-3 — Work Repository Creation Step 3

Now let ODI to finish creating work repository.

Work-4 — Work Repository Creation Step 4

Now ODI will ask if you want it to create a connection for new created work repository.

Work-5 — Work Repository Creation Step 5

When you click Yes, it will ask for connection name, which we said it is connection alias. And when you click OK. It will create a connection.

Work-6 — Work Repository Creation Step 6

Connecting to Work Repository

WorkConnect-1 — Work Repository Connection Step 1

ODI has created a connection for us. When you disconnect your master repository. (Under ODI > Disconnect) You can connect to work repository, click on Connect to a Repository, select your work repository connection from dropdown list. Password field will be empty, enter your SUPERVISOR password and click connect.

As we do not have any other user yet, we will connect to work repository by SUPERVISOR user.

Thanks for your patience to finish reading. Next ODI post will be about creating a user in detail, and assigning profiles to a user.

If you have any questions, please use comment section to ask me.