We help IT Professionals succeed at work.
Get Started

PL/SQL Create Global Temp Table and insert records returned from SELECT query

Glenn Stearns
on
2,101 Views
Last Modified: 2013-12-07
I have a rather simple SELECT query that returns a result set with some 20 columns for each record in the result set. I have to run this query each week. I've read about global temporary tables, but am not sure how to use them in this case.

What I want to do is create a global temporary table that will hold the results of the SELECT statement, then, when I'm done, delete all the records in the table, but save the empty table for use again the next week. Then, what I'd like to be able to do is have the code export the records in the global temporary table as a .csv file. The export segment would need to save the .csv file with a different name each time it runs so that I don't lose the history of previously exported .csv files - something on the order of saving the .csv file as "401k 06/06/10.csv" this week, then as "401k 06/13/10.csv" the next week, and so on. The date part of the saved file name can be populated with the correct date as it is captured in another section of the code that runs before the SELECT (insert rows into the global table and save as a .csv file) section, which is the last step in the process. (The previous SELECT queries create the value for the 20 variables and update another table with each one. Then, the final SELECT statement retrieves those values from that table. This SELECT query returns the result set I want to use to create the records to be inserted into the global temporary table.)

Something like this:

SELECT emp_id, variable a FROM wherever, WHERE conditions exist, etc.
UPDATE (or MERGE INTO) table 'EMPLOYEE' that holds the column for this SELECT query result;

SELECT emp_id, variable b FROM wherever, WHERE conditions exist, etc.
UPDATE (or MERGE INTO) table 'EMPLOYEE' that holds the column for this SELECT query result;

SELECT emp_id, variable c FROM wherever, WHERE conditions exist, etc.
UPDATE (or MERGE INTO) table 'EMPLOYEE' that holds the column for this SELECT query result;
. same for variable d
. same for variable e
. etc, until all the various fields in EMPLOYEE for each of these SELECT statements have been updated.

The final SELECT query
SELECT variable a, variable b, variable c, . . . FROM EMPLOYEE
returns a result set with records that looks like
EMP_ID, variable a, variable b, variable c, ...  

It is the records in the result set from this final SELECT query that I want to use to populate (INSERT INTO) a global temporary table whose fields are the same name and type as those in the query result set, so that the records in this final SELECT query result set are inserted into the global temporary table giving me a table whose records look just like the result set from this final SELECT query.

Then, I want to export the records in the global temporary table as a .csv file, save the .csv file, naming it as described earlier above, then, after exporting the .csv file, delete all the records in the global temporary table (but do not drop the table since I want to repeat this process and re-populate the table next week with a new set of records and not have to create a new table to hold the records each time I run the code). It would be nice if the code execution could pause after creating the .csv file, display the .csv file so I can ensure it is correct, then resume with saving the .csv file, followed by deleting all the records in the global temporary table after saving the .csv file to disk.

In SQL Server, there is a way to email the saved file using Outlook. I do not know if PL/SQL supports this, but since I have to email the saved .csv file, it would be great if there is PL/SQL code that will at least create the email, and then send it, if possible. This would save the extra step of opening Outlook and manually creating the email, attaching the file, and sending the email. This email goes to the same email address each week - only the attached file changes to the filename of the .csv file just created. There is no email body text - just the email address and the attachment get sent. (I wanted to ask about this possibility in this question rather than posting another question on how to do it.)

So...to recap, my question is: Given that my existing final SELECT query returns a record set with all the records to be inserted into a global temporary table

1. How do I create the global temporary table to hold the records in the final SELECT query result set,
2. INSERT the records into the global temporary table,
3. Export the records in the global temporary table as a .csv file,
4. Preview the records in the .csv file before saving it,
5. Save the .csv file with the naming convention described above,
6. Create an email with the .csv file attached,
7. Send the email.

If steps 6 and 7 are not supported in PL/SQL, that's not a problem; I'll just have to do the email part manually.

If I can do all these things in a PL/SQL script, then the entire weekly process can be fully automated, which is my objective.
Comment
Watch Question
IT-specialist
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE