help needed with a procedure

Hi friends,
I am new to pl/sql development. Will someone helpme how to correct the error in the following procedure?


create or replace package pkg1 is
type array is table of number
index by binary_integer;
end;
/


create or replace procedure proc2
(cc_date_start1 number, cc_date_end1 number,
cc_date_start2 number, cc_date_end2 number,
vpar_list varchar2(4000),vpar_d_num pkg1.array)
is
vtmp_counter number:=1;
vtmp_list varchar2(4000);
vtmp_flag boolean:=true;


begin

commit;

vtmp_list:=vpar_list;
loop
if
vtmp_list is not null then
vpar_d_num(vtmp_counter):=substr
(vtmp_list,1,instr(vtmp_list,',')-1);

vtmp_counter:=vtmp_counter+1;

vtmp_list:=substr(vtmp_list,instr(vtmp_list,',')+1,
length(vtmp_list));
end if;

if
instr(vtmp_list, ',')<1 then
vtmp_flag:=false;
end if;

if
vtmp_flag then
vtmp_list:=substr(vtmp_list, instr(vtmp_list, ',')+1,
length(vtmp_list));
else
vtmp_list:='';
end if;

end loop;


FORALL i IN vpar_d_num.FIRST..vpar_d_num.LAST
insert into temp_table1 (
d_num,
cc_date,
i_type,
i_addr,
a_type,
a_addr,
corporate_id,
ser_loc
)
select d_num,
cc_date,
i_type,
i_addr,
a_type,
a_addr,
corporate_id,
ser_loc
from icdr_copy where ( d_num = vpar_d_num(i) and
( cc_date >= cc_date_start1 and
cc_date <= cc_date_end1) ) or
d_num = vpar_d_num(i) and
( cc_date >= cc_date_start2 and
cc_date <= cc_date_end2)
;

end;
/

The error I am getting when I run it is as follows:

MGR-00072: Warning: PROCEDURE PROC2 created with compilation errors.
SQLWKS> sho errors
Errors for PROCEDURE PROC2:
LINE/COL ERROR
--------------------------------------------------------------------------------
17/2 PLS-00363: expression 'VPAR_D_NUM' cannot be used as an assignment targ
17/2 PL/SQL: Statement ignored

Thanks
manjunath

Hi Manjunathk,

I am new to PL/SQL also (ok, very new) but I was wondering if the problem could be with the parameter?

The default mode for parameters is IN, which can't be modified in the procedure. They are treated like constants within the procedure. Both OUT and IN OUT parameter modes can be written to, though.

Good luck with this!

Mary

You are trying to assign a substring to a PL/SQL table of numbers. It is a type mismatch. Either change your array, or change the datatype of the data you store in the array.

Hello manjunath
,

Basic Error in Your Procedure...

You can not have Length specified for any Parameter as you did for
vpar_list varchar2(4000)

You can have it like Tablename.ColumName%Type, or Just as
Varchar or Varchar2 ... You can limit the Length as You did. Rest should be Fine.

Thanks