Copy package in Production DB to Test DB with dependencies?

Question

This is my first question here, so please let me know if this isn't the right place to ask.

I'm a new programmer, fresh out of school. Primarily I develop in Python/C#, with a dash of SQL when needed.

At my job, I've been assigned a task in PL/SQL, which involves modifying some existing procedures. All well and good, some new syntax to learn, but no serious roadblocks.

My problem is, I have no idea how to migrate the existing package/views/tables to our test database, which is basically an un-maintained sandbox environment.

I only need to copy two stored procedures in a package, two views, and 3 tables, so that I can modify the procedures and verify that the output is as-expected. However, I feel like there must be a better way to move this data than "crtl+c, crtl+v" in the editor window, or downloading and uploading table export files via my local machine.

I'm working in Oracle SQL Developer, and really only need to move enough data to execute these procedures and test my work. My job is really the development here, and the more time I spend trying to set up this test environment, the less time I spend coding.

Is there some sort of way to easily copy a package and all its data dependencies from one database to another? Bonus points if it's in Oracle SQL Developer, and easy for a non-DB admin to do. Thanks!

Answer 1

In SQL Developer you can right-click on an object (table, package, procedure, etc) and there will be an option for 'Quick DDL'. Open that and you have options on what to do with the ddl, including saving to a file. That's better than select/ctl-C/ctl-v, but there is still a better way since you know what you want.

You can use expdb (DataPump export) to export the ddl for the selected objects. That will create a binary .dmp file that is, essentially, all of the DDL you want. You will need to read up on the options for limiting what gets exported or you very well could get the entire database, including data. You then use impdp (DataPump import) to process that dmp file and create/load your objects. Even when you limit the objects that get exported, you can specify whether or not to include the data. I leave it as an exercise for the student to study the relevant documentation at https://docs.oracle.com/database/121/SUTIL/GUID-501A9908-BCC5-434C-8853-9A6096766B5A.htm#SUTIL2877

Another method you might consider if you JUST want the DDL, without the DML to load tables, would be to write a script using the DBMS_METADATA.GET_DDL procedure, documented at https://docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS66872

Now, you may say "Can't you just give me the script, getting this DDL is a distraction from my real job." But you are just starting your career and time spent learning to read the docs, learning that these tools even exist, and learning how to use them is time well invested. When I was just starting my career (as a COBOL programmer in 1981) I would take reference manuals home with me for bed-time reading. It served me all, as within 3 years I was the lead tech on a project, directing the work of several programmers with more experience but less inate curiosity about how things really worked and about learning new things about the tools they thought they already knew.