RE: newbie: problem in dynamic SQL

From: Kevin Kostyszyn <kevin_at_dulcian.com>
Date: Wed, 31 Jan 2001 14:02:30 -0500
Message-Id: <10758.127935@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_002C_01C08B8E.734236B0 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

RE: newbie: problem in dynamic SQLthat was funny -----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Koivu, Lisa Sent: Wednesday, January 31, 2001 1:37 PM To: Multiple recipients of list ORACLE-L Subject: RE: newbie: problem in dynamic SQL

Gabriel, the dba needs to know about it, period, because he/she is responsible for storage and performance of the database overall. In addition he or she may have other ideas that you can use instead of actually creating a table (pl/sql tables or something?)

Repeat until you believe it: THE DBA IS YOUR FRIEND! THE DBA IS NOT MEAN! HTH
Lisa

-----Original Message-----
From: Gabriel Galanternik [mailto:ggalanterni_at_tesis-oys.com.ar] Sent: Wednesday, January 31, 2001 12:21 PM To: Multiple recipients of list ORACLE-L Subject: RE: newbie: problem in dynamic SQL

  stephane, just curious, really, no subtle lines...   should I, a developer, tell my dba that I'm gonna create temporary tables?   (I know most of you are dba's, please don't flame me...)   deep inside I would say, yes, but, really why?   Rigth access?
  space allocation?
  performance tunning?
  curious dba?

  FYI, I, as a technical support for my developers, I would think twice before
  creating tables dinamically.
  and I would ask very good reasons to do it.   But when I had to do it I really didn't realize to tell the dba...

Gabriel Galanternik

Original Message ----- To: Multiple recipients of list ORACLE-L Sent: Wednesday, January 31, 2001 7:30 AM

On top of the explanation, I have 2 questions. Are you a dba ?
If not, does your dba knows that you will create dynamically ?

Gabriel Galanternik <ggalanterni_at_tesis-oys.com.ar> a écrit : > that's right and all code referencing this table
> must be dynamic too
>
> my 0.02
> Gabriel Galanternik
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L
> Sent: Tuesday, January 30, 2001 7:35 AM
>
>
> Hi Anurag,
> Your table does not exist till the time the
> procedure is actually
> executed. So, when your procedure tries the access
> the table the compiler
> is going to complain cos it can't find the table you
> are referring to.
> Since at compile time your table does not exist.
> HTH
> Chandini
>
> -----Original Message-----
> dewan
> Sent: 30 January 2001 09:36
> To: Multiple recipients of list ORACLE-L
>
>
> hi,
> I am calling a stored procedure which dynamically
> creates a table in a procedure. But if I am
> accessing
> this table later in the procedure, ORACLE gives an
> error PLS-00201-saying that identifier must be
> declared(the identifier that it quotes is the name
> of
> the table which was created dynamcally).
> please guide me
> anurag
>
>

> Do You Yahoo!?
> Get your free @yahoo.co.uk address at
> http://mail.yahoo.co.uk
> or your free @yahoo.ie address at
> http://mail.yahoo.ie
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?anurag=20dewan?=
> INET: anu_dewan_at_yahoo.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Chandini Paterson
> INET: chandini.paterson_at_morse.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Gabriel Galanternik
> INET: ggalanterni_at_tesis-oys.com.ar
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).

  Stephane Paquette
  DBA Oracle
  stephane_paquette_at_yahoo.com

  Get personalized email addresses from Yahoo! Mail - only $35   a year! http://personal.mail.yahoo.com/   --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com   --
  Author: =?iso-8859-1?q?paquette=20stephane?=     INET: stephane_paquette_at_yahoo.com

  Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
  San Diego, California        -- Public Internet access / Mailing Lists
  --------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com   --
  Author: Gabriel Galanternik
    INET: ggalanterni_at_tesis-oys.com.ar

  Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
  San Diego, California        -- Public Internet access / Mailing Lists
  --------------------------------------------------------------------

------=_NextPart_000_002C_01C08B8E.734236B0 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: newbie: problem in dynamic SQL</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =

charset=3Diso-8859-1">

<META content=3D"MSHTML 5.50.4207.2601" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D343230219-31012001><FONT face=3DArial color=3D#0000ff =

