创建xwiki用户 密码xwiki 10M空间 。 数据库无所谓只要跟10.143.131.53 通就行
命令行实现:
create tablespace xwiki datafile '/app/opt/oracle/oradata/xwiki.dbf' size 5120M AUTOEXTEND OFF;
create user xwiki identified by xwiki default tablespace xwiki;alter user xwiki account unlock;grant connect,resource to xwiki;
现在将其编写为存储过程:
create or replace procedure pro_zz(TABLESPACE_NAME VARCHAR2)
AS
v_flag INT:=0;
v_flag1 INT:=0;
BEGIN
v_tablespacename:=UPPER(TABLESPACE_NAME);
URL:= select rtrim((select file_name FROM dba_data_files where file_id=1),'system01.dbf') FROM dual;
select count(*) INTO v_flag from dba_tablespaces where tablespace_name=''''||v_tablespacename||'''';
select count(*) INTO v_flag1 from user_tables where table_name=''''||v_tablespacename||'''';
IF v_flag=0 THEN
CREATE TABLESPACE v_tablespacename DATAFILE URL SIZE 20M AUTOEXTEND OFF;
END IF;
IF v_flag1=0 THEN
CREATE USER v_tablespacename IDENTIFIED BY v_tablespacename DEFAULT TABLESPACE v_tablespacename;
ALTER USER v_tablespacename ACCOUNT UNLOCK;
GRANT CONNECT,RESOURCE TO v_tablespacename;
END IF;
END;
/
执行总是报错,找不出原因,
7/9 PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <一个带有字符集说明的可带引号的字符串文字> <一个可带引号的 SQL 字符串>
7/76 PLS-00103: Encountered the symbol "," when expecting one of the following: ) intersect minus order union
8/3 PLS-00103: Encountered the symbol "SELECT"
请大神指点!
命令行实现:
create tablespace xwiki datafile '/app/opt/oracle/oradata/xwiki.dbf' size 5120M AUTOEXTEND OFF;
create user xwiki identified by xwiki default tablespace xwiki;alter user xwiki account unlock;grant connect,resource to xwiki;
现在将其编写为存储过程:
create or replace procedure pro_zz(TABLESPACE_NAME VARCHAR2)
AS
v_flag INT:=0;
v_flag1 INT:=0;
BEGIN
v_tablespacename:=UPPER(TABLESPACE_NAME);
URL:= select rtrim((select file_name FROM dba_data_files where file_id=1),'system01.dbf') FROM dual;
select count(*) INTO v_flag from dba_tablespaces where tablespace_name=''''||v_tablespacename||'''';
select count(*) INTO v_flag1 from user_tables where table_name=''''||v_tablespacename||'''';
IF v_flag=0 THEN
CREATE TABLESPACE v_tablespacename DATAFILE URL SIZE 20M AUTOEXTEND OFF;
END IF;
IF v_flag1=0 THEN
CREATE USER v_tablespacename IDENTIFIED BY v_tablespacename DEFAULT TABLESPACE v_tablespacename;
ALTER USER v_tablespacename ACCOUNT UNLOCK;
GRANT CONNECT,RESOURCE TO v_tablespacename;
END IF;
END;
/
执行总是报错,找不出原因,
7/9 PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <一个带有字符集说明的可带引号的字符串文字> <一个可带引号的 SQL 字符串>
7/76 PLS-00103: Encountered the symbol "," when expecting one of the following: ) intersect minus order union
8/3 PLS-00103: Encountered the symbol "SELECT"
请大神指点!
如果一定要使用,请采用动态SQL的写法。
create or replace procedure pro_zz(TABLESPACE_NAME VARCHAR2)
AS
v_flag INT:=0;
v_flag1 INT:=0;
BEGIN
v_tablespacename VARCHAR2:=UPPER(TABLESPACE_NAME);
URL VARCHAR2:= select rtrim((select file_name FROM dba_data_files where file_id=1),'system01.dbf') FROM dual;
select count(*) INTO v_flag from dba_tablespaces where tablespace_name=''''||v_tablespacename||'''';
select count(*) INTO v_flag1 from user_tables where table_name=''''||v_tablespacename||'''';
IF v_flag=0 THEN
EXECUTE IMMEDIATE 'CREATE TABLESPACE v_tablespacename DATAFILE'''URL''' SIZE 10M AUTOEXTEND OFF';
END IF;
IF v_flag1=0 THEN
EXECUTE IMMEDIATE 'CREATE USER '||v_tablespacename||' IDENTIFIED BY'|| v_tablespacename||' DEFAULT TABLESPACE'|| v_tablespacename||'';
EXECUTE IMMEDIATE 'ALTER USER '||v_tablespacename||' ACCOUNT UNLOCK';
EXECUTE IMMEDIATE 'GRANT CONNECT,RESOURCE TO '||v_tablespacename||;
END IF;
END;
/
报错:
PLS-00103: Encountered the symbol "CHAR" when expecting one of the following:
create or replace procedure pro_zz(TABLESPACE_NAME VARCHAR2)
AS
v_flag INT:=0;
v_flag1 INT:=0;
BEGIN
v_tablespacename VARCHAR2:=UPPER(TABLESPACE_NAME);
URL VARCHAR2:= select rtrim((select file_name FROM dba_data_files where file_id=1),'system01.dbf') FROM dual;
select count(*) INTO v_flag from dba_tablespaces where tablespace_name=''''||v_tablespacename||'''';
select count(*) INTO v_flag1 from user_tables where table_name=''''||v_tablespacename||'''';
IF v_flag=0 THEN
EXECUTE IMMEDIATE 'CREATE TABLESPACE v_tablespacename DATAFILE'''URL''' SIZE 10M AUTOEXTEND OFF';
END IF;
IF v_flag1=0 THEN
EXECUTE IMMEDIATE 'CREATE USER '||v_tablespacename||' IDENTIFIED BY'|| v_tablespacename||' DEFAULT TABLESPACE'|| v_tablespacename||'';
EXECUTE IMMEDIATE 'ALTER USER '||v_tablespacename||' ACCOUNT UNLOCK';
EXECUTE IMMEDIATE 'GRANT CONNECT,RESOURCE TO '||v_tablespacename||;
END IF;
END;
/
报错:
PLS-00103: Encountered the symbol "CHAR" when expecting one of the following:
URL VARCHAR2:= select rtrim((select file_name FROM dba_data_files where file_id=1),'system01.dbf') FROM dual;oracle不可以这么直接写吧