help needed with a procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: help needed with a procedure

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    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

  2. #2
    Join Date
    Mar 2001
    Posts
    18

    Talking

    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

  3. #3
    Join Date
    Aug 2000
    Posts
    462
    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.


  4. #4
    Join Date
    Feb 2001
    Posts
    184
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width