Re: Dynamic SQL

Eric,

Issuing direct DDL is easy with DBMS_SQL. On the project I am on, we use it extensively, even querying data back to from the database. Querying is a little harder, but with Steven Feuerstein PL/SQL programming book, his examples are good enough to figure out how to do queries.

Anyhow, to issue the sample code that is in your text, you can issue the following to user DBMS_SQL. This was written against Oracle8, but should work against Oracle7.

--<some trigger name>

declare
  cur_handle integer;
  sql_statement varchar2(2000);
  cursor all_objects_cur is
    select * from sys.user_objects;
  rec_cur all_objects_cur%ROWTYPE;
  status integer;
Begin
  cur_handle := dbms_sql.open_cursor;
  open all_objects_cur;
  While TRUE loop
    fetch all_objects_cur into rec_cur;
    exit when all_objects_cur%NOTFOUND;
    sql_statement := 'drop '||rec_cur.object_type||' '||rec_cur.object_name;     dbms_sql.parse(cur_handle, sql_statement, dbms_sql.v7);     status := dbms_sql.execute(cur_handle);   End loop;
  close all_objects_cur;
End;

• <end of trigger>

If you have any questions, please feel free to email me at bprice2000_at_sprintmail.com

Good luck,

Brian

Eric Veilleux - Ers wrote in message <6890re$5gc$1_at_cti15.citenet.net>...
> I've made a form that let me navigate in the all_objects view in order
>to see the tables, triggers, sequences and indexes, it works pretty good
>execept that I wanted to be able to create new objects and drop new
 objects.
>
> I'm having a hard time doing so. I figured I could put the sql
 statment
>into a varchar2 and then execute it (like in pro*c) but that dosen't work,
>how could I execute something like this?
>
> sql_stmt varchar2(200) := 'drop ' || :all_objects.object_type || ' ' ||
>:all_objects.object_name
>
> Eric Veilleux
> eveilleu_at_ers.ca
>
>
>
>
>
>
Received on Fri Jan 02 1998 - 00:00:00 CET