Sample SQL

select 
    to_char(sysdate,'D') as NumDayOfWeek, 
    to_char(sysdate,'DY') as DayOfWeek, 
    to_char(sysdate,'Dy') as DayOfWeekLower, 
    case when to_char(sysdate,'D') = 3 then 3 else 1 end as DaysBack 
    from dual; 

Result of Query

Oracle_Day_Of_Week

Explanation

to_char(sysdate,’D’) returns 1 for Sunday, 2 for Monday etc…

“dual” is of course the dummy table used to run a select statement when you don’t have any other table you want to use.
I needed to create an extract so on Tuesday morning, it could include data three days back (Saturday, Sunday, and Monday), and on all other days of the week, it would include data from the prior day back.

Intro to Oracle for SQLServer Developers and DBAs – Part 3

After finally getting the GUI “SQL Developer” tool installed, see earlier blog Intro to Oracle SQL for SQL Server Developers and DBAs, I one day noticed an Oracle Icon on my desktop, clicked it, and found the web interface for managing Oracle. I’ve seen in the trainings from Pluralsight that 12c has a much nicer web interface; but at least now I now there is for the 11g Express Edition (XE).

The URL on my machine is: http://127.0.0.1:8080/apex/f?p=4950:7:3175421414017644::NO. I haven’t yet figured out what all the codes are. If I just put http://127.0.0.1:8080 the browser prompts for userid/password (not on a web page) and then gets unauthorized. If just try http://127.0.0.1:8080/apex then I get a valid web page, that wants me to logon to a workspace.

Major Screens in the Oracle Web Interface

Oracle 11g Web Interface – Home Screen

Oracle_11g_XE_Web_Interface_1

Oracle Web Interface – Storage

Oracle_11g_XE_Web_Interface_2

Oracle 11g XE Web Management – Sessions

Oracle_11g_XE_Web_Interface_3

 

 Oracle 11g XE Web Interface – Parameters

Oracle_11g_XE_Web_Interface_4

 

This is what happened when I tried just the URL: http://127.0.0.1:8080/apex

Oracle_11g_XE_Web_Interface_6_Workspace

 

 

 

 

 

 

 

 

 

 

Intro to Oracle for SQLServer Developers and DBAs

1. Oracle has something called V$ tables, which are essential similar to the Dynamic Management View (DMV) of SQLServer, Here are two examples:

select * from v$database;
select * from v$thread;

Result – this shows that my 11g Express Edition has both an instance name of “xe” and a database name of “XE”. See “Intro to Oracle SQL for SQLServer Developers and DBAs – Part 1” for an explanation of instances vs databases.

DBID NAME      CREATED   RESETLOGS_CHANGE# RESETLOGS_TIME
---------- --------- --------- ----------------- --------------
2749400775 XE        06-JAN-15            370965 06-JAN-15

THREAD# STATUS ENABLED      GROUPS INSTANCE
---------- ------ -------- ---------- --------------------------------------------------------------------------------
1 OPEN   PUBLIC            2 xe

2. Oracle has an odd “fake” table called “dual”.

Oracle always requires a table name, so this is the dummy “catch-all” table”  The links below will you the full scoop and history of why it was called “dual”.

select 5+5 from dual;
select sysdate into myDate from dual;

Additional explanation of the “Dual” table;

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

http://stackoverflow.com/questions/73751/what-is-the-dual-table-in-oracle

3. The following example will illustrate many things I put together, from dozens of examples found in many different sources.

Sample Code
set serveroutput on
select 5+5 from dual;
select sysdate from dual;
declare
   myDate date;
   myNumber number;
   myString varchar2(50) := 'Initial Value';
begin
   select sysdate into myDate from dual;
   myNumber := 10;
   myNumber := myNumber + 8;
   myString := 'New Value';
   -- use this function/method to print
   DBMS_OUTPUT.put_line ('The date is ' || myDate);
   DBMS_OUTPUT.put_line ('myNumber is ' || myNumber);
   DBMS_OUTPUT.put_line ('myString is ' || myString);
end;
SQL Output
5+5
----------
10

SYSDATE
---------
09-JAN-15

anonymous block completed
The date is 09-JAN-15
myNumber is 18
myString is New Value
Dbms Output Window:
The date is 09-JAN-15
myNumber is 18
myString is New Value

