0

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!

4
  • Is the DBMS db2? Commented Aug 23, 2019 at 19:16
  • I'm not actually sure - is there an easy way to check? I can remove the tag for now.
    – bracec
    Commented Aug 23, 2019 at 19:22
  • 1
    Not sure if your DBMS is DB2? You do realize of course that DB2 is the relational database product of IBM - a competitor to Oracle. So there should be no question of which product you are working with.
    – EdStevens
    Commented Aug 24, 2019 at 18:18
  • Here, we see the clear delineation between academia and industry, that being that you can complete 4 years of academia and still know only just enough to make an ass out of yourself. It is not DB2, thank you.
    – bracec
    Commented Aug 26, 2019 at 15:39

1 Answer 1

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.

1
  • There is also the "Cart" feature of SQL-Developer. Commented Aug 26, 2019 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.