Oracle8.05不能用excute immediate,Oracle9才可以
你可以改为存储过程CREATE OR REPLACE PROCEDURE Test1 IS
v_Sql VARCHAR2(200);
Cursor_create VARCHAR2(100);
Result NUMBER;
BEGIN
v_Sql := '
CREATE TABLE T1
(
A VARCHAR2(200)
)
';
cursor_create := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_create, v_SQL,dbms_sql.native);
Result := dbms_sql.EXECUTE(cursor_create);
DBMS_SQL.close_cursor(cursor_create);END Test1;
你可以改为存储过程CREATE OR REPLACE PROCEDURE Test1 IS
v_Sql VARCHAR2(200);
Cursor_create VARCHAR2(100);
Result NUMBER;
BEGIN
v_Sql := '
CREATE TABLE T1
(
A VARCHAR2(200)
)
';
cursor_create := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_create, v_SQL,dbms_sql.native);
Result := dbms_sql.EXECUTE(cursor_create);
DBMS_SQL.close_cursor(cursor_create);END Test1;
用DBMS_SQL包是低版本oracle的选择,而且也是保留兼容性的一个选择。
declare
nSQL NUMBER;
begin
nSQL := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE( nSQL, ‘update .....’, DBMS_SQL.v7 ) ;
DBMS_SQL.CLOSE_CURSOR( nSQL);
end;
这样就是多几部,效果是一样的。
insufficient privileges.请再看看吧???
create or replace function test return varchar2 as
v_cursor number;
v_string varchar2(200);
v_row number;
begin
v_cursor:=dbms_sql.open_cursor;
v_string:='create table testdb (text varchar2(200))';
dbms_sql.parse(v_cursor,v_string,dbms_sql.native);
v_row:=dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
return ('成功执行'||v_row||'行!');
exception
when others then
dbms_sql.close_cursor(v_cursor);
return ('执行失败!'||sqlcode||sqlerrm);
raise;
end;