Re: How can I execute an SQL statement from a PL/SQL string ?

> dbms_sql, isn't it?
>

here an example:

declare

    CursorID number;
    Stmt varchar2(100) := 'select username from all_users';  Amount number;
 Name varchar2(100);
begin
 dbms_output.enable(1000000);

    CursorID := dbms_sql.open_cursor;
 dbms_sql.PARSE(CursorID, Stmt, dbms_sql.v7);  Amount := dbms_sql.EXECUTE (CursorID);  DBMS_SQL.DEFINE_COLUMN(CursorID, 1, Name, 30);  loop
  if DBMS_SQL.FETCH_ROWS(CursorID)>0 THEN    DBMS_SQL.COLUMN_VALUE(CursorID, 1, Name); -- The number '1' means the number of the column in the result-set

   dbms_output.put_line (Name);
  else
   exit;
  end if;
 end loop;
 dbms_sql.close_cursor(CursorID);
exception

    when others then
  if dbms_sql.IS_OPEN(CursorID) then
   dbms_sql.close_cursor(CursorID);
  end if;
  raise;
end;

this shold work for you

Regards, Stephan

--

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