Skip to main content

PACKAGE 

CREATE OR REPLACE PROCEDURE EMP_CSV AS
  CURSOR c_data IS
    SELECT empno,
           ename,
           job,
           mgr,
           TO_CHAR(hiredate,'DD-MON-YYYY') AS hiredate,
           sal,
           comm,
           deptno
    FROM   emp
    ORDER BY ename;
    
  v_file  UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN(location     => 'EXTRACT_DIR',
                           filename     => 'emp_csv.txt',
                           open_mode    => 'w',
                           max_linesize => 32767);
  FOR cur_rec IN c_data LOOP
    UTL_FILE.PUT_LINE(v_file,
                      cur_rec.empno    || ',' ||
                      cur_rec.ename    || ',' ||
                      cur_rec.job      || ',' ||
                      cur_rec.mgr      || ',' ||
                      cur_rec.hiredate || ',' ||
                      cur_rec.empno    || ',' ||
                      cur_rec.sal      || ',' ||
                      cur_rec.comm     || ',' ||
                      cur_rec.deptno);
  END LOOP;
  UTL_FILE.FCLOSE(v_file);
  
EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE;
END;
/

Comments

Post a Comment

Popular posts from this blog

face1

--NewHireRehire_AAP_DM SELECT DISTINCT PER.PERSON_NUMBER, ASSIGNMENT_SEQUENCE, NAME.DISPLAY_NAME AS NAME, case when ASS.REASON_CODE in ('REHIRE') then ' ' else TO_CHAR((SELECT MIN(effective_START_date) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID = ASS.PERSON_ID and ASSIGNMENT_TYPE in ('E')                                           AND ACTION_CODE in ('HIRE','ADD_AN_EMPLOYEE','HIRE_ADD_WORK_RELATION') AND EFFECTIVE_START_DATE = ASS.EFFECTIVE_START_DATE  ),'MM/DD/YYYY') end as HIRE_DATE,  case when ASS.REASON_CODE in ('REHIRE') then           to_char((SELECT MAX(effective_START_date) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID = ASS.PERSON_ID               AND (reason_CODE IN ('REHIRE','REHIRE_WKR') or (ACTION_CODE in ('REHIRE')))  AND EFFECTIVE_START_DATE = ASS.EFFECTIVE_START_DATE),'MM/DD...
Post a Comment
Read more

Integration1

Equifax: SELECT '000HEADER' AS RECORD_TYPE, '' AS FILLER1, '02.00' AS VERSION, '17059' AS SOURCE_ID, 'EMPLOYER' AS SOURCE_TYPE, '' AS FILLER2, '' AS SOURCE_SERVICE, '' AS EMP_ID FROM DUAL ------------------- SELECT '202EMPLPIM' AS RECORD_TYPE, '17059' AS COMP_CD, LPAD(REPLACE(PNI.NATIONAL_IDENTIFIER_NUMBER,'-',''),9,'0')  AS NATIONAL_IDENTIFIER_NUMBER, PER.PERSON_NUMBER, PP.ATTRIBUTE6 USERNAME, '' AS ACC_NUM, '' AS PAYROLL, NAME.FIRST_NAME, NAME.MIDDLE_NAMES, NAME.LAST_NAME, '' AS SUFFIX, '' AS TITLE, LPAD(SUBSTR(PNI.NATIONAL_IDENTIFIER_NUMBER,-4),4,'0') AS EMP_ID, 'Y' AS DIRECT_ACCESS, --LOC.INTERNAL_LOCATION_CODE AS DIVISION, '' AS DIVISION, --JOB.NAME,         ASS.ASS_ATTRIBUTE4 AS NAME, CASE WHEN ASS.ACTION_CODE IN ('REHIRE','REHIRE_WKR',...
Read more

face3

--SALES AND PROMOTION SELECT DISTINCT PER.PERSON_NUMBER AS ID, TO_CHAR(NEW_ASS.CREATION_DATE,'MM/DD/YYYY') AS ACTION_DATE, TO_CHAR(NEW_ASS.Effective_start_date,'MM/DD/YYYY') AS EFFECTIVE_DATE, NEW_ASS.ACTION_CODE AS ACTION, NEW_ASS.REASON_CODE AS ACTION_REASON, OLD_LEG.NAME AS LEGAL_ENTITY, OLD_ASS.WORK_AT_HOME AS OLD_REMOTE_INDICATOR, (SELECT LOC.LOCATION_NAME FROM HR_LOCATIONS_ALL_F_VL  LOC WHERE  LOC.LOCATION_ID =OLD_ASS.LOCATION_ID AND TRUNC(sysdate) between LOC.EFFECTIVE_START_DATE and LOC.EFFECTIVE_END_DATE ) AS OLD_PHYSICAL_LOC, --OLD_LOC.LOCATION_NAME AS OLD_PHYSICAL_LOC, (SELECT DISTINCT HROF.ORG_INFORMATION1 FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=OLD_ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND OLD_ASS.EFFECTIVE_START_DATE BETWEEN HROF.EFFECTIVE_START_DATE(+) AND H...
Post a Comment
Read more