CODEWITHSUNDEEP
oracle-databasesvnoracle10goracle-sqldeveloper
craig
craig

Reputation: 26262

Oracle build-automation?

I have a created number of table, function, view, and procedure scripts to support reporting. Due to the complicated environment, migrating scripts (development-->testing) can be a chore.

The DBA does not allow the developers to use the primary tablespace ('VENDOR'), nor either of main schemae ('UTIL','REPORTING'). The UTIL schema is intended for functions and procedures; REPORTING is for tables and views.

Because the development server is often recommissioned for other purposes, development is done on the testing server, using a development tablespace ('DEVL') and a schema for each developer ('CRAIG', for example).

As a result, a table's script must be converted from:

DROP TABLE CRAIG.X_TABLE;
CREATE TABLE CRAIG.X_TABLE;
...
TABLESPACE "DEVL";

to:

DROP TABLE REPORTING.X_TABLE;
CREATE TABLE REPORTING.X_TABLE;
...
TABLESPACE "VENDOR";

A view's script must be changed from:

CREATE OR REPLACE VIEW CRAIG.X_VIEW
...
;

to:

CREATE OR REPLACE VIEW REPORTING.X_VIEW
...
;

A procedure's script must be changed from:

CREATE OR REPLACE PROCEDURE CRAIG.X_PROCEDURE
...
INSERT INTO CRAIG.X_PROCEDURE
SELECT ...
-- reference a table in REPORTING schema
FROM   REPORTING.ANOTHER_TABLE
;

to:

CREATE OR REPLACE PROCEDURE UTIL.X_PROCEDURE
...
INSERT INTO REPORTING.X_PROCEDURE
SELECT ...
FROM   REPORTING.ANOTHER_TABLE
;

The table and procedure scripts require the most intervention, as you can see.

If it makes a difference, I use SQL Developer, TextMate, and Sublime Text 2 for coding and Cornerstone to interact with our organization's Subversion (SVN) repository.

Is there a way to simplify (i.e. automate) the changes that I need to each type of script as I migrate the logic from the development environment to the testing one?

Upvotes: 0

Views: 155

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

I would connect as the schema owner; not sure if you're implying you're connecting as one user and building objects in a different schema? i.e. don't qualify the table names etc. at all. And have a suitable default tablespace for that user. Then the scripts don't need to specify either. Maybe I'm missing something?

If you really want to specify them, you can prompt for and accept the values at the start of the script and use substitution variables:

accept schma char prompt 'Enter schema: '
accept tbspc char prompt 'Enter tablespace: '

create table &&schma..my_table (...) tablespace &&tbspc;

etc.

If there are a limited number of scenarios you could maybe set the values automatically based on the database name, assuming different environments are in different instances:

column q_schma new_value schma
column q_tbspc new_value tbspc

select case name when 'TEST_NAME' then 'CBUCH' else 'PROD_USER' end as q_schma,
    case name when 'TEST_NAME' then 'TBSP_DEV' else 'PROD_SCHEMA' end as q_tbspc
from v$database;

create table &&schma..my_table (...) tablespace &&tbspc;

You could also change your default schema to avoid the prefixes:

alter session set current_schema = &schma
create table my_table (...) tablespace &&tbspc;

Another approach might be to use placeholders in the checked-in code, and run the code through sed or similar to put the real values in.

Upvotes: 1

Related Questions