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.

There’s another function available in this schema – EXPORT_SCHEMA. You can also guess what this does. It also has two booleans, for saying if you want the schema enablement and privs included in the output.

SELECT ords_metadata.ords_export.export_schema()
  FROM dual;
Just what it sounds like.

If you’re looking for a package call to export ALL of the RESTful Services in your entire database, then we’re looking to update our ORDS_ADMIN package to allow that. Stay tune!

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.
thatjeffsmith
Author

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.

7 Comments

  1. Hi Jeff,
    I’m using this functionality for the first time…
    In the “Module Export” dialog, you ask the user to choose a codepage (at least there is such an LOV
    the user should/must pick on)

    For me (Win10) its initial value is Cp1252.
    Q: For what reason shall one change this value?

    Regards
    Andre

    • thatjeffsmith

      That’s a system property used for all generated files..I believe it defaults to that based on your OS/environment.

      I would go into preferences and change the global default to UTF-8

  2. 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.

  3. 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.

    Thanks,
    ~Dietmar.

    • 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