• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1354
  • Last Modified:

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
0
angel7170
Asked:
angel7170
  • 4
1 Solution
 
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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now