Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Location
    UK
    Posts
    21

    Unanswered: Deleting using PL

    I have the following problem.

    I have to delete 2 or more records from one table in another table for example

    in Sql it would be

    delete from past_course where course_id =(select course_id from course where licence_id = &var);

    It will work when there is only one record related but when I get 2 record it won't delete so I tried to use PL. But it says that it returs more than one row so it can't delete.

    Do you have any idea?

    Thank Prophet

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Deleting using PL

    Just change to:

    delete from past_course where course_id in (select course_id from course where licence_id = &var);

  3. #3
    Join Date
    Jan 2004
    Location
    UK
    Posts
    21
    Thanks a lot

    it worked.

    prompt
    accept var prompt "The number of the licence category you want to delete:"

    drop table temp;
    create table temp(
    course_id number(2) primary key);

    declare
    cursor c is select course_id from course where licence_id = 2;
    cursor d is select course_id from past_cour for update;
    x number;
    begin
    open c;
    open d;
    for i in 1..5 loop
    fetch c into x;
    exit when c%notfound;
    insert into temp values(x);
    delete from past_cour where current of d;
    end loop;
    close c;
    close d;
    end;
    /

    declare
    cursor b is select * from past_cour for update;
    cursor a is select * from temp for update;
    begin
    open b;

    /*loop
    delete from past_cour where current of b;
    exit when b%notfound;
    end loop; */
    end;
    /


    accept menu prompt 'Press enter to return to menu:'
    define menu=menu.sql
    start menu

    Just to clarify what was wrong with my statement here?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by profetas
    ...
    Just to clarify what was wrong with my statement here?
    I don't know, what happened when you ran it? Was there a specific error message?

  5. #5
    Join Date
    Jan 2004
    Location
    UK
    Posts
    21
    ERROR at line 1:
    ORA-01410: Invalid ROWID
    ORA-06512: at line 8

    I have tried selecting a table a row, I just don't know.
    What would be the basic deletation using a PL?

    Thanks Prophet

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, that narrowed it down for me. Look at this:

    open d;
    for i in 1..5 loop
    fetch c into x;
    exit when c%notfound;
    insert into temp values(x);
    delete from past_cour where current of d;
    end loop;

    There is no "current" d record, because you never fetch from d!

  7. #7
    Join Date
    Jan 2004
    Location
    UK
    Posts
    21
    Doesn't fetch insert a value into a var?

    Can I just select the first row using fetch? and how do I go to the next row.

    I promise these will be the last questions.

    Thanks

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by profetas
    Doesn't fetch insert a value into a var?

    Can I just select the first row using fetch? and how do I go to the next row.

    I promise these will be the last questions.

    Thanks
    Yes, it may help you to think of cursors as analogous to read-only files:

    PHP Code:
    Cursor operation          Analogous UTL_FILE Operation
    ----------------          ----------------------------
    open                      fopen (mode 'R')
    fetch                     get_line
    close                     fclose 
    So typical usage is:
    PHP Code:
    open c;
    loop
      fetch c into r
    ;
      exit 
    when c%notfound;
      <
    process row>
    end loop
    close c
    i.e. open the cursor, fetch a record, fetch another record, ... until no more records found, then close the cursor.

    A much easier (and less error-prone) way to write the same code would be:
    PHP Code:
    for r in c loop
      
    <process row>
    end loop

  9. #9
    Join Date
    Mar 2004
    Posts
    9

    Lightbulb Re: Deleting using PL

    Originally posted by andrewst
    Just change to:

    delete from past_course where course_id in (select course_id from course where licence_id = &var);
    I guess this would also work if an 'exists' clause is used and would be faster too

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Deleting using PL

    Originally posted by visheetal
    I guess this would also work if an 'exists' clause is used and would be faster too
    Yes that would also work. Whether it would be faster is another matter - it might be, it might not. Consider:

    delete from billion_row_table where pk_col in (select col from one_row_table);

    versus:

    delete from billion_row_table b where exists (select 1 from one_row_table o where o.col = b.pk_col);

    I think the IN version would be much faster there (2 reads vs. 2 billion)! Whereas the EXISTS version would be faster if the tables were reversed.

Posting Permissions

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