Oracle Script

[ofsouto]

Dear Sirs

I'm developing a script to create tablespace, user and objects (tables, stored procedures, etc) on a
existing database.
I need the bellow informations:
. database name
. tablespace name (The client can choose an existing tablespace, a new tablespace or accept the defalut name)
. user name (The cliente can choose a new user or accept the default name)
. password (The client can choose a new password or accept the default password)

script:

*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*--*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
set echo off
set verify off

accept base char format a30 prompt 'Database:'
accept tblspc char format a30 default gestorcreditopj prompt 'Tablespace:'
accept usuario char format a30 default gestorpj prompt 'User:'
accept senha char format a30 default credito prompt 'Password:' hide

declare
v_nome varchar2(30);
begin
select name into v_nome from v$tablespace where upper(name) = upper('&tblspc');

if v_nome is null then
create tablespace &tblspc datafile &tblspc size 50 m
logging
DEFAULT storage(
initial 24 k
NEXT 24 k
minextents 1
maxextents 249
pctincrease 50
)
online permanent;
end if;

create user "&usuario" identified by "&senha"
default tablespace &tblspc
temporary tablespace temp
profile default
quota unlimited on &tblspc;

grant "connect" to "&usuario";
grant create session to "&usuario";
grant unlimited tablespace to "&usuario";
grant select_catalog_role to "&usuario";
alter user "&usuario" default role select_catalog_role;
alter user "&usuario" default role all except connect;

connect &usuario/&senha@&base;

CREATE TABLE USRBD (
BDCOD CHAR (10) NOT NULL,
BDNOME CHAR (30) NOT NULL,
BDVAL DATE NOT NULL,
PERCOD NUMBER (10) NOT NULL,
SERCOD CHAR (10) NOT NULL,
BDSENHA CHAR (10) NOT NULL,
BDNOVOLIM CHAR (1) DEFAULT 'F' NOT NULL,
BDREAVAL CHAR (1) DEFAULT 'F' NOT NULL,
BDEMAIL VARCHAR2 (200),
BDEXPORTARDADOS CHAR (1) DEFAULT 'F',
BDLISTARLOGLOTE CHAR (1) DEFAULT 'F',
BDLIMITESEGMENTO CHAR (1) DEFAULT 'F' NOT NULL,
BDEXIBESTATUSDEC CHAR (1) DEFAULT 'F' NOT NULL,
CONSTRAINT PK_USRBD
PRIMARY KEY ( BDCOD ) );

ALTER TABLE USRCAR ADD CONSTRAINT FK_USRCAR_USRBD
FOREIGN KEY (SE_BDCOD)
REFERENCES USRBD (BDCOD);

INSERT INTO USRBD(BDCOD,BDNOME,BDVAL,PERCOD,SERCOD,BDSENHA,BDNOVOLIM, BDREAVAL, BDEMAIL)
VALUES ('ADMIN', 'ADMINISTRADOR', TO_DATE('31/12/2010', 'DD/MM/YY'),1 ,'99999999', 'am"k!G ', 'F', 'F', NULL);
COMMIT;
end;
/

set echo on
set verify on
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*--*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

When I try to run the script, a messager error shows up:

create tablespace souto datafile souto size 50 m
*
ERROR at line 7:
ORA-06550: line 7, column 3:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>

If I use native dynamic SQL (Execute Immediate), another message erro shows up:

old 37: connect &usuario/&senha@&base;
new 37: connect obede/souto@gcdesen;
ERROR:
ORA-00972: identifier is too long

What's wrong? Could somebody help me?

 

[sem]

You can not call DDL operations from PL/SQL directly: you may either write pure SQL script or call that commands from PL/SQL dynamically (using EXECUTE IMMEDIATE or DBMS_SQL).

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

well .. you don't need to check if tablespace does exist.
If you try to execute a CREATE TABLESPACE ..blahblahblah command against an existing tablespace nothing will happen except an error will be reported, so you can proceed with normal user creation.
In this way you don't need anymore a PL/SQL procedure (declare/begin/end) but a normal SQL script