Oracle schema holder

Posted by Riverside Software on 17-Aug-2015 08:38


I'm working on some Oracle schema holder automation, and have a few questions :

* In order to delete a schema holder, is it enough to first find the _db record, then delete _file, _field, _index-field, _index, _sequence, _constraint, _db-option associated to _db record (by _db-recid), then delete the _db record. Am I missing something ?

* In order to recreate a new (empty) schema holder, is it enough to create the _db record, followed by prodict/ora/_ora_sys.p ?

I have to mention that in both cases, I'm trying to follow what is executed in prodict/ code, and that it works on my development machine, but would like to get confirmation.

By the way, how are other people dealing with schema holder upgrades ? Do they have somebody awake at 3AM to execute the Data Dictionary procedure by hand ? Or something I'm missing ?


All Replies

Posted by Stefan Drissen on 17-Aug-2015 09:11

Our starting point is always a final progress df and the incremental progress df to get from previous version to current version.

We then migrate the final complete progress df to oracle / sql using data administration tool (manually).

We then generate the incremental sql scripts with a custom tool that uses information from the incremental progress df combined with the new schema holder. There is an option in data administration to do this now, but I'm not sure how well if works.

Fresh new schema holders are deployed.

From Ant / PCT / Jenkins the tables are recreated per test run:

<target name="oracle">
		<pct:db_create dbName="exactcssh" destDir="${test}/db" schemaFile="${other}/db/oracle/exactcssh.df" />
		<antcall target="">
			<param name="oracle_user" value="${}" />
			<param name="db.script" value="exactcs" />

		<echo file="${test}/">-db db/exactcssh -1 -db exactcs -dt ORACLE -ld exactcs -U ${}/secret@${}</echo>

	<!-- oracle: create tables -->
	<target name="">

		<property environment="env" />
		<property name="oracle.jdbc" value="${env.ORACLE_HOME}/jdbc/lib/ojdbc6.jar" />

		<!-- sql script attempts to drop everything before creating it, which errors when it does not yet exist, so onerror="continue" -->
		<sql driver="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:oci:@${}" userid="${oracle_user}" password="secret" classpath="${oracle.jdbc}" onerror="continue">
			<transaction src="${other}/db/oracle/${db.script}.sql" />


Posted by Riverside Software on 17-Aug-2015 09:23

Thanks Stefan. I'm trying to deal with a different case where the schema holder is not created from a source Progress schema ; the Oracle database is the main DB of another Java application, so the only option I have is to pull the schema from Oracle, and entirely refreshing the schema holder seems to be the best solution.

Guess I'll push this code to PCT if it's stable enough...

Posted by TheMadDBA on 17-Aug-2015 10:07

In my opinion the easiest way by far is to replace the schema holder database during the deployment.

It just really simplifies the entire process. You deploy exactly what you compiled and tested against and the fall back is as simple as putting the old copy out there again.

Posted by Riverside Software on 17-Aug-2015 10:30

In this context, anything going to deployment has to be built from source, there's no way to tell deployment guys to just copy a few files from the QA server... So, at some stage, I have to create the schema holder by code (but the delete part may be optional).

Posted by TheMadDBA on 17-Aug-2015 11:32

How do they deploy images and other binary files? Why are you recompiling between DEV,QA and Prod?

I have always generated the proper schema holder in DEV and then promoted it to QA, PreProd and Prod.  Just like R-Code and binary files.

Posted by Riverside Software on 18-Aug-2015 00:50

Posting again, my answer didn't show up.

Images and some other binary files are stored in the code repository, and included in the build artifact. However, I can't imagine storing a schema holder in the code repository, that's just something that should be generated during the build process.

Posted by Stefan Drissen on 18-Aug-2015 02:25

We store both (for ease) - the .df and the binary.

Automated builds use the df of the oracle schema holder to create the schema holder (see Ant snippet above).

This thread is closed