| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: CREATE TABLE in procedure?
Alan;
You cannot do any DDL (create table etc.) statements in a PL/SQL
procedure, or any PL/SQL block.
There are 2 ways around this.
1- Create a table in SQLPLUS using simple commands and delete the
table when you're through
-or-
2- Declare a PL/SQL table datatype and use this in your procedure.
(The advantage to this is that hte PL/SQL table lives only while the procedure is being run and takes up no permanent space on the database).
I hope this helps.
Roger
In article <86oqp7$25pg$3_at_dolf.netfront.net>,
"M. Armaghan Saqib" <armaghan_at_yahoo.com> wrote:
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0076_01BF64EE.A5B43CF0
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> My 2 cents:
>
> My SQL PlusPlus (freeware) makes using DBMS_SQL very easy. It
generates =
> all the PLSQL code required to WRAP an SQL statement before executing.
>
> regards,
> M. Armaghan Saqib
> +---------------------------------------------------------------
> | 1. SQL PlusPlus =3D> Add power to SQL Plus command line
> | 2. SQL Link for XL =3D> Integrate Oracle with XL
> | 3. Oracle CBT with sample GL Accounting System
> | Download free: http://www.geocities.com/armaghan/
> +---------------------------------------------------------------
> Sybrand Bakker <postmaster_at_sybrandb.demon.nl> wrote in message =
> news:948435085.4842.0.pluto.d4ee154e_at_news.demon.nl...
> This same question was answered yesterday.
> Use the dbms_sql package (v7)
> Use execute immediate (v8)
>
> Hth,
>
> --=20
> Sybrand Bakker, Oracle DBA
> Alan Hong <hongshi_at_fuse.net> wrote in message =
> news:s8fhhcjseh670_at_corp.supernews.com...
> Hi,
>
> Can I put the "create table temp1 as select .... from .... where =
> ....." into
> a stored procedure/function on the server??
>
> I got the error when I was trying to do this:
>
> PLS-00103: Encountered the symbol "CREATE" when expecting one of
the
> following:
>
> begin declare end exception exit for goto if loop mod null
> pragma raise return select update while <an identifier
>
> Is it possible to put 'CREATE table....' in a procedure?
>
> Any idea/help will be greatly appreciated!!
>
> Dana
> dan_jian_at_hotmail.com
>
> Andreas Michler <Andreas.Michler_at_adicom.de> wrote in message
> news:3887301D.38E238D0_at_adicom.de...
> > I think you mean temp-tables.
> > In this way oracle doesn't have any temp Tables.
> > You can do it in the following way
> > create table temp1 as select .... from .... where .....,;
> > create table temp2 as select .... from .... where ....;
> >
> > Don't forget to drop the table temp1,temp2,,,, manually. The
will =
> not be
> > droppped automatically.
> >
> >
> > Dana Jian wrote:
> >
> > > Hi,
> > >
> > > I'm converting one procedure from SQL server to Oracle =
> procedure.
> > >
> > > Here's the simplified statements in the SQL proc,
> > >
> > > SELECT ... INTO #temp1 FROM....WHERE..
> > >
> > > SELECT ... INTO #temp2 FROM... WHERE
> > >
> > > SELECT ... FROM #temp1, #temp2 , .... WHERE ...
> > >
> > > How to do this in Oracle PL/SQL?
> > >
> > > Any comments/help will be highly appreciated!!
> > >
> > > Dana
> > > djian_at_trasa.com
> >
> > --
> > -------------------------------------
> > ADICOM Informatik GmbH
> > Andreas Michler
> > Wiesfleckenstr. 34
> > 72336 Balingen
> > Tel: 07433/9977-57,Fax: -90
> > E-Mail: Andreas.Michler_at_adicom.de
> > http:\\www.adicom.de
> > -------------------------------------
> >
> >
>
> ------=_NextPart_000_0076_01BF64EE.A5B43CF0
> 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>
> <META content=3D"text/html; charset=3Diso-8859-1" =
> http-equiv=3DContent-Type>
> <META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><FONT face=3D"Courier New" size=3D2>My 2 cents:</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>My SQL PlusPlus (freeware) =
> makes using=20
> DBMS_SQL very easy. It generates all the PLSQL code required to WRAP
an =
> SQL=20
> statement before executing.</FONT></DIV>
> <DIV><BR>regards,<BR>M. Armaghan=20
>
Saqib<BR>+---------------------------------------------------------------=
ghan/</A><BR>+-----------------------------------------------------------=
-------------------------------------<BR>><BR>><BR><BR></DIV></BLOC=
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Jan 28 2000 - 00:00:00 CST
 |
 |