Re: COUNT(*) in DYNAMIC SQL

From: Peter Mroz <pmroz_at_domaincorp.com>
Date: 1997/04/25
Message-ID: <3360AD28.2287@domaincorp.com>#1/1

jagdeeps_at_hotmail.com wrote:
>
> Hi all,
>
> I am sorry if this question has been already answered earlier.
> I am trying to do a 'SELECT COUNT(*) ' from a table, whose name is
> generated dynamically'. How do I do this ?
>
> Thanks
>
> Jagdeep
> jagdeeps_at_hotmail.com
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

You need to use dynamic SQL. Here's some code pulled out of another function of mine that does what you want.

/* count_records: This function determines how many records match some condition.

*/

FUNCTION count_records(

	tbl		IN VARCHAR2,
	i_where_clause	IN VARCHAR2,
	o_error_msg	OUT VARCHAR2)

RETURN integer IS

    pname CONSTANT VARCHAR2(20) := 'COUNT_RECORDS';

    sql_string VARCHAR2(500);

    sql_count		INTEGER;
    usql_count		INTEGER;

    count_cursor	INTEGER := DBMS_SQL.OPEN_CURSOR;
    nrows		INTEGER;

    null_arguments	EXCEPTION;

BEGIN
    IF (tbl IS NULL)

        RAISE null_arguments;
    END IF;     IF (i_where_clause IS NOT NULL) then

	sql_string := 'SELECT COUNT(*) FROM ' || tbl || 
	    ' WHERE ' || i_where_clause;
    ELSE
	sql_string := 'SELECT COUNT(*) FROM ' || tbl;

    END IF;     DBMS_SQL.PARSE(count_cursor, sql_string, DBMS_SQL.V7);

    DBMS_SQL.DEFINE_COLUMN(count_cursor, 1, sql_count);

/* Only need to get one record */

    nrows := DBMS_SQL.EXECUTE_AND_FETCH(count_cursor, FALSE);

    DBMS_SQL.COLUMN_VALUE(count_cursor, 1, sql_count);             

    IF (DBMS_SQL.IS_OPEN(count_cursor)) THEN

        DBMS_SQL.CLOSE_CURSOR(count_cursor);
    END IF;     RETURN sql_count;

EXCEPTION     WHEN null_arguments THEN

	IF (DBMS_SQL.IS_OPEN(count_cursor)) THEN
	    DBMS_SQL.CLOSE_CURSOR(count_cursor);
	END IF;

	o_error_msg := pname || ': ' || 
		'Some arguments are NULL and need to be supplied');
	RETURN NULL;

    WHEN OTHERS THEN
	IF (DBMS_SQL.IS_OPEN(count_cursor)) THEN
	    DBMS_SQL.CLOSE_CURSOR(count_cursor);
	END IF;

	o_error_msg := pname || ': ' || SQLERRM;
	RETURN NULL;

END count_records;

-- 
Peter Mroz				Domain Solutions Corporation
Tel: 610-892-7540			1023 East Baltimore Pike, Suite 205
Fax: 610-892-7616			Media, PA 19063

Received on Fri Apr 25 1997 - 00:00:00 CDT