Re: PL/SQL Problem

Seems to me, before posting a coded answer, one might try to run it first to see if it actually works. The only way to run DDL from PL/SQL is to use dynamic SQL (DBMS_SQL).

Ajaya K Agrawalla <AKA8369_at_tntech.edu> wrote in article <3426C863.7FCA_at_tntech.edu>...
> Gary England wrote:
> >
> > Chinna wrote:
> > >
> > > Here it is:
> > >
> > > declare
> > > found_table number;
> > > cursor tab is select count(*) from user_tables where table_name like
> > > 'table_name';
> > > begin
> > > found_table :=0;
> > > open tab;
> > > fetch tab into found_table;
> > > if tab%found
> > > then
> > > if found_table=1
> > > then
> > > drop table table_name;
> > > create table table_name;
> > > end if;
> > > end if;
> > > close tab;
> > > end;
> > >
> > > Let me know if this helped,
> > > Chinna
> > >
> > [SNIP]
> >
> > When did PL/SQL start supporting DROP and CREATE? I am still on 2.3.3
> > and get:
> >
> > Connected to:
> > Oracle7 Server Release 7.3.3.0.0 - Production Release
> > With the distributed, replication and parallel query options
> > PL/SQL Release 2.3.3.0.0 - Production
> >
> > SQL> declare
> > 2 begin
> > 3 create table temp (col1 as varchar2(2));
> > 4 end;
> > 5 /
> > create table temp (col1 as varchar2(2));
> > *
> > ERROR at line 3:
> > ORA-06550: line 3, column 1:
> > PLS-00103: Encountered the symbol "CREATE" when expecting one of the
> > following:
> > begin declare exit for goto if loop mod null pragma raise
> > return select update while <an identifier>
> > <a double-quoted delimited-identifier> <a bind variable>
> > <a single-quoted SQL string> << close delete fetch lock
> > insert open rollback savepoint set sql commit
> >
> > SQL>
> >
> > What version are you using that allows CREATE TABLE and DROP TABLE?
> >
> > I have heard there is a package function DBMS_CMD that may allow such
> > activity, but have never tried that. Do not even know the syntax for
> > calling it.
> >
> > Anyone else KNOW?
>
> As far as I know DDL Commands can not be issues in the PL/SQL Syntax. I
> have not also heard of DBMS_CMD Package. Have U???
>
> I use the Dynamic SQL (DBMS_SQL) for DDL in PL/SQL. Can u please let me
> know.
>
> Thanks
>
> Ajay
>
Received on Tue Sep 23 1997 - 00:00:00 CDT