RE: URGENT SQL ( Dynamic table name)

From: David Barbour <dbarbour_at_connectsouth.com>
Date: Thu, 25 Jan 2001 09:27:25 -0600
Message-Id: <10752.127488@fatcity.com>

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_at_connectsouth.com

-----Original Message-----
From: Bunyamin K.Karadeniz [mailto:bunyamink_at_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"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<META content="MSHTML 5.50.4522.1800" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=009041715-25012001><FONT face=Arial color=#0000ff
size=2>Bunyamin,</FONT></SPAN></DIV>
<DIV><SPAN class=009041715-25012001><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=009041715-25012001><FONT face=Arial color=#0000ff size=2>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. 
</FONT></SPAN></DIV>
<DIV><SPAN class=009041715-25012001><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=009041715-25012001><FONT face=Arial color=#0000ff
size=2>Something like this:</FONT></SPAN></DIV>
<DIV><SPAN class=009041715-25012001><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=009041715-25012001>
<DIV><FONT face=Arial color=#0000ff size=2>Declare</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>Cursor c1 is Select table_name
from user_tables;</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>c1row c1%rowtype;</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff

size=2>        TYPE RefCurTyp IS REF CURSOR;<BR>        <SPAN class=009041715-25012001>count</SPAN>Cur RefCurTyp;<BR>        <SPAN class=009041715-25012001>count</SPAN>Rec        <SPAN class=009041715-25012001>integer</SPAN>;</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=009041715-25012001><FONT face=Arial color=#0000ff
size=2>Begin</FONT></SPAN></DIV>
<DIV><SPAN class=009041715-25012001><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=009041715-25012001>
<DIV><FONT face=Arial color=#0000ff size=2>BEGIN</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>For c1row in c1 LOOP</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>  <SPAN
class=009041715-25012001>Open countCur for '</SPAN>Select count(*) from <SPAN class=009041715-25012001>' ||
</SPAN>c1row.TABLE_NAME;</FONT></DIV>
<DIV><SPAN class=009041715-25012001><FONT face=Arial color=#0000ff
size=2>    Fetch countCur into countRec;</FONT></SPAN></DIV>
<DIV><SPAN class=009041715-25012001><FONT face=Arial color=#0000ff size=2> 
Close countCur;</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>.....</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>END LOOP;</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>END;</FONT></DIV></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=009041715-25012001>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.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=009041715-25012001></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=009041715-25012001>Hope
it helps.</SPAN></FONT></DIV></SPAN></DIV>
<DIV> </DIV>
<P><FONT face=Arial size=2>David A. Barbour</FONT> <BR><FONT face=Arial

size=2>Oracle DBA - ConnectSouth</FONT> <BR><FONT face=Arial 
size=2>512-681-9438</FONT> <BR><FONT face=Arial 
size=2>dbarbour_at_connectsouth.com</FONT> </P>

<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Bunyamin K.Karadeniz [mailto:bunyamink_at_havelsan.com.tr]<BR><B>Sent:</B> Thursday, January 25, 2001 8:31 AM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> URGENT SQL ( Dynamic table name)<BR><BR></FONT></DIV> <DIV><FONT face=Arial size=2> I have to write a script to query all the tables of a user. So I have to give the table_name as variable.</FONT></DIV>

  <DIV>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>I wrote ....</FONT></DIV>
  <DIV><FONT face=Arial size=2>Declare</FONT></DIV>
  <DIV><FONT face=Arial size=2>Cursor c1 is Select&nbsp;table_name from

user_tables;</FONT></DIV>

  <DIV><FONT face=Arial size=2>c1row c1%rowtype;</FONT></DIV>
  <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>BEGIN</FONT></DIV>
  <DIV><FONT face=Arial size=2>For c1row in c1 LOOP</FONT></DIV>
  <DIV><FONT face=Arial size=2>&nbsp; Select count(*) from

c1row.TABLE_NAME;</FONT></DIV>

  <DIV><FONT face=Arial size=2>.....</FONT></DIV>
  <DIV><FONT face=Arial size=2>END LOOP;</FONT></DIV>
  <DIV><FONT face=Arial size=2>END;</FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>&nbsp;But it does not work How can I give

table_names , table_column_names dynamic. </FONT></DIV> <DIV><FONT face=Arial size=2>  Can you Give an example ??</FONT></DIV> Received on Thu Jan 25 2001 - 09:27:25 CST