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

 

 

 

References:

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:

/usr/local/OracleBI/instances/instance1/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI

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

/usr/local/OracleBI/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml

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

 

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

I got this error today with OBIEE 11.1.1.7.0. 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 :)