5 Replies Latest reply: Mar 7, 2013 10:42 PM by Billy~Verreynne RSS

    i want to inset id id 1,2,3,4..n in pl/sql how to right the code in pl/sql

    963725
      i want to insert the id 1,2,3,4...n dont use sequence and rowid
      id|name
      --------------
      |A
      |B
      |C
      |E
      .
      .
      .
      n

      regards,
      Akhlesh
        • 1. Re: i want to inset id id 1,2,3,4..n in pl/sql how to right the code in pl/sql
          LPS
          Use level with connect by level....


          select level from <tab_name>
          connect by level <= <number>;
          • 2. Re: i want to inset id id 1,2,3,4..n in pl/sql how to right the code in pl/sql
            Manik
            What is that name column?
            What should be inserted after 'Z'?

            To generate the number sequence you can use this sql.
                SELECT ROWNUM num
                  FROM DUAL
            CONNECT BY ROWNUM <= 100;  --- n =100 here
            Cheers,
            Manik.
            • 3. Re: i want to inset id id 1,2,3,4..n in pl/sql how to right the code in pl/sql
              ragu.dba.in
              Assuming your table is TEST1 with id and name as coulmns..

              1. you can write a function...

              Create or Replace funtion ins_id_test1 return number is
              v_id number;
              begin
              select max(nvl(id,0)) into v_id from test1;

              v_id := v_id +1;

              return v_id;

              Exception
              when no_data_found then
              v_id := 1; --initialising the First value.
              return v_id;
              End;

              2. call it in insert statements..

              insert into test1 values (ins_id_test1,'A');
              insert into test1 values (ins_id_test1,'B');

              3. Select id,name from test1;

              id name
              -- ---------
              1 A
              2 B
              • 4. Re: i want to inset id id 1,2,3,4..n in pl/sql how to right the code in pl/sql
                Manik
                And if you are looking for something generic.. Not sure if this is really required for your requirement.. But we can generate the name on the fly based on the id using SQL without PL/SQL.

                WITH chrset AS
                        (    SELECT CHR (ASCII ('A') + ROWNUM - 1) alph
                               FROM DUAL
                         CONNECT BY ROWNUM <= 26),
                     t AS
                        (SELECT alph FROM chrset
                         UNION ALL
                         SELECT a.alph || b.alph
                           FROM chrset a, chrset b
                         UNION ALL
                         SELECT a.alph || b.alph || c.alph
                           FROM chrset a, chrset b, chrset c
                         UNION ALL
                         SELECT a.alph || b.alph || c.alph || d.alph
                           FROM chrset a,
                                chrset b,
                                chrset c,
                                chrset d
                         UNION ALL
                         SELECT a.alph || b.alph || c.alph || d.alph || e.alph
                           FROM chrset a,
                                chrset b,
                                chrset c,
                                chrset d,
                                chrset e),
                     basetbl AS (SELECT ROWNUM r, alph FROM t)
                SELECT r id, alph nm
                  FROM basetbl
                 WHERE r <= 100;
                output
                ID     NM
                ----------------------
                1     A
                2     B
                3     C
                4     D
                5     E
                6     F
                7     G
                8     H
                9     I
                10     J
                11     K
                12     L
                13     M
                14     N
                15     O
                16     P
                17     Q
                18     R
                19     S
                20     T
                21     U
                22     V
                23     W
                24     X
                25     Y
                26     Z
                27     AA
                28     AB
                29     AC
                30     AD
                31     AE
                32     AF
                33     AG
                34     AH
                35     AI
                36     AJ
                37     AK
                38     AL
                39     AM
                40     AN
                41     AO
                42     AP
                43     AQ
                44     AR
                45     AS
                46     AT
                47     AU
                48     AV
                49     AW
                50     AX
                51     AY
                52     AZ
                53     BA
                54     BB
                55     BC
                56     BD
                57     BE
                58     BF
                59     BG
                60     BH
                61     BI
                62     BJ
                63     BK
                64     BL
                65     BM
                66     BN
                67     BO
                68     BP
                69     BQ
                70     BR
                71     BS
                72     BT
                73     BU
                74     BV
                75     BW
                76     BX
                77     BY
                78     BZ
                79     CA
                80     CB
                81     CC
                82     CD
                83     CE
                84     CF
                85     CG
                86     CH
                87     CI
                88     CJ
                89     CK
                90     CL
                91     CM
                92     CN
                93     CO
                94     CP
                95     CQ
                96     CR
                97     CS
                98     CT
                99     CU
                100     CV
                Cheers,
                Manik.

                Edited by: Manik on Mar 8, 2013 11:45 AM
                • 5. Re: i want to inset id id 1,2,3,4..n in pl/sql how to right the code in pl/sql
                  Billy~Verreynne
                  960722 wrote:
                  i want to insert the id 1,2,3,4...n dont use sequence and rowid
                  Why not? What is the goal behind this?