MyEclipse Reports 2.2 - Oracle Stored procedure with OUT parameters

I am using MyEclipse Reports 2.2 for generating PDF reports in my project but was struck with as it does not support the following features out-of-box.
1) Stored procedure Datasets
2) OUT parameters

I sought of worked around this issue by doing the following:

1) Used Eclipse BIRT to create a report(which supports the above features)
2) copied over the Dataset configurations from the BIRT Report XML file to MyEclipse report xml file.
This works like a charm!

Note: Make sure you have appropriate version number in your Report design XML file.

Hope this might save somebody sometime, which would otherwise be spent trying to resolve this issue.

SYBASE SQL for formatting Phone numbers

This sql formats a phone number without using Regular expression.
Steps:
-----
1) check for empty or NULL value.
2) check if size is greater than 10.
a) size > 10, format with extension
b) else, format as a 10 digit phone number

select
case when len(str_replace(phone_no,'',NULL)) = NULL
then ''

when len(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL)) > 10

then
'('||substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 1, 3)
|| ')' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 4, 3)
|| '-' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 7, 4)
|| ' ext:' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 10,5)

else
'('||substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 1, 3)
|| ')' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 4, 3)
|| '-' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 7,4)
end as PHONE_NO
from person

I have not checked if Sybase supports regular expression, in which case it might be much simpler. Please share if you have a better solution.

Oracle SQL - Formatting zip code

This code will format zip code in the format xxxxx-xxx..., using regular expression.

Steps involved:
1) first removes any non-numeric characters
2) if the lenght is greater than 5 characters, introduces a '-' after first 5 characters.
3) returns the non-numeric zip, otherwise.


select decode(ZIP,NULL,'', CHR(13)
||
case when length(REGEXP_REPLACE(ZIP, '[^0-9]+', '')) > 5
then
REGEXP_REPLACE(REGEXP_REPLACE(ZIP, '[^0-9]+', ''),'(.....)(.*)','\1-\2')
else
REGEXP_REPLACE(ZIP, '[^0-9]+', '')
end
)
from address;