方法:
CREATE OR REPLACE FUNCTION F()
IS
BEGIN
strSQL_CREATE := 'CREATE TABLE TEMP_TABLE (TABLE_NM VARCHAR2(10))';
EXECUTE IMMEDIATE strSQL_CREATE;
END
调用
--set serveroutput on;
declare
begin
F();
end;出错--set serveroutput on;
declare
CREATE OR REPLACE FUNCTION F()
IS
BEGIN
strSQL_CREATE := 'CREATE TABLE TEMP_TABLE (TABLE_NM VARCHAR2(10))';
EXECUTE IMMEDIATE strSQL_CREATE;
ENDbegin
F();
end;不出错 求教为什么~?
is
...
方法:
CREATE OR REPLACE FUNCTION F()
RETURN number
IS
BEGIN
strSQL_CREATE := 'CREATE TABLE TEMP_TABLE (TABLE_NM VARCHAR2(10))';
EXECUTE IMMEDIATE strSQL_CREATE;
RETURN 1;
END
调用
--set serveroutput on;
declare
intRTN number;
begin
intRTN := F();
end;出错--set serveroutput on;
declare
intRTN number;
CREATE OR REPLACE FUNCTION F()
RETURN number
IS
BEGIN
strSQL_CREATE := 'CREATE TABLE TEMP_TABLE (TABLE_NM VARCHAR2(10))';
EXECUTE IMMEDIATE strSQL_CREATE;
return 1;
ENDbegin
intRTN := F();
end;
IS
strSQL_CREATE VARCHAR2(1000);
BEGIN
strSQL_CREATE := 'CREATE TABLE TEMP_TABLE (TABLE_NM VARCHAR2(10))';
EXECUTE IMMEDIATE strSQL_CREATE;
END;
/调用
begin
F;
end;
/
IS
strSQL_CREATE varchar2(100);
BEGIN
strSQL_CREATE := 'CREATE TABLE TEMP_TABLE (TABLE_NM VARCHAR2(10))';
EXECUTE IMMEDIATE strSQL_CREATE;
END;用过程,变量也没有声明;
IS
strSQL_CREATE varchar2(1000);
BEGIN
strSQL_CREATE := 'CREATE TABLE TEMP_TABLE (TABLE_NM VARCHAR2(10))';
EXECUTE IMMEDIATE strSQL_CREATE;
return 1;
END;不过呢,这种事情在function里做让人感觉很不普通
全写出来太多了
F()方法在源程序里没有任何问题 参数在上面的代码里没有写不要说语法之类的错误 xx没定义什么的我只想知道为什么两段代码前一段创建表会不成功
而后一段会成功
SQL>grant create table to username;
或者
SQL>grant create any table to username;查看oracle的文档,提到,在procedure和function里,role privilege是失效的。你试试,如果是报的这个错的话。有错最好贴出来,这样大家不需要猜来猜去浪费时间了。
CREATE OR REPLACE FUNCTION FF(in_csvfile IN VARCHAR)
RETURN number
IS
strSQL_CREATE VARCHAR2(1000);
BEGIN
--TEMP_TABLEを作成する
strSQL_CREATE := 'CREATE TABLE TEMP_TABLE (TABLE_NM VARCHAR2(10) , SIKENSU_NO VARCHAR2(10) , SAIBAN VARCHAR2(10))';
EXECUTE IMMEDIATE strSQL_CREATE;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END;
--set serveroutput on;
declare
intRTN number;
begin
dbms_output.ENABLE (25000);
intRTN := FF('ASDAA');
dbms_output.put_line('intRTN:' || intRTN);
dbms_output.put_line('----end--------');
end;
返回结果:
intRTN:-1
----end--------
--set serveroutput on;
declare
intRTN number;
FUNCTION FF(in_csvfile IN VARCHAR)
RETURN number
IS
strSQL_CREATE VARCHAR2(1000);
BEGIN
--TEMP_TABLEを作成する
strSQL_CREATE := 'CREATE TABLE TEMP_TABLE (TABLE_NM VARCHAR2(10) , SIKENSU_NO VARCHAR2(10) , SAIBAN VARCHAR2(10))';
EXECUTE IMMEDIATE strSQL_CREATE;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END;
begin
dbms_output.ENABLE (25000);
intRTN := FF(‘asddf');
dbms_output.put_line('intRTN:' || intRTN);
dbms_output.put_line('----end--------');
end;
返回结果:
intRTN:-1
----end--------
--set serveroutput on;
declare
intRTN number;
FUNCTION FF(in_csvfile IN VARCHAR)
RETURN number
IS
strSQL_CREATE VARCHAR2(1000);
BEGIN
--TEMP_TABLEを作成する
strSQL_CREATE := 'CREATE TABLE TEMP_TABLE (TABLE_NM VARCHAR2(10) , SIKENSU_NO VARCHAR2(10) , SAIBAN VARCHAR2(10))';
EXECUTE IMMEDIATE strSQL_CREATE;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END;
begin
dbms_output.ENABLE (25000);
intRTN := FF(‘asddf');
dbms_output.put_line('intRTN:' || intRTN);
dbms_output.put_line('----end--------');
end;
返回结果:
intRTN:0
----end--------
建议你这样写:
strSQL_CREATE := 'DROP TABLE TEMP_TABLE';
EXECUTE IMMEDIATE strSQL_CREATE;
strSQL_CREATE := 'CREATE TABLE TEMP_TABLE (TABLE_NM VARCHAR(10)....)';
EXECUTE IMMEDIATE strSQL_CREATE;