Q

Updating changes to DB objects with new releases

We are developing a large application base on PL/SQL. We often have to change DB objects. The biggest trouble comes with every new release. To enhance our application we have to know about the latest state of all DB objects. To find this out, we have to run through the history of all PL/SQL files. There must be a better way to solve this problem.

We are developing a large application base on PL/SQL. We often have to change DB objects, e.g., add a new column to a table or grant/revoke some privileges from/to some objects (things like tables, triggers, tablespaces, synonyms and so on).

The biggest trouble comes with every new release. To enhance our application we have to know about the latest state of all DB objects. To find this out, we have to run through the history of all PL/SQL files.

There must be a better way to solve this problem. We want something like "export all objects" so we get a lot of SQL files that contain the definitions of all objects. We take these files and put them into our new DB, so we have the latest state of the DB (without running through the history).

You can build a script that utilizes the dbms_metadata package to do this. Using this package you can capture the command to build any database object. Use a cursor from dba_objects to dynamically dump the SQL for the objects you are interested in.
This was first published in November 2005

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close