上来直接看案例如下:
1. 在system用户下
declare
             v_cursor integer;
             v_sql varchar2(500);
             v_tablename varchar2(10) :='dm_test';
             v_tablecolumn varchar2(100) :='kh varchar(10),nyr varchar2(10)';
begin
     v_cursor:=dbms_sql.open_cursor;
     v_sql:='CREATE TABLE ' || v_tablename || ' ( ' || v_tablecolumn || ' )';
     dbms_sql.parse(v_cursor, v_sql, dbms_sql.v7);
     dbms_sql.close_cursor(v_cursor);
    
end;
可以成功执行上面pl/sql块。

2.将该pl/sql块放入函数中
create or replace function system.fun_test_dbmssql return varchar2 as
                         v_cursor integer;
                         v_sql varchar2(500);
                         v_tablename varchar2(10) :='dm_test';
                         v_tablecolumn varchar2(100) :='kh varchar(10),nyr varchar2(10)';
begin
         v_cursor:=dbms_sql.open_cursor;
         v_sql:='CREATE TABLE ' || v_tablename || ' ( ' || v_tablecolumn || ' )';
         dbms_sql.parse(v_cursor, v_sql, dbms_sql.v7);
         dbms_sql.close_cursor(v_cursor);
         return 'ok';
end fun_test_dbmssql;

---test fun_test_dbmssql-----------
declare
    v_s varchar2(2);
    begin
            v_s:=fun_test_dbmssql();
            dbms_output.put_line(v_s);
    end;
函数编译成功,执行则报错:
ORA-01031:权限不足
ORA-06512:在"SYS.DBMS_SYS_SQL",line**
ORA-06512:在"SYS.DBMS_SQL",line**
ORA-06512:在"SYSTEM.FUN_CREATE_JOIN_VIEW",line**

3. 再测试一个新建的函数
create or replace function fun_create_join_view(
             v_viewName in dm_map.viewName%type,
             v_name1 in dm_map.dimension1%type,
             v_name2 in dm_map.dimension2%type
             ) return varchar2 as
            v_cur integer;
            v_sql varchar(200);
begin
         v_cur:=dbms_sql.open_cursor;
         v_sql:='create or replace view ||v_viewName||' as select kh from '||v_name1||' natural join '|| v_name2;
         dbms_output.put_line(v_sql);
         dbms_sql.parse(v_cur,v_sql,dbms_sql.v7);
         dbms_sql.close_cursor(v_cur);
         return ‘OK’;
         --exception
         --when others then
         --dbms_output.put_line('error!');
         --dbms_sql.close_cursor(v_cur);
         --return false;
end fun_create_join_view;
函数成功编译,测试
----test create_join_view---------------------------------
declare
                                 v_r varchar(20);
         begin
                 v_r:=fun_create_join_view(‘V_2_1’,'V_1_1','V_1_2');
                 if v_r='OK' then dbms_output.put_line('success!');
                 else  dbms_output.put_line('fail!');
                 end if;
         end;
报错:
ORA-01031:权限不足
ORA-06512:在"SYS.DBMS_SYS_SQL",line 826
ORA-06512:在"SYS.DBMS_SQL",line 32
ORA-06512:在"SYSTEM.FUN_CREATE_JOIN_VIEW",line 12

用的是SYSTEM用户,难道也会没有权限?!唉,可惜都没系统的学习过Oralce,权限分配啥的都不懂。要换不SYS用户试试?突然想到SYSTEM用户是使用as normal登录的,于是用system as sysdba登录。

对2,改用system as sysdba登录执行
---test fun_test_dbmssql-----------
declare
    v_s varchar2(2);
    begin
            v_s:=system.fun_test_dbmssql();
            dbms_output.put_line(v_s);
    end;

SQL> desc system.dm_test;
Name Type                                 Nullable Default Comments
---- ------------ -------- ------- --------
KH         VARCHAR2(10) Y                                                                                                
NYR        VARCHAR2(10) Y                                                                                                

SQL>

对3,因为在system as normal下建好的视图都变成名为SYSTEM.V_1_1,SYSTEM.V_1_2,dba下重建这两个视图后重新执行,成功!

由此想到的两个问题:
1.到底是需要什么权限才能执行函数(过程)中的dbms_sql呢?
2.在利用JDBC连接Oracle时使用的用户名为system登录时有没有sysdba的权限呢?