Re: a newbie - pl/sql problem

On Sun, 30 Jan 2005 16:20:31 +0100, Marcin Balcerzak wrote:

> 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.

DROP TABLE abc <== this is DDL. Several comments:

1. DDL can not be executed directly in PL/SQL. It must be invoked through the DBMS_DDL package or through EXECUTE IMMEDIATE;
2. DROP (and other DDL) imply a commit (before and after the statement) which may produce unexpected side effects in your code;
3. Commit in a loop is considered nasty as it has serious resource, performance and scalability implications. Code like you describe generally has a severe 'number of concurrent users' penalty.

On your other note - are you referencing PUT_LINE directly or in context of it's package? Strongly suggest you copy/paste the exact code and message to avoid us guessing and wastng everyone's time.

Also, there are be subtle variations in PL/SQL and it's supplied packages across different versions of Oracle, so identifying the database and OS version (and patch level) is very useful in getting a relevant answer.

lol/FGB Received on Sun Jan 30 2005 - 10:18:12 CST