CODEWITHSUNDEEP
sqloracle-databaseplsql
Xelor
Xelor

Reputation: 1

How do I Display results from Oracle query to the console

I want to get 20 records from all tables on my schema but my code doesn't work.

BEGIN
    FOR R IN (SELECT table_name FROM user_tables) LOOP
        EXECUTE IMMEDIATE 'dbms_output.put_line(select * from '||r.table_name||' where rownum<=20)';
    END LOOP;
END;

Someone know why or can help me with this case?

Upvotes: 0

Views: 3588

Answers (2)

APC
APC

Reputation: 146229

" i need to export 20 records from all tables on my schema. I had a plan to do it in sqlplus and save the output with spool option"

You can do this but it's a two step process:

  1. Generate the SELECT statements and spool them to a .sql file
  2. Run the generated script

So something like this in SQL*Plus :

SQL> set heading off
SQL> set feedback off
SQL> spool get20rows.sql
SQL> select 'prompt '||table_name from user_tables;
SQL> select 'select * from '||table_name||' where rownum <= 20;'
SQL> from user_tables;
SQL> spool off
SQL> 
SQL> spool get20rows.out
SQL> @get20rows.sql
SQL> spool off

Improving the spooled layout is left as an exercise for the reader :) As @Williamrobertson observes...

The results from a plain select * from sometable will typically be unreadable due to wrapping

Upvotes: 2

MT0
MT0

Reputation: 167972

To output the SQL query you do not need EXECUTE IMMEDIATE and can just call DBMS_OUTPUT.PUT_LINE directly:

BEGIN
  FOR R IN (SELECT table_name FROM user_tables) LOOP
    DBMS_OUTPUT.PUT_LINE( 'select * from "'||r.table_name||'" where rownum<=20;' );
  END LOOP;
END;
/

Then you can run the outputted statements to get your desired results.

Also, identifiers are, by default, created with upper-case names; however, if a user creates a table/column/etc using double-quotes then the case-sensitivity of the identifier will be maintained and you will need double quotes in your query.

Upvotes: 1

Related Questions