Re: number of columns in a table?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/08/13
Message-ID: <321115a9.976534_at_dcsun4>#1/1

You could select count(*) from user_tab_columns where table_name = 'XXX' to get it, but for a general purpose solution, I prefer the following:

    for i in 1 .. 99999999999 loop

        begin
            dbms_sql.define_column( p_theCursor, i, columnValue, 2000 );
            colCnt := colCnt + 1;
        exception
            when others then
                if ( sqlcode = -1007 ) then
                    exit;
                else
                    raise;
                end if;
        end;

    end loop;
    status := dbms_sql.execute(p_theCursor);     return colCnt;

Just look for the sqlcode of -1007 that the define_column will cause when you index past the end of the select list.
On Tue, 13 Aug 1996 16:25:12 -0400, Charlene Liang <cliang_at_curagen.com> wrote:

>Hi,
>
>We would like to know if we can get information such as number of
>columns in a table from PL/SQL?
>
>Or if we can loop through dbms_sql.define_column to exhaust all the
>colomns?
>
>Any suggestion is appreciated.
>
>Charlene
>--
>
>Charlene Liang | (203) 481-1104 ext. 287
>CuraGen Corporation |
>322 E. Main St. |
>Branford, CT 06405 |
>cliang_at_curagen.com | (FAX) (203) 481-1102

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

---

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Aug 13 1996 - 00:00:00 CEST