Re: View creation!!Please help!

It is not possible to use DDL-commands in a stored prcedure directly. You have yo use dynamic-SQL which is provided by the package DBMS_SQL

create procedure abc as begin
declare
 i integer;
 ID integer;
 view_template varchar2(2000) = 'create view pd_view__$NUMBER$_ as <select-statement>';
 view_text varchar2(2000);
 Dummy integer;
begin
 ID := dbms_sql.open_cursor ();
 for i in 1..1000 loop
  view_text := replace (view_template, '_$NUMBER$_', to_char(i));   dbms_sql.PARSE (ID, view_text, dbms_sql.native);   Dummy := dbms_sql.EXECUTE (ID);
 end loop;
 dbms_sql.close_cursor (ID);
exception
 when others then
  if dbms_sql.is_open (ID) then
   dbms_sql.close_cursor (ID);
  end if;
  raise;
end;

I didn't test the procedure, but is should give you a hint where to start...

Regards, Stephan
--

Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-0           | Landsberger Allee 392
fax: +49 30 549932-21          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany


---------------------------------------------------------------


       PGP-Key verfügbar       |      PGP-Key available


---------------------------------------------------------------