Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Exporting a PL/SQL Package Just the Way Monty Likes in SQL Developer

2 Mins Read

Got a quick question from my favorite President today.

It's OK, Monty said I could share our conversation :)
It’s OK, Monty said I could share our conversation 🙂

So basically, Monty was asking how to get a PL/SQL Package Spec & Body to:

  • a single file
  • with a SET DEFINE OFF
  • with the schema prefix in the CREATE or REPLACE

Now, there’s more than one way to get one or more objects in the database out to a file. For just PL/SQL stuff, you have this on a right-click:

Hint: it's the item I highlighted
Hint: it’s the item I highlighted

Now, you can use the ‘Save Package Spec and Body,’ but then you don’t get the schema prefix. Or, you could use the Export, but then you don’t have the option to include your ‘scripty’ stuff.

Now the Cart – that gives us the best of both worlds.

Be sure to set the options just as you require.
Be sure to set the options just as you require.

And this will give you the file that Monty wants…mostly.

Where's the SET DEFINE OFF??
Where’s the SET DEFINE OFF??

Ok, he also wanted to start the script with a SET SCAN OFF.

Just add your BEFORE CREATE script. Now, whatever I have in that script, will show up in the file that SQL Developer creates, right before the package spec code.

You can actually add pre and/or post scripts for EVERY object, but just the one will do here :)
You can actually add pre and/or post scripts for EVERY object, but just the one will do here 🙂

Nnormally, I don’t like publishing on a Friday, but when the President asks, the President gets!

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

8 Comments

  1. Omar Reply

    Hi Jeff,
    When I select a package to add to the cart, only the specs is added, not the body of the package, so I need to expand the object browser and select the body and added to the cart, is there any option to add to the cart the package specs and body at one shot?

    Regards,

  2. thirsd Reply

    i want to export all package in oracle,but i don’t expect to select every schemas.how can i do this?

    by the way ,
    i create a package like ‘create or replace package body my.pg_myfirst ‘ and complied

    when i export this package by dbms_metadata.get_ddl,i get “create or replace package body “MY”.”PG_MYFIRST”” with the shemas and package name capitaled

    when i export by dba_source ,i get “create or replace package body pg_myfirst” without the shemas

    that’s why ? could you tell me ?

    • thatjeffsmith Post Author

      every package from every schema?

      if you use the export wizard, you can simply select schemas and object types you want included in the export.

      if you want schemas included, you have to enable the ‘Show Schema’ option on the first page of the wizard

    • thirsd

      i want to export oracle objects like pl/sql developer’s. many schemas with my oracle db , pl/sql export schemas object just one schemas by another,so that will waster time to select schemas by hand when i export every package in every schemas.
      so i try to use dbms_metadata.get_ddl to export object,but the object exported is schemas name capitaled and with double quotation marks like ‘ “MY”.”PG_MYFIRST” ‘,but my wrote code just is ‘ my.pg_myfirst ‘.

Write A Comment