TomBecker.net

Max versus Greatest in Oracle

So I was trying to figure out the greatest of three dates in oracle. They were coming from 3 different columns across three different tables. So I figured I could do something like the following

SELECT MAX(A.date, B.date, C.date) FROM. . .etc

But apparently that doesn't work. The max function will not work across multiple columns. That's when I learned about the greatest function. Does the same thing as max, but lets you do it across multiple columns. The above query became

SELECT GREATEST(A.date, B.date, C.date) FROM . . .etc.

So, Max = one column, Greatest = multiple columns.

Labels: Oracle

posted by Tom Becker at 12/03/2008 | 0 Comments

Delete/Backspace not working in SQL Developer

So I was merrily working along in Oracle SQL Developer and all of a sudden the delete and backspace buttons stopped working.

A quick Google search showed this post. Apparently this is a common problem in SQL Developer and here is the quick fix:

Tools -> Preferences -> Accelerators -> Load Preset -> Default -> OK

Easy as that.

Labels: Oracle

posted by Tom Becker at 11/06/2008 | 0 Comments

Trimming leading characters in Oracle

Back in February I posted about concatenating values across multiple rows , and for those of you who have used that solution, you may have noticed that there are some leading slashes (or your delimiter of choice) that can be quite annoying. Another developer thought it looked sloppy so I trimmed off the leading characters.

The join and everything is the same as the previous article, the only thing that has changed is the select. Since there are no Left nor Right functions in Oracle we had to use substring as seen below:

SUBSTR(TYPELIST, (length(prj.TYPELIST) -2) * -1) as TYPELIST

Just change the 2 from above to however many characters you want to remove and you're all done.

Labels: Oracle

posted by Tom Becker at 10/28/2008 | 0 Comments

Looping Through a Comma Delimited String in Oracle

Normally when I have to loop through an list of items to insert into a database I use VB to loop through the array and then insert each item individually. However, this poses some problems if everything has to be done as one unit of work (for more background see previous post). So instead of doing the looping in VB, i had to do the loop in P/L SQL.

I found an article that told me how to do this quite easily. I typed in all the code and syntactically it was correct. I was happy since I thought I was going to have to do some weird string manipulation to go through each of items I was passing in. However what that article doesn't tell you, is that the comma_to_table function does not work for integers. It will throw the following error:

So after about 20 more minutes of research I came across another person having the same problem, and he wrote his own function that would act the same as the comma_to_table function but it would accept numbers in the delimited string.

His function is called delimstring_to_table (code example below) and it works beautifully. He also includes in his util package a table_to_delimstring function that will do the reverse. I haven't used it but I assume that it works just as well.

I am not going to include the package information on my site, you are going to have to visit the above link for that. I am just going to show the use of his functions:

--declare variables
temp_tab util.varchar2_table;
table_len number;

--convert comma delimated array to table.
util.delimstring_to_table('1,4,90,5,2', temp_tab, table_len, ',');

--loop through array and do inserts
for i in 1..table_len
loop
   INSERT INTO someTable
     (ID, description)
   VALUES
     (IN_ID, temp_tab(i));
end loop;

So as you can see looping is pretty easy in P/L SQL. You can use the comma_to_table in place of the delimstring_to_table function if your delimited string is just alpha characters and the loop will work exactly the same way.

Labels: Oracle

posted by Tom Becker at 7/17/2008 | 0 Comments

Transactions in VB.Net and Oracle

This is going to be kinda a two part post. Each talking about an approach to the same problem. But first some background.

I am creating a questionnaire that is being saved to the database in a mutli-step process. 4 steps to be exact. If any one of those steps fail I want to be able to roll back to the beginning so I don't have a stub of a questionnaire floating around. I need that process to be atomic.

I originally wrote it so that the 4 steps were independent of each other so that it could be as flexible as possible. And then the problem of the stubs came up. So I had to make it atomic. So since I already had four functions and 4 stored procedures in Oracle I created a transaction in .Net and just added it to my command objects (Part 1 of this article), but that didn't work for me. So then I went to combining all 4 procedures into one and using Oracle transactions (Part 2).

