Re: select count(0) from variable_name...

From: Rod Corderey <Lane_Associates_at_compuserve.com>
Date: Wed, 29 Apr 1998 12:45:12 +0100
Message-ID: <354712C8.14D6D9ED_at_compuserve.com>

Hi,

a couple of things here.

1. your construct is syntactically incorrect. A select that is not a cursor must have an INTO.
2. direct dynamic sql in PLSQL is not possible.
3. yes you could build the SQL dynamically and execute it via DBMS_SQL.

If you only want to get a count of all your tables , could you not use something like the following directly in SQLPLUS?

ACCEPT THIS_OBJECT_NAME prompt "Enter name of TABLE TO BE COUNTED :- "
SET TERMOUT OFF
SET LINES 150
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SPOOL SEECOUNT.RUN
SELECT 'select RPAD('''

       ||OBJECT_NAME
       ||''',50,'||''' '||'''), count(*) from '||OBJECT_NAME||';'

FROM USER_OBJECTS
WHERE OBJECT_NAME LIKE UPPER(nvl('&THIS_OBJECT_NAME','%')) AND OBJECT_TYPE = 'TABLE'
;
SPOOL OFF
SET TERMOUT ON
START SEECOUNT.RUN regards

Rod Corderey
Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com

brunson_at_brunson.com wrote:
>
> This is probabaly an FAQ, but I didn't find an FAQ that addressed it.
>
> I want to do this:
>
> declare
> cursor name_cur is
> select table_name
> from tabs;
>
> rowcount INTEGER;
> begin
> for name_row in name_cur
> loop
> select count(0)
> from name_row.table_name;
> end loop;
> end;
>
> I guess I can see why it might be hard to implement, but it seems silly
> not to be able to do it.
>
> Am I just missing something obvious, or is there a trick to it.
>
> Could it be done by dynamically building the SQL and executing it?
>
> Thanks for the input,
> e.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Wed Apr 29 1998 - 13:45:12 CEST