Re: DDL-statement in PL/SQL block

From: Paul Druker <pdruker_at_metaway.com>
Date: 2000/07/30
Message-ID: <uvXg5.52350$c33.1629835@typhoon-news1.southeast.rr.com>#1/1

Dimitry,

You cannot use a DDL statement in PL/SQL block. The solution is to use dynamic SQL.

You may use procedure like this:

  PROCEDURE drop_table (p_table_name IN VARCHAR2) IS

    v_sql_string  varchar2(100);
    v_sql_cursor  number;
    v_sql_result  number;

  BEGIN
    v_sql_cursor := dbms_sql.open_cursor;     v_sql_string := 'drop table '||p_table_name;     dbms_sql.parse(v_sql_cursor, v_sql_string, dbms_sql.v7);     v_sql_result := dbms_sql.execute (v_sql_cursor);     dbms_sql.close_cursor(v_sql_cursor);   EXCEPTION
      dbms_sql.close_cursor(v_sql_cursor);   END drop_table;

and call it drop_table (<your_table>);

Regards,
Paul

"Dimitry" <dmitry_av_at_urbis.net.il> wrote in message news:39844307_at_news.bezeqint.net...
> Hello !
> Does anyone know if it is possible to
> put a DDL statement into PL/SQL-block, for example:
>
> ****************
> BEGIN
> IF <condition> THEN
> DROP TABLE <table>
> END IF;
> END;
> ****************
>
> Thank you in advance.
>
>
Received on Sun Jul 30 2000 - 00:00:00 CDT