Part 1: .Net Transactions

.Net transactions aren't actually that hard, In my case I was using the OracleTransaction class but there is a SQLTransaction class that works the same way for those of you using MS-SQL. Essentially you tie your transaction to your connection and your command object. You must use the same connection for each command object, but that is pretty much your only limitation. The most common implementation that I've seen is just passing the connection and the transaction to all the functions that needed it and that is what I did as well. Code examples below:

Private Sub Save()
Dim conn As New OracleConnection("ConnString")
Dim trans As OracleTransaction

conn.Open()
Try
 trans = conn.BeginTransaction
 saveThingOne(conn, trans)
 saveThingTwo(conn, trans)

 trans.Commit()
Catch ex As Exception
 trans.Rollback()
End Try
End Sub

Private Sub saveThingOne(ByVal conn As OracleConnection, ByVal trans As OracleTransaction)
Using comm As New OracleCommand("Save_Thing_One_Stored_Procedure", conn, trans)
   comm.CommandType = CommandType.StoredProcedure
   comm.Parameters.Add("IN_ID", OracleType.Number).Value = intID
   comm.Parameters.Add("IN_THING_ONE", OracleType.VarChar, 60).Value = strThingONe

   comm.ExecuteNonQuery()
End Using
End Sub

Private Sub saveThingTwo(ByVal conn As OracleConnection, ByVal trans As OracleTransaction)
Using comm As New OracleCommand("Save_Thing_Two_Stored_Procedure", conn, trans)
   comm.CommandType = CommandType.StoredProcedure

   comm.Parameters.Add("IN_ID", OracleType.Number).Value = intID
   comm.Parameters.Add("IN_THING_TWO", OracleType.VarChar, 60).Value = strThingTwo

   comm.ExecuteNonQuery()
End Using
End Sub

However, in my current environment we use stored procedures for all of our database interactions. And that being the case it makes using the .Net transactions impossible to use. Now I don't know if the above code would work in a MS-SQL environment, but in an Oracle environment it does not. The best reason I can figure it is because Oracle treats a stored procedure as a transaction in and of it self. So when you get to the end of the stored procedure it does a full commit automatically...hence nothing to rollback. So I had to combine all 4 procedures into one massive one and use Oracle transaction statements to make sure everything is done at once.

Part 2: Oracle Transactions

There are a lot of articles out there about Oracle transactions, but I found that most of them had all the information that I needed separated out across multiple pages. So here it is all together.

As I mentioned above an Oracle stored procedure does a commit on successful completion of the procedure. But you can also explicitly tell it to do the commit any time you want to during its execution by calling the "commit;" command. You can also rollback at any point if you want to just by calling the "rollback;" command. If you put the two together you have the beginning of a nice way to ensure an atomic transaction.

The only other new part of the stored procedure you must add is a an exception clause. This as you may guess works much like a try/catch clause in VB. You can either catch specific Oracle errors by calling them by name, or you can do a catch all and just handle all of them the same way, which is what I did below. The only thing I would warn you about though, is that if you are having trouble with your procedure and are still testing I wouldn't put the exception clause in there quite yet. Oracle will catch and handle the error and end cleanly. You will not know that your code is bombing. It took me a while to figure out why it was performing a rollback and exiting clean.

So here is the code for a transaction in Oracle:

PROCEDURE SAVE_TWO_THINGS(
IN_THING_ONE IN VARCHAR2,
IN_THING_TWO IN VARCHAR2
)
IS
BEGIN
INSERT INTO TABLEONE
(ACTIVE, THING_DESC)
VALUES
('Y', IN_THING_ONE);


INSERT INTO TABLETWO
(ACTIVE, THING_DESC)
VALUES
('N', IN_THING_TWO);

COMMIT;
EXCEPTION WHEN OTHERS THEN  ROLLBACK;
END;

