Oracle Forms to MS Excel

35 pts.
Tags:
Microsoft Excel
Oracle Forms
I have several reports which extract data via Oracle Forms into MS Excel. But Excel cuts off the input at row 999.  The Oracle Form uses the DDE.EXECUTE(ConvID) command.  Does anybody know if Excel has a limitation of the number of rows it will import via Oracle Forms?

Software/Hardware used:
Oracle Forms 6i

Answer Wiki

Thanks. We'll let you know when a new response is added.

Final solution:

I realized that the varchar2(3) was limiting the export to Excel, so I too amended the variable to varchar2(5) to allow for 65,000 rows in Excel.

See discussion below.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    I don't know of such limitation. Do you get some error message ? Can you share the code you use to send the data to excel ?
    67,880 pointsBadges:
    report
  • Mayac
    We have several reports with this problem. I have selected one of the smaller reports - an Oracle Forms library program unit (a) which uses the package body (b) to write the data from the database into MS Excel. By the way, it does not matter how many columns I have, it always stops at 999 rows and displays the error message 'Cannot load data - Please retry or cancel'. When I try to capture the oracle error message it shows 'ORA-06502:PL/SQL numeric or value error'. I dont believe it is data related as it stops at 999 rows regardless of data. a) PROCEDURE AUTH_BY_OFFICER ( xls_file in varchar2, as_at_date_arg in date, title_arg in varchar2 ) IS data_available boolean := FALSE; authcount number := 0; rowcount number := 0; colcount number := 0; as_at_date date; cursor auth_officer_curs is select Licence_No, client_name from documents ; BEGIN warning ( 'Please wait for the EXCEL data' ); If not data_available then data_available := TRUE; -- -- Open the Excel spreadsheet -- xls.open(xls_file); -- -- Insert the heading text into the spreadsheet -- if title_arg is null then xls.put(1,1,''||' '); else xls.put(1,1,title_arg); if xls_file = 'authoff' then xls.put(3,1,'Licence #'); xls.put(3,2,'Licensee Name'); end if; end if; -- -- Put the data into the Excel spreadsheet -- rowcount := 5; colcount := 1; if xls_file = 'authoffexp' then For inp_rec in auth_officer_curs Loop xls.put (rowcount,colcount,inp_rec.licence_no); colcount := colcount + 1; xls.put (rowcount,colcount,inp_rec.client_name); rowcount := rowcount + 1; colcount := 1; End Loop; end if; end if; if data_available then xls.close; else error('No data available to be loaded for the period'); end if; EXCEPTION when others then xls.close(true); error('Cannot load data - Please retry or cancel'); END; (b)
    PACKAGE BODY XLS IS
        ConvID PLS_INTEGER := 0;
        AppID  PLS_INTEGER := 0;
        row_offset pls_integer;
        col_offset pls_integer;
        fname varchar2(200) := '';
        errtxt varchar2(100);
      Procedure Open (file_name in varchar2) is
        xls_not_open  EXCEPTION;  
        PRAGMA Exception_Init (xls_not_open, -106553); 
        cursor c1 is
          Select rv_meaning
          from cg_ref_codes cg1
          where upper(cg1.rv_domain) = 'XLS_DOCUMENT_LOCATIONS';
      Begin
        Open c1;
        Fetch c1 into fname;
        If fname = ''
        then
          error ('Document Location not found');
        else
          row_offset := 1;
          col_offset := 1;
          DECLARE
          BEGIN
            ConvID := DDE.INITIATE('EXCEL', fname);
          EXCEPTION
            when others
            then
              AppID := DDE.APP_BEGIN('C:Program FilesMicrosoft OfficeOffice11excel.EXE '||fname,DDE.APP_MODE_NORMAL);
                 BEGIN             
                ConvID := DDE.INITIATE('EXCEL', fname);
              EXCEPTION
                when others then
                  warning ( 'Press OK to update EXCEL' );
                  ConvID := DDE.INITIATE('EXCEL', fname);
              END;  
          END;
          DDE.APP_FOCUS(AppID);
        end if;
        close c1;
      End;
    
      Procedure Offset(row_offset_par in pls_integer, 
                     col_offset_par in pls_integer) is
      Begin
        row_offset := row_offset_par;
        col_offset := col_offset_par;
      End;
    
      Procedure Put (rowpos in pls_integer, colpos in pls_integer, 
                     value_par in varchar2) is
        r varchar2(3);
        c varchar2(3);
      Begin
        r := to_char(row_offset+rowpos-1);
        c := to_char(col_offset+colpos-1);
        DDE.EXECUTE(ConvID, '[SELECT("R'||R||'C'||c||'")]', 10000);
        DDE.EXECUTE(ConvID, '[FORMULA("'||value_par||'")]', 10000);
      End;  
    
      Procedure Close (reset_id in boolean default FALSE) is
      Begin
        if reset_id 
        then
          appid := 0;
          convid:= 0;
        else
          DDE.EXECUTE(ConvID, '[SELECT("R1C1")]', 10000);
        end if;
        row_offset := 1;
        col_offset := 1;
      End;
    END;
    
    I hope this helps.
    m.
    35 pointsBadges:
    report
  • Mayac
    In retrieving the text for you, i have seen the error. I have now fixed it and the reports in Excel work well. Thanks for all your help.
    35 pointsBadges:
    report
  • carlosdl
    Sounds like a variable being assigned a value greater than its capacity, maybe some counter, but I see your row counter has more capacity than 3 digits. I would add some debug messages to the procedure, and create smaller Begin-Exception blocks, to identify the line of code that is causing the error. Im guessing the 'r' variable of the xls.put procedure could be de culprit. Try changing this:
    r varchar2(3);
    to this:
    r varchar2(10);
    Please, let us know the results.
    67,880 pointsBadges:
    report
  • carlosdl
    Didn't see your last comment until I inserted mine. Can you share the solution ?
    67,880 pointsBadges:
    report
  • Mayac
    You are correct. I realised that the varchar2(3) was limiting the export to Excel, so I too amended the variable to varchar2(5) to allow for 65,000 rows in Excel. Thanks for your help.
    35 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following