Intro to Oracle SQL for SQL Server Developers and DBAs

This is an Intro to Oracle SQL for SQLServer Developers and DBAs, and probably Part 1 of several blogs that I will write as I learn more, and make  more newbie mistakes.  You probably already know that Microsoft implementation (or extension) of the “standard” SQL is Tranact-SQL (T-SQL for short) and Oracle’s implementation is called PL/SQL.

1. The Server and the Clients

At the place I’m working we use PL/SQL Developer, a third party tool developed by “AllRoundAutomations”.  It wasn’t until a few weeks later that I learned that Oracle does have a client, which you download separately from the server.  Because I wanted to play with all the features of Oracle on my own safe environment (i.e. I probably don’t have privileges I need on clients main server, and I definitely don’t want to put any junk there), I installed Oracle 11g Express (it’s apparently the latest free version) on my own PC.   Once I had installed that, I was totally confused, because if this had been Microsoft SQL Server, the first thing I would do would be to create a database, add a table, stores some data, and run some queries.  Well, so far, that has taken me about three days to get there.

Install the server only gives you a command line SQL tool called “SQL Plus”, which is roughly the equivalent of Microsoft’s osql command line utility. I also had trouble getting it to connect.  More on that maybe in the future.

Oracle Client Issues

For some reason, I still cannot get “PL/SQL Developer” to connect to the XE database.  It wasn’t until several days later that I discovered the SQL client utilities and installed them.  The Oracle SQL client tool is called “SQL Developer”.  They had their own issues.  1) The icon in Win 7 startup/menu pointed to a non-existent .bat file, 2) I had to install the Java JDK for 32-bit windows to get it to run, 3) When I started it, it always was prompting for the Java.exe location.  First I learned that not any .Java.exe works, only the JDK/SDK version for 32-bit windows.  Then, it tries to store this path in the registery, so it bombs with access denied unless you do a “run as Administrator”.  Apparently this only needs to be done the first time.  4) After that I was able to create a table, but it was in the SYSTEM namespace, which was probably not a good idea.  (See below for how to solve that.)   I have to click this to run the tool: c:\Oracle11gClientApp\neal.walters\product\11.2.0\client_1\sqldeveloper\sqldeveloper\bin\sqldeveloper.exe  (since I specified c:\Oracle11gClientApp\neal.walters as the install path during the install).  I don’t understand why this would be specific to one user.

Choose your client

These are some of the popular clients.  Again, Oracle’s client is called “SQL Developer”.  There is TOAD, PL/SQL Developer, SQL Navigator, formerly by Quest, now owned by Dell, and an open source one called SQuirreL SQL.

Using Oracle’s “SQL Developer” is very similar to using Microsoft’s SSMS (SQL Server Management Studio).  Each is a client tool that you install.  The Oracle one is of course dependent on Java.  Similarly, Microsoft allows you to call “CLR” (Common Language RunTime from SQL), Oracle allows you to call Java from their SQL.

2. What is a Database & Instance?

So far, my learning has told me that a Oracle SQL Instance and an Oracle database are identical.  The default database and instance installed with Oracle 11g express is called “XE”.  Hopefully, I will soon learn more about this.  As Microsoft SQL users, we often used to having several databases on the same SQL Server Instance.  (By the way, Oracle 12c allows for that concept, with their “new” concept called pluggable databases.  You can plug additional databases into the initial instance/database.)

So how do large shops manage this?  My guess is that Oracle is more reliant on the Schema to separate types of data.  This feature exists in Microsoft SQL Server, and used by different extents in the companies that I’ve worked at.  Some SQL Users throw everything into one big schema, others try to group data.

As mentioned above, the first table I added went to the System Namespace. When I first opened “SQL Developer” I logged on with username=SYS and my password that I setup during the install.  After all, that’s the only user I had at this time.  Anxious as I was to create a table, I found “tables” and did “right-click” then “New table” much like would be done in MS SQL Studio Management Studio.

In the screen shot above, notice all the System tables on the left.  There are over 1680 of them.  You can do “Select * from dba_tables” if you want to get a count. Then notice in the red box, the first table I created, I went ahead and stored in this SYS schema, just to make sure any simple create would work, and it did.

