how to create table in procedure?..

[amitsukte]

hello every on please tell how should i create table through procedure for following code.

select CTYP "CMDNAME"
    , rpad(OBJ,10) "TNAME"
    , 0 - EXEM   "COUNTS"
    , DAT "USED_DATE"
from (select distinct EXEM, CTYP, OBJ, DAT
from ( select decode (S.COMMAND_TYPE
                                 ,  2, 'Insert into '
                                 ,  3, 'Select from '
                                 ,  6, 'Update  of  '
                                 ,  7, 'Delete from '
                                 , 26, 'Lock    of  ') CTYP
                        , T.OWNER || '.' || T.TABLE_NAME    OBJ
                        , sum(0 - S.EXECUTIONS)       EXEM
                        , trunc(S.LAST_ACTIVE_TIME,'DD') DAT
                   from V$SQL               S
                      , V$OBJECT_DEPENDENCY D
                     -- , V$DB_OBJECT_CACHE   O
                 , dba_tables          T
                  where T.TABLESPACE_NAME='USERS'
              
                        AND S.COMMAND_TYPE in (2,3,6,7,26)
                    and D.FROM_ADDRESS = S.ADDRESS
                    and D.TO_OWNER     = T.OWNER
                    and D.TO_NAME      = T.TABLE_NAME
                    
               group by S.COMMAND_TYPE
                      , T.OWNER
                      , T.TABLE_NAME
                       , trunc(S.LAST_ACTIVE_TIME,'DD') )  )
where substr(OBJ,1,instr(OBJ,'.',1,1)-1) not in ('SYS','OEM')

[rski]

If you use oracle 11g

declare
v_sql varchar2(2000);
begin
 v_sql:=q'#CREATE TABLE table_name AS <your_select_statement>#';
 execute immediate v_sql;
end;

If you use earlier version use that

declare
v_sql varchar2(2000);
begin
 v_sql:='CREATE TABLE table_name AS <your_select_statement>';
 execute immediate v_sql;
end;

But remember to double each ' in your select statement

[amitsukte]

Thank you very much rski... i got the solution ...