Looking for Opinions:  Do you create fancy Excel spreadsheets using Oracle data?

Posted on 2010-01-12
Medium Priority
Last Modified: 2013-12-18
Hi EE,

I have more of a Microsoft Access/Excel VBA background, and am now in an Oracle environment.

My user would like me to populate a fancy Excel spreadsheet using Oracle data.

I was wondering your processing technique:
  a)  Do you code pl/sql to get the data, write a temp table, and then use the temp table to
    populate the spreadsheet?
   b) i have gaps in my computer knowledge. i aware that there are other scripting languages.
        Do you combine pl/sql script with other scripts to create a batch flow that creates the
        excel file.

    c) do you schedule this script or run manually?

i am trying to understand what common practices are, so that i am somewhat in compliance
with techniques, and therefore will understand what skills i need to learn to accomplish this.

tx, sandra
Question by:mytfein
  • 4
  • 3
LVL 11

Accepted Solution

Patmac951 earned 500 total points
ID: 26296695
In my case I query the database using a SQL script, create a temp table and then import the table data into Excel.
LVL 48

Assisted Solution

schwertner earned 1500 total points
ID: 26296988
You can spool the output of the select statement using a separator like  comma
and after that Import the created file into Excel

SQL>spool c:\ut.dat
SQL>SELECT id || ',' || name FROM my_table;
SQL>spool off

Now correct the leading non needed lines in the file and import the file in Excel.

Author Comment

ID: 26297722
Hi Gentlemen,

tx for responding....

It's been years seen i took a class in PL/SQL,

Both ideas have merit and questions.
Pat's idea:
      merit: i can write a complex script to output desired results into a temp table
      con: requires user intervention to run script, and then export to excel

Schwertnzer idea:
     merit: less user intervention, bec. export is part of the code
     con: looks like you can only do this with one sql statement and not a complex script

What do you think?

1)      where do you run this code?
         i have TOAD freeware, and Oracle Developer on my pc.

 2)     i have used Windows scheduler to schedule a .bat file.
       can the above code be placed in a .bat file/.sql file to be executed by a scheduler?

3)      if (2) is yes, where in the code do you sign into Oracle?

tx, s
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 48

Assisted Solution

schwertner earned 1500 total points
ID: 26301893
You can run this script on every Oracle client.
The most reliable is SQL*Plus (part of Oracle client).
The Query could be very complex. The only thing is to
edit the SELECT clause in order to concatenate the
columns and to put the chosen delimeter.

I do not know what you mean saying "complex script".
Anyway every language has PRINT statements.
PL/SQL makes use of  UTIL_FILE package that can
create and write in flat OS files (Oracle directory should be precreate

TOAD should be able to export the SELECT as delimited file.
It is standart service of every goog GUI tool for working with DB.

Author Comment

ID: 26306213
Hi Schwertner,

thx for writing.... and additional info

i would like to understand what my learning of Oracle goals are.
Many years ago i took a PL/SQL class, subsequently worked with Access and VB//vba.
So i have gaps in my computer knowledge.

I think that i have to create a SELECT within a script, loop thru the records, do processing, and
create a temp table to export to excel. I'd like to do this whole process w/o intervention.

I just had an a-ha moment while typing, so i guess i could spool the temp table (end results) to excel using your technique....

So do to accomplish the above, should i learn:
    - creating stored procedure to hold the script
    - how to create a temp table? Is this a virtual table that exists for the duration of the job -
       or a real table created with CREATE TABLE.
    - would the stored procedure be placed in another script (which scrpting language)
          - run stored procedure
          - run spooling of temp table to delimited file that can be used by excel

pls advise, tx, s
LVL 48

Assisted Solution

schwertner earned 1500 total points
ID: 26306419
You do not need to use temporary table.
You need only to decide where your delimited file should be placed:
1. On the Oracle server
2. On some client machine

2. after that you have to check what environment (programming of course)
will you use on that machine.
SQL*Plus is the most convinient for this job, but every programming language can create OS files. Perl, VB, C, C++, etc.
You connect to Oracle, run the query that returns you row by row (every row is in fact Comma Delimited Row of Excel.
Now yoy store the rows in a flat file and on the end close the file.
Thats all ...

Author Comment

ID: 26314443
Thx Schwertner,

I have more ?s which I need to think how to articulate.... in the meantime thx Everyone for the information
It has gotten me thinking....

tx, s
LVL 48

Expert Comment

ID: 26314485
Good Luck!
For further assistance get in touch ...

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question