Oracle equivalent to SQL Server STUFF function?

[Originally posted by]: http://stackoverflow.com/questions/2108194/oracle-equivalent-to-sql-server-stuff-function

Does Oracle have its own implementation of SQL Server stuff function?

Stuff allows you to receive one value from a multi row select. Consider my situation below

 ID   HOUSE_REF   PERSON
 1      A         Dave
 2      A         John
 3      B         Bob

I would like to write a select statement, but I want the PERSON names to be in a single row.

For example, when I select from this table, I want to achieve the following

HOUSE_REF   PERSONS
A           Dave, John
B           Bob

I haven’t been able to find a simple solution so far, it may be a case of writing my own function to use, but I’m not entirely sure of how to approach this, any ideas?

The main business use of this, will be to have a select statement that shows each house, and against that house to have one column which lists everyone that lives in that house. The house ref in this select must be unique, hence needing to concatenate the persons

Thanks

shareedit

Oracle 11.2 includes a new function LISTAGG to do this.

Prior to that you could use Tom Kyte’s STRAGG function.

shareedit

The “no add-ons/no undocumented functions” Oracle solution (prior to 11.2 as Tony mentions) is:

select c1, ltrim(sys_connect_by_path(c2,','),',') persons
 from
  (
   select c1, c2, 
    row_number() over (partition by c1 order by c2 ) rn
     from
      (
       select house_ref c1, person c2 
        from housetable 
      )
   )
  where connect_by_isleaf=1
  connect by prior rn+1 =rn and prior c1 = c1
  start with rn=1
;
shareedit
Advertisements

What is the Oracle equivalent of SQL Server’s IsNull() function?

[Originally posted by]: http://stackoverflow.com/questions/3523036/what-is-the-oracle-equivalent-of-sql-servers-isnull-function

coalesce is supported in both Oracle and SQL Server and serves essentially the same function as nvl and isnull. (There are some important differences, coalesce can take an arbitrary number of arguments, and returns the first non-null one. The return type for isnull matches the type of the first argument, that is not true for coalesce, at least on SQL Server.)

shareedit

Instead of ISNULL(), use NVL().

T-SQL:

SELECT ISNULL(SomeNullableField, 'If null, this value') FROM SomeTable

PL/SQL:

SELECT NVL(SomeNullableField, 'If null, this value') FROM SomeTable
shareedit

‘OraOLEDB.Oracle.1’ is not registered on local machine

[Origianlly Posted From] : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/95d8e522-75b6-4ee3-a691-d2e76b25d56a/oraoledboracle1-is-not-registered-on-local-machine?forum=sqlintegrationservices

I nearly pulled my hair out finding the solution to this problem.

In the end I installed “32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio” on my 64 bit system, and it solved the problem.

http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

  • Proposed as answer by neilg075 Sunday, September 16, 2012 3:01 PM
Sunday, September 16, 2012 3:01 PM
Avatar of neilg075

0 Points

Comparsion of ADO.NET/ADO/DAO/OLEDB/ODBC

http://www.vbforums.com/showthread.php?518058-RESOLVED-Comparsion-of-ADO-NET-ADO-DAO-OLEDB-ODBC

ADO is COM-based and is the data access technology of choice for VB6. DAO is even older and I don’t know, or want to know, much more about it. ADO.NET is what you should be using each and every time in .NET applications. The only time I have ever used ADO in a .NET app is when I was writing an add-in for an app written in VB6. The app exposed a method that returned an ADO Recordset, so I had to get my data from that. On no other occasion have I, or would I, use ADO. I suggest you adopt the same philosophy.

One of the major differences between ADO and ADO.NET is that ADO.NET uses a disconnected model. You connect to the database, get your data, disconnect, manipulate your data, reconnect, save the changes. With ADO you maintain a live connection the whole time you’re working with the data. Each model has its pros and cons but Microsoft have decided that, in this day and age, the disconnected model is better and I have to agree. The main issue with working disconnected is that two users may both retrieve, and then try to update, the same data. Microsoft provide plenty of information and advice on handling these concurrency issues though.

ODBC and OLEDB are both interfaces that provide a standard way to interact with different data sources. ODBC is older and more general. An ODBC driver exists for pretty much every database imaginable, and some data sources that couldn’t really be considered databases. There are also OLEDB providers for a large number of databases and other data sources.

ODBC is the older and should be considered a lowest-common-denominator. ADO.NET supports ODBC through the System.Data.Odbc namespace. You should only ever use ODBC through ADO.NET if there is no other choice.

OLEDB is a Microsoft creation and, like ODBC, provides a standard interface to databases and other data sources. An OLEDB provider exposes a standard set of functionality, while each OLEDB provider performs the necessary translation and talks directly to the individual data sources it was designed to support. An ODBC driver does the same. The .NET Framework supports OLEDB through the System.Data.OleDb namespace. OLEDB should be used in preference to ODBC but any more specific ADO.NET provider, e.g. SqlClient or OracleClient, should be used in preference to OLEDB.