Problem solve Get help with specific problems with your technologies, process and projects.

Deploy entire database schemas

We have developed a Web application using Web objects 5.2 with Oracle 8i. What is the best way to deploy entire database schemas at a client machine, and the first time when project is going to deploy? What are the best ways to load the large data from client machine to our machine? We generally get the insertion script from the client and we run that using SQL*Plus command. But if the statements are more than 2,500 characters, it throws an exception.
From the description, it sounds like this is intended to be a packaged application of some sort. First, for deploying the schema, I would prepare SQL scripts and ship them with the product. Before doing so, make sure that your product's documentation clearly states what versions of Oracle that your product has been certified (or at least tested) with. If there is some significant amount of seed data that is to be included with new deployments, then I would create SQLLDR scripts and load that data from flat text files. Alternatively, you could also create insert statements in a script if there's not too much data.

For getting data back from a site, I would use SQLLDR and use a select statement to generate the raw data file for loading on the remote site. That will bypass any issues you have with length of SQL statements. You can find many scripts on the internet for doing just that task--search for data dumper or SQL unloader.

You should track the version of the database schema in the database somewhere and an upgrade script should check the current version and apply the necessary changes to upgrade that version to the current or target release. Internally, such a script might have smaller functions or blocks that do the individual upgrades from one version to the next so that an outer section would just need to figure out where to start and run all of the functions in sequence.

Dig Deeper on Oracle database security

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.