How do I Display results from Oracle query to the console

Question

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?

The result of a select is not a string (varchar), so it cannot be used with put_line. The whole logic is off... — PKey
– PKey, Commented Feb 23, 2018 at 12:22
This would be possible in 12.x but I don't think there is an easy solution for the outdated 11.x — user330315
– user330315, Commented Feb 23, 2018 at 13:06

APC · Accepted Answer · 2018-02-23 14:43:08Z

2

" 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:

Generate the SELECT statements and spool them to a .sql file
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

edited Feb 23, 2018 at 14:43

answered Feb 23, 2018 at 13:38

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MT0 Over a year ago

select 'prompt '||table_name|| from user_tables; you have a second string concatenation operator but no following string.

APC Over a year ago

@kaushik - that one I'm definitely blaming the app for ;-)

MT0 · Accepted Answer · 2018-02-23 12:25:14Z

1

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.

answered Feb 23, 2018 at 12:25

MT0

173k12 gold badges70 silver badges136 bronze badges

3 Comments

Xelor Over a year ago

Yes it works but 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. Is it possible or i have to do queries one by one?

William Robertson Over a year ago

What format do you need this in? The results from a plain select * from sometable will typically be unreadable due to wrapping (try it). Do you need a generic CSV generator? There are tools to do this.

Kris Rice Over a year ago

Another option is SQLcl ( java based sqlplus which is core of sqldev ) see this example: stackoverflow.com/a/48894107/3715100

Collectives™ on Stack Overflow

How do I Display results from Oracle query to the console

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related