Re: Why do I get this sqlplus error?

In article <8hme77$js7$1_at_nnrp1.deja.com>,   gmei_at_my-deja.com wrote:
> Hi:
>
> I have a sql script file called "test.sql" that I try to run in
> sqlplus. When I run it, it get the following error:
>
> SQL> @e:\catalog_management\test;
> drop table CATALOG_MANAGEMENT.HPXCATALOGBRANCH;
> *
> ERROR at line 27:
> ORA-06550: line 27, column 1:
> PLS-00103: Encountered the symbol "DROP" when expecting one of the
> following:
> begin declare else elsif end exit for goto if loop mod null
> pragma raise return select update while <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql execute commit forall
> <a single-quoted SQL string>
> The symbol "lock was inserted before "DROP" to continue.
> ORA-06550: line 27, column 47:
> PLS-00103: Encountered the symbol ";" when expecting one of the
> following:
> . , @ in <an identifier>
> <a double-quoted delimited-identifier>
>
> The drop table command is the first command
> in "drop_tabs_catalog_management.sql" file.
>
> I am wondering why I got the error.
>
> Thanks.
>
> Guang
>
> PS: Here is the code for "test.sql":
>
> -- test.sql
> -- by Guang Mei, 6/6/2000
> --
>
> set serveroutput on
>
> declare
> TABLE_COUNT NUMBER;
>
> begin
>
> select count(*) into TABLE_COUNT from all_tables where
> owner='CATALOG_MANAGEMENT';
>
> if TABLE_COUNT > 0 then
>
> dbms_output.put_line ('Error: There are tables in schema
> CATALOG_MANAGEMENT!');
> dbms_output.put_line ('Table Count = ' || TABLE_COUNT);
> @e:\catalog_management\drop_tabs_catalog_management.sql;
>
> else
>
> NUll;
> @e:\catalog_management\copy_from_prod_to_catalog_mang.sql;
>
> end if;
>
> end;
>
> /
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Explicit DDL is not allowed in a PL/SQL block, so your script that tries to execute the drop table statement is doing so within a PL/SQL block. Try this instead:

PROCEDURE drop_object
(obj_type_in IN VARCHAR2, obj_name_in IN VARCHAR2) IS
 cursor_id integer;
begin
 cursor_id := dbms_sql.open_cursor;
 dbms_sql.parse(cursor_id, 'DROP '||obj_type_in||' '||obj_name_in, dbms_sql.NATIVE);
 dbms_sql.close_cursor(cursor_id);
exception
 when others
 then
   dbms_sql.close_cursor(cursor_id);
end;
/

PROCEDURE copy_object
(obj_type_in IN VARCHAR2, obj_name_in IN VARCHAR2, schma IN VARCHAR2) IS
 cursor_id integer;
begin
 cursor_id := dbms_sql.open_cursor;
 dbms_sql.parse(cursor_id, 'CREATE '||obj_type_in||' '||obj_name_in||' as select * from '||schma||'.'||obj_name_in, dbms_sql.NATIVE);  dbms_sql.close_cursor(cursor_id);
exception
 when others
 then
   dbms_sql.close_cursor(cursor_id);
end;
/

Create these procedures and call them from your PL/SQL script:

• test.sql
• by Guang Mei, 6/6/2000 --

 set serveroutput on

 declare
   TABLE_COUNT NUMBER;
   cursor get_cat_man_tabs is
   select table_name
   from all_tables
   where owner = 'CATALOG_MANAGEMENT';
   cursor get_prod_tabs is
   select table_name
   from all_tables
   where owner = 'PROD';

 begin

   select count(*) into TABLE_COUNT from all_tables where  owner='CATALOG_MANAGEMENT';

   if TABLE_COUNT > 0 then

      dbms_output.put_line ('Error: There are tables in schema  CATALOG_MANAGEMENT!');

      dbms_output.put_line ('Table Count = ' || TABLE_COUNT);
      for cat_man_tabs in  get_cat_man_tabs loop
          drop_object('table',cat_man_tabs.table_name);
      end loop;

   else

      NUll;
      for prod_tabs in get_prod_tabs loop
          copy_object('table', prod_tabs.table_name, 'prod');
      end loop;

   end if;

 end;

 /

This should work for you. You may need to add cursors to replicate indexes, and you may need to change the procedures a bit to account for tablespace location and storage parameters. Any tablespace or storage parameters in the dynamic create object statement should go before the 'as select' text.

David Fitzjarrell
Oracle DBA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jun 07 2000 - 00:00:00 CDT