How can i execute an CREATE Oracle SQL statement in a SELECT?

Here is the SQL:

    select 'create table XX_' || replace(replace(trim(table_id),'-','_'),'%','X') || '_LOOKUP as select * from p开发者_开发知识库rn_tcodes where trim(table_id) = ''' || trim(table_id) || ''';'
from  prn_tcodes
group by table_id;

I want the resulted SQL statements to be executed, is there a way?

I'm using Oracle 10g

Thank you,,,

---

As an alternative to spooling you can run that as an anonymous PL/SQL block (not tested!)

DECLARE 
  cursor all_codes 
      select 'create table XX_' || 
             replace(replace(trim(table_id),'-','_'),'%','X') || 
             '_LOOKUP as select * from prn_tcodes where trim(table_id) = ''' ||
             trim(table_id) as sql_stmt
      from  prn_tcodes
      group by table_id;

   rec all_codes%ROWTYPE;
BEGIN
   FOR rec IN get_objects LOOP
   BEGIN
      EXECUTE IMMEDIATE rec.sql_stmt;
   END LOOP;
END;
/

(Note that the generated statement does not include the trailing ; any more)

---

Spool your above script to a file, then execute that file.

spool myoutput.sql

-- Your SQL here

spool off;
@myoutput;

---

You want to execute dynamic SQL, so check out execute immediate (see more info here and here).