Money Data

#11

On Nov 28, 11:34*am, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:

Quote:

"Laurence" <i.l.bre... (AT) open (DOT) ac.uk> wrote in message

news:d5d414a8-2dd6-4efc-b8e7-09eb9874e24c (AT) x14g2000yqk (DOT) 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- Hide quoted text -

- Show quoted text -

I agree with Jim. The specific document in question that explains the
to_char function and format masks is the SQL manual.

http://download.oracle.com/docs/cd/B...htm#SQLRF06130

HTH -- Mark D Powell --

#12

On Nov 28, 11:34*am, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:

Quote:

"Laurence" <i.l.bre... (AT) open (DOT) ac.uk> wrote in message

news:d5d414a8-2dd6-4efc-b8e7-09eb9874e24c (AT) x14g2000yqk (DOT) 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- Hide quoted text -

- Show quoted text -

I agree with Jim. The specific document in question that explains the
to_char function and format masks is the SQL manual.

http://download.oracle.com/docs/cd/B...htm#SQLRF06130

HTH -- Mark D Powell --

#13

On Nov 28, 11:34*am, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:

Quote:

"Laurence" <i.l.bre... (AT) open (DOT) ac.uk> wrote in message

news:d5d414a8-2dd6-4efc-b8e7-09eb9874e24c (AT) x14g2000yqk (DOT) 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- Hide quoted text -

- Show quoted text -

I agree with Jim. The specific document in question that explains the
to_char function and format masks is the SQL manual.

http://download.oracle.com/docs/cd/B...htm#SQLRF06130

HTH -- Mark D Powell --

#14

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

Quote:

"Laurence" <i.l.breeze (AT) open (DOT) ac.uk> wrote in message
news:d5d414a8-2dd6-4efc-
b8e7-09eb9874e24c (AT) x14g2000yqk (...oglegroups.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

#15

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

Quote:

"Laurence" <i.l.breeze (AT) open (DOT) ac.uk> wrote in message
news:d5d414a8-2dd6-4efc-
b8e7-09eb9874e24c (AT) x14g2000yqk (...oglegroups.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

#16

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

Quote:

"Laurence" <i.l.breeze (AT) open (DOT) ac.uk> wrote in message
news:d5d414a8-2dd6-4efc-
b8e7-09eb9874e24c (AT) x14g2000yqk (...oglegroups.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

#17

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

Quote:

"Laurence" <i.l.breeze (AT) open (DOT) ac.uk> wrote in message
news:d5d414a8-2dd6-4efc-
b8e7-09eb9874e24c (AT) x14g2000yqk (...oglegroups.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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Money Data

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Money Data - 11-29-2008 , 04:12 PM

Re: Money Data - 11-29-2008 , 04:12 PM

Re: Money Data - 11-29-2008 , 04:12 PM

Re: Money Data - 12-03-2008 , 05:57 PM

Re: Money Data - 12-03-2008 , 05:57 PM

Re: Money Data - 12-03-2008 , 05:57 PM

Re: Money Data - 12-03-2008 , 05:57 PM