Export to Excel - complete Table oracle 12c Form

SANPAT

Dear Friends

How to Export the table from oracle 12c From , as we export from sql using spool .

If i give the command : Select * from tab, it gives me all the fields with data , in the similar way want to provide the button on 12c Form.

Sanjay

Michael Ferrante-Oracle

You have several options.

1. Use the HOST command to export to a text file on the server. Format it as a CSV, which would be compatible with Excel
2. Use the Forms OLE2 package to create an Excel file (xls) on the server. This would require that the server be Windows based and have Excel installed on it. This option is not recommended but mentioned because it is technically possible.
3. Use CLIENT_HOST (WebUtil required) to generate a text file on the user's machine. Format it as a CSV, which would be compatible with Excel
4. Use CLIENT_OLE2 to create an Excel file (xls) on the user's machine.

There are other options, but the above are the most common. Option 1 will be the most efficient. Option 3 and 4 can be use but are less efficient. If using option 3 or 4, the size of the output file should be small. Attempting to create large files directly on the user's machine using these methods may result in instability or other problems. If the Excel file needs to end up on the user's machine, simply create it on the server (Option 1) then use WebUtil to transfer the file to the user's machine.

Examples of how to create Excel files using OLE with Forms can be found in a variety of MyOracleSupport Notes that can be easily found with a simple search.

Marwim

We use as_xlsx and xml_spreadsheet to generate reports on a filer since we have to document each report anyhow. Then we open it in Forms

client_host( 'cmd /c start excel.exe '||:path||:filename||'');

Most reports are viewed once and then discarded but the user can use Save As to store it locally.

as_xlsx generates native XLSX, xml_spreadsheet an XML-format that Excel can interpret. xml_spreadsheet gives you more options for formatting, as_xlsx with its native format is more future-proof.