我的存储过程是这样的,编译执行都通过了,可用desc WS_CALL_EVENT_200804,系统提示表不存在,怎么回事,高手指教。
create or replace procedure createcall IS
table_name varchar2(27);
V_ERRM VARCHAR2(1000);
V_ERRCODE VARCHAR2(1000);
begin
table_name:='WS_CALL_EVENT'||'_'||to_char(sysdate,'yyyymm');
EXECUTE IMMEDIATE 'create table table_name
(
STAT_ID NUMBER(16) not null,
ACCT_ID1 NUMBER(12))';
exception
when others then
V_ERRM:= SQLERRM;
V_ERRCODE:=SQLCODE;
end createcall;
/
create or replace procedure createcall IS
table_name varchar2(27);
V_ERRM VARCHAR2(1000);
V_ERRCODE VARCHAR2(1000);
begin
table_name:='WS_CALL_EVENT'||'_'||to_char(sysdate,'yyyymm');
EXECUTE IMMEDIATE 'create table table_name
(
STAT_ID NUMBER(16) not null,
ACCT_ID1 NUMBER(12))';
exception
when others then
V_ERRM:= SQLERRM;
V_ERRCODE:=SQLCODE;
end createcall;
/
可以看到吗?
已写入 file afiedt.buf 1 create or replace procedure createcall
2 authid current_user
3 IS
4 table_name varchar2(27);
5 V_ERRM VARCHAR2(1000);
6 V_ERRCODE VARCHAR2(1000);
7 begin
8 table_name:='WS_CALL_EVENT'||'_'||to_char(sysdate,'yyyymm');
9 EXECUTE IMMEDIATE 'create table '||table_name||'(STAT_ID NUMBER(16) not null,ACCT_ID1 NUMBER(
12))';
10 exception
11 when others then
12 V_ERRM:= SQLERRM;
13 V_ERRCODE:=SQLCODE;
14 dbms_output.put_line(V_ERRM);
15* end createcall;
SQL> /过程已创建。SQL> select * from tab;TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$A21fNS5/Qc2MCVhN9azKqw==$0 TABLE
BIN$wblEE32NTy+zgKd2TJSf6g==$0 TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
ORDERLINE TABLE
PART TABLE
SALGRADE TABLE
T TABLE
TC TABLE已选择10行。SQL> exec createcall;PL/SQL 过程已成功完成。SQL> select * from tab;TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$A21fNS5/Qc2MCVhN9azKqw==$0 TABLE
BIN$wblEE32NTy+zgKd2TJSf6g==$0 TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
ORDERLINE TABLE
PART TABLE
SALGRADE TABLE
T TABLE
TC TABLE
WS_CALL_EVENT_200804 TABLE已选择11行。SQL>
2.create table需要权限,加authid current_user 显示授权才可以;
3.EXECUTE IMMEDIATE 'create table table_name
(
STAT_ID NUMBER(16) not null,
ACCT_ID1 NUMBER(12))';
这一句也不对!
-------------------
create or replace procedure createcall(table_name_head in varchar2)
authid current_user
IS
table_name varchar2(30);
v_errm varchar2(1000);
v_errcode varchar2(1000);
v_dyn_sql varchar2(300);
begin
table_name:=table_name_head||'_'||to_char(sysdate,'yyyymm');
v_dyn_sql:= 'create table'|| table_name||
'(
STAT_ID NUMBER(16) not null,
ACCT_ID1 NUMBER(12))';
EXECUTE IMMEDIATE v_dyn_sql;
commit;
exception
when others then
v_errm:= sqlerrm;
v_errcode:= sqlcode;
dbms_output.put_line(v_errm);
end createcall;
/
-------------------
SQL> execute createcall('sw_call');PL/SQL procedure successfully completed
SQL> select * from tab;TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
S_m TABLE
S_n TABLE
S_x TABLE
就是没有这张表啊。怎么回事呢。
v_dyn_sql:= 'create table' ¦ ¦ table_name ¦ ¦
这里在 'create table' 里面的table后面加一个空格就可以了
这样:
v_dyn_sql:= 'create table ' ¦ ¦ table_name ¦ ¦