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

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已经有创建表结构的权限了。

4个回答

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

手动设定执行function用户的 创建表权限了吗?也就是下面的
[code="sql"]
GRANT CREATE TABLE TO user1
[/code]

报什么异常?

如果权限没有问题的话,
你的代码
vsql :=
'create table ''saas_98304''.tc_sex1(code varchar2(2),text varchar2(8),memo varchar2(80))';

改成
vsql :=
'create table saas_98304.tc_sex1(code varchar2(2),text varchar2(8),memo varchar2(80))';

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!