ORA-06550: line 1, column 7: 1

[urgent72]

HI,

I am getting the following error
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: Message 306 not found; No message file for product=plsql,
facility=PCM
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am trying to run the below procedure.
The procedure runs fine with out any problems when
do not include the cSQL_Statement||' '|| statement in the cSQL_Statement. Which only gives one of the usage table but i wan to include all the usage tables under one root_id .So in order to include all the bans under the usage table i include cSQL_Statement||' '|| and when i try to run my procedure i get the above error.

CAN ANY ONE PLEASE HELP ME.I AM in CRISIS.
CREATE OR REPLACE procedure conf_test(Rootid in Number, Monthno in Number, Yearno in Number) AS
v_cursor integer;
v_dname char(30);
v_rows integer;
Minutes integer(10);
Lines integer(6);
Units integer(9);
BillCycle number;
UsageTable Varchar2(15);
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 BILL_CYCLE into BillCycle from BILLING_ACCOUNT where BAN=Ban_Val.Ban ;

Select rtrim(US_PHYSICAL_NAME) into UsageTable from CYCLE_CONTROL where CYCLE_CODE=BillCycle and CYCLE_RUN_YEAR=Yearno and CYCLE_RUN_MONTH=Monthno;

cSQL_Statement:= cSQL_Statement||' '||
' SELECT
'||UsageTable||'.ban,
decode(nvl(cust_data_crossref.item_type,''*''),''ARNO'',substr(cust_data_crossref.data_value,1,9),null) ARNO ,
decode(nvl(cust_data_crossref.item_type,''*''),''EIT'',substr(cust_data_crossref.data_value,1,15),null) EIT,
ban_list.comp_bill_name_line2,
ban_list.comp_bill_name_line1,
bill.statement_no,
bill.bill_due_date,
'||UsageTable||'.alt_billing_no,
'||UsageTable||'.dialed_tn||'||UsageTable||'.special_8xx_number || '||UsageTable||'.orig_city_desc || '||UsageTable||'.term_city_desc ,
'||UsageTable||'.dialed_tn||'||UsageTable||'.special_8xx_number chrfrstnam,
'||UsageTable||'.orig_city_desc || '||UsageTable||'.term_city_desc chrlstnam,
'||UsageTable||'.connect_date,
feature.feature_desc,
'||UsageTable||'.feature_code,
'||UsageTable||'.air_pp_seq_no,
'||UsageTable||'.orig_cell_trunk_id,
'||UsageTable||'.term_cell_trunk_id,
'||UsageTable||'.unit_esn,
'||UsageTable||'.primary_acc_cd,
'||UsageTable||'.secondary_acc_cd,
'||UsageTable||'.emr_indicator_1,
'||UsageTable||'.emr_indicator_2,
'||UsageTable||'.charge_amt,
'||UsageTable||'.EMR_RECORD_TYPE,
'||UsageTable||'.CALL_DUR_ROUNDED,
cust_data_crossref.item_type,
'||UsageTable||'.air_start_rate_period ,
'||UsageTable||'.call_type
from '||UsageTable||', feature , ban_list , bill, cust_data_crossref where
'||UsageTable||'.feature_code=feature.feature_code and
'||UsageTable||'.ban=ban_list.ban and
'||UsageTable||'.ban=bill.ban and '||'cust_data_crossref.ban='||UsageTable||'.ban and cust_data_crossref.item_type in(''ARNO'',''EIT'') and '||UsageTable||'.ban= '||Ban_Val.Ban||' UNION ALL' ;



/*v_cursor:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (v_cursor,cSQL_Statement,DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor,1,v_dname,30);
v_rows:= DBMS_SQL.EXECUTE(v_cursor);

loop
if DBMS_SQL.FETCH_ROWS(v_cursor)=0 then
DBMS_OUTPUT.PUT_LINE('No Rows');
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor,1,v_dname);
DBMS_OUTPUT.PUT_LINE('city:'||v_dname);
end loop;
*/
end loop;
cSQL_Statement:=RTRIM(cSQL_Statement, ' UNION ALL');
cSQL_Statement:=cSQL_Statement||' order by 6,8,9,10,11 ';
---DBMS_SQL.CLOSE_CURSOR(v_cursor);
----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;
/


Thanks a lot in advance for the helping hand.

 

[Dagon]

Might be the fact that you are using a LONG and it doesn't like concatenating something to a long. Try using a bing PL/SQL string (e.g. varchar2(320000)) or a CLOB, if you have a version of the database which supports it.

 

[urgent72]

HI,

I tried using CLOB and i also tried to calculate the Length if the cSQL_Statement and when the length is 31809 i again get the following error

ERROR at line 1:
ORA-20000: Unknown Exception Raised:-6502 ORA-06502: PL/SQL: numeric or value
error
ORA-06512: at "LBT16CRC.CONFDETAILMAST", line 117
ORA-06512: at line 1

any further suggestions please

 

[sem]

As you may see the error has been raised in LBT16CRC.CONFDETAILMAST, not in conf_test. Just check its 117-th line.

Regards, Dima

http://seminihin.narod.ru