2 Replies Latest reply on Sep 27, 2017 7:29 AM by John_K

    Need to update two columns, Batch ID and Sequence_Number.

    SandeepSB4

      Hi All,

       

      I need to load data in custom table. table is having around 20 columns, in which, user will be entering data in 18 columns using excel. For remaining two columns Batch_ID and Sequence_Num, data should inserted insert by default.

      I created two sequences, one for each.

       

      Here the issue is, For

      If user inserts, 100 records at time using excel, Batch ID should be same for all those 100 records and Sequence_Num be in sequencing..

       

      How can this be achieve.

       

      Thanks,

      Sandeep

        • 1. Re: Need to update two columns, Batch ID and Sequence_Number.
          SandeepSB4

          Even tried there as well.

          But the problem is, for every record, the same value is populating.

          For example, I have created a sequence and the next value is 75 in the sequence and I have created a layout where 10 records layout is coming and user can add more manually by inserting rows in excel.

          But here in all those 10 rows, default is coming as 75 but my requirement is to get the value as 75, 76, 77 ..... so on.

           

          Hope this clarifies the question.

          • 2. Re: Need to update two columns, Batch ID and Sequence_Number.
            John_K

            What are you using to load the data in? I.e. are you using a pl/sql process?

             

            $> cat a.sql
            create table xxa(batch_id number, sequence_id number);
            create sequence xxa_s;
            create sequence xxb_s;
            
            
            declare
              l_a number;
            begin
              l_a := xxa_s.nextval;
              insert into xxa(batch_id, sequence_id)
              select l_a, xxb_s.nextval from dual connect by level < 5;
            end;
            /
            
            
            select * from xxa;
            
            
            drop table xxa;
            drop sequence xxa_s;
            drop sequence xxb_s;
            
            
            exit;
            $> spe @a.sql
            
            
            SQL*Plus: Release 10.1.0.5.0 - Production on Wed Sep 27 08:28:31 2017
            
            
            Copyright (c) 1982, 2005, Oracle.  All rights reserved.
            
            
            
            
            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            
            
            
            
            Table created.
            
            
            
            
            Sequence created.
            
            
            
            
            Sequence created.
            
            
            
            
            PL/SQL procedure successfully completed.
            
            
            
            
              BATCH_ID SEQUENCE_ID
            ---------- -----------
                     1           1
                     1           2
                     1           3
                     1           4
            
            
            
            
            Table dropped.
            
            
            
            
            Sequence dropped.
            
            
            
            
            Sequence dropped.
            
            
            Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options