Generating Excel using PL/SQL & Storing in CLOB

Rapid Value Solutions

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.

Saubhik

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

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

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

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

@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

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

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

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

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

Marcus