There were other schema to choose from the list.   I have yet to learn why so many default schemas (including one called “HR” which already had some tables in it).

So then later, I forged out to try to figure out how to create another schema.  Well, it turns out that schemas are apparently users.  So I created an HR user, so I could create an test “Employee” table in the HR schema/namespace. I then created a user called “Neal”, closed “SQL Developer” (as I didn’t know how to switch users yet inside of it), then logged on as user=Neal.

 

In future blogs, I probably be able to explain more about multiple schema, users, profiles and granting security.  I logged on to Neal, created my table, and from now on when I logon as “Neal” – I only see “my stuff” in “SQL Developer”:

 

Interestingly enough, when you expand the users, you can see the tables tied to each user (see picture to the left).

I did a select of the Employees in the HR schema/user, and this is what I saw (below):

The only plausible conclusion is that this is the “Demo” database, something like the “Pubs” database or “Northwind” database in Microsoft-land.  Only it’s not a database, it’s a user/schema within the bigger XE database.

I later found out that my version of “SQL Developer” is 1.5.5 and heard that was about 8 years old.  Not sure why downloading the latest client gave me such an old version.  Here’s the latest and greatest (4.0.3 or 4.1 for early adopters), apparently you can download SQL Developer without the entire client: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html. The surprise in this download, is that it did not include an installer program, it was just a zip file of the raw files; so I guess you just pick whatever directory your prefer and unzip it there.  It seemed to work great. The newer version has a place on the “Tool/Preferences/Advanced” options to set the location (directory path) containing the desired TNSNames.ora file.

 

 

3. The magical and all important file: TNSNAMES.ORA

I will save most everything else for future blogs, but there is one more critical thing I learned.  One way to connect to a database (especially on another machine), is by means of the “tsnames.ora” file.  This file is found in your $ORACLE_HOME directory under network/Admin, such as c:\oracle\ora81\network\ADMIN\TNSNAMES.ORA  (the first one) or c:\Oracle11gClientApp\neal.walters\product\11.2.0\client_1\network\admin\sample\tnsnames.oRA (the second one, created with install of the Oracle Client).

<pre>
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MyPCName.myDomain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
</pre>

Note that 1521 is the default port for Oracle database.  There is a listener utility that runs, and could be changed to a different port number.

Yes, you can apparently have multiples of these (TNSNAMES.ORA) on your machine, and that’s one of the things I’m trying to sort out.  Apparently on my PC, “PL/SQL Developer” is using the first one.  I can prove this by copying the above text, and adding XEDUMMY1 to it.  My client also has all their databases in this tsnames.ora file.  I then see XE and XEDummy on the drop-down list of Databases.  I’m not sure databases are discoverable on their own, or if you must define the tnsnames.ora file.

In theory, the ORACLE_HOME is supposed to point to the one being used. There does not appear to be any environment variable called ORACLE_HOME.

 

 

 

 

 

 

 

 

 

 

 

 

This would imply that I have a third file: c:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora  on my disk, and sure enough, it does exist as well.

4. Shocking surprises.

1. Until Oracle 12c, it didn’t support AUTO_INCREMENT identity columns.

StackOverflow -How to create id with AUTO_INCREMENT on Oracle?   – and  New Oracle 12c Identity Columns –

2. The varchar2 string data type in Oracle cannot distinguish between a NULL and an empty string (i.e. empty strings are treated as null)

StackOverflow – Oracle not distinguishing between nulls and empty strings?

 

 

 

5. Oracle Release Names

Believe it or not, those little letters after the release are release numbers, they are mnemonics (or abbreviations) for major focus of that release.
And here is what they stand for:

8i = Internet – from 1999
9i = internet – from 2001
10g = grid      – from 2003
11g = grid      – from 2007
12c = cloud   – from 2013

 

The following keywords may also help you and others find this article be found:
Microsoft SQL vs Oracle SQL, T-SQL VS PL/SQL, MSSQL vs Oracle SQL.