create table test1 (
column1 varchar2(100)
, id varchar2(40) not null
, column2 number(10) )
tablespace jzsoft pctfree 10 pctused 40 initrans 1
maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited )
;
comment on table test1 is '测试1' ;
comment on column test1.column2 is '字段2';
这个是我在procedure中生成的sql语句。
存在一个变量(ls_create)中。
execute immediate ls_create;时 会报ORA-00911: 无效字符;
但是我copy出来直接执行又不报错。请问这个是怎么回事。
column1 varchar2(100)
, id varchar2(40) not null
, column2 number(10) )
tablespace jzsoft pctfree 10 pctused 40 initrans 1
maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited )
;
comment on table test1 is '测试1' ;
comment on column test1.column2 is '字段2';
这个是我在procedure中生成的sql语句。
存在一个变量(ls_create)中。
execute immediate ls_create;时 会报ORA-00911: 无效字符;
但是我copy出来直接执行又不报错。请问这个是怎么回事。
is
ls_create varchar2(4000);
ls_tablespace varchar2( 4000 );
--ls_alter varchar2(4000);
ls_type varchar2( 1000 );
ls_default varchar2( 4000 );
ls_isnull varchar2( 30 );
ls_ispk varchar2( 500 );
ls_comment_column varchar2(4000);
ls_comment_tab varchar2(500);
ls_sqlcode varchar2(500);
ls_errm varchar2(500);
begin
ls_create := null ;
ls_tablespace := null;
--ls_alter := null;
for a in ( select x.id,x.tab_name,x.tab_cname,x.tab_space,y.column_name,y.column_cname,y.data_type,y.data_length,y.isnull,y.column_default,y.ispk
from tab_object x,tab_column y where x.id=y.tab_name and x.tab_name = l_tabname ) loop
ls_type := case when a.data_type = 'date' then a.data_type
when a.data_type <> 'date' and a.data_length is null then a.data_type
when a.data_type <> 'date' and a.data_length is not null then a.data_type||'('||a.data_length||')'
end ;
ls_isnull := case when a.isnull = 1 then ' not null ' else null end;
ls_ispk := case when a.ispk = 1 And ls_ispk is null then ' constraint PK_'||l_tabname||' primary key ('||a.column_name
when a.ispk = 1 and ls_ispk is not null then ls_ispk||', '||a.column_name
end ;
ls_tablespace := case when ls_tablespace is null then 'tablespace '|| a.tab_space || ' pctfree 10 pctused 40 initrans 1
maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited )' end ; ls_default := case when a.column_default is not null then ' default '||a.column_default end ;
if ls_create is null then
ls_create := ' create table '||l_tabname||' ( '
||chr(10)||a.column_name||chr( 32 ) ||ls_type|| chr(32)||ls_default||chr( 32 )||ls_isnull ;
else
ls_create := ls_create||chr(10)||' , '||a.column_name||chr( 32 ) ||ls_type|| chr(32)||ls_default||chr( 32 )||ls_isnull ;
end if;
ls_comment_tab := case when a.tab_cname is not null and ls_comment_tab is null then ' comment on table '||l_tabname ||' is ' ||chr(39)||a.tab_cname||chr(39)||' ;' end ;
ls_comment_column := case when a.column_cname is not null then ' comment on column '||l_tabname||'.'||a.column_name||' is '||chr(39)||a.column_cname ||chr(39)||';' end ;
end loop;
ls_create := ls_create||chr( 32 )
||chr(10)||case when ls_ispk is not null then ' , '||ls_ispk end
||chr(10)||' ) '
||chr(10)||ls_tablespace
||chr(10)||';'
||chr(10)||ls_comment_tab
||chr(10)||ls_comment_column;execute immediate ls_create;
insert into createtable_log(table_name,c_sqlcode,beizhu) values( l_tabname,'','建表成功!' );
exception when others then
ls_sqlcode := sqlcode;
ls_errm:=sqlerrm;
insert into createtable_log(table_name,c_sqlcode,beizhu) values( l_tabname,ls_sqlcode,ls_errm );
commit;
end sp_createtable;
declare
sqlstr varchar2(4000);
begin
sqlstr:=' create table test1 (
column1 varchar2(100)
, id varchar2(40) not null
, column2 number(10)
)';
execute immediate sqlstr;
sqlstr:='comment on table test1 is ''测试1''';
execute immediate sqlstr;
sqlstr:='comment on column test1.column2 is ''字段2''';
execute immediate sqlstr;
end;
column1 varchar2(100)
, id varchar2(40) not null
, column2 number(10) )
tablespace jzsoft pctfree 10 pctused 40 initrans 1
maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited )
;我把comment 这些全部取消了,怎么还是要报错呢? 而且也是911