动态建表如何实现分区,我写的这个已经编译成功,可执行时说ORA-06502: PL/SQL: 数字或值错误 ,请高手指点一二;
----------------
create or replace procedure createcall(table_name_head in varchar2,
table_name out varchar2,
retcd out integer,
retmsg out varchar2)
authid current_user
IS
v_dyn_sql varchar2(2000);
begin
retcd:=0;
table_name:=table_name_head||'_'||to_char(sysdate,'yyyymm');
v_dyn_sql:= 'create table '|| table_name||
'(
STAT_ID NUMBER(16) not null,
CREATED_DATE NUMBER(16)
today date
)';
v_dyn_sql:=v_dyn_sql||
'partition by range (today)
( partition PP1 values less than ('||TO_DATE(' 2008-04-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')||')
tablespace NEWBIL,
partition PP2 values less than ('||TO_DATE(' 2008-04-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')||')
tablespace NEWBIL,
partition POTHER values less than (MAXVALUE)
tablespace NEWBIL)';
EXECUTE IMMEDIATE v_dyn_sql;
commit;
end createcall;
--------------
到底那里有问题啊。
----------------
create or replace procedure createcall(table_name_head in varchar2,
table_name out varchar2,
retcd out integer,
retmsg out varchar2)
authid current_user
IS
v_dyn_sql varchar2(2000);
begin
retcd:=0;
table_name:=table_name_head||'_'||to_char(sysdate,'yyyymm');
v_dyn_sql:= 'create table '|| table_name||
'(
STAT_ID NUMBER(16) not null,
CREATED_DATE NUMBER(16)
today date
)';
v_dyn_sql:=v_dyn_sql||
'partition by range (today)
( partition PP1 values less than ('||TO_DATE(' 2008-04-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')||')
tablespace NEWBIL,
partition PP2 values less than ('||TO_DATE(' 2008-04-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')||')
tablespace NEWBIL,
partition POTHER values less than (MAXVALUE)
tablespace NEWBIL)';
EXECUTE IMMEDIATE v_dyn_sql;
commit;
end createcall;
--------------
到底那里有问题啊。
然后ctrl+O 单步调试
在做上述步骤之前在你的动态sql语句excute之前加上这句
dbms_output.put_line (length(v_dyn_sql));
for i in 1..floor(length(v_dyn_sql)/255)+1 loop
dbms_output.put_line (substr(v_dyn_sql,(i-1)*255,255));
end loop;
在test script 旁边的dbms output窗口里面就可以得到执行的sql语句,
把这个sql语句单独拿出来进行排错