Oracle SUBSTR (Seperate strings) based on a delimiter

Using regexp_substr we can match a expression in the parent string the delimiter here is '.'
[^.]* --> match one or more occurences of the string [any chars]ending with . so this matches anything like abc. xyz. 123. or even .

select regexp_substr('1.00..60200....','[^.]*.',1,4) from dual;
Result: 60200.

Using the below rtrim we can trim the '.' in the end.

select rtrim('123.','.') from dual;

Final SQL:
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,5),';') seg from dual;
Result: null
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,2),';') seg from dual;
Result: 00
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,3),';') seg from dual;
Result: 123

PIVOT (Oracle) - Convert multiple Rows to Columns

I had to convert multiple rows into columns today. Fetch balances from gl_balances and apply multiple conversions(say GBP,USD,AUD,HKD).

First approach:

select period_net_dr , period_net_dr*gt1.avg_rate gbp_value
, period_net_dr*gt2.avg_rate usd_value
, period_net_dr*gt3.avg_rate aud_value
, period_net_dr*gt4.avg_rate hkd_value
from gl_balances glb,
gl_translation_rates GT1,
gl_translation_rates GT2,
gl_translation_rates GT3,
gl_translation_rates GT4
where glb.period_name = 'AUG-12'
and GT1.period_name = 'AUG-12'
and GT2.period_name = 'AUG-12'
and GT3.period_name = 'AUG-12'
and GT4.period_name = 'AUG-12'
and GT1.to_currency = 'GBP'
and GT2.to_currency = 'USD'
and GT3.to_currency = 'AUD'
and GT4.to_currency = 'HKD';

The table gl_translation_rates appears 4 times in the query. So how we can do it better.

Using Oracle PIVOT feature this is how it can be done.

select period_net_dr, period_net_dr*gbp_rate,period_net_dr*usd_rate, period_net_dr*aud_rate,period_net_dr*hkd_rate from gl_balances glb,

(SELECT period_name,gbp_rate,usd_rate,aud_rate,hkd_rate
FROM (SELECT period_name,TO_CURRENCY_CODE, conversion_rate
FROM gl_translation_rates
where period_name = 'AUG-12' and set_of_books_id = 1)
PIVOT (sum(conversion_rate) FOR TO_CURRENCY_CODE IN ('GBP' gbp_rate,'USD' usd_rate,'EUR' eur_rate ,'HKD' hkd_rate,'SGD' sgd_rate,'AUD' aud_rate)) ) gl_rates

where glb.period_name = 'AUG-12'
and gl_rates.period_name = glb.period_name
and period_net_dr <> 0;

-----

As you can see the gl_rates pseudo table which I built in the view is the key and the beauty of pivot feature is it allows us to name the columns like gbp_rate etc.

So we have only one hit on the rates table. And the query is more readable

Unable to set NLS_LANGUAGE - Workflow Builder

When I installed workflow builder for the first time I had the following error

220: Unable to set NLS_LANGUAGE.
210: Oracle Error: ORA-00942: table or view does not exist. SQL text: SELECT NLS_LANGUAGE FROM WF_LANGUAGES WHERE :l IN (NLS_LANGUAGE, CODE)

To fix the error.
1.Open REGEDIT from cmd promt
2.Navigate to My Computer -> HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
3. Find key NLS_LANG and update it to charset value from the below query
select nls_language||'_'||nls_territory||'.'||nls_codeset from wf_languages where code = 'US';
4. Also recommended to do the same for the NLS_LANG key under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME2 (or whatever home workflow is installed.