0

How do I use a procedure to run a select that exports to an excel spreadsheet? Using PL/SQL - Oracle 11g

We currently have people copy pasting information into spread sheets that they use for testing every release. I am trying to convert all of their statements into one procedure that will run it for them instead of wasting days worth of time pasting.

Werbel
  • 113
  • 1
  • 2
  • 15
  • 2
    Does it need to write the data to an actual .xlsx / .xls file or would it suffice to write the data to a .csv file that can be opened in Excel? The reason that I ask is that it is considerably easier to do the latter. There is no built in functionality to write directly to Excel that I know of, though I have heard of libraries written by third parties to do this. – ChrisProsser Oct 21 '13 at 14:55
  • 2
    Could you create a view in a separate reporting Schema that your users could connect to through Excel using an ODBC Connection? – Starfighter Oct 21 '13 at 16:41
  • YEs a .csv file would be fine. – Werbel Oct 21 '13 at 18:53

1 Answers1

1

With the following simple query you could have your whole set of tables converted in XML, one clob row for each table:

SELECT DBMS_XMLQUERY.getxml('select * from ' || table_name)
FROM user_tables;

The package DBMS_XMLQUERY has many option to control the format, but you could just write down XML on files (with a custom client, or directly by PLS UTL_FILE), spreadsheets area able to import them.

dbra
  • 621
  • 3
  • 11
  • What I need pulled is from many tables to make one result set. Would I still be able to use this kind of statement somehow? – Werbel Oct 22 '13 at 13:57
  • 1
    Yes, you could simply make a UNION: the query withing GETXML function is generic. Example that return the union of names of all tables and views in current schema: SELECT DBMS_XMLQUERY.getxml('select table_name from user_tables UNION select view_name from user_views') FROM DUAL – dbra Oct 22 '13 at 14:05