Re: Why do I get this sqlplus error?

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Wed, 07 Jun 2000 20:47:25 -0400
Message-Id: <10521.108156@fatcity.com>

You are attempting to use the SQL*Plus @ command from within a PL/SQL block. The @ command has meaning only to the SQL*Plus executable. PL/SQL runs within the database. Try something like this:

--declare SQL*Plus bind variable
variable next_script_bind varchar2(50)

declare
...
begin
if ... then

   :next_script_bind :=3D 'drop_tabs.sql'; else

   :next_script_bind :=3D 'do_not_drop.sql'; endif;
end;
/

--you are back in SQL*Plus now. Get the bind variable --into a user variable
column x new_value next_script
select next_script_bind x from dual;

--next_script now has the name of the next script to run @&next_script

Hope this helps.

Jonathan

---

jonathan_at_gennick.com =20
http://gennick.com
Brighten the Corner Where You Are

On Wed, 07 Jun 2000 14:24:14 -0800, you wrote:

>Hi:
>
>I have a sql script file called "test.sql" that I try to run in sqlplus.=
=20
>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=20
>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=20
>"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=20
>owner=3D'CATALOG_MANAGEMENT';
>
> if TABLE_COUNT > 0 then
>
> dbms_output.put_line ('Error: There are tables in schema=20
>CATALOG_MANAGEMENT!');
> dbms_output.put_line ('Table Count =3D ' || 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;
>
>/
>
>________________________________________________________________________
Received on Wed Jun 07 2000 - 19:47:25 CDT