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?

Answers


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

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?


Need Your Help

SQL Reporting Services 2005 - Access by Active Directory Security Group

reporting-services

Is there a way to lock down the security of a SSRS report by AD group?

Rolling correlation between zoo objects of unequal size

r

I have two zoo objects of unequal size (inflow and outflow). Outflow values lag inflow by some unknown amount of time. I would like to determine the correlation between the smaller outflow object...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.