Forum Stats

  • 3,839,467 Users
  • 2,262,495 Discussions
  • 7,900,971 Comments

Discussions

Generating Excel using PL/SQL & Storing in CLOB

Rapid Value Solutions
Rapid Value Solutions Member Posts: 147
edited Jun 1, 2016 6:04AM in SQL & PL/SQL

Hello Everyone,

I want to create a excel of a SQL data and store the excel into CLOB instead of any directory. Can anyone please let me know how this can be achieved. Any pointers would be of great help.

Thanks a lot.

-S.Jain.

Tagged:

Answers

  • Saubhik
    Saubhik Member Posts: 5,805 Gold Crown
    edited May 17, 2016 3:33AM

    Excel is a binary file, why want to store in a CLOB (not BLOB)? Are you talking about "excel" or "csv"? What is your database version?

    Generating CSV:

  • Rapid Value Solutions
    Rapid Value Solutions Member Posts: 147
    edited May 17, 2016 4:54AM

    Hi Saubhik,

    Thanks for your response!

    A small correction, I want to save to BLOB to save file to fnd_lobs table. I want excel and database version is 11G.

    Thanks for the link, I will refer them. Any other pointer to updated question.

  • Saubhik
    Saubhik Member Posts: 5,805 Gold Crown
    edited May 17, 2016 4:58AM

    Yes, your tables seems to be of Oralce EBS table, there are some aspects of generating excel within the application, you may want to explorer those also.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,834 Red Diamond
    edited May 17, 2016 6:17AM

    The Excel XML format specification is at https://www.microsoft.com/en-us/download/details.aspx?id=101

    Oracle has a number of methods for generating XML from SQL cursors, or PL/SQL code.

    Oracle's XMLTYPE data type has a method for conversion to CLOB.

    There are numerous source code examples using Oracle to generate Excel XML formatted responses to SQL cursors - google and ye shall find?

  • Rapid Value Solutions
    Rapid Value Solutions Member Posts: 147
    edited May 25, 2016 1:44AM

    @Saubhik @Billy~Verreynne

    Hi guys,

    Thank you for your reply , I was able to do this using the UTL_FILE method but one prob is when I open the excel I am getting the warning message that file is in different format and do you want to continue. Is there any way to avoid this or fix this warning as file created is using xml logic. Any pointers for fixing would be of utmost help.

    Thank you.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,834 Red Diamond
    edited May 25, 2016 1:58AM

    Very little detail as what the error pertains to. Invalid XML? Unsupported tags? Invalid values in tags? Not liking Unix linefeeds? XML structure too large?

    Your guess is as good as mine.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,150 Red Diamond
    edited May 25, 2016 3:39AM
    Rapid Value Solutions wrote:
    
    Saubhik Billy~Verreynne
    
    Hi guys,
    
    Thank you for your reply , I was able to do this using the UTL_FILE method but one prob is when I open the excel I am getting the warning message that file is in different format and do you want to continue. Is there any way to avoid this or fix this warning as file created is using xml logic. Any pointers for fixing would be of utmost help.
    
    Thank you.
    

    Probably because you've stored the data into a file in a format like character separated values (CSV) but named your file with .xls or suchlike.

    Just because you name a file with a particular file extension doesn't mean the contents conform to the expected internal format for that file type.

    As already mentioned, Excel has several internal formats (usually with the extensions .xls, .xlsx, .xlsm etc) and these are usually binary formats (or xml formats compressed, hence binary data)

    Unless you post the code you've used to create your file so we can see what you've tried we can only guess what you're doing wrong.
    The one thing that is obvious is that you are not creating the data in a format that is compatible with the file type you are trying to tell Excel it is.  Excel isn't stupid... if you give it a file with an extension .xls, then it expects some bytes of information at the start of the file contents that indicate the Excel version, and other internal attributes relating to the file.  If it doesn't find those, it knows that the extension is wrong and will then try and analyse the format inside the file to see if it's a common one it recognises, like CSV, but at the same time it will throw up the warning to tell you you've done something wrong.

  • Rapid Value Solutions
    Rapid Value Solutions Member Posts: 147
    edited Jun 1, 2016 3:23AM

    Hello Blushadow,

    Thank you for your reply.

    I am using the UTL file method to create excel. File format created is in XML but it opens in excel with a warning. So I wanted to know work around if we can can create excel directly without having any warning message.

    Code I used is from this discussion, I think its approach suggested by you as reply from KPR mention your Id.

    Generating excel(xls) using plsql

    Please let me know if there is any way to overcome the issue I am facing.

    Thanks alot!

  • Marwim
    Marwim Member Posts: 3,653 Gold Trophy
    edited Jun 1, 2016 6:04AM

    Did you name the file ...xls? It is an XML-file so you have to name it ...xml.

    Marcus

This discussion has been closed.