Note that the 'OTHERS' keyword in my exception clause is where you would catch a specific oracle error, you can string together as many of those as you want to, but I would recommend using the 'OTHERS' as a kind of catch all at the end if you want to make sure you catch everything.

Summary: Unless you are using inline SQL to connect to your Oracle database then you're really not going to be able to use .Net transactions. So you are going to have to use the Oracle transaction. Many would argue that I should have done that from the beginning since Oracle is such a powerhouse that doing processing there isn't a big deal so any atomic transaction should be tried to be done in Oracle anyway, but I really think it is up to the programmer. It really is personal preferences.

Labels: .NET, Oracle, SQL

posted by Tom Becker at 7/16/2008 | 0 Comments

Concatenating values across multiple rows

I had a hard time coming up with a relatively short name for the post, but here is what I wanted to get at. I have been struggling with this for quite some time now, and today I finally found a solution.

I have a base table that has a one-to-many relationship to a cross reference table. And what I wanted to do was create a single row per entry in the base base table with all of values from the cross ref table in a single column concatenated together.

So I looked at creating a pivot table, but that only got me so far. I could get all the different values that were in the cross ref table as columns, but that would give me multiple instances of my base record in the result set.

So, after much research and banging my head against the wall. I came across Tom Kyte's blog and an article called Stringing them Up.

Here is my version of his example. It has a little more in it than his example:

select A.BaseTable_ID,
      A.NAME,
      A.METHOD_DESC,
      A.DATA_DESC,
      A.REF_NO,
      B.TYPELIST

FROM   BaseTable A

INNER JOIN(
 select BaseTable_ID,
 max(sys_connect_by_path(type_desc, '/ ')) TYPELIST

 from (SELECT ID,
              type_desc,
              ROW_NUMBER() OVER PARTITION BY BaseTable_ID
               ORDER BY type_desc) rn
      from baseTable A
      inner join XRef B
      on A.TYPE_ID = b.type_id)

 start with rn =1
 connect by prior rn = rn-1
 and prior BaseTable_ID = BaseTable_ID
 group by BaseTable_ID
 order by BaseTable_ID ) B

ON A.BaseTable_ID = B.BaseTable_ID

Labels: Oracle

posted by Tom Becker at 2/20/2008 | 0 Comments

Uploading a file into an Oracle Blob

I was actually pleasantly surprised how easy it was to upload a blob into an Oracle database from a .NET app. I had been putting off this part of the app off for quite some time, and I am just about done with the rest of it so I thought it was about time. And I'll share the code here with you:

I just used a standard ASP.Net file upload control in the front end (not included here), and the code behind was just as simple

'file upload control
fileName = Me.fupAttach.FileName  

'friendly name for the file
friendlyName = Me.txtFriendlyName.Text

'quick description
FileDesc = Me.txtFileDesc.Text     

'Since a blob is just a byte array, the file upload control has a nice built in
'option to convert the file into a byte[]
Dim btAry As Byte() = Me.fupAttach.FileBytes

'The rest is just basic Oracle

Dim conn As New OracleConnection
Dim comm As OracleCommand
 
conn.ConnectionString = .Common.ProjectConnectionString
conn.Open()

comm = New OracleCommand("############", conn)
comm.CommandType = CommandType.StoredProcedure

comm.Parameters.Add("IN_FILE_NAME", OracleType.VarChar, 500).Value = fileName
comm.Parameters.Add("IN_FRIENDLY_NAME", OracleType.VarChar, 1).Value = friendlyName
comm.Parameters.Add("IN_FILE_DESC", OracleType.VarChar, 220).Value = FileDesc
comm.Parameters.Add("IN_FILE_BLOB", OracleType.Blob).Value = btAry

comm.ExecuteNonQuery()

And that is it. Like I said pretty easy. I was actually very surprised how little I found on the internet to do this. Most of the other examples I found were using a standard HTML upload filed, back from classic ASP days.

Hope this helps.

Labels: .NET, Oracle

posted by Tom Becker at 1/08/2008 | 0 Comments