Results 1 to 2 of 2

Thread: Need help

  1. #1
    Join Date
    Apr 2011
    Posts
    4

    Post Unanswered: Need help

    Could anyone help me on below

    declare
    cursor s_cur is
    select set_id
    from staging
    where trunc(inserted_date) between to_date('10/mar/2011', 'dd/mm/yyyy') and to_date('25/mar/2011', 'dd/mm/yyyy')
    ORDER BY 1;


    TYPE RecList IS TABLE OF s_cur%ROWTYPE;
    recs RecList;

    a integer;

    Begin
    open s_cur;
    LOOP
    FETCH s_cur BULK COLLECT INTO recs ;
    FORALL i IN recs.first..recs.last

    Delete
    FROM staging WHERE set_id = recs(i);

    DBMS_OUTPUT.PUT_LINE('Total records deleted: ' || SQL%ROWCOUNT);
    EXIT WHEN s_cur%NOTFOUND;
    end loop;
    close s_cur;

    end ;

    i get below error
    WHERE data_set_id = recs(i);
    *
    ERROR at line 22:
    ORA-06550: line 22, column 28:
    PLS-00382: expression is of wrong type

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >WHERE data_set_id = recs(i);
    >PLS-00382: expression is of wrong type

    datatype of DATE_SET_ID is different from datatype of RECS(i) & results in error above
    SQL is different from PL/SQL. must only use SQL datatype in SQL statements

    whole procedure can be replaced with single statement below
    Code:
    DELETE FROM staging
    WHERE  Trunc(inserted_date) BETWEEN To_date('10/mar/2011', 'dd/mm/yyyy') AND
                                        To_date('25/mar/2011', 'dd/mm/yyyy');
    Last edited by anacedent; 04-26-11 at 19:22.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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