9 Replies Latest reply: Jun 21, 2013 12:55 AM by john2013 RSS

    Showing Text file as external table without trimming spaces

    john2013

      Hi all:

       

      I am trying to display a text file from db sever with original spacing.

      Well it's not entirely related to APEX.

      This is how external table is created:

      create table u_x

        (rowline varchar2(255))

           ORGANIZATION EXTERNAL

           (

             TYPE ORACLE_LOADER

             DEFAULT DIRECTORY ext_tab         <-- this is a dir i have created

             access parameters

             (

             records delimited by newline

             fields notrim

             )

             LOCATION ('aaa.txt')

            )

      REJECT LIMIT UNLIMITED;

       

      on the first line of the aaa.text (its a report), it shows something like:

      Sat Dec 29                                                                Page 1

      but when I do select * from u_x , the result shows:

      Sat Dec 29 Page 1

      all above is done in sql workshop /sql command in APEX 4.2.1 (db 10.2.0.5)

      Is there any way to preserve the spaces between "29" and "Page"?

      (the result compressed all spaces in-between to 1 space).

       

      I think the ideal way is to invoke client's notepad to open the server's text file.

      anyway to generate the link?

       

      Thanks

      John

        • 1. Re: Showing Text file as external table without trimming spaces
          john2013

          Well, I have tried to download the file using below procedure.

           

          create or replace PROCEDURE download_bfile (

                directory_in   IN   VARCHAR2,

                file_in        IN   VARCHAR2

             )

           

           

             AS

                lob_loc    BFILE;

                v_mime     VARCHAR2 (48);

                v_length   NUMBER(16);

           

           

             BEGIN

                lob_loc := BFILENAME (UPPER (directory_in), file_in);

                v_length := DBMS_LOB.getlength (lob_loc);

                -- set up HTTP header

                -- use an NVL around the mime type and

                -- if it is a null set it to application/octect

                -- application/octect may launch a download window from windows

                OWA_UTIL.mime_header (nvl(v_mime, 'application/octet'), FALSE);

                -- set the size so the browser knows how much to download

                HTP.p ('Content-length: ' || v_length);

                -- the filename will be used by the browser if the users does a save as

                HTP.p (   'Content-Disposition:attachment; filename="'

                       || SUBSTR (file_in, INSTR (file_in, '/') + 1)

                       || '";'

                      );

                -- close the headers

                OWA_UTIL.http_header_close;

                -- download the BLOB

                WPG_DOCLOAD.download_file (lob_loc);

                apex_application.stop_apex_engine;

             END download_bfile;

           

          I run it inside sql command in apex, but it does not show a pop window to save the file,

          it displays the whole text content of the file in the result window.

           

          Any idea why the save-as window does not pop up?

           

          thanks,

          John.

          • 2. Re: Showing Text file as external table without trimming spaces
            john2013

            well, i tested, the first method, actual content of the external table still contains spaces,

            only compressed in the sql command results pane.

            but when i download as .csv, it adds addtionan "" to the fields,

            I want the original text file content.

            • 3. Re: Showing Text file as external table without trimming spaces
              Mike Kutz

              john2013 wrote:

              I want the original text file content.

              Then you'll have to read the file as a CLOB, not a table.

               

              Using a BFILE is one possibility.  I'm not familiar with that LOB data type.

               

              The ORDDoc data type can also be used.

              http://docs.oracle.com/cd/B28359_01/appdev.111/b28414/ch_docref.htm#i1088766

               

              From the documentation:

              DECLARE
                obj ORDSYS.ORDDoc;
                ctx RAW(64) :=NULL;
              BEGIN
                SELECT product_testimonials INTO obj FROM pm.online_media
                WHERE product_id=2999 FOR UPDATE;
                DBMS_OUTPUT.PUT_LINE('setting and getting source');
                DBMS_OUTPUT.PUT_LINE('--------------------------');
                -- set source to a file
                -- import data
                obj.importFrom(ctx,'file','FILE_DIR','modem.jpg',FALSE);
                -- check size
                DBMS_OUTPUT.PUT_LINE('Length: '||TO_CHAR(DBMS_LOB.GETLENGTH(obj.getContent())));
                DBMS_OUTPUT.PUT_LINE(obj.getSource());
                UPDATE pm.online_media SET product_testimonials=obj WHERE product_id=2999;
                COMMIT;
                EXCEPTION
                WHEN ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED THEN
                DBMS_OUTPUT.PUT_LINE('ORDSourceExceptions.METHOD_NOT_SUPPORTED caught');
                WHEN ORDSYS.ORDDocExceptions.DOC_PLUGIN_EXCEPTION THEN
                DBMS_OUTPUT.put_line('DOC PLUGIN EXCEPTION caught');
              END;
              /
              
              • 4. Re: Showing Text file as external table without trimming spaces
                fac586

                john2013 wrote:

                 

                Hi all:

                 

                I am trying to display a text file from db sever with original spacing.

                All white space in HTML is collapsed by default. To display the content in the original format in APEX, there are 2 possible options to modify this behaviour:

                1. Use the HTML <pre> element.
                2. Use the CSS white-space property to modify the presentation of an element using a style sheet:

                   

                  #your-selector {
                  white-space: pre;
                  }
                  
                  

                 

                Use whichever is most appropriate for your requirements, in conjunction with a monospace font.

                • 5. Re: Showing Text file as external table without trimming spaces
                  john2013

                  i used following in the page's html header:

                   

                  <style type="text/css">

                    td[headers="ROWLINE"] {

                    font-family: Courier New;

                    font-weight: bold;

                    white-space: pre;

                    color: green;

                  }

                  </style>

                   

                  but not working, anything wrong? "ROWLINE" is the table cell header showed in 'view source'.

                  <pre> did not change font to even spaced one, so the results is not aligned correctly in the browser.

                  • 6. Re: Showing Text file as external table without trimming spaces
                    john2013

                    will try the ORDDoc method, thanks Mike.

                    • 7. Re: Showing Text file as external table without trimming spaces
                      fac586

                      john2013 wrote:

                       

                      i used following in the page's html header:

                       

                      <style type="text/css">

                        td[headers="ROWLINE"] {

                        font-family: Courier New;

                        font-weight: bold;

                        white-space: pre;

                        color: green;

                      }

                      </style>

                       

                      but not working, anything wrong? "ROWLINE" is the table cell header showed in 'view source'.

                      <pre> did not change font to even spaced one, so the results is not aligned correctly in the browser.

                      Don't think that's a valid font specification. Always include one of the generic family names as a fallback option at the end of the rule: not everyone uses a device with Courier New as an installed font.

                       

                      <style type="text/css">
                        td[headers="ROWLINE"] {
                        font-family: "Courier New", monospace;
                        font-weight: bold;
                        white-space: pre;
                        color: green;
                      }
                      </style>
                      

                       

                      Reproducing the problem on apex.oracle.com is the best way to get assistance with layout and visual formatting issues.

                      • 8. Re: Showing Text file as external table without trimming spaces
                        john2013

                        put following in the region header file:

                        <style type="text/css">

                            .apexir_WORKSHEET_DATA td{

                             font-family: monospace;

                             white-space: pre;

                             color: blue;

                            }

                        </style>

                         

                        Only white-space: pre worked,

                         

                        font-family and color not working...

                        • 9. Re: Showing Text file as external table without trimming spaces
                          john2013

                          Finally done it inside:

                          Application Builder>>Application 110>>Page 8>>Report Attributes>>Column Attributes

                          with:

                          Column Formatting  

                          CSS Class : 

                          CSS Style : white-space: pre; font-family: monospace;

                          If do it in Region header, have to use this report template: default: HTML (Standard)\\u

                           

                          Thanks for your help!

                           

                          btw, used classic report