RE: newbie: problem in dynamic SQL

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Wed, 31 Jan 2001 19:38:17 -0000
Message-Id: <10758.127945@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0012_01C08BBD.5BC30080 Content-Type: text/plain;

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

RE: newbie: problem in dynamic SQLLMAO! I love it! You trying saying that to me who has to battle you DBA types on a daily basis to earn a living.. Whoops.. off topic there.. Runs off in a flame retardant suit shouting sorry..
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Koivu, Lisa Sent: Wednesday, January 31, 2001 06:37 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_0012_01C08BBD.5BC30080 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 content=3D"text/html; charset=3Diso-8859-1" =

http-equiv=3DContent-Type>

<META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =

class=3D280083619-31012001>LMAO!=20
I love it! You trying saying that to me who has to battle you DBA types = on a=20
daily basis to earn a living..  Whoops.. off topic there.. Runs off = in a=20
flame retardant suit shouting sorry..</DIV> <BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px"> <DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr>-----Original Message----- From: root_at_fatcity.com=20 [mailto:root_at_fatcity.com]On Behalf Of Koivu, = Lisa Sent:=20
 Wednesday, January 31, 2001 06:37 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=20 href=3D"http://mail.yahoo.co.uk"=20
 target=3D_blank>http://mail.yahoo.co.uk</A> > or your=20
 free @yahoo.ie address at > <A=20 href=3D"http://mail.yahoo.ie" =
target=3D_blank>http://mail.yahoo.ie</A>=20 > -- > Please see = the official=20
 ORACLE-L FAQ: > <A = href=3D"http://www.orafaq.com"=20
 target=3D_blank>http://www.orafaq.com</A> > --=20
 > Author: =
=3D?iso-8859-1?q?anurag=3D20dewan?=3D <FONT=20 size=3D2>>   INET: anu_dewan_at_yahoo.co.uk <FONT =

size=3D2>> > 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 href=3D"http://www.orafaq.com"=20 target=3D_blank>http://www.orafaq.com</A> > --=20
 > Author: Chandini Paterson <FONT=20 size=3D2>>   INET: chandini.paterson_at_morse.com = <FONT=20
 size=3D2>> > 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 href=3D"http://www.orafaq.com"=20 target=3D_blank>http://www.orafaq.com</A> > --=20
 > Author: Gabriel Galanternik <FONT=20 size=3D2>>   INET: ggalanterni_at_tesis-oys.com.ar = <FONT=20
 size=3D2>> > 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=20 href=3D"http://personal.mail.yahoo.com/"=20 target=3D_blank>http://personal.mail.yahoo.com/</A> <FONT=20 size=3D2>-- Please see the official ORACLE-L = FAQ: <A=20
 href=3D"http://www.orafaq.com" =
target=3D_blank>http://www.orafaq.com</A>=20 -- 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 href=3D"http://www.orafaq.com"=20 target=3D_blank>http://www.orafaq.com</A> -- =

Author: Gabriel Galanternik <FONT =

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:38:17 CST