# calculation in pl/sql (oralce) RSS

## 5 replies

Last post Dec 30, 2012 04:32 PM by -_-

Member

376 Points

1046 Posts

### calculation in pl/sql (oralce)

if i have input parameter "i_aa" and datatype is varchar2.

and define a variable "v_bb" with datatype is number

and a output parameter "o_cc" with datatype varchar2

how can i convert the datatype in order to do calculation.

my formula is as below:

o_cc := v_bb - i_aa;

also, add some condition to input parameter if the input is null, then convert to zero and if output o_cc is with 1 decimal.

how can i write the script in pl/sql?

Contributor

3970 Points

1096 Posts

### Re: calculation in pl/sql (oralce)

Oracle's cast() function will get you started.

Member

376 Points

1046 Posts

### Re: calculation in pl/sql (oralce)

any code reference for me ?

Member

376 Points

1046 Posts

### Re: calculation in pl/sql (oralce)

i don't know how to use case function for handling data type problem.

would you provide code reference to me??

Contributor

4820 Points

1167 Posts

### Re: calculation in pl/sql (oralce)

you can use to_number() to convert varchar2 value to number

example: select to_number('22') from dual; will return 22 as number

for you

to_number(o_cc) := v_bb - to_number(i_aa);

but you have to check for whether the values are valid number or not.

u can use below query on any varchar2. it will return a number if the varchar is a valid number other wise it will return 0

select case when lENGTH(TRIM(TRANSLATE(i_aa, ' +-.0123456789',' '))) >0 then 0 else to_number(i_aa) end from dual;

the below query will return 1 if i_aa is not a valid number otherwise will return the number

select case when lENGTH(TRIM(TRANSLATE(o_cc, ' +-.0123456789',' '))) >0 then 1 else to_number(o_cc) end from dual;

if you need any other help please let me know

Shuvo

Member

376 Points

1046 Posts