Sophis Risque: how to list the portfolio columns which are used?
May
29
Written by:
5/29/2014 10:31 PM
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:
'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;