Re: Money Data

On Fri, 28 Nov 2008 16:34:14 +0000, gym dot scuba dot kennedy at gmail wrote:

> "Laurence" <i.l.breeze_at_open.ac.uk> wrote in message > news:d5d414a8-2dd6-4efcb
8e7-09eb9874e24c_at_x14g2000yqk.googlegroups.com...
>> Hi ,
>>
>> I'm in the process of migrating a SQLServer database to Oracle. This
>> is the first Oracle database I've been involved with and I'm aware that
>> there is no money data type in Oracle. I appreciate that the columns
>> that need to hold money will need to be number datatypes, and I notice
>> that number(19,4) seems to crop up frequently in gateway documents.
>>
>> My question is, is there any way in which the currency symbol can be
>> retrieved with the data without needing to alter the SQL that exists
>> now ? This will need to work irrespective of the type of program that
>> connects to the database - UNIX/C/COBOL and VB/ASP/.NET. If it's not
>> possible, how is the SQL altered to achieve this ?
>>
>> Thanks in advance.
>>
>> Laurence

> It sounds like you want to alter a number to look like $xxx.xx or
> whatever the currency is that you desire.  You can set the nls_currency
> for the particular connection and request the number be converted to a
> string to_char(field, format string)  see the docs. Jim

If I'm reading between the lines right, I'm guessing you don't want to change application code. You can optionally implement the table in Oracle with a 'virtual' column, which would be defined with the to_char function allowing you to mask the function from the app (no change to SQL). Or you can create a view to do the same thing.

See the syntax for the CREATE TABLE statement to see how to implement a virtual column.

s2 Received on Wed Dec 03 2008 - 17:57:03 CST