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

36 Comments

  1. “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!

    hi Jeff, when this function be available, I am on version 23.1 but don’t have it yet. as DBA, I need this functions for backup/restore/transport for db refresh.
    thanks
    Jiulu

    • For the moment you just need to loop through your rest enabled schemas using ORDS_EXPORT.EXPORT_SCHEMA()

  2. For clarity: in my example above in step 5 the unaltered script from the first database is run against the second database. In the second database the module was not dropped first.

    Extra I performed a different, second test.
    Now in the second database I first drop the entire REST module and then again I run the export script from the first database against the second database.
    Upon completion I export the rest definitions from the second database and make a file compare against the script I ran.
    Again these are different in order.

    I hope this extra testing helps in reproduction of this behavior

    • Exporting a module in SQLDev Web on DB1 running ORDS 23.2 and using that to put into DB2 running ORDS 23.3

      Export from DB2

      Compare the files – handlers and everything else are the same.

    • Great that both exports match.

      The question that arises is that was there a code change to make the ordering specific or did the underlying queries reported in the same ordering by accident?

      Otherwise stated: in the code of the export of the module is there an explicit ordering by module, template, handler, parameter?

  3. For clarity: in my example above in step 5 the unaltered script from the first database is run against the second database. In the second database the module was not dropped first.

    Extra I performed a different, second test.
    Now in the second database I first drop the entire REST module and then again I run the export script from the first database against the second database.
    Upon completion I export the rest definitions from the second database and make a file compare against the script I ran.
    Again these are different in order.

  4. How do I get an output that is sorted consistently?

    I have a files template with 2 handlers: Delete and Post.
    These endpoints are implemented on 2 databases.

    When I use the SQL Developer to export then in one export the Delete template is first and the Post is second, however in the second export the Post handler is first exported.

    This make a diff very difficult.
    Also for Git it seems that exports are different, while only the export order is different.

    • On both database: ORDS REST Data Services 21.4.3.r1170405

      In SQL-Developer exporting on 2 different days of the same Database / ORDS gives different ordering

    • That’s 2 years old, but ok

      While I try to find you an answer, if you drop and recreate the module using the desires order, does the export still differ?

    • I can reproduce it in these steps:
      1) Start SQL-Developer (version 32.1.0.097)
      2) Database 1: Rest Data Services -> Modules -> Rest-Definition -> Save to File
      3) Exit SQL-Developer (so that nothing could stay in the cache)

      4) Start SQL-Developer
      5) Database 2 connection: run script from step 2
      6) Exit SQL-Developer (so that nothing could stay in the cache)

      7) Start SQL-Developer
      8) Database 2: Rest Data Services -> Modules -> Rest-Definition -> Save to File
      9) Compare scripts from step 2 and 8.
      In my case they are different.
      The orde of the templates is Ok, but the order of the Handlers differs.
      In one file the PUT is first and in the order the GET is first.

      I hope this helps

  5. any chance to get ORDS_METADATA.ORDS_EXPORT.EXPORT_SCHEMA(‘SCHEMA’)

    • You have that ability now

      SQL> set long 10000
      SQL> select ords_export.export_schema(
      2 p_include_enable_schema => FALSE,
      3 p_include_privs => TRUE,
      4* p_include_oauth => TRUE);

      ORDS_EXPORT.EXPORT_SCHEMA(P_INCLUDE_ENABLE_SCHEMA=>FALSE,P_INCLUDE_PRIVS=>TRUE,P_INCLUDE_OAUTH=>TRUE)

      ________________________________________________________________________________________________________________________________________________________________________________________
      ___________________________________________________________________

      -- Generated by ORDS REST Data Services 23.3.0.r2471836
      -- Schema: HR Date: Mon Nov 06 12:51:22 2023
      --

      DECLARE
      l_roles OWA.VC_ARR;
      l_modules OWA.VC_ARR;
      l_patterns OWA.VC_ARR;

      BEGIN

      ORDS.DEFINE_MODULE(
      p_module_name => 'cody',
      p_base_path => '/cody/',
      p_items_per_page => 25,
      p_status => 'PUBLISHED',
      p_comments => NULL);

      ORDS.DEFINE_TEMPLATE(
      p_module_name => 'cody',
      p_pattern => 'something',
      p_priority => 0,
      p_etag_type => 'HASH',
      p_etag_query => NULL,
      p_comments => NULL);

      ORDS.DEFINE_MODULE(
      p_module_name => 'date',
      p_base_path => '/date/',
      p_items_per_page => 25,
      p_status => 'PUBLISHED',
      p_comments => NULL);

      ORDS.DEFINE_TEMPLATE(
      p_module_name => 'date',
      p_pattern => 'right_now',
      p_priority => 0,
      p_etag_type => 'HASH',
      p_etag_query => NULL,
      p_comments => NULL);

      ORDS.DEFINE_HANDLER(
      p_module_name => 'date',
      p_pattern => 'right_now',
      p_method => 'GET',
      p_source_type => 'json/item',
      p_items_per_page => 25,
      p_mimes_allowed => NULL,
      p_comments => NULL,
      p_source =>
      'select systimestamp, sysdate from dual');

      ORDS.DEFINE_MODULE(...

    • Hi , Thanks for the answer … but my question was
      will it be possible to

      connect scott/tigger
      select ords_export.export_schema(p_schema=> 'HR') from dual;

      thanks

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

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

  7. Hesham Abu Elenain Reply

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

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

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

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

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

    • 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;
      /

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

  10. 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?

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

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

    • 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

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

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

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

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

Reply To Dietmar Aust Cancel Reply