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 |
Manoj.Gupta.91
Messages: 239 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 |
|
BlackSwan
Messages: 26766 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.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:10:36 CDT 2024
|