create or replace package OWB_AGENT_PKG as procedure SS_UNLOAD (AGENT_BCP_EXE VARCHAR2, AGENT_LOC VARCHAR2, AGENT_CRED VARCHAR2, AGENT_DIR VARCHAR2, SRC_SERVER VARCHAR2, SRC_USER VARCHAR2, SRC_PWD VARCHAR2, TGT_NAME VARCHAR2, SQLQ VARCHAR2, REC_DELIM VARCHAR2 := '0x0A', FLD_DELIM VARCHAR2 := '\t'); -- ideally use a hex '0x07' procedure ZIP_DATA (AGENT_ZIP VARCHAR2, AGENT_LOC VARCHAR2, AGENT_CRED VARCHAR2, AGENT_DIR VARCHAR2, TGT_NAME VARCHAR2); procedure GET_FILE (AGENT_LOC VARCHAR2, AGENT_CRED VARCHAR2, AGENT_DIR VARCHAR2, LOCAL_SQL_DIR VARCHAR2, TGT_NAME VARCHAR2); end OWB_AGENT_PKG; / show errors create or replace package body OWB_AGENT_PKG as procedure SS_UNLOAD (AGENT_BCP_EXE VARCHAR2, AGENT_LOC VARCHAR2, AGENT_CRED VARCHAR2, AGENT_DIR VARCHAR2, SRC_SERVER VARCHAR2, SRC_USER VARCHAR2, SRC_PWD VARCHAR2, TGT_NAME VARCHAR2, SQLQ VARCHAR2, REC_DELIM VARCHAR2, FLD_DELIM VARCHAR2) as lastid number; stillrunning boolean := true; status VARCHAR2(30); jobname VARCHAR2(30) := 'UNLOADJOB'; BEGIN jobname := DBMS_SCHEDULER.GENERATE_JOB_NAME('OWB$SSUNLOAD'); begin select max(log_id) into lastid from user_scheduler_job_run_details where job_name=jobname; exception when others then null; end; DBMS_SCHEDULER.CREATE_JOB( job_name => jobname, job_type => 'EXECUTABLE', job_action => AGENT_BCP_EXE, number_of_arguments => 11, enabled => FALSE); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 1, ''||sqlq||''); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 2, 'queryout'); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 3, AGENT_DIR||'/'||TGT_NAME||'.dat'); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 4, '/S'||SRC_SERVER); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 5, '/U'||SRC_USER); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 6, '/P'||SRC_PWD); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 7, '-r'); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 8, REC_DELIM); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 9, '-c'); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 10, '-t'); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 11, FLD_DELIM); DBMS_SCHEDULER.SET_ATTRIBUTE(jobname, 'logging_level', DBMS_SCHEDULER.LOGGING_FULL); DBMS_SCHEDULER.SET_ATTRIBUTE(jobname, 'credential_name', AGENT_CRED); DBMS_SCHEDULER.SET_ATTRIBUTE(jobname, 'destination', AGENT_LOC); DBMS_SCHEDULER.ENABLE(jobname); while stillrunning loop dbms_lock.sleep(1); begin select status into status from user_scheduler_job_run_details where job_name=jobname and log_id >lastid; if status = 'SUCCEEDED' OR status = 'FAILED' then exit; end if; exception when others then exit; end; end loop; END; procedure ZIP_DATA (AGENT_ZIP VARCHAR2, AGENT_LOC VARCHAR2, AGENT_CRED VARCHAR2, AGENT_DIR VARCHAR2, TGT_NAME VARCHAR2) as lastid number := 0; stillrunning boolean := true; status VARCHAR2(30); jobname VARCHAR2(30) := 'UNLOADZJOB'; BEGIN jobname := DBMS_SCHEDULER.GENERATE_JOB_NAME('OWB$SSZIP'); begin select max(log_id) into lastid from user_scheduler_job_run_details where job_name=jobname; exception when others then null; end; DBMS_SCHEDULER.CREATE_JOB( job_name => jobname, job_type => 'EXECUTABLE', job_action => AGENT_ZIP, number_of_arguments => 3, enabled => FALSE); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 1, '-j'); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 2, AGENT_DIR||'/'||TGT_NAME||'.zip'); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(jobname, 3, AGENT_DIR||'/'||TGT_NAME||'.dat'); DBMS_SCHEDULER.SET_ATTRIBUTE(jobname, 'logging_level', DBMS_SCHEDULER.LOGGING_FULL); DBMS_SCHEDULER.SET_ATTRIBUTE(jobname, 'credential_name', AGENT_CRED); DBMS_SCHEDULER.SET_ATTRIBUTE(jobname, 'destination', AGENT_LOC); DBMS_SCHEDULER.ENABLE(jobname); while stillrunning loop dbms_lock.sleep(1); begin select status into status from user_scheduler_job_run_details where job_name=jobname and log_id >lastid; if status = 'SUCCEEDED' OR status = 'FAILED' then exit; end if; exception when others then exit; end; end loop; END; procedure GET_FILE (AGENT_LOC VARCHAR2, AGENT_CRED VARCHAR2, AGENT_DIR VARCHAR2, LOCAL_SQL_DIR VARCHAR2, TGT_NAME VARCHAR2) as begin DBMS_SCHEDULER.GET_FILE(AGENT_DIR||'/'||TGT_NAME||'.zip', AGENT_LOC, AGENT_CRED, TGT_NAME||'.zip', LOCAL_SQL_DIR); end; end OWB_AGENT_PKG; / show errors