DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Error: PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Error: PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

    Hi All

    I am getting this error.

    Error: PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

    Can some tell me a work around?

    I am goin thru this doc http://asktom.oracle.com/pls/ask/f?p...:2367352052686

    Many Thanks
    Cheers!
    OraKid.

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    This is my Query...

    procedure pr_create_payment_invoice (
    ip_batch_load_id in number,
    ip_file_id in number
    ) is
    c_unit_name varchar2(30):='pr_create_payment_invoice';

    l_counter NUMBER := 0;
    l_insert_count NUMBER := 0;

    TYPE t_invoice_id IS TABLE OF pk_fin_data_source.t_invoice;
    l_invoice_id t_invoice_id;

    CURSOR cr_get_invoice_id(cp_batch_load_id in number) IS
    SELECT /*+ use_hash(invoice) */
    DISTINCT sfpi.invoice_id invoice_id
    ,invoice.customer_account_invoice_str invoice_no
    ,sfpi.payment_id
    ,NULL
    BULK COLLECT INTO l_invoice_id
    FROM stage_fin_payment sfp
    ,stage_fin_payment_invoice sfpi
    ,(SELECT /*+ no_merge */
    invoice_id
    ,customer_account_invoice_str
    FROM stage_fin_invoice sfi
    WHERE batch_load_id = cp_batch_load_id
    GROUP BY invoice_id
    ,customer_account_invoice_str) invoice
    WHERE sfp.to_account_id = sfpi.account_id
    AND sfp.payment_id = sfpi.payment_id
    AND sfp.from_account_id IS NULL
    AND sfp.batch_load_id = sfpi.batch_load_id
    AND sfp.batch_load_id = cp_batch_load_id
    AND EXISTS
    (SELECT 1
    FROM invoice_col ic
    WHERE sfpi.invoice_id = ic.invoice_id)
    AND sfpi.invoice_id = invoice.invoice_id(+);

    begin

    pk_globals.pr_add_call(GC_PACKAGE_NAME||'.'||c_unit_name);

    OPEN cr_get_invoice_id(ip_batch_load_id);

    LOOP
    FETCH cr_get_invoice_id BULK COLLECT INTO l_invoice_id LIMIT GC_COMMIT_SIZE;

    l_counter := l_invoice_id.COUNT;
    l_insert_count := l_insert_count + l_counter;

    --
    FORALL l_invoiceid IN 1..l_counter
    insert into tallyman_interface_line (
    file_id,
    line_type,
    sequence_no,
    batch_load_id,
    interface_line_1,
    interface_line_2,
    record_created_timestamp
    ) select
    ip_file_id,
    2,
    tallyman_line_no_seq.nextval,
    ip_batch_load_id,
    customer_account_invoice_str --CR 75539
    ||'|'||current_due
    ||'|'||case when (payment_date + grace_period_days > payment_extract_time)
    then 0 else 1 end
    ||'|'||null
    ,
    null,
    sysdate
    from stage_fin_invoice,
    stage_control
    where batch_load_id = ip_batch_load_id
    and invoice_id = l_invoice_id.invoice_id(l_invoiceid);

    COMMIT;

    EXIT WHEN cr_get_invoice_id%NOTFOUND;

    END LOOP;

    CLOSE cr_get_invoice_id;
    Cheers!
    OraKid.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    Your error is with the table definition in:
    Code:
    TYPE t_invoice_id IS TABLE OF pk_fin_data_source.t_invoice;
    l_invoice_id t_invoice_id;
    You need to define separate tables for each column in the SELECT...BULK COLLECT INTO...
    For example:
    Code:
    TYPE TXT_TYP IS TABLE OF VARCHAR2(100)
         INDEX BY BINARY_INTEGER;
    V_INVOICE_ID TXT_TYP;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thank. I got t.
    Cheers!
    OraKid.

  5. #5
    Join Date
    Mar 2013
    Posts
    1
    This is the error i am getting:-
    PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

    And my Procedure is as follows:-
    DECLARE
    v_count NUMBER;
    TYPE ARRAY IS TABLE OF sourcing%ROWTYPE;
    l_data ARRAY;
    p_array_size PLS_INTEGER DEFAULT 5;

    CURSOR c IS SELECT * FROM sourcing where rownum<10 ;
    BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
    FORALL i IN 1..l_data.COUNT
    update sourcing set DISC = trunc(to_date('01/01/1901','MM/DD/YYYY'));
    COMMIT;
    EXIT WHEN c%NOTFOUND;
    END LOOP;

    CLOSE c;



    Please help me in this regard

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    update sourcing set DISC = trunc(to_date('01/01/1901','MM/DD/YYYY')); is the only statement you need as you are changing every row in the table in every iteration of the loop.

    and stop fetching across a commit - you will end up with ORA-0155

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by davey23uk View Post
    update sourcing set DISC = trunc(to_date('01/01/1901','MM/DD/YYYY')); is the only statement you need as you are changing every row in the table in every iteration of the loop.

    and stop fetching across a commit - you will end up with ORA-0155
    I disagree with both of your answers... There are restrictions on using a FORALL clause. The original poster should change his code to a regular for loop.

    Code:
    FOR i IN 1..l_data.COUNT
    LOOP
    ...
    END LOOP;
    COMMIT;
    You don't need to commit that much, you might be able to only commit at the end.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width