You’re using ORDS.

You’ve written some web services.

Now you need to control the underlying PL/SQL source.

How do I get the code out?

Yes, how do I get THIS code on the right?

There’s a few ways.

1. Use SQL Developer

On the Module, mouse right click.

Now pick your poison…I usually just send to a worksheet so i can format, comment, whatever, AND then save to a file.

2. Use SQLcl

The REST command will get what you want.

If you want this in a file, just use the SPOOL command.

3. Get it Yourself with SQL

The ORDS_METADATA schema comes with some PL/SQL packages for managing your web services. One of those is named ‘ORDS_EXPORT’ – one guess what this does 🙂

SET long 5000
SET pagesize 1000
SELECT ORDS_METADATA.ords_export.export_module(p_module_name=>'ora_magazine')
FROM dual;

Which comes out looking pretty familiar…

By default includes any associated security privs.

If you look at the package.function, you’ll notice it’s overloaded – you can also pass a couple of BOOLEANs to determine if you want the code to ENABLE the schema or if you want the associated PRIVILEGES included.

You see these same options when using the GUI and saying export to file.

4. Via REST Development

If you use this panel, you can export all of your MODULES to an archive (ZIP) of XML files that which can then be uploaded to another ORDS server if you want. I wouldn’t recommend using this method unless you only have REST access to ORDS, or in other words, no DATABASE access where the REST services are defined.

Not the ideal format for your source control systems, just use the other methods I showed above.

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


  1. walid kharrat Reply

    Hi Jeff,
    is there a way to export one service from module (an url pattern). I have to do export import utility between development environment and prod. So the developer or manager can choose a service to send from dev to prod. I will put the script in version control. Do the replace if there’s a different pattern between dev an prod. And execute the script in the prod database.

    • thatjeffsmith

      In a schema, it’s easy, you can simply right click on the Modules item in the tree and export from there.

      And since I wrote this post, we’ve added a new method – Liquibase command in SQLcl.

  2. Dietmar Aust Reply

    Hi Jeff,

    it seems like autorest enabled objects are not exported using ORDS_METADATA.ords_export.export_schema(). The asscociated roles and privileges are part of the export, but not the ords.enable_object() call.

    Why? What is the reasoning behind this, I just don’t understand it.

    And it would be great to have the autorest enabled objects included in the navigation tree of the database connection … like the other (user-created) REST modules.


    • thatjeffsmith

      Both are known issues, and both will be addressed in upcoming releases of SQL Developer (although probably not for 19.1)

Write A Comment