CODEWITHSUNDEEP
sqloracle-databaseplsql
n0ob
n0ob

Reputation: 1285

how to print out the whole table using anonymous block in pl sql?

I want to use DBMS_OUTPUT.PUT_LINE, but the number of rows exceeds just 1. There is nothing in the table which increments sequentially, so I can't use a loop. Is there some way to print out each row in the table?

Upvotes: 10

Views: 45286

Answers (3)

Md Sultan
Md Sultan

Reputation: 39

This may 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;

Upvotes: 3

APC
APC

Reputation: 146249

The quick and dirtiest way of doing 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 functionality; we can even generate HTML files.

If you have a very long table (many rows) or a wide one (many columns) you may be better off outputting directly to a file, like this.

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 look like a chore, but the PUT() calls can be generated from USER_TAB_COLUMNS. There are a couple of gotchas with UTL_FILE so read the documentation.

You could use the same control 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 spool out from a SQL*Plus, why not use the easier option?

Upvotes: 2

Jonathan
Jonathan

Reputation: 12025

try with 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;
        /

Upvotes: 19

Related Questions