This discussion is archived
2 Replies Latest reply: Dec 16, 2012 7:43 PM by 903814 RSS

Delete specific column from PL SQL Table

903814 Newbie
Currently Being Moderated
Hi All,

I have a specific requirement to delete some rows from a pl sql table. E.g. in the below code there are 2 rows in the table with pankaj as name. Could you please help me modify the code such that it deletes any one of them.


Code:
DECLARE
TYPE EXTRACT_RECTYPE
IS
  RECORD
  (
    NAME VARCHAR2 (240 BYTE),
    AGE  NUMBER );
TYPE extract_tabtype
IS
  TABLE OF EXTRACT_RECTYPE;
  L_EXTRACT_TAB EXTRACT_TABTYPE := EXTRACT_TABTYPE();
  L_EXTRACT_REC EXTRACT_RECTYPE ;
BEGIN
  L_EXTRACT_REC.NAME := 'pankaj';
  L_EXTRACT_REC.AGE  := 23;
  L_EXTRACT_TAB.extend;
  L_EXTRACT_TAB(1) := L_EXTRACT_REC;
  L_EXTRACT_TAB.EXTEND;
  L_EXTRACT_REC.NAME := 'snehav';
  L_EXTRACT_REC.AGE  := 23;
  L_EXTRACT_TAB(2)   := L_EXTRACT_REC;
  L_EXTRACT_REC.NAME := 'pankaj';
  L_EXTRACT_REC.AGE  := 24;
  L_EXTRACT_TAB.extend;
  L_EXTRACT_TAB(3) := L_EXTRACT_REC;
  FOR i IN L_EXTRACT_TAB.FIRST .. L_EXTRACT_TAB.LAST
  LOOP
    DBMS_OUTPUT.put_line (L_EXTRACT_TAB(i).name || ' ' || L_EXTRACT_TAB(i).age);
  END LOOP;
END;
I tried the below code but it is deleting both the rows with pankaj as name.Could you please assist me.
FOR I IN L_EXTRACT_TAB.first .. L_EXTRACT_TAB.last
LOOP
IF (L_EXTRACT_TAB(I).name = 'pankaj' ) THEN
DBMS_OUTPUT.PUT_LINE ('pankaj exists. Deleting one of them');
L_EXTRACT_TAB.DELETE(i);
END IF;
END LOOP;
FOR i IN L_EXTRACT_TAB.FIRST .. L_EXTRACT_TAB.LAST
LOOP
DBMS_OUTPUT.PUT_LINE (L_EXTRACT_TAB(I).name || ' ' || L_EXTRACT_TAB(I).AGE);
END LOOP;
Regards,
Pankaj

Edited by: Pankaj on Dec 16, 2012 8:33 AM

Edited by: Pankaj on Dec 16, 2012 10:56 AM
  • 1. Re: Delete specific column from PL SQL Table
    rp0428 Guru
    Currently Being Moderated
    Wrong forum! This forum is for SQL Developer questions only and this question has nothing to do with sql developer.

    Mark this question ANSWERED and post it in the sql and pl/sql forum.
    SQL and PL/SQL
    >
    I have a specific requirement to delete some rows from a pl sql table. E.g. in the below code there are 2 rows in the table with pankaj as name. Could you please help me modify the code such that it deletes any one of them.
    . . .
    I tried the below code but it is deleting both the rows with pankaj as name.
    >
    That's because after you delete one you continue through the loop so it finds, and deletes, any others that exist. If you find one and delete it use EXIT to break out of the loop.

    Mark this question ANSWERED and if you need more help with it you need to repost it in the proper forum.
  • 2. Re: Delete specific column from PL SQL Table
    903814 Newbie
    Currently Being Moderated
    Moving this question to SQL and PL SQL forum.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points