2 Replies Latest reply: Dec 16, 2012 9:43 PM by 903814 RSS

    Delete specific column from PL SQL Table

    903814
      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
          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.
          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
            Moving this question to SQL and PL SQL forum.