Oracle ORDS使用SQL Developer发布Webservice

版权声明:http://www.sunansheng.com/ https://blog.csdn.net/sunansheng/article/details/74455546

Assumptions and Comments

This article assumes the following.

  • You already have a functioning installation of ORDS.
  • The paths for the ORDS configuration match those from the ORDS installation article listed above.
  • You have an Oracle database available. In this article I will be using a 12c database, but it works just the same with an 11g database.
  • You have a way to call the web services. Some GET web services can be called from a browser, but the other methods require some coding, or a REST client. I used "curl" and the "Advanced REST client" extension for Chrome.
  • You already have an understanding of the various pieces of ORDS functionality. This article will not teach you about this, but the linked articles will.

Enable ORDS for a Schema

Right-click on the connection and select the "REST Services > Enable REST Services..." menu option.

Enable ORDS for a Schema : Menu

Check the "Enable schema" checkbox and alter the schema alias if you don't want to expose the schema name in the URL. In this case we will ignore authorization. Click the "Next >" button.

Enable ORDS for a Schema : Specify Details 1

If you want to see the code that will run to enable ORDS for the schema, click the "SQL" tab.

Enable ORDS for a Schema : Specify Details 2

Click the "Finish" button.

Enable ORDS for a Schema : Specify Details 3

Click the "OK" button.

Enable ORDS for a Schema : Complete

Enable AutoREST for an Object

Right-click on the object and select the "Enable REST Service..." menu option.

Enable AutoREST for an Object : Menu

Check the "Enable object" checkbox and specify an object alias if you don't want to expose the object name in the URL. In this case we will ignore authorization. Click the "Next >" button.

Enable AutoREST for an Object : Specify Details 1

If you want to see the code that will run to enable AutoREST for the object, click the "SQL" tab.

Enable AutoREST for an Object : Specify Details 2

Click the "Finish" button.

Enable AutoREST for an Object : Specify Details 3

Click the "OK" button.

Enable AutoREST for an Object : Complete

Create Basic RESTful Web Service

To let SQL Developer connect to your ORDS server, you will need to create a user through ORDS with the "SQL Developer" role.

cd /u01/ords
$JAVA_HOME/bin/java -jar ords.war user tim_hall "SQL Developer"

The credentials are stored on the ORDS server in the following location.

cat /u01/ords/conf/ords/credentials

With the ORDS user in place, you can now proceed to develop web services using SQL Developer.

Select the "View > REST Data Service > Development" menu option.

RESTful Web Service : Menu

On the resulting "REST Development" pane, click the connect button.

RESTful Web Service : Connect

If you don't already have a connection, click the "+" button.

RESTful Web Service : Connections

Enter the connection details, including the "Username" configured on the ORDS server, then click the "OK" button.

RESTful Web Service : Connection Details

Click the "OK" button.

RESTful Web Service : Connections

Enter the ORDS credentials and click the "OK" button.

RESTful Web Service : Authentication

Right-click on the "Modules" tree node and select the "New Module..." menu option.

RESTful Web Service : New Module

Enter the module details and click the "Next >" button.

RESTful Web Service : Module

Enter the template details and click the "Next >" button.

RESTful Web Service : Template

Enter the handler details and click the "Next >" button.

RESTful Web Service : Handler

Click the "Finish" button.

RESTful Web Service : Finish

Expand the tree and click on the GET handler. Click on the "Worksheet" tab. Enter the query associated with the GET handler.

RESTful Web Service : Handler Query

All changes are performed locally, so changes need to be uploaded to the server to take effect. Right-click on the module and select the "Upload..." menu option.

RESTful Web Service : Upload

Click the "OK" button.

RESTful Web Service : Complete

The web service should now be available from your ORDS.

http://ol7-121.localdomain:8080/ords/pdb1/testuser2/testmodule1/emp/

If you want to save a local copy of the configuraton, right-click on the tree and select the "Save As..." menu option, then name and save the zip file. At the time of writing, this configuration is only a XML representation of the configuration, not the SQL used to create it.

RESTful Web Service :

ORDS Administration

You will need to create an ORDS user with the "Listener Administrator" role to let SQL Developer connect to your ORDS server for administration.

cd /u01/ords
$JAVA_HOME/bin/java -jar ords.war user tim_hall "SQL Developer","Listener Administrator"

The credentials are stored on the ORDS server in the following location.

cat/u01/ords/conf/ords/credentials

With the ORDS user in place, you can now administer ORDS using SQL Developer.

Select the "View > REST Data Service > Administration" menu option.

ORDS Administration Menu

Connect as you did for the developer pane. All configuration changes are local and need to be uploaded to the ORDS server for them to take effect.

ORDS Administration

SQLcl REST Command

The SQLcl utility includes a REST command that can be quite useful.

$ ./sql testuser1/testuser1@pdb1

SQLcl: Release 4.2.0.16.175.1027 RC on Fri Jul 01 16:13:22 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> rest help
REST
------

REST allows to export ORDS 3.X services.

             REST export                  -      All modules
             REST export     -      Export a specific module
             REST export   -      Export a specific module related to the given prefix
             REST modules                 -      List the available modules
             REST privileges              -      List the existing privileges
             REST schemas                 -      List the available schemas

SQL>

Let's get a list of the REST modules.

SQL> rest modules
NAME        PREFIX        STATUS    ITEMS_PER_PAGE
----------- ------------- --------- --------------
testmodule1 /testmodule1/ PUBLISHED 0
SQL>

Now we know the module name, we can export the definition.

SQL> rest export testmodule1
declare
  l_module_id number;
  l_template_id number;
  l_handler_id number;
  l_parameter_id number;
begin

  l_module_id := ORDS_METADATA.ORDS_SERVICES.create_module( p_name => 'testmodule1' , p_uri_prefix => '/testmodule1/' , p_items_per_page => 0 , p_status => 'PUBLISHED' );
  l_template_id := ORDS_METADATA.ORDS_SERVICES.add_template( p_module_id => l_module_id, p_uri_template => 'emp/:empno' , p_priority => 0 , p_etag_type => 'HASH' );
  l_handler_id := ORDS_METADATA.ORDS_SERVICES.add_handler( p_template_id => l_template_id, p_source_type => 'json/query' , p_method => 'GET' , p_items_per_page => 0 , p_source => 'SELECT * FROM emp WHERE empno = :empno');

commit;
end;
SQL>
阅读更多

没有更多推荐了,返回首页