| Create Database Scripts [message #10626] |
Wed, 04 February 2004 08:02  |
Jay
Messages: 127
Registered: October 1999
|
Senior Member |
|
|
I have several databases in different location around the state all with differrent schema names "ie:pinweevil2003" every year i Have to recreate the schema on all these database so this years would be "pinweevil2004" So what i have been doing in previous years is making a new schema for every office. Is there a way in a create schema script to use variables, Basiclly have it ask for the first 3 characters of the scheme name. Plus i would like it to prompt at the first of the script for the year as well
Example:
create user TRAweevil2003 identified by netware2000
default tablespace user_data
temporary tablespace temporary_data;
grant dba to TRAweevil2003;
CREATE TABLE TRAweevil2003.action
(ID number(1)
CONSTRAINT action_id_pk_tra03 PRIMARY KEY,
noun varchar2(15)
CONSTRAINT action_noun_nn_tra03 NOT NULL)
PctFree 10 PctUsed 80
Tablespace Weevil_data_2003;
|
|
|
|
|
|
| Re: Create Database Scripts [message #10792 is a reply to message #10636] |
Tue, 17 February 2004 12:17  |
Jay
Messages: 127
Registered: October 1999
|
Senior Member |
|
|
This does not seem to work. This is what i have
spool oracle:orahomedatabaseMSql.log
ACCEPT first3 PROMPT 'Enter first3: '
ACCEPT theyear PROMPT 'Enter year: '
ACCEPT conyear PROMPT 'Enter second year: '
create user &first3.weevil&the_year identified by password
default tablespace user_data
temporary tablespace temporary_data;
grant dba to &first3.weevil&the_year;
CREATE TABLE &first3.weevil&the_year..action
(ID number(1)
CONSTRAINT action_id_pk_&first3.&con_year PRIMARY KEY,
noun varchar2(15)
CONSTRAINT action_noun_nn_&first3.&con_year NOT NULL)
PctFree 10 PctUsed 80
Tablespace Weevil_data_2003;
Commit;
|
|
|
|
|
|
|
|
| Re: Create Database Scripts [message #10829 is a reply to message #10792] |
Thu, 19 February 2004 00:07 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
ACCEPT, PROMPT, and the usage of substitution variables prefaced with an ampersand (&) are all SQL*Plus things. I am not a DBA, so I am not familiar with svrmgr, so I don't know if it actually accesses SQL*Plus in the background. When in doubt, test it in SQL*Plus first to make sure it works as is. Then test it from svrmgr and let us know. I am guessing that it probably won't work. If you can execute a sql script without SQL*Plus items, then you might want to consider generating the script from SQL*Plus, then executing it from svrmgr. Copy and start the script below to see what I mean. It will create a script called msql.sql that will contain a pure sql script without any SQL*Plus items with your entered values properly substituted. Then you can execute that msql.sql script separately.
STORE SET saved_settings REPLACE
ACCEPT first3 PROMPT 'Enter first3: '
ACCEPT the_year PROMPT 'Enter year: '
ACCEPT con_year PROMPT 'Enter second year: '
SET ECHO OFF FEEDBACK OFF HEADING OFF PAGESIZE 0 VERIFY OFF
SPOOL msql.sql
PROMPT spool oracle:orahomedatabaseMSql.log
SELECT 'create user &first3.weevil&the_year identified by password'
FROM DUAL
/
PROMPT default tablespace user_data
PROMPT temporary tablespace temporary_data
PROMPT /
SELECT 'grant dba to &first3.weevil&the_year'
FROM DUAL
/
PROMPT /
SELECT 'CREATE TABLE &first3.weevil&the_year..action'
FROM DUAL
/
PROMPT (ID number(1)
SELECT 'CONSTRAINT action_id_pk_&first3.&con_year PRIMARY KEY,'
FROM DUAL
/
PROMPT noun varchar2(15)
SELECT 'CONSTRAINT action_noun_nn_&first3.&con_year NOT NULL)'
FROM DUAL
/
PROMPT PctFree 10 PctUsed 80
PROMPT Tablespace Weevil_data_2003
PROMPT /
SPOOL OFF
START saved_settings
EDIT msql.sql
|
|
|
|
|
|
| Re: Create Database Scripts [message #11022 is a reply to message #10830] |
Mon, 01 March 2004 07:06 |
Jay
Messages: 127
Registered: October 1999
|
Senior Member |
|
|
I think i got most it worked out i am just getting a error on one of my tables that i am trying to create below is the log and the table create
--------------------LOG FILE------------------------
SP2-0734: unknown command beginning "CONSTRAINT..." - rest of line ignored.
SP2-0734: unknown command beginning "CONSTRAINT..." - rest of line ignored.
SP2-0734: unknown command beginning "REFERENCES..." - rest of line ignored.
SP2-0734: unknown command beginning "CONSTRAINT..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "REFERENCES..." - rest of line ignored.
SP2-0734: unknown command beginning "CONSTRAINT..." - rest of line ignored.
SP2-0734: unknown command beginning "REFERENCES..." - rest of line ignored.
SP2-0734: unknown command beginning "CONSTRAINT..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
(sensite in (0,1))
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
--------------------END LOG---------------------------
--------------------Table-----------------------------
CREATE TABLE &first3.weevil&the_year..fieldinfo
(wu varchar2(6),
fieldno varchar2(4),
county varchar2(3)
CONSTRAINT fieldinfo_county_nn_&first3.&con_year NOT NULL,
fsano Varchar2(4)
CONSTRAINT fieldinfo_fsano_nn_&first3.&con_year NOT NULL,
acres number(7,2)
CONSTRAINT fieldinfo_acres_nn_&first3.&con_year NOT NULL,
grower VarChar2(9)
CONSTRAINT fieldinfo_grower_nn_&first3.&con_year NOT NULL,
nickname varchar2(30),
p_date date,
traps number(3)
CONSTRAINT fieldinfo_traps_nn_&first3.&con_year NOT NULL,
sensite Number(1),
organic Number(1),
historical Number(1),
irrigated Number(1),
sscontname varchar2(30),
sscontphone varchar2(15),
consultant varchar2(30),
consultphone varchar2(15),
fname NUMBER(6)
CONSTRAINT fieldinfo_fname_nn_&first3.&con_year NOT NULL,
CONSTRAINT fieldinfo_pk_&first3.&con_year PRIMARY KEY (wu,fieldno),
CONSTRAINT fieldinfo_county_fk_&first3.&con_year FOREIGN KEY (county)
REFERENCES &first3.weevil&the_year..county(ID),
CONSTRAINT fieldinfo_fname_fk_&first3.&con_year FOREIGN KEY (fname)
REFERENCES &first3.weevil&the_year..Entity(ID),
CONSTRAINT fieldinfo_grower_fk_&first3.&con_year FOREIGN KEY (grower)
REFERENCES &first3.weevil&the_year..grower(ID),
CONSTRAINT fieldinfo_sensite_ck_&first3.&con_year CHECK
(sensite in (0,1)))
PctFree 40 PctUsed 50
Tablespace Fieldinfo_2003;
---------------------End Table-----------------------
|
|
|
|
| Re: Create Database Scripts [message #11029 is a reply to message #11022] |
Mon, 01 March 2004 20:31 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
|
It doesn't like the blank lines before the lines that start with "CONSTRAINT". It doesn't recognize that "CONSTRAINT" is part of the create table statement and thinks you are trying to begin a new command with that word. You need to eliminate the blank lines. If the blank lines are the result of spooled dynamic sql, you might try increasing the linesize or selecting the code in smaller chunks.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
