1 2 Previous Next 25 Replies Latest reply on Jan 6, 2016 3:19 AM by partlycloudy

    ORDS 3.0 - Spreadsheet upload

    partlycloudy

      File Upload: Multiple sheets

       

      Following up on the archived thread above. Can someone from Oracle please chime in? Thanks

        • 1. Re: ORDS 3.0 - Spreadsheet upload
          Christoph

          Kris just blogged about loading CSV files. See if that helps: Kris' blog: New Tools releases , now with Java

          • 2. Re: ORDS 3.0 - Spreadsheet upload
            partlycloudy

            Kris talks about all the brand new REST-ful features in 3.0.1 which is good to know but doesn't really address my question about the basic XLS(X) spreadsheet load which has been available since the earliest versions.

             

            Any update on this? A consequence of #2 above is that any existing custom code that expects columns C001..C050 to contain user data would need to be modified to shift over all the columns to compensate for the sheet name in C001. This makes ORDS less than a drop-in replacement for migrating apps from OHS and wishing to make use of the new native XLS upload feature.

             

            Kris - Can you chime in on my observation above? Thanks

            • 3. Re: ORDS 3.0 - Spreadsheet upload
              Kris Rice-Oracle

              I'm not sure what you mean by

                "This makes ORDS less than a drop-in replacement for migrating apps from OHS and wishing to make use of the new native XLS upload feature."

               

              OHS has nothing so how does this even relate?

              • 4. Re: ORDS 3.0 - Spreadsheet upload
                partlycloudy

                Well, OHS does support file uploads into WWV_FLOW_FILES and our existing apps have custom-built APIs that use write out the BLOB to the file system and read it back into a APEX collection using a external table mapped to the file. Of course, this is currently limited to CSV files and since ORDS supports native Excel files, I would have liked to seamlessly migrate my applications from using our custom CSV code to the built-in ORDS apex.excel2collection feature and announce to users that they no longer need to Save As CSV but just use the Excel file directly. But I can't do that easily because all the collection columns are shifted by 1 to accomodate the sheet name in C001.

                • 5. Re: ORDS 3.0 - Spreadsheet upload
                  Kris Rice-Oracle

                  So you have 50 cols and we're only allowing 49 is the crux?

                   

                  Do you want to see how to make a plugin to process the file yourself and put into real tables or any place you want instead of just collections?

                  • 6. Re: Re: ORDS 3.0 - Spreadsheet upload
                    partlycloudy

                    So you have 50 cols and we're only allowing 49 is the crux?

                    Regardless of the values of apex.excel2collection.onecollection and apex.excel2collection.useSheetName, there doesn't appear to be a way to create multiple collections using the sheet name for the collection name (pre-pended by the file browse item name) and use columns C001 thru C050 for the data. In all my tests, column C001 in the collection always contained the sheet name.

                    Well, that and what I said above. If useSheetName=true and the collection name=Sheet Name, why repeat and "use up" C001 with the sheet name? Why not leave C001...C050 for data corresponding to Excel columns A to AX respectively?!

                     

                    Do you want to see how to make a plugin to process the file yourself and put into real tables or any place you want instead of just collections?

                     

                    Nah, why write custom code when the built-in functionality is sufficient for me if you make this minor tweak to behavior :-)

                    • 7. Re: ORDS 3.0 - Spreadsheet upload
                      Kris Rice-Oracle

                      It's 1 code path is why.  Ask Apex for more than 50 and it'll support more :-)

                      • 8. Re: ORDS 3.0 - Spreadsheet upload
                        partlycloudy

                        OK I'll bite, how about you make the change to let us use all 50 columns as I describe and show how to write a plug-in for more than 50? :-)

                        • 9. Re: ORDS 3.0 - Spreadsheet upload
                          partlycloudy

                          Kris - How about it?

                          • 10. Re: ORDS 3.0 - Spreadsheet upload
                            Kris Rice-Oracle

                            I'm working on it.  Trying to sort out posting code to github so it's more useful than a post in here or in a blog.

                            • 11. Re: ORDS 3.0 - Spreadsheet upload
                              partlycloudy

                              how about you make the change to let us use all 50 columns as I describe and show how to write a plug-in for more than 50?

                               

                              So you are working on the second part. That's great. How about the first part? Can we expect a change to ORDS to allow us to use all 50 columns (C001...C050 for data corresponding to Excel columns A to AX respectively)  when useSheetName=true?

                              • 12. Re: Re: ORDS 3.0 - Spreadsheet upload
                                partlycloudy

                                While I have your attention, would you mind taking a look at this error I just received in catalina.yyy-mm-dd.log when trying to upload a 7.3MB XLSX workbook with 4 sheets.

                                 

                                Looks like it is running out of memory. Is there a configuration parameter I can bump up to accomodate the large file?

                                 

                                Thanks

                                 

                                16-Jul-2015 12:03:17.971 SEVERE [ajp-nio-127.0.0.1-8009-exec-5] oracle.dbtools.http.errors.ErrorPageFilter.log Java heap space
                                java.lang.OutOfMemoryError: Java heap space
                                    at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3039)
                                    at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3060)
                                    at org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3250)
                                    at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.reportStartTag(Piccolo.java:1082)
                                    at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseAttributesNS(PiccoloLexer.java:1802)
                                    at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseOpenTagNS(PiccoloLexer.java:1521)
                                    at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseTagNS(PiccoloLexer.java:1362)
                                    at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXMLNS(PiccoloLexer.java:1293)
                                    at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXML(PiccoloLexer.java:1261)
                                    at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:4808)
                                    at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
                                    at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
                                    at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
                                    at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3439)
                                    at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1270)
                                    at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1257)
                                    at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
                                    at org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown Source)
                                    at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:137)
                                    at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:129)
                                    at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:269)
                                    at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:159)
                                    at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:190)
                                    at oracle.dbtools.apex.hooks.fileUpload.ExcelToCollectionLoader.process(ExcelToCollectionLoader.java:111)
                                    at oracle.dbtools.apex.hooks.fileUpload.ExcelToCollectionLoader.processFile(ExcelToCollectionLoader.java:79)
                                    at oracle.dbtools.apex.hooks.ProcessorRegistry.fileUploadPreProcess(ProcessorRegistry.java:82)
                                    at oracle.dbtools.apex.FileUpload.loadFiles(FileUpload.java:63)
                                    at oracle.dbtools.apex.ModApex.doPost(ModApex.java:172)
                                    at oracle.dbtools.apex.ModApex.service(ModApex.java:90)
                                    at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:109)
                                    at oracle.dbtools.http.entrypoint.EntryPoint$FilteredServlet.service(EntryPoint.java:117)
                                    at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:60)
                                
                                • 13. Re: Re: Re: ORDS 3.0 - Spreadsheet upload
                                  partlycloudy

                                  OK so I followed the instructions in this thread to add set JAVA_OPTS=-Xmx1024m  in a setenv.bat file in the Tomcat \bin folder. This got me a new error

                                   

                                  Now what?

                                   

                                  Thanks

                                   

                                   

                                  16-Jul-2015 12:22:02.225 SEVERE [ajp-nio-127.0.0.1-8009-exec-3] oracle.dbtools.http.errors.ErrorPageFilter.log GC overhead limit exceeded
                                   java.lang.OutOfMemoryError: GC overhead limit exceeded
                                      at org.apache.xmlbeans.impl.store.Cur.createElementXobj(Cur.java:257)
                                      at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.startElement(Cur.java:2992)
                                      at org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3198)
                                      at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.reportStartTag(Piccolo.java:1082)
                                  
                                  • 14. Re: Re: Re: Re: ORDS 3.0 - Spreadsheet upload
                                    partlycloudy

                                    This would appear to be a issue with the Apache POI library. Going by the error stack, ORDS already seems to be using the recommended XSSF method instead of the deprecated UserEvent model but I can't tell for sure.

                                     

                                    In any case, if there is limit to the size of the spreadsheet that ORDS can handle, it needs to be known and documented. We can't have an application in Production that may fail randomly based on different spreadsheet sizes.

                                     

                                    Kris?

                                    1 2 Previous Next