Home » Infrastructure » Unix » Data getting truncated while passing a data of type Number(3,2) from SHELL script to ORACLE Proc (Oracle 10g R2, UNIX)
Data getting truncated while passing a data of type Number(3,2) from SHELL script to ORACLE Proc [message #453306] Wed, 28 April 2010 03:55 Go to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
I am passing a number of datatype Number(3,2) from shell script to a PL/SQL stored proc.

From shell script I am passing 101.10 as the second argument($2).

For eg:

    targetDB=$1
    rollnumber=$2
    
    echo "exec registerroll('$targetDB', $rollnumber);" >> ${REGISTER_ROLL_FILENAME}

But, in the stored proc the value is truncated as 101.1

Lets say the stored proc "registerroll" is inserting rollnumber into role table.

The rollnumber gets inserted as 101.1

but the actual data I am passing is 101.10

Suggestions to fix this ll be highly appreciated.
Re: Data getting truncated while passing a data of type Number(3,2) from SHELL script to ORACLE Proc [message #453309 is a reply to message #453306] Wed, 28 April 2010 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you parameter is of number datatype, trailing 0 do not exist by definition.

Regards
Michel
Re: Data getting truncated while passing a data of type Number(3,2) from SHELL script to ORACLE Proc [message #453360 is a reply to message #453309] Wed, 28 April 2010 06:31 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Ok, let say the datatype of my parameter is Number(3,4).

In this case, all 101.10, 101.100, 101.1000 gets truncated to 101.1

In this case how to figure out how many zeros to pad???

Pls help with a generic solution...
Re: Data getting truncated while passing a data of type Number(3,2) from SHELL script to ORACLE Proc [message #453368 is a reply to message #453360] Wed, 28 April 2010 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In this case how to figure out how many zeros to pad???

This is TOTALLY irrelevant for a number. A number does NOT have trailing 0. Only strings representing number may have.
To convert a number to a string use TO_CHAR function.

Regards
Michel
Re: Data getting truncated while passing a data of type Number(3,2) from SHELL script to ORACLE Proc [message #453369 is a reply to message #453360] Wed, 28 April 2010 06:44 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In mathematics, 101.1 = 101.10 = 101.100 = 101.1000 etc. so there's no difference from that point of view.

However, if you are manipulating values that require trailing zeros, perhaps you should consider changing data type from NUMBER to CHARACTER.
Previous Topic: Quarterly delete files older than 3 months
Next Topic: dbhome
Goto Forum:
  


Current Time: Thu Mar 28 07:13:16 CDT 2024