关于在oracle的function中动态创建表结构的问题~~~

Question

create or replace function jfids.sys_createobjectcopy (
puser in varchar2, --用户或schama
ptype in varchar2, --对象类型=table,view,function,...etc
pobjectname in varchar2, --对象名称
pdestuser in varchar2 --目标用户或schama
)
return number
is
/******************************************************************************
NAME: sys_createobjectcopy
PURPOSE: 根据已知的对象创建对象副本(从一个表空间到另一个表空间)
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2012/3/15 showgo 1. Created this function.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: sys_createobjectcopy
Sysdate: 2012/3/15
Date and Time: 2012/3/15, 16:28:23, and 2012/3/15 16:28:23
Username: showgo (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
--vars
retcode integer;
vcursor integer;
vcnt integer;
--clob to varchar2 array
vscript clob;
vscriptblob blob;
vtmpblob blob;
loblen integer;
buffer integer := 4000;
vsql varchar2 (32767 byte);
vtmp varchar2 (4000 byte);
--call convert blob param
dest_offset integer;
src_offset integer;
lang_ctx integer := dbms_lob.default_lang_ctx;
warning integer;
--exception
script_overlength_exception exception;
begin
retcode := 0;

/*

• 参数验证
  */

  if (puser is null or puser = '')
  then
  return 0;
  end if;

  if (pdestuser is null or pdestuser = '')
  then
  return 0;
  end if;

  if (ptype is null or ptype = '')
  then
  return 0;
  end if;

  if (pobjectname is null or pobjectname = '')
  then
  return 0;
  end if;

  /*

• 取得对象的脚本的字符串(脚本可能很长,故先截取再组装)
  /
  begin
  vscript := dbms_metadata.get_ddl (ptype, pobjectname, puser);
  /
  vscript := replace (vscript, upper (puser), upper (pdestuser));
  dbms_lob.converttoblob (vscriptblob,
  vscript,
  dbms_lob.lobmaxsize,
  dest_offset,
  src_offset,
  dbms_lob.default_csid,
  lang_ctx,
  warning);
  */
  vscriptblob := sys_clob2blob (vscript);
  loblen := dbms_lob.getlength (vscriptblob);

  if (loblen > 0)
  then
  vcnt := ceil (loblen / buffer);
  vsql := '';

   for i in 1 .. vcnt
   loop
      loblen := dbms_lob.getlength (vscriptblob);
  
      if (loblen <= buffer)
      then
         buffer := loblen;
      end if;
  
      vtmpblob := dbms_lob.substr (vscriptblob, buffer, 1);
      vtmp :=
         utl_raw.cast_to_varchar2(utl_raw.convert (
                                     vtmpblob,
                                     'simplified chinese_china.zhs16gbk',
                                     'simplified chinese_china.zhs16gbk'));
      vscriptblob :=
         dbms_lob.substr (vscriptblob, loblen - buffer, buffer + 1);
      vsql := vsql || vtmp;
   end loop;
  

  else
  return 0;
  end if;

  /*

  • 按照脚本创建对象
  • 创建之前,需要将原对象中用户(或schama)和表空间替换成新的用户(或schama)和表空间

  • 注意: 默认用户及表空间使用的是同一名称.
    */
    --vsql := trim (replace (vsql, puser, pdestuser));
    --vcursor := dbms_sql.open_cursor;
    --dbms_sql.parse (dbms_sql.open_cursor, vsql, dbms_sql.native);
    --dbms_sql.close_cursor (vcursor);

    --vsql := TRIM (REPLACE (vsql, puser, pdestuser));
    vsql :=
    'create table ''saas_98304''.tc_sex1(code varchar2(2),text varchar2(8),memo varchar2(80))';

    execute immediate vsql;
    exception
    when no_data_found
    then
    retcode := 0;
    rollback;
    when others
    then
    retcode := 0;
    rollback;
    end;

  return retcode;
  exception
  when no_data_found
  then
  retcode := 0;
  rollback;
  when others
  then
  -- Consider logging the error and then re-raise
  retcode := 0;
  rollback;
  raise;
  end sys_createobjectcopy;
  /
  类似以上的代码在function中。
  背景:此function为用户JFIDS的对象，执行时要求能将用户JFIDS下的某些对象(比如表tc_sex)创建到另一用户saas_98304下。红色字体处为另一函数，未给出(其作用是将一个clob值转换成blob值)，绿色字体处是一段测试代码，意思就是运行完后希望能得到在saas_98304用户下一个表tc_sex。貌似在pl/sql环境能正常执行，但是一旦写到function中就要报异常了，另外用户jfids已经有创建表结构的权限了。

Answer 1

grant GRANT ANY OBJECT PRIVILEGE to user
那个权限不够，用这个