Oracle equivalent of MySQL group_concat

MySQL has a very useful function called GROUP_CONCAT() that concatenate the aggregated values of a group (GROUP BY). With Oracle 11G, you can use listagg to do the same, but Oracle 10G does not have an equivalent function.

If you are still working with Oracle 10G, this is the simplest way I found to concatenate aggregated values.

The separator used CANNOT be present in any of the concatenated values . It can be a problem if you deal with large text. I used “|” because I know it’s a character that cannot be present in my values.

Original Data:

Oracle Group Concat






Concatenated Data

Oracle Group Concat 2





OBIEE 11G: Refresh user GUIDs

You most probably have to refresh the user GUIDs

  • After a migration from Dev to Prod (or whatever environment)
  • After a server crash
  • After the OBIEE partition got full
  • After having unexplained problems to signing in

This is an expected operation after a migration of the entire catalogue. I don’t really understand why the GUIDs has to be refreshed after a crash (AFAIK, they don’t change), but I had to do it so many times in this situation to be able to log in again.

The task cannot be done using the Weblogic Console neither Weblogic Enterprise Manager. It requires to manually edit 2 config files on the server, bounce the services, remove the edited lines from the config and bounce the services again.

Edit NQSConfig.INI

The file is located in the Oracle BI Server config folder:


  • Locate the parameter FMW_UPDATE_ROLE_AND_USER_REF_GUIDS and change its value to YES
  • Save and Close the file

Edit instanceconfig.xml

The file is located in the Oracle Presentation Server config  folder:


  • Locate the XML tag <Catalog>
  • Add the following tag within the <Catalog> tag: <UpdateAccountGUIDs>UpdateAndExit</UpdateAccountGUIDs>
  • Save and Close the file

It should looks like:

Now restart all the services. BI Presentation should fail to start but should not throw errors.

You can now undo all the modifications you made to both config files and restart all the services. Et voilà ! You did refresh the user GUIDs.


OBIEE 11G: Display hierarchy and columns from the hierachy at the same time

One of the newest feature of 11G is the possibility to expand/collapse hierarchy level (yep.. even in 2014 it seems to be a killer feature…heh!). The problem is you can’t display the hierarchy and the column used to build the hierarchy at the same time with a table. The hierarchy will be OK but the columns will display null value.

Suppose the Hierarchy Year -> Month -> Week -> Day

Add the hierarchy AND the columns used to build it like this:

Hierarchy OBIEE 11g

As you can see, Month and Week are empty. This is caused by the Hierarchy.


OBIEE Hierarchy Result

If you need to display the value, there is a hack. You only need to use the _whatever_ function with the columns ! The less intrusive is to append an empty string at the end.
OBIEE Hierarchy Hack


et voilà !


[nQSError: 42039] Columns in BY clause of REPORT totalling function must be in select list. (HY000)

I got this error today with OBIEE Obviously, all my columns were in the select list. I found a few blogs and articles with people asking for help for that bug but none of them provided/found a solution. Here is mine.

The Error

[nQSError: 42039] Columns in BY clause of REPORT totalling function must be in select list. (HY000)

The symptoms

The error is thrown when sorting one specific column (a measure) of a Table with 14 columns ((5 measures, 7 dimensions columns including 2 hierarchies). All the others columns could be sorted without problem except one.

 The solutions

Uncheck “Report-Based Total (when applicable)” in the aggregation rule of your measure. That’s it !

OBIEE 11G Aggregation Rule


It worked for me !

How to search for a specific column in all Oracle schema

As an ETL developer, this is something I have to do when developers of a source database application have to extend the length of a field. Because that field can be used in so many different projects and/or datamart, I need to find all the possible tables and views of all schemas having the field in my database.

Here is how I do it.. Suppose i’m looking for a column named “awesome_id”.  All I have to do is to execute this query with a user having the privileges to read into all schemas.

It will return all the tables having a column with the word “awesome_id”.

But it didn’t look the queries used to build views. Instead I have to use:

Unfortunately, it only returns the first 4000 characters of the queries, but the lookup is done on the complete queries.

Oracle SQL Update Statement with INNER JOIN

Oracle (the database) does not support joins in the UPDATE statement. This is one of many tiny annoying fact about Oracle. Here are 2 different ways to overcome that limitation.

The subqueries


The updatable view


MySQL provides, in my opinion, a  clearer syntax to update


I can now refer to my blog as an extension of my brain :)