Run Same Query on tables in Schema

[phatz]

Hi,

I have a schema with 10 tables. I want to run the same query on all 10 of these tables and output the results from the SQL query to a .txt file but am unsure how to do this. I know this will probably involve a loop and cursor etc. Any help apreciated. A simple starter would be good so I can go away and work it out, will need to know the sys table the schema tables are held in as well if possible.

Many Thanks

[Oralloy]

Use a union query.

All queries must be mergeable - same column count, column types are compatible.

This sample assumes that you want to pull data that are compatible, but in different columns.

SELECT tab1.field1, tab1.field2 FROM tab1
UNION
SELECT tab2.field3, tab2.field1 FROM tab2
;

As for the text file, that depends on your interface tool.

Good luck!

[phatz]

Thanks for your help but to save time wanted to loop like the following which works for listing the tables but basically for each table I want to also show the count(*) of rows from each table in the loop for example
'MY TABLE COUNT IS ' || select count(*) from myresult;
I am not sure how to do this next step in my script below?

set serveroutput on

DECLARE

myresult varchar2(6000);

CURSOR c1 is
select table_name from dba_tables where owner = 'PHATZ'
and TABLE_NAME like 'PP%';


BEGIN
FOR tresult IN c1

LOOP
myresult:= myresult ||chr(10)|| tresult.table_n ame;

--myresult:= NOT SURE WHERE TO PUT THIS myresult || select count(*) from tresult.table_n ame;

END LOOP;

DBMS_OUTPUT.PUT _LINE ('TABLE NAME =' ||TO_CHAR(myres ult));

END;
/

[amitpatel66]

You want to generate the seperate txt files for each table or a single txt file for all the tables output data?

[phatz]

Single file for all tables output.

[OraMaster]

1. What is/are the SQL/SQLs you want to execute for all tables?
2. Exactly what contents you want to put into text file.

Please note you need to use UTL_FILE (oracle supplied package) to write/read into/from text file within oracle PL/SQL. Try to search on Google for how to use UTL_FILE
and any other details. Let us know these details.

[phatz]

I want to return as an example from the cursor c1 is for each tablename that is returned from the cursor do a count against that tablename so for example C1 would return tablea I then want to do a select count(*) from tablea and return this to an output to screen, txt file then repeat for next tablename in c1 cursor i.e. tablenameb and so on until there are no more tablenames returned from the cursor. So for example output line would be something like this

TABLE NAME - TABLENAMEA ROWCOUNT = 120
TABLE NAME - TABLENAMEB ROWCOUNT = 34

Once I understand this and where this appears in the loop I can then modifiy the queries to return different outputs, I am only using row count as an example

[OraMaster]

Phatz

See below is the code you might use by modifying cursor for loop SQL as per your requirement.

declare
  v_count number(10);
begin
  for rec in (select 'select count(*) from ' || table_name stmt, table_name
                from user_tables) loop
    execute immediate rec.stmt
      into v_count;
    dbms_output.put_line('Total Number of Records in ' || rec.table_name ||
                         ' = ' || v_count);
  end loop;
end;
/

Now in place of dbms_output.put _line statement in above code you can use UTL_FILE package.
First try to search on Google about how to use UTL_FILE and let us know what information you need.

[phatz]

Many Thanks this now givesme an idea on how to proceed. Thanks again