Re: Newbie ? - Howto find nbr records in a table

From: Jason Jay Weiland <archduke_at_uclink4.berkeley.edu>
Date: Tue, 06 Oct 1998 11:44:54 -0700
Message-ID: <361A6526.3B04961F@uclink4.berkeley.edu>

Brian,

Try this:

SELECT COUNT(*) FROM FOO; ...where FOO is the table name.

Or you can make a procedure (I HIGHLY recomend NOT doing this, use PHP or whatever scripting language you are using for your browser, unless you are reusing this with another PL/SQL package or procedure):

In a text file: c:\temp\count.sql

CREATE OR REPLACE PROCEDURE pr_count

    (v_table_name IN VARCHAR, v_count IN OUT NUMBER) IS

    v_foo INTEGER;
    cursor_handle INTEGER;
BEGIN
    /* create a cursor to use for dynamic SQL */     cursor_handle := DBMS_SQL.OPEN_CURSOR;

    /* Parse in Version 7 Mode */
    DBMS_SQL.PARSE

      (cursor_handle,
       'SELECT COUNT(*) FROM ' || v_table_name, DBMS_SQL.V7);

    /* Now execute */

    DBMS_SQL.DEFINE_COLUMN(cursor_handle, 1, v_count);
    v_foo := DBMS_SQL.EXECUTE_AND_FETCH (cursor_handle);
    DBMS_SQL.COLUMN_VALUE (cursor_handle, 1, v_count);

    /* Close the cursor */
    DBMS_SQL.CLOSE_CURSOR (cursor_handle); END;
/
SHOW ERRORS Then in SQL Plus:
SQL> @c:\temp\count
Procedure created.
No errors.
SQL> VARIABLE v_jay NUMBER
SQL> execute pr_count('FOO', :jay);
PL/SQL procedure successfully completed. SQL> print jay;

      JAY

---

      107

Jay!!!

Brian Richardson wrote:

> Hello,

>

> I want to find out how many records (total) are in a table. I'm writing
> some
> PL/SQL to allow a user to interact with an Oracle database from a
> pc-based
> browser.

>

> Can anyone suggest a simple method for this ?

>

> Environment: Oracle v.7.3.x, Sun Solaris, Netscape clients, PL/SQL
> Packages

>

> Thanks in advance.

>
> Brian Richardson
Received on Tue Oct 06 1998 - 13:44:54 CDT