Home » SQL & PL/SQL » SQL & PL/SQL » How we can run pl/sql procedure in parallel (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
How we can run pl/sql procedure in parallel [message #573433] Fri, 28 December 2012 08:42 Go to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Hi Experts,

we have many tables in Cursor For Loop :


CREATE OR REPLACE PROCEDURE EBILL_BULK_UPDATE_SERVICE(in_cycle  VARCHAR2)
  AS

      v_cnt   NUMBER;    -----Variable used for checking table is partitioned or not partitioned

    CURSOR cur_update                -----Cursor defined for Updating EBILL tables for service_id
    is
    SELECT  table_name
          , cycle_name
    FROM    NNP_EBILL_UPDATE
    WHERE   TABLE_NAME IN
     (
        'EBILL_ACCESS_FEES',
        'EBILL_ACCESS_FEES_REFUND',
        'EBILL_CURRENT_DUE',
        'EBILL_DETAIL_DSL_CALL',
        'EBILL_DISCOUNT_DETAILS',
        'EBILL_FAMILY_FRIENDS',
        'EBILL_JAWAL_NET',
        'EBILL_LOCAL_NETWORK_CALL',
        'EBILL_OTHER_CHARGES',
        'EBILL_ROAMING_CHARGE',
        'EBILL_SERVICES',
        'EBILL_SERVICES_TOTAL',
        'EBILL_SERVICE_DETAILS',
        'EBILL_USAGE_CHARGES',
        'EQUIP_INSTALL_TOTALS',
        'EQUIP_INST_INSTALLMENTS',
        'EQUIP_INST_NEW_AGREEMENT',
        'EQUIP_INST_PAYMENTS'
    )
    AND cycle_name like  in_cycle ;

-------------------------------------------------------------------------------------------------------
        BEGIN

        dbms_application_info.set_module(module_name => 'E-Bill',action_name => 'EBILL_GATHER_SP_STATS Procedure');


        FOR  cur_rec IN cur_update
        LOOP

           v_table_name := cur_rec.table_name;
           v_cycle_name := cur_rec.cycle_name;


            SELECT  COUNT(1)
            INTO    v_cnt
            FROM    all_tab_partitions
            WHERE   table_owner = 'EBILL'
            AND     table_name = v_table_name;

           IF v_cnt = 0 THEN

             EXECUTE IMMEDIATE 'update ebill.' ||v_table_name || ' set service_id = ''01'' || service_id where service_id like ''1%'''  ;
             dbms_output.put_line('Rows updated: ' || To_char(SQL%RowCount));
             DBMS_OUTPUT.put_line('Bulk Updates   : ' || (DBMS_UTILITY.get_time - l_start));

              v_count:= SQL%RowCount;

              UPDATE   NNP_EBILL_UPDATE
              SET      updated_count =  v_count
                     , update_status =  1
              WHERE    table_name = v_table_name
              AND      cycle_name = v_cycle_name
              AND      update_status = 0;

           ELSE

           BEGIN

            SELECT    partition_starts_with
            INTO      v_partition_starts_with
            FROM      EBILL_PARTITION_TAB_DTLS
            WHERE     table_name = v_table_name;

            v_partition_name := v_partition_starts_with||v_cycle_name;
            Dbms_output.put_line ('The table ' || v_table_name  || ' Has  partition '|| v_partition_name );

            EXECUTE IMMEDIATE  'select COUNT(1) FROM ebill.' ||v_table_name  || ' partition("' ||v_partition_name ||'")'
            INTO  v_count ;

            Dbms_output.put_line ('The v_sql is '|| v_sql|| ' The table ' || v_table_name  || ' Has '|| v_count|| ' rows');

            l_start:=DBMS_UTILITY.get_time;


            EXECUTE IMMEDIATE 'update ebill.' ||v_table_name || ' partition("' ||v_partition_name ||'")' || ' set service_id = ''01'' || service_id where service_id like ''1%'''  ;

            dbms_output.put_line('Rows updated: ' || To_char(SQL%RowCount));

            DBMS_OUTPUT.put_line('Bulk Updates   : ' || (DBMS_UTILITY.get_time - l_start));

            v_count:= SQL%RowCount;

               UPDATE    NNP_EBILL_UPDATE
               SET       updated_count =  v_count
                       , update_status =1
               WHERE     table_name = v_table_name
               AND       cycle_name = v_cycle_name
               AND       update_status = 0;

          EXCEPTION
          WHEN  no_data_found THEN

            v_err_cd  :=  SQLCODE;
            v_err_msg :=  substr(SQLERRM,1,500);

            INSERT INTO
            err_log
            VALUES(v_err_cd,v_err_msg,v_table_name,'Error During ebill_partition_tab_dtls table');

            commit;

          END;

          END IF;

        End loop;

        COMMIT;
       END EBILL_BULK_UPDATE_SERVICE; 



As our requirement that Execute Immediate should work for 5 or more tables updation parallely at a time.If one table get completed then it should take next table from loop and then start the code till completion of all tables.

If not possible please tell what needs to be done for updating one or more table parallely?

[Updated on: Fri, 28 December 2012 08:43]

Report message to a moderator

Re: How we can run pl/sql procedure in parallel [message #573434 is a reply to message #573433] Fri, 28 December 2012 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59747
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use jobs for this.
Search on AskTom there is a complete solution for this issue.

Regards
Michel
Re: How we can run pl/sql procedure in parallel [message #573437 is a reply to message #573434] Fri, 28 December 2012 08:50 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Michel,

I will search on AskTom, but please if you have reference for this, then please share with me as i need to write code urgently.

Many Thanks !!!
Re: How we can run pl/sql procedure in parallel [message #573438 is a reply to message #573437] Fri, 28 December 2012 08:55 Go to previous messageGo to next message
BlackSwan
Messages: 23026
Registered: January 2009
Senior Member
>then please share with me as i need to write code urgently.

Please hold your breath while waiting for complete solution.

BTW, SQL runs sequentially by default.
Re: How we can run pl/sql procedure in parallel [message #573440 is a reply to message #573438] Fri, 28 December 2012 09:07 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Michel,

Please help me !!!
Re: How we can run pl/sql procedure in parallel [message #573441 is a reply to message #573440] Fri, 28 December 2012 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59747
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I know it is and I'm sure I'm not better than you to search.
Search for "parallel pl/sql" or "parallel procedure" or the like.

Regards
Michel

[Updated on: Fri, 28 December 2012 09:10]

Report message to a moderator

Re: How we can run pl/sql procedure in parallel [message #573445 is a reply to message #573441] Fri, 28 December 2012 09:14 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Thanks Michel,

I am searching...Please help me if you find any reference...
Re: How we can run pl/sql procedure in parallel [message #573446 is a reply to message #573445] Fri, 28 December 2012 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59747
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have NO reference.
SEARCH! (instead of wrongly answer in other topics)

Regards
Michel

[Updated on: Fri, 28 December 2012 09:17]

Report message to a moderator

Re: How we can run pl/sql procedure in parallel [message #573447 is a reply to message #573433] Fri, 28 December 2012 11:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2060
Registered: January 2010
Senior Member
a) create nested table of numbers.
b) inside your FOR CURSOR loop change EXECUTE IMMEDIATE to DBMS_JOB.SUBMIT and add job number to nested table
c) add a loop inside your FOR CURSOR loop where you check if nested table has 5 elements. If it does, issue DBMS_LOCK.SLEEP(n) to wait for n seconds. If not execute your FOR CURSOR loop body

FOR cur_rec IN cur_update LOOP
  LOOP
    IF job_table.COUNT = 5
      THEN
        -- check if any job in job_table completed by querying USER_JOBS view
        -- do what ever you need if some job(s) failed
        -- delete finished job(s) from job_table and issue EXIT;
        -- if all 5 jobs are still running issue DBMS_LOCK.SLEEP(n);
      ELSE
        EXIT;
    END IF;
-- change EXECUTE IMMEDIATE to DBMS_JOB.SUBMIT + add job number to nested table + COMMIT;
  END LOOP; 


I hope you got the idea. But keep in mind, you will lose transactionality since each job will commit changes individually.

SY.

[Updated on: Fri, 28 December 2012 11:16]

Report message to a moderator

Re: How we can run pl/sql procedure in parallel [message #573448 is a reply to message #573447] Fri, 28 December 2012 11:40 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Thanks Solomon,

I hope i have some idea now !!!
I will implement this approach and hope will come with solution.
If I'll get struck somewhere then will call u..
Many Thanks for your solution !!!
Re: How we can run pl/sql procedure in parallel [message #573449 is a reply to message #573448] Fri, 28 December 2012 11:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59747
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why didn't you search on AskTom?
You'd have much more.

Regards
Michel
Re: How we can run pl/sql procedure in parallel [message #573450 is a reply to message #573449] Fri, 28 December 2012 12:10 Go to previous message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Hi Michel,

Yes,I am referring to Ask tom also.
I hope will get some good solution !!!
Previous Topic: Join tables on Date with MAX
Next Topic: Associative array - no data found
Goto Forum:
  


Current Time: Sat Nov 22 08:23:02 CST 2014

Total time taken to generate the page: 0.21312 seconds