Extract code or script of database objects

Question

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

Ben · Accepted Answer · 2012-06-08T12:23:40.960

6

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.

edited Jun 08 '12 at 12:23

answered Jun 08 '12 at 10:15

Ben

51,770
36
127
149

Is it possible to integrate version control with database? – Jacob Jun 09 '12 at 14:41
2

Not that I know of. You could create your own by storing all objects DDL in a table, however, it still isn't that safe as you could lose everything. We just have a one-line script that loops through all the folders in our VCS and executes the scripts therein. – Ben Jun 09 '12 at 14:58
What needs to be done to enable VCS in database? Any set up or scripts has to executed to enable this? Thanks – Jacob Jun 10 '12 at 12:28
1

@user75ponic You do not enable VCS in database. Instead you write all SQL and PL/SQL DDL/DML as text files using your favorite editor in your own computer. Save the files to VCS (SVN, git, hg, etc.) and "execute" the files to the database with a command line tool (`sqlplus` in Oracle). – user272735 Aug 14 '15 at 06:08

score 1 · Answer 2 · answered Jun 08 '12 at 10:14

1

I don't know about the database itself, but SQL Developer will do it. I'm pretty sure TOAD and other such tools will as well.

answered Jun 08 '12 at 10:14

John Watts

8,717
1
31
35

score 1 · Answer 3 · answered Sep 21 '18 at 12:58

In my case, all the custom objects start with 'XX'. You could use or modify my script:

DECLARE
   CURSOR c_tables IS
     SELECT replace(object_type, ' ', '_') type,
            object_name name,
            owner schema,
            object_type||'#'||object_name||'.sql' files
       FROM all_objects
      WHERE UPPER(object_name) LIKE 'XX%'
        AND object_type NOT IN ('DIRECTORY');

   ddl_text CLOB;

 BEGIN
   FOR rec_tables IN c_tables LOOP
     dbms_output.put_line('OBJECT_TYPE: '||rec_tables.type||', OBJECT_NAME: '||rec_tables.name||', SCHEMA: '||rec_tables.schema||' ->'||rec_tables.files);

     SELECT dbms_metadata.get_ddl(rec_tables.type, rec_tables.name, rec_tables.schema)
       INTO ddl_text
       FROM DUAL;

     dbms_xslprocessor.clob2file(ddl_text, 'UTL_CUSTOM_DDL', rec_tables.files, nls_charset_id('WE8MSWIN1252'));
   END LOOP;
 END;

Regards

score 0 · Answer 4 · answered Aug 14 '15 at 06:53

Sorry but you have a non-optimal development practice that doesn't scale beyond one developer.

Never use SQL GUI clients' abilities to edit database objects directly in the database. (Unless you deliberately want to create artificial problems.)

Instead all SQL and PL/SQL DDL and DML should be located in text files saved into a version control system (VCS) (e.g. subversion, git, mercurial). Use your favorite editor to edit the files.

Modify the database by executing the text files saved in VCS with a command line tool (in Oracle that's sqlplus). This applies to all database instances: development, QA, production.

For large development projects consider using schema migration tools (e.g. Flyway, Roundhouse).

Over the years I have been able to eliminate tons of development and production issues by enforcing a simple rule:

If the code is not in the VCS it doesn't exists.

(It's amazing that still in 2015 there's many development teams not using a VCS.)

Extract code or script of database objects

4 Answers4

Linked

Related