RESTful Web Services

2. RESTful Web Services Rick GreenwaldDirector, Product Management, Database Cloud

3. Program Agenda • Application development and the Database Cloud • RESTful Web Services and the Database Cloud • GET methods • DML methods • Security and RESTful Web Services

4. To fill a shape with an image. Use existing picture box, DO NOT delete and create new picture box. Right click on the shape. At the bottom of the submenu select “Format Shape” Select “Fill” at the top of the “Format Shape” dialog box. Select “Picture or Texture fill” from the options. And select “File” under the “Insert from” option. Navigate to the file you want to use and select “Insert” On the “Format” tab, in the Size group, click on “Crop to Fill” in the Crop tool and drag the image bounding box to the desired size DELETE THIS INSTRUCTION NOTE WHEN NOT IN USE Application Development and the Database Cloud

5. Oracle Database Cloud Service Application development • Oracle Application Express • Rapid application development • Declarative and extendable • Instant deployment • RESTful Web Services • Standard Web architecture • Stateless access to data and logic

6. RESTful Web Services

7. Oracle Database Cloud Service developmentRESTful Web Services • RESTful Web Services • An architecture standard for accessing information • Built on HTTP • Commonly used in dynamic languages, such as PHP • Called as a URI to an endpoint – SQL or PL/SQL • RESTful Web Services wizard • Builds RESTful Web Services to run against a Database Cloud Service • SQL Developer interface coming in next release

8. RESTful Web ServicesRESTful Web Services wizard • Module • Template • Name • Priority • Handler • Methods

9. GET method

10. RESTful Web ServicesMethods - GET • GET • Used for queries • Supports multiple source types • Query • Query One Row • Feed • PL/SQL • Media resource

11. RESTful Web ServicesGET method demos • Simple query • Query with parameter • Feed • Multiple parameter passing techniques

12. RESTful Web ServicesGET method demos • Conditional result set select empno, ename, deptno, job from emp where ((select job from emp where ename = :empname) IN ('PRESIDENT', 'MANAGER')) OR (deptno = (select deptno from emp where ename = :empname)) order by deptno, ename

13. RESTful Web ServicesPL/SQL formatting example DECLARE CURSOR getemps IS select * from emp where ((select job from emp where ename = :empname) IN ('PRESIDENT', 'MANAGER')) OR deptno = (select deptno from emp where ename = :empname) order by deptno, ename; deptname VARCHAR(30); deptloc VARCHAR(30); prevdeptno NUMBER;

14. RESTful Web ServicesPL/SQL formatting example BEGIN htp.htmlopen; htp.headopen; htp.title('Differential access based on security'); htp.headclose; htp.bodyopen;

15. RESTful Web ServicesPL/SQL formatting example FOR emprecs IN getemps LOOP IF emprecs.deptno != prevdeptno OR prevdeptno IS NULL THEN select dname, loc into deptname, deptloc from dept where deptno = (select deptno from emp where ename = emprecs.ename); IF prevdeptno IS NOT NULL THEN htp.print('</ul>'); END IF; htp.print('Department ' || deptname || ' located in ' || deptloc || '<p>'); htp.print('<ul>'); END IF;

16. RESTful Web ServicesPL/SQL formatting example htp.print('<li>' || emprecs.ename || ', ' || emprecs.job || ', ' || emprecs.sal); prevdeptno := emprecs.deptno; END LOOP; htp.print('</ul>'); htp.bodyclose; htp.htmlclose; END;

17. DML methods

18. RESTful Web ServicesDML • Only supports PL/SQL source type • Uses PUT, POST and DELETE methods • Rough mapping to CRUD • DELETE = delete • PUT = update • POST = insert (not idempotent)

19. RESTful Web ServicesDML demos • DELETE BEGIN DELETE FROM EMP WHERE EMPNO = :empid; END;

20. RESTful Web ServicesDML demos • UPDATE BEGIN UPDATE EMP SET SAL = :SAL WHERE EMPNO = :empid; END;

21. RESTful Web ServicesDML demos • INSERT DECLARE id EMP.empno%type; BEGIN INSERT into EMP(ename, sal, deptno) values(:ENAME, :SAL, :DEPTNO) returning empno into id; :location := id; commit; END; • Add X-APEX-FORWARD parameter • X-APEX-STATUS

22. Security and RESTful Web Services

23. RESTful Web ServicesSecurity • Database Cloud Service is schema-based • RESTful Services are executed as schema-owner • Can use first party authentication (APEX-based) or third-party authentication (OAUTH2-based)

24. RESTful Web ServicesSecurity with first-party authentication • Create user group in APEX • Create RESTful Service Privilege • Assign Service Privilege to RESTful modules

25. RESTful Web ServicesOAUTH2 • OAUTH authentication • Emerging standard for 3rdparty Web Service security • Utilizes 2 tokens • Request token • Access token • Request token is specific to a client application

26. RESTful Web ServicesOAUTH2 • Why OAUTH2? • Designed to allow a single authentication source to work with multiple applications • Each of those applications acts as a ‘man-in-the-middle’ • User connects to MitM, who is registered with the server for the endpoint • MitM contacts server, passing access token (obtained when registered) • Server authenticates user • Server passes access token to MitM • All subsequent communications through MitM • MitM never has credentials

27. RESTful Web ServicesOAUTH2 • Why OAUTH2? • Very useful for servers with many applications requesting access on behalf of users identified by the server • Please see documentation for details on using OAUTH2 with RESTful Web Services

28. Graphic Section Divider