ITQuants blog

Sophis Risque: how to list the portfolio columns which are used?

May 29

Written by:
5/29/2014 10:31 PM  RssIcon

In some circumtances, it is interesting to get the list of columns which are currently used in the different portfolio configurations. Getting such a list permits to see which toolkit columns are really used. Missing columns can be removed from the toolkit dlls.

The configuration of portfolio columns are stored in a BLOB field of the Oracle table USER_SETTINGS. We just need to use an XPATH query to get the list.

Depending on national character set configuration of the database, the following query should work on most configurations:

select distinct x.name from user_settings t, XMLTABLE(XmlNamespaces(DEFAULT 'http://www.sophis.net/settings'),
'settings/portfolioManagement/columnsSets/columnsSet/column' 
PASSING xmltype(t.xmlcontent,nls_charset_id('WE8ISO8859P1')) 
COLUMNS name VARCHAR2(256) PATH '@name') x

 

The defintion of the default namespace has to be indicated. It is done using the XmlNamespaces(DEFAULT xxx) syntax. Depending on the national character settings, the parameter used in the NLS_CHARSET_ID method could changed. To display the character set used by the database, the following query can be used;

SELECT * FROM NLS_DATABASE_PARAMETERS;

 

 

Search blog