ORA-01722 when returning ROWTYPE in function [message #653618] |
Tue, 12 July 2016 13:48 |
rkhatiwala
Messages: 178 Registered: April 2007
|
Senior Member |
|
|
Hello,
I have function as follows. I am getting ORA-01722 Invalid Number. It is returning only one row, when SELECT statement is run in TOAD.
FUNCTION get_state_rules_fun (p_state IN varchar2)
RETURN state_sales_tax_rules%rowtype IS
v_st_rules state_sales_tax_rules%rowtype;
BEGIN
select *
into v_st_rules
from state_sales_tax_rules
where state = p_state;
return(v_st_rules);
exception
when others then
message('get_state_rules_fn err ='||sqlerrm);
raise;
END;
|
|
|
|
Re: ORA-01722 when returning ROWTYPE in function [message #653622 is a reply to message #653618] |
Tue, 12 July 2016 14:00 |
rkhatiwala
Messages: 178 Registered: April 2007
|
Senior Member |
|
|
This is how it is called
if not check_veh_elig_fun(:sales_tax_info.vehicle_id) then
Here is the function without EXCEPTION
FUNCTION get_state_rules_fun (p_state IN varchar2)
RETURN state_sales_tax_rules%rowtype IS
v_st_rules state_sales_tax_rules%rowtype;
BEGIN
select *
into v_st_rules
from state_sales_tax_rules
where state = p_state;
return(v_st_rules);
END;
|
|
|
Re: ORA-01722 when returning ROWTYPE in function [message #653623 is a reply to message #653622] |
Tue, 12 July 2016 14:03 |
rkhatiwala
Messages: 178 Registered: April 2007
|
Senior Member |
|
|
Here is the other function:
UNCTION check_veh_elig_fun
(p_vehicle_id IN vehicles.vehicle_id%type) RETURN boolean IS
v_state varchar2(2);
v_payment_type vehicles.veh_payment_type%type;
v_request_code vehicles.request_code%type;
v_st_rules state_sales_tax_rules%rowtype;
v_sales_tax_status_code vehicles.sales_tax_status_code%type;
v_bool boolean := false;
-- v_dummy varchar2(1);
BEGIN
select state_reg_code, veh_payment_type, request_code, sales_tax_status_code
into v_state, v_payment_type, v_request_code, v_sales_tax_status_code
from vehicles rv
where rv.vehicle_id = p_vehicle_id
and upper(record_status_code) not in ('REJECTED', 'CANCELLED')
and rv.check_date is not null;
if v_sales_tax_status_code = 'CLARION' then -- old data
v_bool := false;
else
v_st_rules := get_state_rules_fun(v_state);
.
.
.
.
|
|
|
|
Re: ORA-01722 when returning ROWTYPE in function [message #653625 is a reply to message #653624] |
Tue, 12 July 2016 14:08 |
rkhatiwala
Messages: 178 Registered: April 2007
|
Senior Member |
|
|
get_state_rules_fun(v_state) is called in check_veh_elig_fun
so in procedure, check_vehicle_id_pro - check_veh_elig_fun is called:
if not check_veh_elig_fun(:sales_tax_info.vehicle_id) then
and in check_veh_elig_fun :
v_st_rules := get_state_rules_fun(v_state);
|
|
|
|
Re: ORA-01722 when returning ROWTYPE in function [message #653627 is a reply to message #653626] |
Tue, 12 July 2016 14:18 |
rkhatiwala
Messages: 178 Registered: April 2007
|
Senior Member |
|
|
It is throwing error, ORA-01722 INVALID NUMBER, in following function:
FUNCTION get_state_rules_fun (p_state IN varchar2)
RETURN state_sales_tax_rules%rowtype IS
v_st_rules state_sales_tax_rules%rowtype;
BEGIN
select *
into v_st_rules
from state_sales_tax_rules
where state = p_state;
return(v_st_rules);
exception
when others then
message('get_state_rules_fn err ='||sqlerrm);
raise;
END;
|
|
|
|
|
|
|
|
|