Message-Id: <10752.127488@fatcity.com> From: David Barbour Date: Thu, 25 Jan 2001 09:27:25 -0600 Subject: RE: URGENT SQL ( Dynamic table name) This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C086E3.51C72E00 Content-Type: text/plain; charset="iso-8859-1" Bunyamin, You're going to need two cursors, one of which has to be dynamic. Cursor c1 has to have a dynamic "partner" that performs the count function. Something like this: Declare Cursor c1 is Select table_name from user_tables; c1row c1%rowtype; TYPE RefCurTyp IS REF CURSOR; countCur RefCurTyp; countRec integer; Begin BEGIN For c1row in c1 LOOP Open countCur for 'Select count(*) from ' || c1row.TABLE_NAME; Fetch countCur into countRec; Close countCur; ..... END LOOP; END; This is just off the top of my head, and I can think of a number of different ways to do this, but this should give you the basic idea. Hope it helps. David A. Barbour Oracle DBA - ConnectSouth 512-681-9438 dbarbour@connectsouth.com -----Original Message----- From: Bunyamin K.Karadeniz [mailto:bunyamink@havelsan.com.tr] Sent: Thursday, January 25, 2001 8:31 AM To: Multiple recipients of list ORACLE-L Subject: URGENT SQL ( Dynamic table name) I have to write a script to query all the tables of a user. So I have to give the table_name as variable. I wrote .... Declare Cursor c1 is Select table_name from user_tables; c1row c1%rowtype; BEGIN For c1row in c1 LOOP Select count(*) from c1row.TABLE_NAME; ..... END LOOP; END; But it does not work How can I give table_names , table_column_names dynamic. Can you Give an example ?? ------_=_NextPart_001_01C086E3.51C72E00 Content-Type: text/html; charset="iso-8859-1"

Bunyamin,

You're going to need two cursors, one of which has to be dynamic. Cursor c1 has to have a dynamic "partner" that performs the count function.

Something like this:

Declare

Cursor c1 is Select table_name from user_tables;

c1row c1%rowtype;

        TYPE RefCurTyp IS REF CURSOR;
        countCur RefCurTyp;
        countRec        integer;

Begin

BEGIN

For c1row in c1 LOOP

Open countCur for 'Select count(*) from ' || c1row.TABLE_NAME;

Fetch countCur into countRec;

Close countCur;

.....

END LOOP;

END;

This is just off the top of my head, and I can think of a number of different ways to do this, but this should give you the basic idea.

Hope it helps.

David A. Barbour
Oracle DBA - ConnectSouth
512-681-9438
dbarbour@connectsouth.com

-----Original Message-----
From: Bunyamin K.Karadeniz [mailto:bunyamink@havelsan.com.tr]
Sent: Thursday, January 25, 2001 8:31 AM
To: Multiple recipients of list ORACLE-L
Subject: URGENT SQL ( Dynamic table name)

I have to write a script to query all the tables of a user. So I have to give the table_name as variable.

I wrote ....

Declare

Cursor c1 is Select table_name from user_tables;

c1row c1%rowtype;

BEGIN

For c1row in c1 LOOP

Select count(*) from c1row.TABLE_NAME;

.....

END LOOP;

END;

But it does not work How can I give table_names , table_column_names dynamic.

Can you Give an example ??