Shell Programming and Scripting

I am currently returning an sql result with a number value that I want to format as an amount. The sql runs smoothly on its own, but when run inside my ksh script an error is encountered:

ERROR at line 3:
ORA-01481: invalid number format model

My sql is --

Code:

select distinct
    to_char(amount,'$9,999,999,999.00')
from    account;

The amount field in the account table is already defined as a number. Is there a specific way for me to return this value with the $9,999,999,999.00 format?

Thanks in advance.

Hi,

The problem is becuase of the $9,999,999,999. When you run this query in sql, it will work fine. When you run this from shell, shell interprets $9,999,999,999(since there is a $ in it) as a variable name and tries to replace the value in it.

Remove the $ in the to_char and run it, and you can prepend the $ later in the output.

Thanks
Guru.

How do you call the SQL? Do you echo to sqlplus, or do you use a here-doc?

Thanks for your reply. Does this mean that ksh doesn't interpret the $ symbol?

Hi
ksh does interpret the $ symbol. That is why by the time sql query gets executed, the $9 gets replaced with nothing and hence the error.

In order to overcome, use your query like this:

select distinct
to_char(amount,'9,999,999,999.00')
from account;

This will return the amount without the $ symbol. You can prepend the $ symbol to the amount later in the shell, once the sql query is executed.

Thanks
Guru.

Or, if you pass the SQL proper, you can get the dollar sign without post-processing. Eg:

Code:

sqlplus user/pass@sid << 'EOF'
select distinct
    to_char(amount,'$9,999,999,999.00')
from    account;
EOF

will not expand $9, but instead will pass the whole text as is.

I did your suggestion but I still encountered the error. I was able to work around it by taking out the $ sign. Thanks!