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.6.6 m9
MS SQL Server 2008
MS SQL Server 2012
Jake Feasel, creator of SQLFiddle, explains why he built the site as :
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.
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.
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.
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.
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 firstname.lastname@example.org may be taken in gmail but it is quite possible to find email@example.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.
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.
TeamViewerRemote 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.comThis 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.
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.
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.
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.
Now you’ll see three options, we’ll continue with Master Repository Creation Wizard, select it and click OK to continue.
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.
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.
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.
And here we have created our master repository. Now it is time to setup a connection and create a work repository.
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.
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.
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.
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.
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.
Now let ODI to finish creating work repository.
Now ODI will ask if you want it to create a connection for new created work repository.
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.
Connecting to Work Repository
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.