解决方案 »
- 如何将Date型yyyy-mm-dd hh24:mi:ss字段转换获取yyyy-mm-dd
- update更新两条数据怎么写呀
- 求一条SQL语句
- oracle 9I数据库的有关序列的问题的请教,高手请帮我解惑,谢谢!
- ORACLE 为什么会自动改变1521端口????
- Oracle9i 9201升级9208的状况以及问题!(高分求解啊!急!)
- 那位更给个Oracle 9i Release2 for windows的下载地址,这先谢谢了
- 有数据的字符型转换成可能大于4000的数据类型应该怎样去转换呢!
- 关于存储过程
- oracle 客户端安装到arm
- 关于层次查询
- Delphi做的程序,在Oracle客户端不能运行怎么回事?
在行 134 上开始执行命令时出错:
declare
TAB_NAME varchar2(20);
SQL_IN varchar2(1000);
begin
TAB_NAME :='USER_'||TO_CHAR(SYSDATE,'YYYY-MM-DD');
SQL_IN := 'CREATE TABLE '||TAB_NAME||'_2'||' (
USER_MOBILE VARCHAR2(20) NOT NULL ,
FORECAST_AREA VARCHAR2(20) NOT NULL ,
USER_AREA VARCHAR2(20),
SEND_TIME CHAR(8),
SERVICE_ID NUMBER,
MESSAGE VARCHAR2(140) NOT NULL ,
SEV_CODE VARCHAR2(20),
FEE_TYPE VARCHAR2(2),
FEECODE VARCHAR2(20),
SEND_FLAG NUMBER,
FORECAST_TYPE NUMBER
);';
EXECUTE IMMEDIATE SQL_IN ;
end;
错误报告:
ORA-00922: missing or invalid option
ORA-06512: at line 19
00922. 00000 - "missing or invalid option"
*Cause:
*Action:
TAB_NAME varchar2(20);
SQL_IN varchar2(1000);
begin
TAB_NAME :='USER_'||TO_CHAR(SYSDATE,'YYYY-MM-DD');
SQL_IN := 'CREATE TABLE "'||TAB_NAME||'_2'||'" ( --注意这里表名加了双引号
USER_MOBILE VARCHAR2(20) NOT NULL ,
FORECAST_AREA VARCHAR2(20) NOT NULL ,
USER_AREA VARCHAR2(20),
SEND_TIME CHAR(8),
SERVICE_ID NUMBER,
MESSAGE VARCHAR2(140) NOT NULL ,
SEV_CODE VARCHAR2(20),
FEE_TYPE VARCHAR2(2),
FEECODE VARCHAR2(20),
SEND_FLAG NUMBER,
FORECAST_TYPE NUMBER
)';
execute immediate sql_in ;
end;
USER_2011-02-15_2
没有这样的表名以减号-连接的
要么你就用双引号括起来
比如:如上回复
--动态执行EXECUTE IMMEDIATE语句貌似是不能在匿名的程序中执行
create or replace procedure aa as
--declare
TAB_NAME VARCHAR2(20);
SQL_IN VARCHAR2(32767);
BEGIN
TAB_NAME := 'USER_' || TO_CHAR(SYSDATE, 'YYYY-MM-DD');
SQL_IN := 'CREATE TABLE ' || TAB_NAME || '_2' || ' (
USER_MOBILE VARCHAR2(20) NOT NULL ,
FORECAST_AREA VARCHAR2(20) NOT NULL ,
USER_AREA VARCHAR2(20),
SEND_TIME CHAR(8),
SERVICE_ID NUMBER,
MESSAGE VARCHAR2(140) NOT NULL ,
SEV_CODE VARCHAR2(20),
FEE_TYPE VARCHAR2(2),
FEECODE VARCHAR2(20),
SEND_FLAG NUMBER,
FORECAST_TYPE NUMBER
)';
EXECUTE IMMEDIATE SQL_IN;
END;
2 /
create table user_2001-10-12_2(id number)
*
第 1 行出现错误:
ORA-00922: 选项缺失或无效
SQL> ed
已写入 file afiedt.buf 1* create table user_2001_10_12_2(id number)
SQL> /表已创建。
ORA-00955名称已有对象使用表已经创建成功 你在创建表名相同的表肯定ORA-00955名称已有对象使用
ORA-01031: insufficient privilegesCREATE OR REPLACE PROCEDURE PRO_USER_CREATETABLE
IS
SQL_IN VARCHAR2(5000);
TAB_NAME VARCHAR2(20);
ERR1 VARCHAR2(100);
TOM_SQLCODE VARCHAR2(10);
TOM_IP VARCHAR2(20);
BEGIN
TAB_NAME :='USER_'||TO_CHAR(SYSDATE,'YYYYMMDD');
SQL_IN := 'CREATE TABLE "'||TAB_NAME||'_1'||'"
(
USER_MOBILE VARCHAR2(20) NOT NULL , FORECAST_AREA VARCHAR2(20) NOT NULL , USER_AREA VARCHAR2(20), SEND_TIME CHAR(8), SERVICE_ID NUMBER, MESSAGE VARCHAR2(140) NOT NULL , SEV_CODE VARCHAR2(20), FEE_TYPE VARCHAR2(2), FEECODE VARCHAR2(20), SEND_FLAG NUMBER, FORECAST_TYPE NUMBER
)
';
EXECUTE IMMEDIATE SQL_IN ;
commit;
SQL_IN := 'CREATE TABLE "'||TAB_NAME||'_2'||'"
(
USER_MOBILE VARCHAR2(20) NOT NULL , FORECAST_AREA VARCHAR2(20) NOT NULL , USER_AREA VARCHAR2(20), SEND_TIME CHAR(8), SERVICE_ID NUMBER, MESSAGE VARCHAR2(140) NOT NULL , SEV_CODE VARCHAR2(20), FEE_TYPE VARCHAR2(2), FEECODE VARCHAR2(20), SEND_FLAG NUMBER, FORECAST_TYPE NUMBER
)
';
EXECUTE IMMEDIATE SQL_IN ;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
ERR1 := SUBSTR(SQLERRM, 1, 100);
TOM_SQLCODE := SQLCODE;
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO TOM_IP FROM DUAL;
INSERT INTO T_ROVER_ALERT
(ALERTNAME,
ALERTKEY,
ALERTDATE,
DATETIME,
FLAG,
MSG,
ALARTERR,
ALERTLEVEL)
VALUES
(TOM_IP,
'PRO_USER_CREATETABLE',
TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),
SYSDATE,
0,
ERR1,
TOM_SQLCODE,
5);
COMMIT;
END PRO_USER_CREATETABLE;
CREATE OR REPLACE PROCEDURE PRO_USER_CREATETABLE IS
SQL_IN VARCHAR2(5000);
TAB_NAME VARCHAR2(20);
ERR1 VARCHAR2(100);
TOM_SQLCODE VARCHAR2(10);
TOM_IP VARCHAR2(20);
BEGIN
TAB_NAME := 'USER_' || TO_CHAR(SYSDATE, 'YYYYMMDD');
SQL_IN := 'CREATE TABLE "' || TAB_NAME || '_1' || '"
(
USER_MOBILE VARCHAR2(20) NOT NULL ,
FORECAST_AREA VARCHAR2(20) NOT NULL ,
USER_AREA VARCHAR2(20), SEND_TIME CHAR(8),
SERVICE_ID NUMBER, MESSAGE VARCHAR2(140) NOT NULL ,
SEV_CODE VARCHAR2(20),
FEE_TYPE VARCHAR2(2),
FEECODE VARCHAR2(20),
SEND_FLAG NUMBER,
FORECAST_TYPE NUMBER
)
';
EXECUTE IMMEDIATE SQL_IN;
COMMIT;
SQL_IN := 'CREATE TABLE "' || TAB_NAME || '_2' || '"
(
USER_MOBILE VARCHAR2(20) NOT NULL ,
FORECAST_AREA VARCHAR2(20) NOT NULL ,
USER_AREA VARCHAR2(20),
SEND_TIME CHAR(8),
SERVICE_ID NUMBER,
MESSAGE VARCHAR2(140) NOT NULL ,
SEV_CODE VARCHAR2(20),
FEE_TYPE VARCHAR2(2),
FEECODE VARCHAR2(20),
SEND_FLAG NUMBER,
FORECAST_TYPE NUMBER
)
';
EXECUTE IMMEDIATE SQL_IN;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
ERR1 := SUBSTR(SQLERRM, 1, 100);
TOM_SQLCODE := SQLCODE;
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO TOM_IP FROM DUAL; /* INSERT INTO T_ROVER_ALERT
(ALERTNAME, ALERTKEY, ALERTDATE, DATETIME, FLAG, MSG, ALARTERR, ALERTLEVEL)
VALUES
(TOM_IP, 'PRO_USER_CREATETABLE', TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
SYSDATE, 0, ERR1, TOM_SQLCODE, 5);
COMMIT;*/
END PRO_USER_CREATETABLE;
SQL> exec PRO_USER_CREATETABLE;
PL/SQL procedure successfully completed
--注释中的那段没有那个表
怎么我的你没看TO_CHAR(SYSDATE,'YYYY-MM-DD') 改成 TO_CHAR(SYSDATE,'YYYY_MM_DD')
192.168.110.120 PRO_USER_CREATETABLE 2011-02-15 15:55:06 15-2月 -11 0 ORA-01031: insufficient privileges -1031 5
CREATE OR REPLACE PROCEDURE PRO_USER_CREATETABLE IS
SQL_IN VARCHAR2(5000);
TAB_NAME VARCHAR2(20);
ERR1 VARCHAR2(100);
TOM_SQLCODE VARCHAR2(10);
TOM_IP VARCHAR2(20);
BEGIN
TAB_NAME := 'USER_' || TO_CHAR(SYSDATE, 'YYYYMMDD');
SQL_IN := 'CREATE TABLE "' || TAB_NAME || '_1' || '"
(
USER_MOBILE VARCHAR2(20) NOT NULL ,
FORECAST_AREA VARCHAR2(20) NOT NULL ,
USER_AREA VARCHAR2(20), SEND_TIME CHAR(8),
SERVICE_ID NUMBER, MESSAGE VARCHAR2(140) NOT NULL ,
SEV_CODE VARCHAR2(20),
FEE_TYPE VARCHAR2(2),
FEECODE VARCHAR2(20),
SEND_FLAG NUMBER,
FORECAST_TYPE NUMBER
)
';
EXECUTE IMMEDIATE SQL_IN;
COMMIT;
SQL_IN := 'CREATE TABLE "' || TAB_NAME || '_2' || '"
(
USER_MOBILE VARCHAR2(20) NOT NULL ,
FORECAST_AREA VARCHAR2(20) NOT NULL ,
USER_AREA VARCHAR2(20),
SEND_TIME CHAR(8),
SERVICE_ID NUMBER,
MESSAGE VARCHAR2(140) NOT NULL ,
SEV_CODE VARCHAR2(20),
FEE_TYPE VARCHAR2(2),
FEECODE VARCHAR2(20),
SEND_FLAG NUMBER,
FORECAST_TYPE NUMBER
)
';
EXECUTE IMMEDIATE SQL_IN;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
ERR1 := SUBSTR(SQLERRM, 1, 100);
TOM_SQLCODE := SQLCODE;
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO TOM_IP FROM DUAL; INSERT INTO T_ROVER_ALERT
(ALERTNAME, ALERTKEY, ALERTDATE, DATETIME, FLAG, MSG, ALARTERR, ALERTLEVEL)
VALUES
(TOM_IP, 'PRO_USER_CREATETABLE', TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
SYSDATE, 0, ERR1, TOM_SQLCODE, 5);
COMMIT;
END PRO_USER_CREATETABLE;
当前用户 已经有CREATE ANY TABLE的权限 OLAP_DBA CREATE ANY TABLE NO
DBA CREATE ANY TABLE YES
IMP_FULL_DATABASE CREATE ANY TABLE NO
--改成
CREATE OR REPLACE PROCEDURE PRO_USER_CREATETABLE
authid current_user
IS