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?
There’s a few ways.
0. Use SQL Developer Web
A Single Module
Proceed to the REST Workshop and your list of Modules.
It’ll include required roles and privileges…
You can then copy that to the clipboard or download it as a file.
All the modules/Your Entire Schema
New for ORDS 23.1 – simply use this button:
1. Use SQL Developer
On the Module, mouse right click.
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…
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;
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.
36 Comments
“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()
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?
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.
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.
Can you confirm versions of ORDS for both databases?
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
any chance to get ORDS_METADATA.ORDS_EXPORT.EXPORT_SCHEMA(‘SCHEMA’)
You have that ability now
SQL> set long 10000SQL> 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
No, you’ll have to login as HR, and then run that.
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.
Where can I find the exported file using SQLcl? Or how to export it to file?
Thanks I got it, using spool 🙂
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.
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.
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?
I’ll have to check on Monday…
Did you get a chance to track this item down? I’m just doing a bit of planning for extract and version control workflows.
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
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
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.
Is there a way (planned?) to extract all modules from all, or some, schemas?
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.
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)