1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to modify this pl/sql block by using bulk collect

Discussion in 'SQL PL/SQL' started by 13478, Apr 13, 2016.

  1. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello,

    I have below pl/sql block, it runs slow against big databases, how to modify it by using bulk collect?

    --original script
    declare
    cursor adjustment_cursor
    is
    select adjm_uid, mf_aj.pryr_adjm
    from mf_aj, mf_tj
    where mf_aj.tj_uid_lo = mf_tj.uid_lo and mf_aj.pryr_adjm is not null
    and yba is not null and adjm_uid is not null;

    cursor validDocs_cursor(adjm_uid_in in varchar2)
    is
    select mf_tj.uid_lo
    from mf_aj, mf_tj
    where mf_aj.tj_uid_lo = mf_tj.uid_lo and mf_aj.pryr_adjm is null and mf_tj.adjm_uid = adjm_uid_in;

    begin

    for adj_row in adjustment_cursor
    loop
    for valid_row in validDocs_cursor(adj_row.adjm_uid)
    loop
    update mf_aj set mf_aj.pryr_adjm = adj_row.pryr_adjm
    where tj_uid_lo = valid_row.uid_lo;
    end loop;
    end loop;

    end;
    /

    Thank you very much in advance !!!




    cc:
     
  2. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    this is what I did for changing, is there anything wrong?

    declare


    cursor adjustment_cursor
    is
    select adjm_uid, mf_aj.pryr_adjm
    from mf_aj, mf_tj
    where mf_aj.tj_uid_lo = mf_tj.uid_lo and mf_aj.pryr_adjm is not null
    and yba is not null and adjm_uid is not null;

    TYPE c_arr1 IS TABLE OF adjustment_cursor%ROWTYPE;
    c_rows1 c_arr1;

    cursor validDocs_cursor(adjm_uid_in in varchar2)
    is
    select mf_tj.uid_lo
    from mf_aj, mf_tj
    where mf_aj.tj_uid_lo = mf_tj.uid_lo and mf_aj.pryr_adjm is null and mf_tj.adjm_uid = adjm_uid_in;
    TYPE c_arr2 IS TABLE OF validDocs_cursor%ROWTYPE;
    c_rows2 c_arr2;

    begin
    OPEN adjustment_cursor;
    FETCH adjustment_cursor
    BULK COLLECT
    INTO c_rows1 ;

    OPEN validDocs_cursor;
    FETCH validDocs_cursor
    BULK COLLECT
    INTO c_rows2 ;


    FORALL i IN c_rows1.FIRST .. c_rows1.LAST
    FORALL i IN c_rows2.FIRST .. c_rows2.LAST
    update mf_aj set mf_aj.pryr_adjm = c_rows1(i).pryr_adjm
    where tj_uid_lo = c_rows2(i).uid_lo;
    CLOSE adjustment_cursor;
    CLOSE validDocs_cursor;
    end;
    /
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    It won't work ....
    Mistake in use of FORALL
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    for example;

    Code (SQL):

    DECLARE
    cursor cur IS
      WITH
      adjust AS
      (
      SELECT
      adjm_uid,  mf_aj.pryr_adjm
      FROM
      mf_aj, mf_tj
      WHERE
      mf_aj.tj_uid_lo = mf_tj.uid_lo
      AND
      mf_aj.pryr_adjm IS NOT NULL
      AND
      yba IS NOT NULL
      AND adjm_uid IS NOT NULL
      ),
      valid_cursor AS
      (
      SELECT
      mf_tj.uid_lo,mf_tj.adjm_uid
      FROM
      mf_aj, mf_tj
      WHERE
      mf_aj.tj_uid_lo = mf_tj.uid_lo
      AND mf_aj.pryr_adjm IS NULL
      )  
      SELECT
      vc.uid_lo,aj.pryr_adjm  
      FROM
      valid_cursor vc
      JOIN
      adjust aj
      ON
      aj.adjm_uid =  vc.adjm_uid;
       
       
    TYPE  t_arr IS TABLE OF cur%rowtype;

    l_rows  t_arr;

    BEGIN

      OPEN cur;

      loop
      fetch cur bulk collect INTO l_rows LIMIT 10000;
      exit WHEN l_rows.COUNT = 0 ;
      forall z IN 1 .. l_rows.COUNT
      UPDATE
      mf_aj
      SET
      mf_aj.pryr_adjm = l_rows1(z).pryr_adjm
      WHERE tj_uid_lo = l_rows(z).uid_lo;  
      END loop;
      close cur;
      commit;
    END;

     
     
    Last edited: Apr 14, 2016
  5. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello Krasnoslobodtsev_si,

    Thank you very much.

    I tested it, get a little error, I am thinking too.



    Error report:
    ORA-06550: line 18, column 10:
    PL/SQL: ORA-00928: missing SELECT keyword
    ORA-06550: line 3, column 8:
    PL/SQL: SQL Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    DECLARE
    cursor cur IS
    WITH
    adjust AS
    (
    SELECT
    adjm_uid, mf_aj.pryr_adjm
    FROM
    mf_aj, mf_tj
    WHERE
    mf_aj.tj_uid_lo = mf_tj.uid_lo
    AND
    mf_aj.pryr_adjm IS NOT NULL
    AND
    yba IS NOT NULL
    AND adjm_uid IS NOT NULL
    )
    valid_cursor AS ===============> error point here, looks like some syntax is wrong
    (
    SELECT
    mf_tj.uid_lo,mf_tj.adjm_uid
    FROM
    mf_aj, mf_tj
    WHERE
    mf_aj.tj_uid_lo = mf_tj.uid_lo
    AND mf_aj.pryr_adjm IS NULL
    )
    SELECT
    vc.uid_lo,aj.pryr_adjm
    FROM
    valid_cursor vc
    JOIN
    adjust aj
    ON
    aj.adjm_uid = vc.adjm_uid;

    Thank you so much.




     
  6. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    DECLARE
    cursor cur IS
    WITH
    adjust AS
    (
    SELECT
    adjm_uid, mf_aj.pryr_adjm
    FROM
    mf_aj, mf_tj
    WHERE
    mf_aj.tj_uid_lo = mf_tj.uid_lo
    AND
    mf_aj.pryr_adjm IS NOT NULL
    AND
    yba IS NOT NULL
    AND adjm_uid IS NOT NULL
    ), ====>add "," here
    valid_cursor AS
    (
    SELECT
    mf_tj.uid_lo,mf_tj.adjm_uid
    FROM
    mf_aj, mf_tj
    WHERE
    mf_aj.tj_uid_lo = mf_tj.uid_lo
    AND mf_aj.pryr_adjm IS NULL
    )
    SELECT
    vc.uid_lo,aj.pryr_adjm
    FROM
    valid_cursor vc
    JOIN
    adjust aj
    ON
    aj.adjm_uid = vc.adjm_uid;

    TYPE t_arr IS TABLE OF cur.pryr_adjm%rowtype;

    l_rows t_arr;

    BEGIN

    OPEN cur;

    loop
    fetch cur bulk collect INTO l_rows LIMIT 10000;
    exit WHEN l_rows.COUNT = 0 ;
    forall z IN 1 .. l_rows.COUNT
    UPDATE
    mf_aj
    SET
    mf_aj.pryr_adjm = l_rows1(z).pryr_adjm
    WHERE tj_uid_lo = l_rows(z).uid_lo;
    END loop;
    close cur;

    commit;

    END;
    /

    Error report:
    ORA-06550: line 37, column 26:
    PLS-00225: subprogram or cursor 'CUR' reference is out of scope
    ORA-06550: line 37, column 1:
    PL/SQL: Item ignored
    ORA-06550: line 46, column 36:
    PLS-00597: expression 'L_ROWS' in the INTO list is of wrong type
    ORA-06550: line 46, column 8:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 53, column 30:
    PLS-00487: Invalid reference to variable 'CUR.PRYR_ADJM%ROWTYPE'
    ORA-06550: line 53, column 30:
    PLS-00382: expression is of wrong type
    ORA-06550: line 52, column 34:
    PL/SQL: ORA-00904: "L_ROWS1": invalid identifier

    Thank you
    ORA-06550: line 49, column 12:
    PL/SQL: SQL Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:













     
  7. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    DECLARE
    cursor cur IS
    WITH
    adjust AS
    (
    SELECT
    adjm_uid, mf_aj.pryr_adjm
    FROM
    mf_aj, mf_tj
    WHERE
    mf_aj.tj_uid_lo = mf_tj.uid_lo
    AND
    mf_aj.pryr_adjm IS NOT NULL
    AND
    yba IS NOT NULL
    AND adjm_uid IS NOT NULL
    ),
    valid_cursor AS
    (
    SELECT
    mf_tj.uid_lo,mf_tj.adjm_uid
    FROM
    mf_aj, mf_tj
    WHERE
    mf_aj.tj_uid_lo = mf_tj.uid_lo
    AND mf_aj.pryr_adjm IS NULL
    )
    SELECT
    vc.uid_lo,aj.pryr_adjm
    FROM
    valid_cursor vc
    JOIN
    adjust aj
    ON
    aj.adjm_uid = vc.adjm_uid;

    TYPE t_arr IS TABLE OF cur%ROWTYPE;

    l_rows t_arr;

    BEGIN

    OPEN cur;

    loop
    fetch cur bulk collect INTO l_rows LIMIT 10000;
    exit WHEN l_rows.COUNT = 0 ;
    forall z IN 1 .. l_rows.COUNT
    UPDATE
    mf_aj
    SET
    mf_aj.pryr_adjm = l_rows(z).pryr_adjm
    WHERE tj_uid_lo = l_rows(z).uid_lo;
    END loop;
    close cur;

    commit;

    END;
    /


    Works now, thank you so much!!!