| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> unable to execute procedure containg pl/sql tables
Hi,
I have a procedure containing pl/sql tables and i am getting error:
PLS-00418: array bind type must match PL/SQL table row type ORA-06550: line 1, column 340: PLS-00418: array bind type must match PL/SQL table row type ORA-06550: line 1, column 9:
I am not able to find any mismatch between varaibles and table columns: what can be possible reasons for this error:
Thanks
-Harvinder
procedure is declared as:
CREATE OR REPLACE package GetPCViewHierarchy_pkg as
TYPE temp_name is TABLE of t_base_props.n_display_name%type INDEX BY BINARY_INTEGER;
TYPE temp_parent is TABLE
of t_pi_template.id_template_parent%type INDEX BY BINARY_INTEGER;
TYPE temp_tx_desc is TABLE of
t_description.tx_desc%type INDEX BY BINARY_INTEGER;
TYPE temp_nm_enum_data is
TABLE of t_enum_data.nm_enum_data%type INDEX BY BINARY_INTEGER;
TYPE temp_id_enum_data is
TABLE of t_enum_data.id_enum_data%type INDEX BY BINARY_INTEGER;
TYPE temp_pv_parentid is
TABLE of t_base_props.N_DISPLAY_NAME%type INDEX BY BINARY_INTEGER;
TYPE temp_aggrate is TABLE
of varchar2(1) INDEX BY BINARY_INTEGER;
TYPE temp_viewid is TABLE
of t_acc_usage.id_view%type INDEX BY BINARY_INTEGER;
TYPE temp_realpvid is TABLE
of t_acc_usage.id_view%type INDEX BY BINARY_INTEGER;
TYPE temp_viewname is TABLE
of t_base_props.NM_DISPLAY_NAME%type INDEX BY BINARY_INTEGER;
TYPE temp_viewtype is TABLE
of varchar2(8) INDEX BY BINARY_INTEGER;
TYPE temp_descriptionid is
TABLE of t_acc_usage.ID_VIEW%type INDEX BY BINARY_INTEGER;
TYPE temp_amount is TABLE
of t_acc_usage.AMOUNT%type INDEX BY BINARY_INTEGER;
TYPE temp_count is TABLE of
integer INDEX BY BINARY_INTEGER;
TYPE temp_currency is TABLE
of t_acc_usage.AM_CURRENCY%type INDEX BY BINARY_INTEGER;
TYPE temp_taxamount is
TABLE of t_acc_usage.TAX_FEDERAL%type INDEX BY BINARY_INTEGER;
TYPE temp_amountwithtax is
TABLE of t_acc_usage.tax_federal%type INDEX BY BINARY_INTEGER;
procedure
GetPCViewHierarchy(temp_id_acc int,temp_id_interval int,temp_id_lang_code
int,
name_temp out
temp_name,parent_temp out temp_parent,tx_desc_temp out temp_tx_desc ,
nm_enum_data_temp out
temp_nm_enum_data,id_enum_data_temp out temp_id_enum_data,
pv_parentid_temp out
temp_pv_parentid,
aggrate_temp out
temp_aggrate ,viewid_temp out temp_viewid,
realpvid_temp out temp_realpvid,
viewname_temp out
temp_viewname,viewtype_temp out temp_viewtype,
descriptionid_temp out temp_descriptionid,
amount_temp out
temp_amount,
count_temp out temp_count,
currency_temp out temp_currency,
taxamount_temp out
temp_taxamount
,amountwithtax_temp out
temp_amountwithtax);
end;
CREATE OR REPLACE package body GetPCViewHierarchy_pkg as
procedure GetPCViewHierarchy(temp_id_acc
int,temp_id_interval int,temp_id_lang_code int,
name_temp out
temp_name,parent_temp out temp_parent,tx_desc_temp out temp_tx_desc ,
nm_enum_data_temp out
temp_nm_enum_data,id_enum_data_temp out temp_id_enum_data,
pv_parentid_temp out
temp_pv_parentid,
aggrate_temp out
temp_aggrate ,viewid_temp out temp_viewid,
realpvid_temp out temp_realpvid,
viewname_temp out
temp_viewname,viewtype_temp out temp_viewtype,
descriptionid_temp out temp_descriptionid,
amount_temp out
temp_amount,
count_temp out temp_count,
currency_temp out temp_currency,
taxamount_temp out
temp_taxamount
,amountwithtax_temp out
temp_amountwithtax)
is
cursor c1 is
select
tb_po.n_display_name id_po,-- use the
display name as the product offering ID
pi_template.id_template_parent
id_template_parent,
decode(t_description.tx_desc,NULL,template_desc.tx_desc,t_description.tx_des c) as po_nm_name,
ed.nm_enum_data pv_child,
ed.id_enum_data pv_childID,
decode(parent_kind.nm_productview,NULL,tb_po.n_display_name,tenum_parent.id_ enum_data) as pv_parentID,
decode(pi_props.n_kind,15,'Y','N') as
AggRate,
decode(au.id_pi_instance,NULL,id_view,-au.id_pi_instance) as viewID,
id_view realPVID,
decode(tb_instance.nm_display_name,NULL,tb_template.nm_display_name,tb_insta nce.nm_display_name) as ViewName,
'Product' ViewType,
decode(t_description.tx_desc,NULL,template_props.n_display_name,id_view) as DescriptionID,
sum(au.amount) "Amount",
count(au.id_sess) "Count",
au.am_currency "Currency",
sum((nvl((au.tax_federal),
0.0) + nvl((au.tax_state), 0.0) +
nvl((au.tax_county), 0.0) +
nvl((au.tax_local), 0.0) +
nvl((au.tax_other), 0.0))) TaxAmount,
sum(au.amount + (nvl((au.tax_federal), 0.0)
+ nvl((au.tax_state), 0.0) +
nvl((au.tax_county), 0.0) +
nvl((au.tax_local), 0.0) +
nvl((au.tax_other), 0.0))) AmountWithTax
from t_usage_interval,t_acc_usage
au,t_base_props tb_template,t_pi_template pi_template,t_pi child_kind,
t_base_props pi_props,t_enum_data ed,t_base_props
template_props,t_description template_desc,
t_pi_template parent_template,t_pi parent_kind,t_enum_data
tenum_parent,t_base_props tb_po,
t_base_props tb_instance,t_description
where au.id_acc = temp_id_acc AND
au.id_usage_interval = temp_id_interval AND au.id_pi_template is not NULL
and tb_template.id_prop = au.id_pi_template
and pi_template.id_template =
au.id_pi_template
and child_kind.id_pi = pi_template.id_pi
and pi_props.id_prop = child_kind.id_pi
and ed.id_enum_data = au.id_view
and pi_template.id_template =
template_props.id_prop
and template_props.n_display_name =
template_desc.id_desc AND template_desc.id_lang_code = temp_id_lang_code
and parent_template.id_template =
pi_template.id_template_parent(+)
and parent_kind.id_pi =
parent_template.id_pi(+)
and tenum_parent.nm_enum_data =
parent_kind.nm_productview(+)
and tb_po.id_prop = au.id_prod(+)
and tb_instance.id_prop = au.id_pi_instance
and t_description.id_desc =
tb_po.n_display_name(+)
and t_description.id_lang_code =
temp_id_lang_code
and
t_usage_interval.id_interval =
temp_id_interval
GROUP BY
--t_pl_map.id_po,t_pl_map.id_pi_instance_parent,
tb_po.n_display_name,tb_instance.n_display_name,
t_description.tx_desc,template_desc.tx_desc,
tb_instance.nm_display_name,tb_template.nm_display_name,
tb_instance.nm_display_name, -- this
shouldn't need to be here!!
child_kind.nm_productview,
parent_kind.nm_productview,tenum_parent.id_enum_data,
pi_props.n_kind,
id_view,ed.nm_enum_data,ed.id_enum_data,
au.am_currency,
tb_template.nm_name,
pi_template.id_template_parent,
au.id_pi_instance,
template_props.n_display_name
ORDER BY tb_po.n_display_name ASC,
pi_template.id_template_parent ASC;
temp_1 t_base_props.n_display_name%type;
temp_2 t_pi_template.id_template_parent%type;
temp_3 t_description.tx_desc%type;
temp_4 t_enum_data.nm_enum_data%type;
temp_5 t_enum_data.id_enum_data%type;
temp_6 t_base_props.N_DISPLAY_NAME%type;
temp_7 varchar2(1);
temp_8 t_acc_usage.id_view%type;
temp_9 t_acc_usage.id_view%type;
temp_10 t_base_props.NM_DISPLAY_NAME%type;
temp_11 varchar2(8);
temp_12 t_acc_usage.ID_VIEW%type;
temp_13 t_acc_usage.AMOUNT%type;
temp_14 integer;
temp_15 t_acc_usage.AM_CURRENCY%type;
temp_16 t_acc_usage.tax_federal%type;
temp_17 t_acc_usage.tax_federal%type;
i number := 1;
begin
open c1;
loop
fetch c1 into
temp_1,temp_2,temp_3,temp_4,temp_5,temp_6,temp_7,temp_8,temp_9,
temp_10,temp_11,temp_12,temp_13,temp_14,temp_15,temp_16,temp_17;
exit when c1%notfound;
name_temp(i) := temp_1;
parent_temp(i) := temp_2;
tx_desc_temp(i) := temp_3;
nm_enum_data_temp(i) := temp_4;
id_enum_data_temp(i) := temp_5;
pv_parentid_temp(i) := temp_6;
aggrate_temp(i) := temp_7;
viewid_temp(i) := temp_8;
realpvid_temp(i) := temp_9;
viewname_temp(i) := temp_10;
viewtype_temp(i) := temp_11;
descriptionid_temp(i) := temp_12;
amount_temp(i) := temp_13;
count_temp(i) := temp_14;
currency_temp(i) := temp_15;
taxamount_temp(i) := temp_16;
amountwithtax_temp(i):= temp_17;
i := i+1;
end loop;
close c1;
exception
when others then null;
end;
end;
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: Harvinder.Singh_at_MetraTech.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Sep 05 2001 - 11:51:40 CDT
 |
 |