Re: a newbie - pl/sql problem

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 30 Jan 2005 14:10:43 -0800
Message-ID: <117123043.0000885d.084@drn.newsguy.com>

In article <ctitqs$cnq$1_at_news.onet.pl>, Marcin Balcerzak says...
>
>Hello,
>
>I'm trying to do sth like this:
>
>declare cursor x is (select * from user_tables where table_name='abc';
>begin
> if x%found then
> drop table abc;
> end if;
>end;
>.
>/
>
>
>or, "equivalently" (I know there're errors because otherwise it'd work but I
>suppose my intension are quite clear to you...):
>
>begin
> if (select count(*)m form user_tables where yable_name='abc')>0 then
> drop table abc;
> end if;
>end;
>.
>/
>
>And it does not work. As far as I've realized there are two problems: the
>very condition isn't proper and enforcing dropping the table this way on
>Oracle also appears out of my reach...
>Oh: one more. Despite doing 'set serveroutput on;' in the console, when
>using "put_line" or "put" in pl/sql scripts, I'm given back: "PLS-00201:
>identifier 'PUT_LINE' must be declared".
>What do I do wrong?
>Thanks for all answers. :-)
>Best regards.
>
>

if you must convert your sqlserver scripts in the sqlserver fashion, then you can:

ops$tkyte_at_ORA9IR2> begin
  2 execute immediate 'drop table t';   3 exception

  4      when others
  5      then
  6          if (sqlcode <> -942) then
  7              raise;
  8          end if;

  9 end;
 10 /  

PL/SQL procedure successfully completed.  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> /  

PL/SQL procedure successfully completed.  

Me, I would just put:

drop table t;

into the script. It either drops it, or not. You don't really need to "look" first.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Sun Jan 30 2005 - 16:10:43 CST