Re: PL/SQL Problem

From: Gary England <gengland_at_hiwaay.net>
Date: 1997/09/22
Message-ID: <34265247.1BBE@hiwaay.net>#1/1

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? Received on Mon Sep 22 1997 - 00:00:00 CDT