I have a program that must support both Oracle and SQL Server for it's database. At some point I must execute a query where I want to concatenate 2 columns in the select statement.

In SQL Server, this is done with the + operator

select column1 + ' - ' + column2 from mytable

And oracle this is done with concat

select concat(concat(column1, ' - '), column2) from mytable

I'm looking for a way to leverage them both, so my code has a single SQL query literal string for both databases and I can avoid ugly constructs where I need to check which DBMS I'm connected to.

My first instinct was to encapsulate the different queries into a stored procedure, so each DBMS can have their own implementation of the query, but I was unable to create the procedure in Oracle that returns the record set in the same way as SQL Server does.

Update: Creating a concat function in SQL Server doesn't make the query compatible with Oracle because SQL Server requires the owner to be specified when calling the function as:

select dbo.concat(dbo.concat(column1), ' - '), column2) from mytable

It took me a while to figure it out after creating my own concat function in SQL Server.

On the other hand, looks like a function in Oracle with SYS_REFCURSOR can't be called with a simple

exec myfunction

And return the table as in SQL Server.

In the end, the solution was to create a view with the same name on both RDBMs but with different implementations, then I can do a simple select on the view.

• c++
• sql-server
• oracle
• qt

If you want to go down the path of creating a stored procedure, whatever framework you're using should be able to more or less transparently handle an Oracle stored procedure with an OUT parameter that is a SYS_REFCURSOR and call that as you would a SQL Server stored procedure that just does a SELECT statement.

CREATE OR REPLACE PROCEDURE some_procedure( p_rc OUT sys_refcursor )
AS
BEGIN
  -- You could use the CONCAT function rather than Oracle's string concatenation
  -- operator || but I would prefer the double pipes.
  OPEN p_rc
   FOR SELECT column1 || ' - ' || column2
         FROM myTable;
END;

Alternatively, you could define your own CONCAT function in SQL Server.

Nope, sorry.

As you've noted string concatentaion is implemented in SQL-Server with + and Oracle with concat or ||.

I would avoid doing some nasty string manipulation in stored procedures and simply create your own concatenation function in one instance or the other that uses the same syntax. Probably SQL-Server so you can use concat.

The alternative is to pass + or || depending on what RDBMS you're connected to.

Apparently in SQL Server 2012 they have included a CONCAT() function:

http://msdn.microsoft.com/en-us/library/hh231515.aspx

If you are trying to create a database agnostic application, you should stick to either

1. Stick to very basic SQL and do anything like this in your application.

2. Create different abstractions for different databases. If you hope to get any kind of scale out of your application, this is the path you'll likely need to take.

   I wouldn't go down the stored procedure path, you can probably get it to work, but but week you'll find out you need to support "database X", then you'll need to rewrite your stored proc in that database as well. Its a recipe for pain.