Updated 30 March 2023

You’re using Oracle REST Data Services, AKA ORDS.

You’ve written some REST APIs or sometimes referred to as RESTful 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.

0. Use SQL Developer Web

A Single Module

Proceed to the REST Workshop and your list of Modules.

What you want is on the ‘kebab’ button in the right top corner of each module ‘card’

It’ll include required roles and privileges…

You can then copy that to the clipboard or download it as a file.

You should be source controlling these just like any other bits of application code you have.

All the modules/Your Entire Schema

New for ORDS 23.1 – simply use this button:

You’ll get a slider with a code preview and a Download button.

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 Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

21 Comments

  1. Avatar

    Hi Jeff,

    With the sqlcl “rest export” command is there a way to suppress the header comments? I want to check the source code into Git without the current date/timestamp info in those comments.

    Thanks!
    Jack

    • thatjeffsmith

      No, so I would write some shell script or python to strip out the comments at the top of the file. Or just ignore the whitespace deltas in your diffs.

      It’s not SQLcl doing that, it’s burned into the ORDS_METADATA PL/SQL API itself.

  2. Avatar
    Hesham Abu Elenain Reply

    Where can I find the exported file using SQLcl? Or how to export it to file?

  3. Avatar

    Hi Jeff,

    Is there a way to import back this file into the same schema using sqlplus? We put our code in git and we take the source from there when deploying to prod and we actually need to open apex web administrator to export/import the rest services and doing via sqlplus would save us time.

    Thank you

    • thatjeffsmith

      They’re plsql scripts, of course you can run those thru sqlplus.

      But sqlcl has a command to additional export them if you want to save some keystrokes.

  4. Avatar
    ENOCK KOLOO Reply

    Hi,

    its a bit confusing but whats if you want to use REST services to export (json/excel etc) dataset from oracle db.

    • thatjeffsmith

      I think you’re confused because this post is how to export your API definitions.

      Any SQL based API by default will return JSON.

      If you want Excel you’ll need to write some plsql code.

    • Avatar

      Hi Jeff,

      Searching a little more in the web, i found that by running the following would allow me to execute an ords script through sqlplus and it did compile fine, however, when i go to our apex web manager -> sql workshop -> restful services -> ords restful services -> click the module i made changes, the changes i made are not present. Would you mind to help me to make this script work please?

      Thank you in advance

      BEGIN
      ORDS.ENABLE_SCHEMA;
      COMMIT;
      END;
      /

    • thatjeffsmith

      Yeah, use SQL Developer Web’s REST console for managing your modules, not APEX. If you want to use APEX, that’s fine, but if you run into problems, you’ll need to work with the APEX team. Also, have you tried SQLcl? It’s much nicer and has better support for ORDS than SQLPlus.

  5. Avatar

    Jeff,
    This comment:
    “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!”

    What the latest status on this functionality making into an ORDS version?

  6. Avatar

    Hi Jeff,

    I’m using SQL Developer version 20.2.0.175 and as far as I can tell autorest enabled objects are still not included and cannot be REST exported like modules.

    Is there a timeline when this functionality will be implemented?

    Thanks and regards,

    I

  7. Avatar

    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

  8. Avatar
    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.

  9. Avatar
    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