PL/SQL export results into a table and Excel

Hello,

I have a PL/SQL that follows like below

1. Drop table A
2. Create table A with select statement
3. Drop table B
4. Create table B with select statement
5. Select * from table A that does not exist in Table B
6. Select * from Table B that does not exist in Table A
7. Select * from Table A and B that does not match values in certain columns

What I want to be able to do is

1. Drop table A
2. Create table A with select statement
3. Select count (*) from Table A and insert into a table C
4. Drop table B
5. Create table B with select statement
6. Select count (*) from Table B and inserts the count into Table C
7. Select * from table A that does not exist in Table B
8. Export the results into Excel spreadsheet
9. Select count (*) from table A that does not exist in Table B and inserts the count into Table C
10. Select * from Table B that does not exist in Table A
11. Export the results into Excel spreadsheet
12. Select count (*) from table B that does not exist in Table A and inserts the count into Table C
13. Select * from Table A and B that does not match values in certain columns
14. Export the results into Excel spreadsheet
15. Select count (*) from Table A and B that does not match values in certain columns and inserts the count into Table C

and of course I want to create a table C to hold all the counts.

Can someone please assist on how I can exporting the results to Excel?

Thank you
angel7170Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Best way is to create a CSV file and load that into Excel.  Just spool out the results.

There are MANY string aggregation examples out there.

In 11g there is LISTAGG as long as the concatenated string is less than 4000 characters.

I like the XML version:
http://www.experts-exchange.com/Database/Oracle/Q_24120361.html#a23572892


For table c:
insert into tablec (select 'tableA count: ' || tochar(count(*)) from tablea);
0
slightwv (䄆 Netminder) Commented:
Sorry, the XMLAGG example above takes a column and generates a CSV.

I have an example of taking a table's rows and generating a csv.  I'll post it when I find it.
0
slightwv (䄆 Netminder) Commented:
Here's an example of a CSV from the differences between two tables:
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

insert into tab1 values('a','b');
insert into tab1 values('1','2');
commit;


drop table tab2 purge;
create table tab2(col1 char(1), col2 char(1));

insert into tab2 values('a','b');
commit;



select xmlcast(
		xmlquery(
			'
				for $i in /ROW
				return string-join($i/child::*, ",")
			'
			passing t.column_value
			returning content
		)
	as clob
	) as "CSV"
from (
select column_value
from table(xmlsequence(extract(
(
	select xmltype(dbms_xmlgen.getxml('select * from tab1 minus select * from tab2 ')) myxml from dual
)
, '/ROWSET/ROW'))) 
) t
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
angel7170Author Commented:
Thank you .

I am getting an error when trying to execute XMLCAST.

Execution (121: 8): ORA-04030: out of process memory when trying to allocate 1032 bytes (qmxlu subheap,qmemNextBuf:alloc)
0
slightwv (䄆 Netminder) Commented:
How many rows are being returned from the MINUS?
Can you post the table description?
What is your specific database version number (all 4 numbers please)?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.