size=3D2>that=20
was funny</DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px"> <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft>-----Original Message----- From: root_at_fatcity.com=20 [mailto:root_at_fatcity.com]On Behalf Of Koivu, = Lisa Sent:=20
 Wednesday, January 31, 2001 1:37 PM To: Multiple recipients = of list=20
 ORACLE-L Subject: RE: newbie: problem in dynamic=20 SQL </DIV>
 Gabriel, the dba needs to know about it, period, = because=20
 he/she is responsible for storage and performance of the database=20 overall.   In addition he or she may have other ideas that = you can=20
 use instead of actually creating a table (pl/sql tables or=20 something?)
 Repeat until you believe it:  THE DBA IS YOUR=20 FRIEND!  THE DBA IS NOT MEAN!  HTH Lisa -----Original Message----- From:=20
 Gabriel Galanternik [<A=20
 =
href=3D"mailto:ggalanterni_at_tesis-oys.com.ar">mailto:ggalanterni_at_tesis-oys= .com.ar</A>]=20
 Sent: Wednesday, January 31, 2001 12:21 PM = <FONT=20
 size=3D2>To: Multiple recipients of list ORACLE-L <FONT=20 size=3D2>Subject: RE: newbie: problem in dynamic SQL stephane, just curious, really, no subtle = lines...=20
 should I, a developer, tell my dba that I'm gonna = create=20
 temporary tables? (I know most of you are = dba's,=20
 please don't flame me...) deep inside I = would say,=20
 yes, but, really why? Rigth access? = <FONT=20
 size=3D2>space allocation? performance = tunning?=20
 curious dba? FYI, I, as a technical support for my developers, I = would=20
 think twice before creating tables = dinamically.=20
 and I would ask very good reasons to do it. = <FONT=20
 size=3D2>But when I had to do it I really didn't realize to tell the=20

  dba...</FONT> </P>
  <P><FONT size=3D2>Gabriel Galanternik</FONT> </P>
  <P><FONT size=3D2>----- Original Message -----</FONT> <BR><FONT =

size=3D2>To:=20
 Multiple recipients of list ORACLE-L Sent: = Wednesday,=20
 January 31, 2001 7:30 AM On top of the explanation, I have 2 = questions.=20
 Are you a dba ? If not, = does your dba=20
 knows that you will create dynamically = ? 
 --- Gabriel Galanternik=20 <ggalanterni_at_tesis-oys.com.ar> a = =E9crit : >=20
 that's right and all code referencing this <FONT=20 size=3D2>table > must be dynamic = too <FONT=20
 size=3D2>> > my 0.02 >=20
 Gabriel Galanternik > ----- Original = Message=20
 ----- > To: Multiple recipients of list=20 ORACLE-L > Sent: Tuesday, January 30, = 2001 7:35=20
 AM > > <FONT=20
 size=3D2>> Hi Anurag, >   = Your table=20
 does not exist till the time the > = procedure is=20
 actually > executed.  So, when your = procedure=20
 tries the access > the table the = compiler=20
 > is going to complain cos it can't find the = table=20
 you > are referring to. <FONT=20 size=3D2>> Since at compile time your table does not exist. = <FONT=20
 size=3D2>> HTH > Chandini = <FONT=20
 size=3D2>> > -----Original = Message-----=20
 > dewan > Sent: 30 = January 2001=20
 09:36 > To: Multiple recipients of list=20 ORACLE-L > >=20
 > hi, >   = I am=20
 calling a stored procedure which dynamically >=20
 creates a table in a procedure. But if I am >=20
 accessing > this table later in the = procedure,=20
 ORACLE gives an > error PLS-00201-saying = that=20
 identifier must be > declared(the = identifier that=20
 it quotes is the name > of = <FONT=20
 size=3D2>> the table which was created dynamcally). = <FONT=20
 size=3D2>> please guide me > = anurag=20
 > > = <FONT=20
 =

size=3D2>____________________________________________________________</FO=

NT>=20
 > Do You Yahoo!? > = Get your=20
 free @yahoo.co.uk address at > <A = target=3D_blank=20
 href=3D"http://mail.yahoo.co.uk">http://mail.yahoo.co.uk</A> = <FONT=20
 size=3D2>> or your free @yahoo.ie address at > <A=20
 target=3D_blank =
href=3D"http://mail.yahoo.ie">http://mail.yahoo.ie</A>=20 > -- > Please see = the official=20
 ORACLE-L FAQ: > <A target=3D_blank=20 href=3D"http://www.orafaq.com">http://www.orafaq.com</A> = <FONT=20
 size=3D2>> -- > Author:=20 =3D?iso-8859-1?q?anurag=3D20dewan?=3D >   INET:=20
 anu_dewan_at_yahoo.co.uk > <FONT=20 size=3D2>> Fat City Network Services    -- (858)=20 538-5051  FAX: > (858) = 538-5051=20
 > San Diego,=20
 California        -- Public = Internet=20
 > access / Mailing Lists <FONT=20 size=3D2>> <FONT=20
 =

size=3D2>----------------------------------------------------------------=

----=20
 > To REMOVE yourself from this mailing list, = send=20
 an > E-Mail message >=20
 to: ListGuru_at_fatcity.com (note EXACT spelling of >=20
 'ListGuru') and in > the message BODY, = include a=20
 line containing: UNSUB > ORACLE-L = <FONT=20
 size=3D2>> (or the name of mailing list you want to be = removed=20
 > from).  You may > also=20
 send the HELP command for other information > (like=20
 subscribing). > >=20
 -- > Please see the official ORACLE-L = FAQ:=20
 > <A target=3D_blank=20 href=3D"http://www.orafaq.com">http://www.orafaq.com</A> = <FONT=20
 size=3D2>> -- > Author: Chandini = Paterson=20
 >   INET: = chandini.paterson_at_morse.com=20
 > > Fat City = Network=20
 Services    -- (858) 538-5051  FAX: = <FONT=20
 size=3D2>> (858) 538-5051 > San Diego, =

California        -- Public = Internet=20
 > access / Mailing Lists <FONT=20 size=3D2>> <FONT=20
 =

size=3D2>----------------------------------------------------------------=

----=20
 > To REMOVE yourself from this mailing list, = send=20
 an > E-Mail message >=20
 to: ListGuru_at_fatcity.com (note EXACT spelling of >=20
 'ListGuru') and in > the message BODY, = include a=20
 line containing: UNSUB > ORACLE-L = <FONT=20
 size=3D2>> (or the name of mailing list you want to be = removed=20
 > from).  You may > also=20
 send the HELP command for other information > (like=20
 subscribing). > >=20
 -- > Please see the official ORACLE-L = FAQ:=20
 > <A target=3D_blank=20 href=3D"http://www.orafaq.com">http://www.orafaq.com</A> = <FONT=20
 size=3D2>> -- > Author: Gabriel = Galanternik=20
 >   INET: = ggalanterni_at_tesis-oys.com.ar=20
 > > Fat City = Network=20
 Services    -- (858) 538-5051  FAX: = <FONT=20
 size=3D2>> (858) 538-5051 > San Diego, =

California        -- Public = Internet=20
 > access / Mailing Lists <FONT=20 size=3D2>> <FONT=20
 =

size=3D2>----------------------------------------------------------------=

size=3D2>__________________________________________________</FONT>=20

Get personalized email addresses from Yahoo! Mail - = only=20
 $35 a year!  <A target=3D_blank=20 =
href=3D"http://personal.mail.yahoo.com/">http://personal.mail.yahoo.com/<= /A>=20
 -- Please see the = official ORACLE-L=20
 FAQ: <A target=3D_blank=20
 href=3D"http://www.orafaq.com">http://www.orafaq.com</A> = <FONT=20
 size=3D2>-- Author:=20 =3D?iso-8859-1?q?paquette=3D20stephane?=3D   INET:=20
 stephane_paquette_at_yahoo.com Fat City Network Services    -- (858) =

538-5051  FAX: (858) 538-5051 San = Diego,=20
 California        -- Public = Internet access=20
 / Mailing Lists <FONT=20
 =

size=3D2>----------------------------------------------------------------=

----=20
 To REMOVE yourself from this mailing list, send an = E-Mail=20
 message to: ListGuru_at_fatcity.com (note EXACT = spelling=20
 of 'ListGuru') and in the message BODY, = include a line=20
 containing: UNSUB ORACLE-L (or the name of = mailing=20
 list you want to be removed from).  You may also=20
 send the HELP command for other information (like subscribing). = 
 -- Please see the official = ORACLE-L=20
 FAQ: <A target=3D_blank=20
 href=3D"http://www.orafaq.com">http://www.orafaq.com</A> = <FONT=20
 size=3D2>-- Author: Gabriel = Galanternik <FONT=20
 size=3D2>  INET: ggalanterni_at_tesis-oys.com.ar Fat City Network Services    -- (858) =

538-5051  FAX: (858) 538-5051 San = Diego,=20
 California        -- Public = Internet access=20
 / Mailing Lists <FONT=20
 =

size=3D2>----------------------------------------------------------------=

----=20
 To REMOVE yourself from this mailing list, send an = Received on Wed Jan 31 2001 - 13:02:30 CST