sql query...columns to row
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: sql query...columns to row

  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Lightbulb sql query...columns to row

    hi,

    I need to convert a single row table of 100+ columns to single column table with 100+ rows.
    Just need to do this avoiding control statements.If possible

    Can any one help in this query.

    Thank you

    bye
    Raj

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    This is from Tom Kyte (Right Amar ?? ) You can slightly modify this code to achieve your objective.

    Code:
    CREATE OR REPLACE procedure print_table( p_query in varchar2 )
    AUTHID CURRENT_USER
    is
        l_theCursor     integer default dbms_sql.open_cursor;
        l_columnValue   varchar2(4000);
        l_status        integer;
        l_descTbl       dbms_sql.desc_tab;
        l_colCnt        number;
    begin
        dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
        dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl);
    
        for i in 1 .. l_colCnt loop
            dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
        end loop;
    
        l_status := dbms_sql.execute(l_theCursor);
    
        while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
            for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i, l_columnValue );
               dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 )
                                      || ': ' ||
                                      l_columnValue );
            end loop;
            dbms_output.put_line( '-----------------' );
        end loop;
    exception
        when others then
            dbms_sql.close_cursor( l_theCursor );
            RAISE;
    end;
    /
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width