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.
21 Comments
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)