0

How to change below query to see results of my_sql (now I see only query, not it results) in SQL Developer?

DECLARE
    my_sql VARCHAR(1000);
BEGIN
    FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner = 'O' )
    LOOP
        my_sql := 'select max(DATE) from ' || t.owner || '.' || t.table_name ;
        dbms_output.put_line('executing: ' || my_sql);
        EXECUTE IMMEDIATE my_sql;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('ERROR!! -- '  || SQLCODE || '-- ' || sqlerrm || ' --' );
END;

I want to get one value (in this case - max) from all tables (all tables have these same column).

  • I presume that you do have a user/schema owner with the username 'O' and every table has a column called DATE and that you have turned serveroutput on in the session i.e. SET SERVEROUTPUT ON SIZE unlimited ? – BriteSponge May 9 '16 at 12:37
  • No, I don't have these tables in my user. I use 'Other Users' (O) in SQL Developer. I turned serveroutput. – PNPTestovir May 9 '16 at 12:41
  • 'Other users' in SQL Developer is simply a heading for the list of users that you ARE NOT logged in as. It isn't actually a user. you need to qualify the 'owner' column to either include or exclude a list of owners or use the LIKE operand. Its also unlikely that every table will have the 'DATE' column so your dynamic query will likely fail. Tell us what you wish to achieve and help will likely appear. – BriteSponge May 9 '16 at 12:43
  • This script works (all tables in this owner have DATA column) but I don't see results of these queries but 'executing: select max(REPO_DATE) from OA.FX' etc. – PNPTestovir May 9 '16 at 12:48
0

The reason you are not seeing any results is that the FOR loop is not returning any rows as there is no owner called 'O'. To select all the table for a user who is not the logged in user you could use;

FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner <> USER)

However you will get a failure almost immediately as MAX(DATE) suggests that every table has a column called DATE. Which is not true.

It looks like you probably want a list of all the tables for a specific list of users (not SYS or SYSTEM or any other 'system' account) with the latest date that a row was added or created or something else involving a date. To do that EVERY table for the schema must have the column you want to check.

DECLARE
my_sql VARCHAR(1000);
BEGIN
    FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner IN ('SCHEMA1', 'SCHEMA2','SCHEMA3')
    LOOP
        my_sql := 'SELECT MAX(update_date) FROM ' || t.owner || '.' || t.table_name ;
        dbms_output.put_line('executing: ' || my_sql);
        EXECUTE IMMEDIATE my_sql;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('ERROR!! -- '  || SQLCODE || '-- ' || sqlerrm || ' --' );
END;

SCHEMA1 / 2 / 3 are the users you know have the tables you are interested in and it assumes that each table has a'update_date' column.

  • Yes, I have these column in all my tables in owner 'O'. Using this query I got information about executing queries for each tables, but I don't see results (max[update_date]). – PNPTestovir May 9 '16 at 13:30
  • The example you gave above shows that the user is actually 'OA' and the column is REPO_DATE. I've done my best to answer from the detail in the question and that detail still seems a bit 'uncertain'. I have run a version of the above script in my DB and I see results. Perhaps an edit to the question showing what you have tried might help someone else get you sorted. – BriteSponge May 9 '16 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.