传入table_name ,和file_name 用动态sql创建table
报措 others errorcreate or replace
PROCEDURE GET_TABLE(v_name VARCHAR2,v_filename VARCHAR2)
IS
v_fengefu varchar2(12):=',';
v_create varchar2(1000):='create table'|| v_name||'(
emp_no varchar(128),
emp_name varchar(128),
emp_gendar varchar(128)
)
organization external
(
type oracle_loader
default directory EXP
access parameters
(
records delimited by newline
fields terminated by '',''
missing field values are null
)
location(' ||v_filename|| ')
)
reject limit unlimited';
BEGIN
EXECUTE IMMEDIATE v_create;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no data found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('others error');
END GET_TABLE;等待高手介绍 execute immediate 与 dbms包命令的不同
报措 others errorcreate or replace
PROCEDURE GET_TABLE(v_name VARCHAR2,v_filename VARCHAR2)
IS
v_fengefu varchar2(12):=',';
v_create varchar2(1000):='create table'|| v_name||'(
emp_no varchar(128),
emp_name varchar(128),
emp_gendar varchar(128)
)
organization external
(
type oracle_loader
default directory EXP
access parameters
(
records delimited by newline
fields terminated by '',''
missing field values are null
)
location(' ||v_filename|| ')
)
reject limit unlimited';
BEGIN
EXECUTE IMMEDIATE v_create;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no data found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('others error');
END GET_TABLE;等待高手介绍 execute immediate 与 dbms包命令的不同
一是create table 后面少了个空格
二是location(' ||v_filename|| ') 这里应该location(''' ||v_filename|| ''')
PROCEDURE GET_TABLE(v_name VARCHAR2,v_filename VARCHAR2)
IS
v_create varchar2(1000):='create table '|| v_name||'(
emp_no varchar(128),
emp_name varchar(128),
emp_gendar varchar(128)
)
organization external
(
type oracle_loader
default directory EXP
access parameters
(
records delimited by newline
fields terminated by '',''
missing field values are null
)
location('''||v_filename||''')
)
reject limit unlimited ';
BEGIN
dbms_output.put_line(v_create);
EXECUTE IMMEDIATE v_create;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no data found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('others error'); END GET_TABLE; 这是改了之后的代码
在 EXECUTE IMMEDIATE v_create; 抛了EXCEPTION
我用 在 sqlplus 下执行打印出来的 v_create 可以创建成功
难道是不支持 EXECUTE IMMEDIATE
我的是oracle 10g
修改一下,把错误号打印出来便于定位问题:WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('others error:'||sqlcode);