CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
user3224907
user3224907

Reputation: 768

Oracle bulk collect error PLS-00201

My following code is not working as expected. I am getting the following error:

Error at line 1
ORA-06550: line 20, column 18:
PLS-00201: identifier 'I' must be declared
ORA-06550: line 20, column 9:
PL/SQL: Statement ignored

My code:

DECLARE

process_limit  CONSTANT  SIMPLE_INTEGER := 500;

CURSOR c1 IS 
  SELECT * FROM cdtx cx
  WHERE EXISTS (SELECT 1 FROM clmx c
                WHERE c.cid = cx.cid);

TYPE t_c1 IS TABLE OF c1%ROWTYPE;
v_c1 t_c1;

BEGIN
  OPEN c1;
  LOOP
    FETCH c1 BULK COLLECT INTO v_c1 LIMIT process_limit;
    EXIT WHEN v_c1.COUNT = 0;

    BEGIN
      FORALL i IN 1..v_c1.COUNT
        INSERT INTO cdx
          (cid,
           oind,
           tind,
           ot_date)
        VALUES (v_c1(i).cid,
                v_c1(i).new_oind,
                v_c1(i).new_tind,
                v_c1(i).ot_date);
        COMMIT;

        IF  v_c1(i).new_t_amt IS NOT NULL 
        AND v_c1(i).new_t_date IS NOT NULL
        THEN
          INSERT INTO ctx
             (cid,
              t_amt,
              t_date,
              t_id)
          VALUES
             (v_c1(i).cid,
              v_c1(i).new_t_amt,
              v_c1(i).new_t_date,
              '1');
          COMMIT;
        END IF;
    END;
  END LOOP;
END;

The cursor returns close to 10k records. I initially had working code without oracle's BULK COLLECT feature but I figured BULK COLLECT would be faster. I have tried several variations for the IF condition on line 20 without success. I need to evaluate columns new_t_amt and new_t_date and check if they are NOT NULL. Only if they are NOT NULL should the INSERT happen. Thanks for your help.

Upvotes: 0

Views: 600

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

PL/SQL is a programming language directly installed in your DBMS. However, The PL/SQL engine must still "talk" to the SQL engine in order to have the SQL statements executed. So it's always faster to run an SQL statement direcly rather than calling a PL/SQL function. For this reason Boneist's suggestion to use a mutli table insert, should be faster than your approach.

In some rare situations the classic approach with single inserts could prove even faster. That would particularily apply with covering indexes:

CREATE INDEX idx_for_cdx ON cdtx(cid, new_oind, new_tind, ot_date);
CREATE INDEX idx_for_ctx ON cdtx(cid, new_t_amt, new_t_date);

The column order in the second index may matter, so you could add

CREATE INDEX idx_for_ctx2 ON cdtx(new_t_amt, new_t_date, cid);

and see which index gets used.

INSERT INTO cdx (cid, oind, tind, ot_date) 
  SELECT cid, new_oind, new_tind, ot_date
  FROM cdtx
  WHERE cid IN (SELECT cid FROM clmx);

INSERT INTO ctx (cid, t_amt, t_date, t_id) 
  SELECT cid, new_t_amt, new_t_date, '1'
  FROM cdtx
  WHERE cid IN (SELECT cid FROM clmx)
  AND new_t_amt IS NOT NULL 
  AND new_t_date IS NOT NULL;

COMMIT;

You could also put this in an anonymous PL/SQL block, if you preferred so:

BEGIN
  INSERT INTO cdx ...
  INSERT INTO ctx ...
  COMMIT;
END;

The sole difference would be that you would have less round trips from your machine to the database machine, as these would be moved to inside the database machine between the PL/SQL engine and the SQL engine.

Upvotes: 1

Boneist
Boneist

Reputation: 23588

If you were using a cursor for loop, then it's possible you would see an improvement by moving to bulk collect, bearing in mind the caveats mentioned by Thorsten in the comments on your question.

However, you can easily do this in a single insert statement, assuming you don't run into any of the restrictions (and I'm hoping there isn't a foreign key from ctx.cid to cdx.cid), using a multitable insert like so:

INSERT ALL
  WHEN new_t_amt IS NOT NULL
       AND new_t_date IS NOT NULL THEN
       INTO ctx (cid, t_amt, t_date, t_id) VALUES (cid, new_t_amt, new_t_date, '1')
  WHEN 1 = 1 THEN INTO cdx (cid, oind, tind, ot_date) VALUES (cid, new_oind, new_tind, ot_date)
SELECT *
FROM   cdtx cx
WHERE  EXISTS (SELECT 1
               FROM   clmx c
               WHERE  c.cid = cx.cid);

Upvotes: 1

Related Questions