1. “set serveroutput on” will bring the needed to use the DBMS_OUPUT_put_line into the SQL result window.  Otherwise, it will only show in the “Dbms Output” window.  “:DBMS_Output.put_line()” is basically the SQLServer Print command. Use || to concatenate, and to actually see the results of these ‘print’ statement, you may have to manually “view” and then “enable” the DBMS-Output window in the SQL “GUI” tool that you are using.

2. “from dual” see prior point above.

3.  declare/begin/end – is the structure – variables have local scope to the block in which they are declared, and these blocks can be nested.

4.  Semicolons to end the lines are very important!  This small sample gives you an idea of where they go, and where they don’t go.

5. “Number” is a data type  You could use “PLS_INTEGER” is you want an integer.  VarChar2(nn) is the standard string type.

6. := is the assignment operator, to set the value of something equal to another value

7.  Comments work the same between PL/SQL and Transact-SQL (T-SQL)

 

 

 

 

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.

Oracle_SQLDeveloper_CreateTable_1

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).

Oracle_SQLDeveloper_CreateTable_2

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”:

Oracle_SQLDeveloper_CreateTable_3

 

Oracle_SQLDeveloper_CreateTable_4Interestingly 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):

Oracle_SQLDeveloper_HR_User

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.

Oracle_PLSQLDeveloper_LogonYes, 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.

RegEdit_OracleHome

 

 

 

 

 

 

 

 

 

 

 

 

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.

For whatever reason, I have escaped using any Oracle database in my 30+ year IT Consulting career, and now at my new client, I’m finally using it, at least to run simple queries. As a BizTalk developer, so much depends on the client. I have worked with clients that call MSSQL Stored Procs from BizTalk, and I have maintained those huge stored procs. In other clients that are more SOA based, BizTalk only calls web services, and the web services do all the communication with the SQL back end databases.

Today I was trying to find a “PARTS” table, which was like finding a needle in a haystack of about 1000 different tables. The client uses an ERP system called “IFS” and all I was told it was a special table created by the client outside of ERP system.

SELECT *   FROM dba_tables WHERE table_name LIKE '%PART%' and OWNER not like 'IFS%' and OWNER not like 'SYS%'
ORDER BY TABLE_NAME

I cannot include any client tables in this blog, but here’s an example of searching the Oracle SYS tables:

SELECT *   FROM dba_tables WHERE table_name LIKE '%COL%'  and OWNER not like 'IFS%'
ORDER BY TABLE_NAME

PLSQL_Example

You can take it one level deeper and join tables and columns, then search for either table name of column name:

select * from cols C
inner join dba_tables T on C.Table_Name = T.Table_Name 
where T.table_name like '%PART%'
order by T.Table_Name, C.Column_ID 

PLSQL_Example_Columns

 

When switching from Microsoft’s SQL Studio Manager to “PL/SQL Developer”, there are a few things to get used to.
In Microsoft, you use F5 to execute the SQL, but in PL/SQL you use F8.  If you hit “F5” like you maybe in the habit of doing, you will get the “Explain SQL” screen (which you can then close).

The other biggest difference, is that by default, “PL/SQL Developer” returns about 40 rows, which I believe is based on my screen size and resolution.

There are two icons that sort of look like double-arrows or the fast-forward button on a VCR/DVD/DVR player.
PLSQL_Toolbar1

Click the one on the left to get another 40 rows.  Click the one on the right to retrieve all the data.

There is not “top x” syntax in Oracle.  In Microsoft, you can say “Select Top 25 * from TableName”, but in PL/SQL you either use this feature built into the query tool, or you can manually enter “Select* from TableName where RowNum <= 25”.

 

PL/SQL can be licensed for about $216 per developer (2014 prices) and is sold by “AllAroundAutomations” http://www.allroundautomations.com/plsqldev.html (but I also found a download here – not sure if it just a trial or limited functionality: http://download.cnet.com/PL-SQL-Developer/3000-10254_4-10012692.html  A more expensive tools is “TOAD” (created by Quest and pricing available ($975 and up) from Dell here: https://shop.software.dell.com/682/purl-toad-for-oracle.

 

References:

1. http://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle/205746#205746  (for how to enumerate or list Oracle table names)

2, http://www.w3schools.com/sql/sql_top.asp  (for Top 25 vs rownum <= 25)