Oracle Error

[urgent72]

HI ,
I have a report developed in crystal 8.5 and been upgraded to 10.I have a procedure that i creataed when running in 8.5 and was sucessful.Now the issue is i am trying to run the same procedure with crystal 10 and when i try to run
the same procedure at SQL i am getting the error
1460 ORA-01460: unimplemented or unreasonable conversion requested and ORA-06512: at "LBT22CRC.EV_4", line 136
ORA-06512: at line 1

i have no idea what is going wrong.

The procedure i am using is

CREATE OR REPLACE procedure ev_4(rootid in Number,monthno in NUMBER ,yearno in NUMBER ) AS
v_cursor integer;
v_dname char(30);
v_rows integer;
BillCycle Number;
UsageTable Varchar2(15);
TaxTable Varchar2(15);
Tablename Varchar2(15);
v_dummy number:=0;
v_tax_table cycle_control.TX_PHYSICAL_NAME%type;bill_count number(3):=0;
cSQL_Statement long;
Cursor Ban_cursor is
Select Ban
From billing_account
Where billing_account.ROOT_ID= Rootid;
Ban_Val Ban_cursor%ROWTYPE;
BEGIN
For Ban_Val In Ban_cursor
Loop
select count(*) into bill_count From Billing_Account
Where Ban = Ban_Val.Ban;
Select Bill_cycle Into BillCycle From Billing_Account
Where Ban = Ban_Val.Ban;
begin
Select rtrim(US_PHYSICAL_NAME),TX_PHYSICAL_NAME
Into UsageTable,v_tax_table
From CYCLE_CONTROL
Where CYCLE_CODE = BillCycle
And CYCLE_RUN_YEAR = yearno
And CYCLE_RUN_MONTH = monthno;
cSQL_Statement :=cSQL_Statement || ' SELECT substr( bill.statement_no,1,9), '|| 'substr(ban_list.COMP_BILL_NAME_LINE2,1,40), bill.bill_due_date,'||
UsageTable||'.fed_tax_amt,
decode(nvl(cust_data_crossref.item_type,''*''),''ARNO'',substr(cust_data_crossref.data_value,1,8),null) ARNO ,
decode(nvl(cust_data_crossref.item_type,''*''),''EIT'',substr(cust_data_crossref.data_value,1,15),null) EIT,
'||UsageTable||'.state_tax_amt,
'||UsageTable||'.Local_tax_amt, '||UsageTable||'.county_tax_amt ,
'||UsageTable||'.unit_esn ,
'||UsageTable||'.air_pp_seq_no,
'||UsageTable||'.alt_billing_no,
'||UsageTable||'.special_8xx_number,
'||UsageTable||'.dialed_tn,
'||UsageTable||'.orig_city_desc,
'||UsageTable||'.term_city_desc,
'||UsageTable||'.feature_code,
'||UsageTable||'.primary_acc_cd,
'||UsageTable||'.secondary_acc_cd,
'||UsageTable||'.term_tn,
'||UsageTable||'.call_type,
to_char('||UsageTable||'.connect_date,''yyyymmddhhmiss'') yyyymmddhhmiss,
to_char('||UsageTable||'.connect_date,''mm/dd/yyyy'') mmddyyyy,
to_char(bill.bill_due_date,''mm/dd/yyyy'') invdate,
to_char('||UsageTable||'.connect_date,''HH:MM'') hhmm,
'||UsageTable||'.connect_date,
'||UsageTable||'.emr_group,
'||UsageTable||'.ban,
'||UsageTable||'.charge_amt,
bill.total_due_amt,
'||UsageTable||'.call_dur_rounded,
'||Usagetable||'.air_start_rate_period, '||'getSurcharge('||Ban_Val.Ban||','''||v_tax_table||''') '||
' from bill,ban_list, cust_data_crossref,'||UsageTable||' where
'||UsageTable||'.ban=ban_list.ban and
'||UsageTable||'.ban=bill.ban and
'||UsageTable||'.ban =cust_data_crossref.ban and
'||UsageTable||'.cycle_code=ban_list.cycle_code and
'||UsageTable||'.cycle_run_month=ban_list.cycle_run_month and
'||UsageTable||'.cycle_run_year=ban_list.cycle_run_year and
'||UsageTable||'.sub_market_code=ban_list.sub_market_code and
'||UsageTable||'.ban=bill.ban and
'||UsageTable||'.bill_seq_no=bill.bill_seq_no and
'||UsageTable||'.cycle_code=bill.cycle_code and
'||UsageTable||'.cycle_run_month=bill.cycle_run_month and
'||UsageTable||'.cycle_run_year=bill.cycle_run_year and
'||UsageTable||'.ban=cust_data_crossref.ban and
cust_data_crossref.item_type in(''ARNO'', ''EIT'') and ban_list.sub_market_code=''AU'' and
'||UsageTable||'.ban= '||Ban_Val.Ban||' and '||UsageTable||'.product_type=''AU'' UNION ALL';
/* '||UsageTable||'.ban=bill.ban and '||UsageTable||'.cycle_code=bill.cycle_code and '||UsageTable||'.cycle_run_month=bill.cycle_run_month and '||UsageTable||'.cycle_run_year=bill.cycle_run_year and '||UsageTable||'.ban=cust_data_crossref.ban and cust_data_crossref.item_type=''ARNO'' and '||UsageTable||'.ban=ban_list.ban and '||UsageTable||'.cycle_code=ban_list.cycle_code and '||UsageTable||'.cycle_run_year=ban_list.cycle_run_year and '||UsageTable||'.cycle_run_month=ban_list.cycle_run_month and '||UsageTable||'.ban='||Ban_Val.Ban||' UNION ALL ';*/
exception
when others then
v_dummy :=v_dummy;
dbms_output.put_line('....') ;
-- dbms_output.put_line('ThisBillCycle('||BillCycle||') had no data. hence ignored'||' BAN IS='||Ban_Val.Ban) ;
end;
End Loop;
cSQL_Statement:=RTRIM(cSQL_Statement,' UNION ALL ');
select decode(length(ltrim(rtrim(cSQL_Statement))),0,' Select ''NO DATA AVAILABLE'' from dual',cSQL_Statement) into cSQL_Statement from dual;
dbms_output.put_line(substr(cSQL_Statement ,1,200));
dbms_output.put_line(substr(cSQL_Statement ,201,200));
dbms_output.put_line(substr(cSQL_Statement ,401,200));
dbms_output.put_line(substr(cSQL_Statement ,601,200));
dbms_output.put_line(substr(cSQL_Statement ,801,200));
dbms_output.put_line(substr(cSQL_Statement ,1001,200));
dbms_output.put_line(substr(cSQL_Statement ,1201,200));
dbms_output.put_line(substr(cSQL_Statement ,1401,200));
dbms_output.put_line(substr(cSQL_Statement ,1601,200));
dbms_output.put_line(substr(cSQL_Statement ,1801,200));
dbms_output.put_line(substr(cSQL_Statement ,2001,200));
dbms_output.put_line(substr(cSQL_Statement ,2201,200));
dbms_output.put_line(substr(cSQL_Statement ,2401,200));
dbms_output.put_line(substr(cSQL_Statement ,2601,200));
dbms_output.put_line(substr(cSQL_Statement ,2801,200));
dbms_output.put_line(substr(cSQL_Statement ,3001,200));
dbms_output.put_line(substr(cSQL_Statement ,3201,200));
dbms_output.put_line(substr(cSQL_Statement ,3401,200));
dbms_output.put_line(substr(cSQL_Statement ,3601,200));
dbms_output.put_line(substr(cSQL_Statement ,3801,200));
dbms_output.put_line(substr(cSQL_Statement ,4001,200));
dbms_output.put_line(substr(cSQL_Statement ,4201,200));
dbms_output.put_line(substr(cSQL_Statement ,4401,200));
dbms_output.put_line(substr(cSQL_Statement ,4601,200));
dbms_output.put_line(substr(cSQL_Statement ,4801,200));
dbms_output.put_line(substr(cSQL_Statement ,5001,200));
dbms_output.put_line(substr(cSQL_Statement ,5201,200));
dbms_output.put_line(substr(cSQL_Statement ,5401,200));
dbms_output.put_line(substr(cSQL_Statement ,5601,200));
dbms_output.put_line(substr(cSQL_Statement ,5801,200));
dbms_output.put_line(substr(cSQL_Statement ,6001,200));
dbms_output.put_line(substr(cSQL_Statement ,6201,200));
dbms_output.put_line(substr(cSQL_Statement ,6401,200));
dbms_output.put_line(substr(cSQL_Statement ,6601,200));
dbms_output.put_line(substr(cSQL_Statement ,6801,200));
dbms_output.put_line(substr(cSQL_Statement ,7001,200));
dbms_output.put_line(substr(cSQL_Statement ,7201,200));
dbms_output.put_line(substr(cSQL_Statement ,7401,200));
dbms_output.put_line(substr(cSQL_Statement ,7601,200));
dbms_output.put_line(substr(cSQL_Statement ,7801,200));
dbms_output.put_line(substr(cSQL_Statement ,8001,200));
dbms_output.put_line(substr(cSQL_Statement ,8201,200));
dbms_output.put_line(substr(cSQL_Statement ,8401,200));
dbms_output.put_line(substr(cSQL_Statement ,8601,200));
dbms_output.put_line(substr(cSQL_Statement ,8801,200));
dbms_output.put_line(substr(cSQL_Statement ,9001,200));
dbms_output.put_line(substr(cSQL_Statement ,9201,200));
--Open Test_Cursor For cSQL_Statement;Test_Cursor IN OUT Test_Package.Test_Type,
EXCEPTION
when others then
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
End;
/


Any suggestions please. Thanks in advance for any helping hand out there.

 

[Turkbear]

Hi,
The Oracle docs ( for 8i, since you did not specify which Oracle version) explains that error as:

ORA-01460 unimplemented or unreasonable conversion requested

Cause: The requested format conversion is not supported.

Action: Remove the requested conversion from the SQL statement. Check the syntax for the TO_CHAR, TO_DATE, and TO_NUMBER functions to see which conversions are supported.



So check those factors to be sure the values supplied are appropriate for the conversion you are attempting..

 

[synapsevampire]

What does "when i try to run the same procedure at SQL" mean?

Meaning that you're running it from SQL Plus, or?

Does the SP run from SQL Plus?

What connectivity type are you using?

If using Native, try switching to OLE DB/ODBC ot vice versa.

-k