Sign In| Join
Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Dec 30, 2012 04:32 PM by -_-
Member
376 Points
1046 Posts
Dec 29, 2012 04:25 PM|LINK
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
Dec 29, 2012 04:58 PM|LINK
Oracle's cast() function will get you started.
Dec 30, 2012 03:45 AM|LINK
Dec 30, 2012 02:27 PM|LINK
i don't know how to use case function for handling data type problem.
would you provide code reference to me??
4820 Points
1167 Posts
Dec 30, 2012 02:48 PM|LINK
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
Dec 30, 2012 04:32 PM|LINK
-_-
Member
376 Points
1046 Posts
calculation in pl/sql (oralce)
Dec 29, 2012 04:25 PM|LINK
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?
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: calculation in pl/sql (oralce)
Dec 29, 2012 04:58 PM|LINK
Oracle's cast() function will get you started.
-_-
Member
376 Points
1046 Posts
Re: calculation in pl/sql (oralce)
Dec 30, 2012 03:45 AM|LINK
-_-
Member
376 Points
1046 Posts
Re: calculation in pl/sql (oralce)
Dec 30, 2012 02:27 PM|LINK
i don't know how to use case function for handling data type problem.
would you provide code reference to me??
Shuvo Aymon
Contributor
4820 Points
1167 Posts
Re: calculation in pl/sql (oralce)
Dec 30, 2012 02:48 PM|LINK
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
If more posts give you useful answers, Please mark each post as "Answer".
-_-
Member
376 Points
1046 Posts
Re: calculation in pl/sql (oralce)
Dec 30, 2012 04:32 PM|LINK