CODEWITHSUNDEEP
oracle-databaseweblogic12coracleformsoraclereports
Michael
Michael

Reputation: 11

Saving Oracle Reports 12c to PDF file

I have the below code which generates a pdf file for each record in the table with an email address to a specific folder, but what I have realized is that on each iteration the URL is appended to the next iteration, which makes the URL increases on each loop. So after the browser reaches its limit it throws an error and generates only 10 files when I am expecting about 1500 pdf files.

PROCEDURE RUN_REPORT2 IS   
    M_PARAM_ID      PARAMLIST ;    
    M_REP_ID        VARCHAR2(3000);    
    V_REP_CURRENCY  VARCHAR2(5);    
    V_BASE_CURRENCY VARCHAR2(5);    
    v_show_document VARCHAR2(30000);    
    v_report_name   VARCHAR2(30000);    
    v_format        VARCHAR2(200)   := 'PDF';
    
    CURSOR C1 IS 
    
    SELECT EMP_EMPLOYEE_NO,
           INITCAP(EMP_FIRSTNAME) EMP_FIRSTNAME,
           EMP_EMAIL_ADDRESS,
           TO_CHAR(PMA_PAY_PERIOD_TO, 'MONTH' || ' ' || 'YYYY') PAY_PERIOD,
           EMP_TIN DOB,
           TRIM(TO_CHAR(PMA_PAY_PERIOD_TO, 'MONTH')) PAY_MONTH,
           TRIM(TO_CHAR(PMA_PAY_PERIOD_TO, 'YYYY')) PAY_YEAR
      FROM HR_EMPLOYEES, PAY_PAYROLL_MASTER
     WHERE EMP_EMPLOYEE_ID = PMA_EMPLOYEE_ID
       AND EMP_COMPANY_CODE = PMA_COMPANY_CODE
       AND EMP_EMAIL_ADDRESS IS NOT NULL
       AND EMP_EMPLOYEE_NO BETWEEN :ONE.EMPNO_FM AND :ONE.EMPNO_TO
       AND TO_CHAR(PMA_PAY_PERIOD_TO, 'Month YYYY') = :ONE.PAY_PERIOD
       AND PMA_COMPANY_CODE = :GLOBAL.COMPNAME
    
BEGIN
        
    SELECT PAR_REPORT_URL, PAR_FORMS_DIRECTORY
      INTO v_show_document, v_report_name
      FROM APPS_PARAMETERS
     WHERE PAR_COMPANY_CODE = :GLOBAL.COMPNAME;
         
    FOR C1_R IN C1 LOOP    
      v_show_document := v_show_document
      
      || '&report='||v_report_name||'PAYSLIP_EIC_EMAIL.jsp'    
      || '&destype=file'    
      || '&desformat='||v_format    
      || '&P_EMPNO_FM='||C1_R.EMP_EMPLOYEE_NO    
      || '&P_EMPNO_TO=' ||C1_R.EMP_EMPLOYEE_NO    
      || '&P_DEPT_FM='||:ONE.DEPT_FM    
      || '&P_DEPT_TO=' ||:ONE.DEPT_TO    
      || '&P_REG_FM='||:ONE.REG_FM    
      || '&P_REG_TO=' ||:ONE.REG_TO    
      || '&P_REP_CURRENCY=' ||V_REP_CURRENCY    
      || '&P_PERIOD='||:ONE.PAY_PERIOD    
      || '&P_COMPCODE=' ||:GLOBAL.COMPNAME    
      || '&desname='||v_report_name||'EMAIL_FOLDER\'||C1_R.EMP_EMAIL_ADDRESS||' '||C1_R.EMP_FIRSTNAME||' 
      '||C1_R.PAY_MONTH||C1_R.PAY_YEAR||'.PDF';
      
      WEB.SHOW_DOCUMENT(v_show_document||'&cmdkey=userlogin','_blank');
    
      SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT');
      DESTROY_PARAMETER_LIST(M_PARAM_ID) ;
       
      CLEAR_MESSAGE;  
    END LOOP;
     
END;

Upvotes: 1

Views: 848

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

The way I understood it, this:

 v_show_document := v_show_document
    
    || '&report='||v_report_name||'PAYSLIP_EIC_EMAIL.jsp'

should be

 v_show_document := 
    
       '&report='||v_report_name||'PAYSLIP_EIC_EMAIL.jsp'

Otherwise, you're concatenating v_show_document with previous loop's iteration every time, making it HUGE (and more or less useless).

Upvotes: 1

Related Questions