PL/SQL insert data into a column with values corresponding to values of another column

Question

There is a table with table structure something like this customer_id number(10), listing_id number(12).

Now The data in this table is somewhat above 10 million so i've been given a task of adding a process_id to the table so that the data can be processed in batches in future operations.

so I added a column process_id to the table
alter table temp_lid_cid add process_id number(1) ;

Now i have to add process ids to the customer_ids at random 1 2 3 4 5 6 7, so that they will get processed according to their process_ids when condition where process_id = $1

There are millions of data so i wrote a simple PL

declare 
i temp_lid_cid.customer_id%type; 
c temp_lid_cid.process_id%type;
begin
c:=0;
for i in (select customer_id from temp_lid_cid)
loop
    if (c = 7) then
        c := 0;
    end if;
    c := c+1;
        execute immediate q'[insert into temp_lid_cid(process_id) select :var1 as process_id from temp_lid_cid where customer_id = :var2]'using i,c;
  end loop;
end;

It throws this error

Error report -
ORA-06550: line 12, column 145:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 12, column 9:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I tried running the insert statement without execute immediate too but it still threw an error.I also tried running the insert statement for a single customer outside the PL and it worked fine.

If you can suggest any other way to do what i'm trying to do without PL that would be great too.

XING · Accepted Answer · 2019-11-19 06:38:02Z

3

Now The data in this table is somewhat above 10 million so i've been given a task of adding a process_id to the table so that the data can be processed in batches

Insert will Insert a new row to your table. You need an Update statement to fullfill your requirement. See below:

DECLARE
    i   temp_lid_cid.customer_id%TYPE;
    c   temp_lid_cid.process_id%TYPE;

BEGIN
    c   := 0;
    FOR i IN (SELECT customer_id FROM temp_lid_cid ) 
LOOP
        IF ( c = 7 )
        THEN
            c   := 0;
        END IF;
        c   := c + 1;

        UPDATE temp_lid_cid
            SET
                process_id = c
        WHERE customer_id = i.customer_id;

    END LOOP;

    COMMIT;
END;

Error in your code:

In your Loop, customer_id is fetched using i.customer_id. So in your insert statement replace as below:

using c,i.customer_id;

Suggestion:

Since the number of rows is 10 Million so i would recommend to use BULK Operation to perform Update.

DECLARE   
    c   temp_lid_cid.process_id%TYPE;    
    type v_cust_id is table of temp_lid_cid.customer_id%TYPE index by pls_integer;    
    i v_cust_id;

BEGIN
    c   := 0;

    SELECT customer_id 
    BULK COLLECT INTO i
    FROM temp_lid_cid;

    FORALL rec IN 1..i.count
        UPDATE temp_lid_cid
            SET
                process_id = c + i(rec) -- Updating Process_Id with Customer_id
        WHERE customer_id = i(rec);

    COMMIT;
END;

edited Nov 19, 2019 at 6:38

answered Nov 19, 2019 at 6:09

XING

9,9264 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Abal Over a year ago

can you please explain to me what changes you have made to perform the bulk operation. why declare v_cust_id as a table. what is process_id = c + i(rec)

Abal Over a year ago

is commit necessary for bulk operation?

XING Over a year ago

Commit save the transaction on the disk. Irrespective of either BULK operation or LOOP commit is needed. This process_id = c + i(rec) adds your loop variable to your C variable. In this case it would be 1 2 3 4 ...

Ed Bangga · Accepted Answer · 2019-11-19 05:55:15Z

1

you interchanged your sql type.

declare 
i temp_lid_cid.process_id%type; 
c temp_lid_cid.customer_id%type;

answered Nov 19, 2019 at 5:55

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Comments

Popeye · Accepted Answer · 2019-11-19 14:51:56Z

Why use PL/SQL block and loop for it. It can be done using a single merge statement as following: (I am using the range from 1-4 numbers, you can use 1-7 numbers by replacing 4 with 7 in merge statement)

Oracle table creation:

SQL> CREATE TABLE TEMP_LID_CID (
  2      CUSTOMER_ID   NUMBER(10),
  3      LISTING_ID    NUMBER(12),
  4      PROCESS_ID    NUMBER(1)
  5  );

Table created.

Inserting data into the table:

SQL> insert into temp_lid_cid values (1,10,null);

1 row created.

SQL> insert into temp_lid_cid values (1,20,null);

1 row created.

SQL> insert into temp_lid_cid values (1,30,null);

1 row created.

SQL> insert into temp_lid_cid values (1,40,null);

1 row created.

SQL> insert into temp_lid_cid values (1,50,null);

1 row created.

SQL> insert into temp_lid_cid values (2,10,null);

1 row created.

SQL> insert into temp_lid_cid values (2,20,null);

1 row created.

SQL> insert into temp_lid_cid values (2,30,null);

1 row created.

Current view of the data

SQL> select * from TEMP_LID_CID;

CUSTOMER_ID LISTING_ID PROCESS_ID
----------- ---------- ----------
          1         10
          1         20
          1         30
          1         40
          1         50
          2         10
          2         20
          2         30

8 rows selected.

SQL>

query to achieve the desired result:

SQL> MERGE INTO TEMP_LID_CID T USING (
  2                                      SELECT
  3                                          T1.*,
  4                                          T1.ROWID   AS RID,
  5                                          MOD(ROW_NUMBER() OVER(
  6                                              ORDER BY
  7                                                  T1.CUSTOMER_ID
  8                                          ), 4) AS RANDOM_PROCESS_ID -- replace 4 with 7
  9                                      FROM
 10                                          TEMP_LID_CID T1
 11                                  )
 12  T1 ON ( T.ROWID = T1.RID )
 13  WHEN MATCHED THEN UPDATE SET T.PROCESS_ID = DECODE(T1.RANDOM_PROCESS_ID, 0, 4, T1.RANDOM_PROCESS_ID); -- replace 4 with 7

Data after update:

SQL> select * from TEMP_LID_CID;

CUSTOMER_ID LISTING_ID PROCESS_ID
----------- ---------- ----------
          1         10          1
          1         20          2
          1         30          3
          1         40          4
          1         50          1
          2         10          2
          2         20          3
          2         30          4

8 rows selected.

SQL>

theoa · Accepted Answer · 2019-11-19 07:42:32Z

0

Since process_id can be random, you could also use a simple query like this:

update temp_lid_cid set process_id = mod(rownum,7)+1;

answered Nov 19, 2019 at 7:42

theoa

806 bronze badges

Collectives™ on Stack Overflow

PL/SQL insert data into a column with values corresponding to values of another column

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related