Search code examples
databaseplsqloracle10gtoadplsqldeveloper

Extract code or script of database objects

I need to extract code(script) from all my functions, procedures, packages, views and tables, so that when I move to production I could run the script to create all the objects.

While developing I did not take script backup of all database objects.

What is the best way to extract code or script? Any suggestion or help is highly appreciable.

Thanks

Solution

  • You do use a version control system don't you? Please do.

    Failing that you can use the system function dbms_metadata.get_ddl, which will return a clob of the DDL used to create the object. This will need to be done for each individual object so it might be worth looping through user_objects.

    Alternatively you can go through the definitions in PL/SQL Developer by right-clicking on an object and using the view option then in the bottom right corner you can view the SQL used to create the object.

    There is also an export "tool", which doesn't have as good an explanation of how to use it as the SQL Developer variant.

    Of course, the correct answer is get a version control system and use it.