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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 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