Home » SQL & PL/SQL » SQL & PL/SQL » Use multi-threading in PL/SQL (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Use multi-threading in PL/SQL [message #670203] Mon, 18 June 2018 01:56 Go to next message
Manoj.Gupta.91
Messages: 232
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I've a scenario where we write few CSV files on Oracle PL/SQL. For this there is only one procedure which takes RefCursor and FileName as input parameter and write file to disk. I want to execute this in parallel (multi-threading) so that I can save some time. If I execute file writing procedure by creating and submitting a scheduler job at run time I will be writing many files in parallel.

The issue I'm facing is I'm not able to pass RefCursor while submitting a job at runtime. Can you please help me to doing this or if you know any other way to do this Please guide me and point me to some URL's where I can find blogs related to such scenarios.

create or replace PROCEDURE PRC_WRITE_FILE
(
     pr_Ref_Cursor           IN        SYS_REFCURSOR
    ,ps_File_Name            IN        VARCHAR2
    ,ps_out_ErrorCD          OUT       VARCHAR2
    ,ps_out_ErrorMsg         OUT       VARCHAR2
) 
AS 
     lt_Destination_Directory_Name      CR_GENE_PARA.Abbreviation%TYPE := 'REPORT_DIR' ;    --Destination Directory.
     lfh_File_Handler                   UTL_FILE.File_Type ;
     ls_File_Name                       VARCHAR2(100) ;                                     --File Name.
     lt_File_Field_Sep                  CR_GENE_PARA.Abbreviation%TYPE  := ',' ;            --Field Separator.
     ls_File_Record                     VARCHAR2(4000) := NULL ;

     ls_ObjName                         VARCHAR2(61) ;

BEGIN

    ls_File_Name := ps_File_Name ;
    ls_ObjName := $$PLSQL_UNIT ;

    IF UTL_FILE.Is_Open( lfh_File_Handler ) THEN
         UTL_FILE.FClose ( lfh_File_Handler ) ;
    END IF ;

    lfh_File_Handler := UTL_FILE.FOpen( lT_Destination_Directory_Name, ls_File_Name, 'W' ) ;

    LOOP
         FETCH pr_Ref_Cursor INTO ls_File_Record ;
         EXIT WHEN pr_Ref_Cursor%NOTFOUND ;

         --Writing to file line by line.
         UTL_FILE.Put_Line( lfh_File_Handler, ls_File_Record, FALSE ) ;
    END LOOP ;

    --Flushing buffer data to file.
    UTL_FILE.FFlush( lfh_File_Handler ) ;

    UTL_FILE.FClose( lfh_File_Handler ) ;

EXCEPTION
    WHEN OTHERS THEN
        IF UTL_FILE.Is_Open( lfh_File_Handler ) THEN
            UTL_FILE.FClose ( lfh_File_Handler ) ;
        END IF ;

END PRC_WRITE_FILE ;
/


CREATE GLOBAL TEMPORARY TABLE MYTEST_DATA
(
     COLUMN_1    NUMBER(10)
    ,COLUMN_2    VARCHAR2(50)
) ON COMMIT PRESERVE ROWS ;

INSERT INTO MYTEST_DATA( COLUMN_1, COLUMN_2 ) VALUES( 1, 'FIRST RECORD' ) ;
INSERT INTO MYTEST_DATA( COLUMN_1, COLUMN_2 ) VALUES( 1, 'SECOND RECORD' ) ;
INSERT INTO MYTEST_DATA( COLUMN_1, COLUMN_2 ) VALUES( 1, 'THIRD RECORD' ) ;
INSERT INTO MYTEST_DATA( COLUMN_1, COLUMN_2 ) VALUES( 2, 'FIRST RECORD' ) ;
INSERT INTO MYTEST_DATA( COLUMN_1, COLUMN_2 ) VALUES( 2, 'SECOND RECORD' ) ;
INSERT INTO MYTEST_DATA( COLUMN_1, COLUMN_2 ) VALUES( 2, 'THIRD RECORD' ) ;


DECLARE
  PR_REF_CURSOR SYS_REFCURSOR ;
  PS_FILE_NAME VARCHAR2(200) ;
  PS_IN_USERNAME VARCHAR2(200) ;
  PN_OUT_HANDLEDERRCD NUMBER ;
  PS_OUT_ERRORCD VARCHAR2(200) ;
  PS_OUT_ERRORMSG VARCHAR2(200) ;
  
  L_job_name VARCHAR2(500) ;
  

  CURSOR C1 IS 
  SELECT LEVEL FILTER_DATA
  FROM DUAL
  CONNECT BY LEVEL <= 2 ;
BEGIN

  FOR R1 IN C1 LOOP
      OPEN PR_REF_CURSOR FOR 'SELECT COLUMN_1 || '','' || COLUMN_2 FROM MYTEST_DATA WHERE COLUMN_1 = ' || R1.FILTER_DATA ;

      PS_FILE_NAME := 'MYFILE_' || R1.FILTER_DATA || '.CSV' ;

      L_job_name := '"ACCT"."MYJOB_' || R1.FILTER_DATA || '"' ;

    DBMS_SCHEDULER.CREATE_JOB (
            --job_id => TO_NUMBER(R1.EXCEPTION_LIST_CODE),
            job_name => L_job_name,
            job_type => 'STORED_PROCEDURE',
            job_action => 'ACCT.PRC_WRITE_FILE',
            number_of_arguments => 6,
            start_date => SYSDATE,
            repeat_interval => NULL,
            end_date => NULL,
            enabled => FALSE,
            auto_drop => TRUE,
            comments => 'THIS IS THE JOB');

    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( 
             job_name => L_job_name, 
             argument_position => 1, 
             argument_value => PR_REF_CURSOR
             );

    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( 
             JOB_NAME => L_JOB_NAME, 
             ARGUMENT_POSITION => 2, 
             ARGUMENT_VALUE => PS_FILE_NAME);
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( 
             JOB_NAME => L_JOB_NAME, 
             ARGUMENT_POSITION => 3, 
             ARGUMENT_VALUE => PS_IN_USERNAME);
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( 
             JOB_NAME => L_JOB_NAME, 
             ARGUMENT_POSITION => 4, 
             ARGUMENT_VALUE => PN_OUT_HANDLEDERRCD);
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( 
             JOB_NAME => L_JOB_NAME, 
             ARGUMENT_POSITION => 5, 
             ARGUMENT_VALUE => PS_OUT_ERRORCD);
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( 
             JOB_NAME => L_JOB_NAME,  
             ARGUMENT_POSITION => 6, 
             ARGUMENT_VALUE => PS_OUT_ERRORMSG);
         
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             NAME => L_JOB_NAME, 
             ATTRIBUTE => 'PARALLEL_INSTANCES', VALUE => TRUE); 
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             NAME => L_JOB_NAME, 
             ATTRIBUTE => 'STORE_OUTPUT', VALUE => TRUE);
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             NAME => L_JOB_NAME, 
             ATTRIBUTE => 'JOB_PRIORITY', VALUE => '1');
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             NAME => L_JOB_NAME, 
             ATTRIBUTE => 'LOGGING_LEVEL', VALUE => DBMS_SCHEDULER.LOGGING_FULL);

    DBMS_SCHEDULER.enable( name => L_job_name);

  END LOOP ;

END ;

Thanks & Regards
Manoj

[Updated on: Mon, 18 June 2018 02:03]

Report message to a moderator

Re: Use multi-threading in PL/SQL [message #670205 is a reply to message #670203] Mon, 18 June 2018 07:53 Go to previous message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
REFCURSOR is an object that only exists within the database.
AFAIK, there is no way to manifest a REFCURSOR at the OS level.
So the job needs to instantiate the REFCURSOR after it starts.
Previous Topic: Commit Process
Next Topic: problem of inserting a long string of characters :ORA-22835: Taille de tampon insuffisante pour la c
Goto Forum:
  


Current Time: Wed Jan 29 01:23:59 CST 2020