Oracle SQL Developer – How to Return Output of Oracle Query

oracleoracle-sql-developerplsql

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).

Best Answer

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.

Related Solutions

Sql-server – Oracle GoldenGate add trandata errors

I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.

How to raise_application_error beyond a when others clause

There are a variety of approaches you could take. You should pick the one you feel is most suitable for your application.

Include no WHEN OTHERS exception handlers and let the raw Oracle errors propagate to your users.
Include WHEN OTHERS exception handlers in the top-level blocks.
Include WHEN OTHERS exception handlers in all/most blocks

The first option requires no effort on your part, but can expose users to very unfriendly error messages. They can also expose details about the database which could be of use to hackers, etc.

The second option require some effort. You can use the built-in packages, such as DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to identify where an exception occurred, which can be included in any logging processes you may have. You can convert the raw Oracle errors into application specific ones.

The third option requires most effort. However, as well as the above, you can also log items such as variable values to help you identify why an error occurred, not just where it occurred. Using some common exception handling routines -as you seem to be doing - will reduce the effort required for this approach.

WHEN OTHERS THEN
   exception_pkg.handler('<variable_values_of_interest>')
   RAISE;

where you pass the variables values to be logged via a standard process before re-raising the exception.

You should always re-raise an exception in a WHEN OTHERS block, either the original exception or one with an application specific message - via RAISE_APPLICATION_ERROR.

Best Answer

Related Solutions

Sql-server – Oracle GoldenGate add trandata errors

How to raise_application_error beyond a when others clause

Related Question