SQL & PL/SQL

Hi all,

I wrote a select statement that joins one table (base table --> abc_imp_policy ) to another subquery. I am joining based on a column which varchar2 (it still supposed to be numbers) on the base table and number field on the subquery. The base table(abc_imp_policy) doesn't have a primary key.

So when I ran the select statement , it didn't complete and threw an exception invalid number. I wrote a pl/sql procedure and use the select statement as a cursor and added an exception handler invalid_number.

My question how can I find the bad record (policynum)_. The field that is causing the invalid number prob. is the policy num


I cannot create a view or a temp table. This issue is production. I have pasted the my procedure below. Your help is appreciated.

Thanks,


DECLARE

CURSOR POLICY_CUR IS
select x.policy_num,substr(t.account_holder,1,6) account_hold
from abc.abc_imp_policy t,
(select d.policy_num
from abc.abc_policy_distributed d
where d.policy_year > 2009
and d.dp_id NOT IN ( select pa.dp_dp_id
from abc.abc_tp_policy_applied pa))X
where t.policy_num = X.policy_num
and substr(t.account_holder,1,6) NOT IN (select s.account
from abc.abc_accounts s);

lv_outfile varchar2(23) := 'policy_file.dat';
LV_WRITE_FILE utl_file.file_type;
lv_invalid varchar2(50);
BEGIN

LV_WRITE_FILE := utl_file.fopen('POLICY_DIR',lv_outfile,'W');

FOR POLICY_REC IN POLICY_CUR LOOP
utl_file.put_line (LV_WRITE_FILE,POLICY_REC.policy_num||','||POLICY_REC.account_hold||chr(13));
END LOOP;

utl_file.fclose_all;

EXCEPTION
when value_error then
dbms_output.put_line('Value Error' );
when invalid_number then
dbms_output.put_line('Invalid Number');
dbms_output.put_line( dbms_utility.format_error_backtrace );
when others then
dbms_output.put_line('Others'||sqlerrm);
END ;