Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    24

    Question Unanswered: Need help in excel download through PL/SQL

    Dear Friends,

    I need to download data from SQL table and spool it to excel and allow user to download the same through the front end(J2EE).
    Currently, I am passing the data to Java through a JDBC connection and creating the excel. Since the data has grown over years, creation of excel is getting failed due to high load.(data of around 6 million excel cells)

    Hence I am planning to change the design and create the excel in PL/SQL package and zip it and then pass the file to front end. This would, I believe will reduce the total time taken to download.

    Though I am aware of excel creation through utl_file, please let me know the usage of same in efficient way. Also the best method to zip the file created.

    Thanks in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is not clear to me why Excel is involved at all.
    J2EE could be programmed to access the Oracle DB & then do anything required with the returned rows; such as write the data to a local file.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2010
    Posts
    24
    Thanks anacedent for reply, thats absolutely possible. Thats the way its working currently. But please see my observation:
    Time taken for query execution: 5 secs
    time taken for result set to fetch data: 800 secs
    time taken for writing into excel: 300 secs
    time taken to zip: 150 secs


    From the above time-intervals, it is taking much time for fetching the data. As the data is huge (around 100 MB), I am planning to pass the zipped file to J2EE (which will be just around 12-15 MB).

    Please suggest.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •