How to print the whole table using an anonymous block in pl sql?

Question

I want to use DBMS_OUTPUT.PUT_LINE , but the number of rows exceeds 1. There is nothing increasing in the table, so I cannot use a loop. Is there a way to print each row in a table?

Answer 1

try something like this.

 SET SERVEROUTPUT ON BEGIN -- A PL/SQL cursor FOR cursor1 IN (SELECT * FROM table1) LOOP DBMS_OUTPUT.PUT_LINE('Column 1 = ' || cursor1.column1 || ', Column 2 = ' || cursor1.column2); END LOOP; END; / 

Answer 2

The quickest and dirtiest way to do this is actually through SQL * Plus:

 SQL> set lines 200 SQL> set heading off SQL> set feedback off SQL> spool $HOME/your_table.out SQL> select * from your_table; SQL> spool off 

SQL * Plus has some neat if basic reporting features; we can even generate HTML files .

If you have a very long table (many rows) or wide (many columns), you might be better off outputting directly to a file, for example.

 declare fh utl_file.file_type; begin fh := utl_file.fopen('TARGET_DIRECTORY', 'your_table.lst', 'W'); for lrec in ( select * from your_table ) loop utl_file.put( fh, id ); utl_file.put( fh, '::' ); utl_file.put( fh, col_1 ); utl_file.put( fh, '::' ); utl_file.put( fh, col_2 ); utl_file.put( fh, '::' ); utl_file.put( fh, to_char ( col_3, 'dd-mm-yyyy hh24:mi:ss' ) ); utl_file.new_line(fh); end loop; utl_file.fclose(fh); end; / 

This may sound like a chorus, but PUT () calls can be generated from USER_TAB_COLUMNS. There are a couple of fixes with UTL_FILE, so read the documentation .

You can use the same management structure with DBMS_OUTPUT ....

 begin for lrec in ( select * from your_table ) loop dbms_output.put( id ); dbms_output.put( '::' ); dbms_output.put( col_1 ); dbms_output.put( '::' ); dbms_output.put( col_2 ); dbms_output.put( '::' ); dbms_output.put( to_char ( col_3, 'dd-mm-yyyy hh24:mi:ss' ) ); dbms_output.new_line; end loop; end; / 

... but if you are going to extract from SQL * Plus, why not use a simpler option?

Answer 3

This can help:

 BEGIN FOR MY_CURSOR IN (SELECT COLUMN1,COLUMN2,COLUMN3 FROM MY_TABLE) LOOP DBMS_OUTPUT.PUT_LINE('COLUMN1 = ' || MY_CURSOR.COLUMN1 ||', COLUMN2 = ' || MY_CURSOR.COLUMN2 ||', COLUMN3 = ' || MY_CURSOR.COLUMN3); END LOOP; END;