OracleORDS–Quickstart fürEntwickler DietmarAust Opal-Consul=ng,Köln www.opal-consul=ng.de DietmarAust ► Dipl.-Inform.DietmarAust,FreelanceConsultant ▪ Master'sDegreeinComputerScience(MSCS) ► BuildingOraclebasedWebApplica=onssince1997 ▪ Portal,Forms,Reports,OWAToolkit,nowAPEX! ► 1997-2000:ConsultantatOracleGermany ► Since09/2000:FreelanceConsultant,Since2006–APEX only! ► Blog:h\p://daust.blogspot.com/ ► RegularpresenteratOracleconferences(ODTUG,DOAG, OOW) ► AuthoroftheJasperReportsIntegra=ontoolkit ▪ h\p://www.opal-consul=ng.de/tools Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 2 DietmarAust ► 2015DatabaseDeveloperoftheyearintheORDScategory Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 3 Agenda Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 4 Agenda ► WhatisREST? ► WhatisORDS? ▪ ComponentsandArchitecture ► ManagementoftheRESTdefini=onswithSQLDeveloperandtheAPI ► UseCases ▪ Naviga=on/Links/Filter/Sor=ng/Parameter(Input/Output) ► Security ▪ Authen=ca=onandAuthoriza=on Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 5 WhatisREST? Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 6 WhatisREST? Defini=on ► Itisanarchitecturalstyleforapplica=ons,neitheraprotocolnoraW3C standard ► REST:=Representa=onalStateTransfertermcoinedin2000byRoyFielding ▪ h\ps://en.wikipedia.org/wiki/Representa=onal_state_transfer ► Characteris=cs: ▪ ▪ ▪ ▪ Stateless(100%oftheapplica=onstateismanagedbytheclient) Basedontheh\pprotocol Highlyscaleable RESTusesh\pmethods(POST,PUT,GET,DELETE,…)toimplementCRUD opera=ons(Create/Read/Update/Delete) ► Why? ▪ Lightweightalterna=vetoRPC(RemoteProcedureCalls)andotherWebServices (SOAP,WSDL,…) ▪ IncreasinglypopularthroughAPIsprovidedbyGoogle,Facebook,Twi\erand others. Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 7 WhatisREST? Ressources ► Ressourcesprovideservicesandareuniquely iden=fyable ▪ h\p://api.example.com/customers/ ▪ h\p://api.example.com/customers/1234 ▪ h\p://api.example.com/customers/1234/orders/ ► Mul=pleURIscanpointtothesameressource: ▪ h\p://example.org/NewOrleans/traffic/I10 ▪ h\p://example.org/traffic/NewOrleans/I10 ► Wemodeltheressource,nottheac=on! ▪ ▪ ▪ ▪ ▪ Useofnounsinpluralform PUTh\p://example.com/accounts/12345 PUTh\p://example.com/accounts/edit/12345 POSTh\p://example.com/accounts/ POSTh\p://example.com/accounts/addaccount Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 8 WhatisREST? Methods ► Methodsimplementaspecificopera=on ▪ Uniformopera=onsforallressources ▪ GET,POST,PUT,DELETE,OPTIONS,HEAD Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 9 WhatisREST? Methods ► Communica=onofsuccessanderror messagesthroughstandardHTTPResponse codes1xx,2xx,3xx,4xx,5xx ▪ h\p://www.restapitutorial.com/ h\pstatuscodes.html# Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 10 WhatisREST? Representa=ons ► Representa=onsdeterminehowtheanswerwill beinterpreted ▪ XMLrepresenta=onusingmime-type:text/xml ▪ JSONrepresenta=onusingmime-type: applica=on/json ► Asingleressourcecanprovidemul=pledifferent representa=ons ▪ JSON,XML,CSV… ▪ Therightrepresenta=onisac=vely„nego=ated“ ▪ Theclientsendsalistofpreferredmime-types– theserverrespondswiththebestanswerand sendsthechosenmime-typeinthe„ContentType“h\pheader. Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 11 WhatisORDS? Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 12 WhatisORDS? ► OracleRestDataServices(ORDS) ▪ MiddlewareJ2EEcomponentinthe applica=onserver(WLS,Glassfish,Tomcat) ▪ TranslatesURLsintoacallinthedatabase (eitherselectorstoredprocedurecall) ► Threemajorusecases ▪ SupportforOWAtoolkitapplica=ons(will replacemod_plsql) ▪ OracleApplica=onExpress(APEX) ▪ RESTfulWebservices Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 13 WhatisORDS? TheHistory Version Date Description 1.0 2010 First release as Oracle APEX Listener with with support for OWA toolkit used by APEX 1.1 2011 First release with REST support for JSON, Microdata, CSV, Pagination. Also added FOP 2.0 2012 OAuth2 support, Integrated with APEX, Multi Database, SQL Developer integration 2.0.5 2013 Added support for Oracle Pluggable Databases (12c) 2.0.6 2014 Renamed to Oracle REST Data Services to emphasize REST commitment, integration with APEX 4.2 in SQL Workshop 2.0.8 2014 Added REST Filtering 3.0.0 2015 REST AutoTable, NoSQL, DB12 JSON, Bulk loading over REST,… Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 14 WhatisORDS? APEXRESTvs.ORDS_METADATARESTSupport ORDSiscurrentlytransi2oningawayfromthedependencyonAPEX ► ORDSrequiresarepositorytostorethewebservicedefini=ons ► ORDS2.0 ► ORDS3.0 ► Schemas ► Schemas ▪ APEX_040200/APEX_050000 ▪ APEX_LISTENER ▪ APEX_REST_PUBLIC_USER ► Configurationusing ▪ APEXSQLWorkshop Oracle ORDS – Quickstart für Entwickler ▪ ORDS_METADATA ▪ ORDS_PUBLIC_USER ► Configurationusing ▪ SQLDeveloper ▪ PL/SQLAPI 26.04.2016 Page 15 WhatisORDS? APEXRESTvs.ORDS_METADATARESTSupport ► APEXRESTsupportintheAPEXSQLWorkshop Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 16 WhatisORDS? APEXRESTvs.ORDS_METADATARESTSupport ► Twodifferentrepositories:APEXRESTandORDS_METADATAREST ▪ TypicallybothareinstalledwhenusingAPEX5 ▪ APEX5requiresthatyourunapex_rest_config.sqlwhichcreatesAPEX_LISTENER andAPEX_REST_PUBLIC_USER ► TheFuture? ▪ NewfeatureswillonlybeaddedtoORDS_METADATAREST ► InwhichrepositorydoIcreatethewebservice? ► APEXREST ▪ Integra=onwithAPEXSession ► ORDS_METADATAREST ▪ ThenewRESTfunc=onalitybasedonthenewmetadatarepository ▪ PL/SQLAPIs(defineandoauth) Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 17 WhatisORDS? Architecture ► HowisaRESTwebservicecallactuallyprocessed? 2. ORDS maps to “EMPLOYEES” SQL http://myhost/ords/employees/7536 SELECT*FROMEMP WHEREEMPNO=:b1 1. Browser RESTful get request 5. JSON Browser Oracle REST Data Services ORDS Runs in WLS, Tomcat, Glassfish container Oracle ORDS – Quickstart für Entwickler 3. SQ L Ca ll ove 4. DB returns JD r JDB C BC Results Oracle DB 26.04.2016 Page 18 WhatisORDS? Architecture ► MapandBind: ▪ ImplicitlyaccessallURIparametersintheURLorinthebody(e.g.POSTrequest) − Happensautoma=cally,evenJSONParameters(usingContent-Type: applica=on/json) − AllparametersareSTRINGs ▪ Explicitparameterspossible − Properdatatypes − Accessheadervariables Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 19 WhatisORDS? Architecture ► TransformtoJSON ▪ ReturnJSONbyusingbindvariables(declara=vely)orcreatetheJSONmanually yourself ▪ Declara=veFormats:JSONorCSV,manuallyyoucancreateanything ▪ Canchangetheh\preturncodeorseth\pheadervariables Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 20 WhatisORDS? Architecture ► Connec=onPooling ▪ ThetargetOracleuser(schema)isac=vatedusingaProxyConnect ▪ TheuserORDS_PUBLIC_USERconnecttothedatabaseandthenswitchesitsiden=tyto thetargetOracleuser ▪ Thusweneedfewerconnec=onpoolsandeachconnec=onpoolbecomessmallersince mul=pleOracleuserscanbeservedwiththesameconnec=onpool ▪ EachSQLandPL/SQLstatementisexecutedusingthetheoriginalusersession Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 21 ManagementoftheREST defini=onswithSQL DeveloperandtheAPI Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 22 RESTDefini=ons ManagementwithSQLDeveloper ► ManagementoftheRESTdefini=onswithSQLDeveloper Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 23 RESTDefini=ons ManagementwithSQLDeveloper ► ManagementoftheRESTdefini=onswithSQLDeveloper =>connecttoORDSrepositorythroughJ2EEapp jdbc http/https SQL Developer Oracle REST Data Services authenticate and authorize user APEX_050000 jdbc Local filestore, Admin user needs role „SQLDeveloper“ ORDS_METADATA Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 24 RESTDefini=ons ManagementwithSQLDeveloper ► ManagementoftheRESTdefini=onswithSQLDeveloper Schema/Workspace will decide between APEX REST and ORDS REST ► Createuseroncommandline ##UsertomanageRESTdefinitionsinSQLDeveloper java-jarords.waruserdietmar.aust"SQLDeveloper” Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 25 RESTDefini=ons ManagementthroughtheAPI ► ManagementthroughthePL/SQLAPI ► Simplefile…containsallresource templatesandmethodsforamodule inasingleplace ► Firstwedeletetheexis=ngdefini=on andthenwerecreateitfromscratch ► Verywellsuitedforscriptbased deployment ► APIreference(PackageORDS): h\p://docs.oracle.com/cd/ E56351_01/doc.30/e56293/ ords_ref.htm#AELIG90180 Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 26 RESTDefini=ons Handler-Types ► Handler–types ▪ ▪ ▪ ▪ ▪ ▪ SQLQuery(legacy)(source_type_query) SQLQuery(eineZeile)(legacy)(source_type_query_one_row) Feed(source_type_feed) Collec=on(source_type_collec=on_feed) Collec=onItem(source_type_collec=on_item) PL/SQL(source_type_plsql) − Generateeverythingmanuallymyself ▪ Media(source_type_media) − Binaryrepresenta=ons Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 27 RESTDefini=ons Handler-Typen ► Handler–Typ:SQLQuery (legacy)(source_type_query) ▪ Containsalinktoitself selectemp.* fromemp Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 28 RESTDefini=ons Handler-Typen ► Handler–TypSQL:SQLQuery(onerow) (legacy)(source_type_query_one_row) selectemp.* fromemp whereempno=:empno Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 29 RESTDefini=ons Handler-Typen ► Handler–TypSQL:Feed (source_type_feed) selectemp.* fromemp Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 30 RESTDefini=ons Handler-Typen ► Handler–TypSQL:Collec=on (source_type_collec=on_feed) selectemp.* fromemp ► Completeincl.naviga=onlinks: ▪ ▪ ▪ ▪ ▪ Self Describedby First(onlybypagina=onorlimit) Next(onlybypagina=onorlimit) Previous(onlybypagina=onorlimit) Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 31 RESTDefini=ons Handler-Typen ► Handler–TypSQL:Collec=onItem (source_type_collec=on_item) ▪ Containsalinktothecollec=onitself selectemp.* fromemp whereempno=:empno Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 32 UseCases Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 33 UseCases EnableRESTinSchema Firststep:EnableRESTcapabili=esforaschemainthedatabase ► UsingtheGUI(right-clickontheconnec=on) ► Usingthecommandline/API BEGIN ORDS.ENABLE_SCHEMA(p_enabled=>TRUE, p_schema=>'ORDSTEST', p_url_mapping_type=>'BASE_PATH', p_url_mapping_pattern=>'ordstest', p_auto_rest_auth=>FALSE); COMMIT; END; Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 34 UseCases Naviga=onandLinks Implementnaviga=onlinkstonavigatebetweenthedifferentressources ► Linksusedfor: ▪ Linktothecurrentrow ▪ Linktoanimageoranembeddedlist(ressourceorderscancontainalisttothe relatedorderitems) ▪ Linktotheparent ▪ Linktoother“siblings”usingrela=vepaths,e.g.../.. Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 35 UseCases ModifyressourcesusingPOST,PUTandDELETE ModifyressourcesusingPOST,PUTandDELETE ► Createanewressource(POST) ► Updatearessource(PUT) ► Deletearessource(DELETE) Demo Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 36 UseCases PL/SQLHandler–implementeverythingyourself ► RendereverythingmanuallywithPL/SQLyourself ▪ GETwithTypPL/SQL ▪ UseOWAToolkittowriteitout − APEX_JSON,PL/JSON − 12cJSONFunk=onen Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 37 UseCases Media-Ressourcen ► Displayanimage ▪ GEThandler(typeMediaressource) selectmimetype,product_image fromdemo_product_info whereproduct_id=to_number(:product_id) Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 38 UseCases Pagina=on ► Pagina=on Allowstopaginatethroughtheresultset Onlyapplicableforhandlertypecollec=on(source_type_collec=on_feed) h\p://docs.oracle.com/cd/E56351_01/doc.30/e56293/develop.htm#BABIHBDH Pa\ern:GEThttp://<HOST>:<PORT>/ords/<SchemaAlias>/ <ObjectAlias>/?offset=<Offset>&limit=<Limit> ▪ Alsocreatesthelinks“NEXT”,“PREVIOUS”und“FIRST”mit ▪ ▪ ▪ ▪ ► Example: Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 39 UseCases FilterandSort ► ResultSetFiltering ▪ QuerySyntaxtofilteracollec=on ▪ Onlyapplicableforhandlertypecollec=on(source_type_collec=on_feed) ▪ h\p://docs.oracle.com/cd/E56351_01/doc.30/e56293/develop.htm#AELIG90104 ► Sor=ng/OrderBy ▪ QuerySyntaxtosortacollec=on ▪ Onlyapplicableforhandlertypecollec=on(source_type_collec=on_feed) ▪ h\p://docs.oracle.com/cd/E56351_01/doc.30/e56293/develop.htm#AELIG90104 Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 40 UseCases Parameter ► Inputparameters ▪ Implicit − AllvariablesthatarepassedintheURLorinthecontentbody − :content_type(varchar2,z.B.applica=on/json) − :body(alsBLOB) ▪ Explicit(usingdeclara=veparameters) − Allregularh\pheadervariables − AllvariablesthatarepassedintheURLorinthecontentbody Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 41 UseCases Parameter ► Outputparameters ▪ ONLYExplicit(usingdeclara=veparameters) − Returnaresponsebysezngasimplebindvariablewhichisthen convertedautoma=callytoJSONbyORDS(:empno:=99) − Setanh\pResponseCode(Pseudo-Header:X-APEX-STATUS-CODE), e.g.:status:=201,403 − RedirecttoadifferentURL(Pseudo-Header:X-APEX-FORWARD) − Setah\pheadervariable Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 42 Demo Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 43 Security Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 44 Security ► Differentwaysofauthen=ca=ngthecurrentuser ▪ Authen=ca=onusingtheintegratedpasswordstore(“creden=als”file–just recommendedfordevelopmentandtestenvironments) ▪ Authen=ca=onusingtheapplica=onserver(authen=ca=onisdelegated,e.g.to Glassfish) ► Authen=ca=onusingOAUTH2 ▪ Establishedstandard–usedwidely ▪ Basicallycontrolsa“session”betweenclient/serverandyous=llneedto authen=catewiththeappserver ► MoredetailstousingOAUTH2withORDS:Ar=clesfromCarstenCzarski(in German,butcanbetranslatedusingGoogleTranslator) ▪ h\p://json-rest-oracledb.blogspot.de/2015/12/vorher-anmelden-bi\eauthen=fizierung.html ▪ h\p://json-rest-oracledb.blogspot.de/2016/01/ords-und-3-legged-oauth-sogehts.html Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 45 Security ► Authoriza=on:=Protectaccesstoressourcesforcertainuserroles ► CreateaROLEfirst(onlypossiblethroughtheAPI) ► CreateaprivilegetoprotectafullmoduleorjustaURIpa\ern ► Cannotrequireprotec=onjustforaspecificmethod,e.g.limitaccesstoPUT, POST,DELETEandallowGETforeverybody. ▪ Perhapsusingtwomodules: − /public/departments/(implementGEThandler) − /protected/departments/(implementGET,POST,PUT,DELETE handler) Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 46 Security Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 47 Demo Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 48 Debugging/ Troubleshoo=ng Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 49 Debugging/Troubleshoo=ng ► Displayerrormessagesdirectlyinthebrowser(onlyuseondevelopment/ testenvironments,notproduc=on!) ▪ Modifydefault.xml <entrykey="debug.debugger">true</entry> <entrykey="debug.printDebugToScreen">true</entry> ► Fullloggingwithalldetailsusingjava.u=l.logging ▪ h\ps://cdivilly.wordpress.com/2013/03/08/configuring-logging-in-oracleapplica=on-express-listener-2-0-1/ Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 50 Tools Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 51 Tools ► Commandlinetool:curl-h\ps://curl.haxx.se/ ► AdvancedRESTClient(forGoogleChrome) ▪ h\ps://chrome.google.com/webstore/detail/advanced-rest-client/ hgmloofddffdnphfgcellkd{{jeloo ► PLSQLlogger ▪ h\ps://github.com/OraOpenSource/Logger Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 52 FurtherReading Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 53 FurtherReading ► Slidestodownload:h\p://daust.blogspot.de ► Wikipedia:h\p://en.wikipedia.org/wiki/Representa=onal_State_Transfer ► REST–APIDesign ▪ h\p://www.vinaysahni.com/best-prac=ces-for-a-pragma=c-res|ul-api ▪ h\ps://www.thoughtworks.com/de/insights/blog/rest-api-design-resourcemodeling ▪ h\p://blog.octo.com/en/design-a-rest-api/ ▪ h\ps://res|ul-api-design.readthedocs.org/en/latest/intro.html ▪ h\p://blog.mwaysolu=ons.com/2014/06/05/10-best-prac=ces-for-be\er-res|ulapi/ ► CarstenCzarskiBlogaboutREST:h\p://json-rest-oracledb.blogspot.de/(can betranslatedusinggoogletranslator) Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 54 WeitereInforma=onsquellen ► RESTfulWebServices,byLeonardRichardsonandSamRuby,availablefrom O’ReillyMediaath\p://oreilly.com/catalog/9780596529260/ ► Thesource:h\p://www.ics.uci.edu/~fielding/pubs/disserta=on/top.htm mostlychapters5and6 ► Anice14minutevideointroduc=on: h\p://www.youtube.com/watch?v=YCcAE2SCQ6k ► HTTPspec:h\p://tools.ie|.org/html/rfc2616 ► URIspec:h\p://tools.ie|.org/html/rfc3986 ► JSONformat:h\p://json.org/ Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 55 1-DayDeveloperWorkshop 1TagesEntwicklerWorkshopORDS ► InKölnam11.Juli ► Themen ▪ ▪ ▪ ▪ ▪ Installa=on/Konfigura=onfürAPEX/mod_plsqlundREST „RealWorld“Projekt(kompliziertesBeispiel) VerschiedeneUseCasesmitvielenHands-Ons Authen=fizierungmitWLS,GlassfishundTomcat OAUTH2Implemen=erung Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 56 Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 57 Contact DietmarAust Opal-Consul=ng,Köln www.opal-consul=ng.de daust.blogspot.com dietmar.aust@opal-consul=ng.de Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 58 Auto-REST Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 59 Auto-REST QuicklyAuto-RESTenableadatabasetableorview ► Pros: ▪ Fastandeasy ▪ CandosomecleverthingsusingINSTEAD_OFtriggersontheview ► Cons: ▪ Can’tusetheauthen=cated:current_uservariabletofigureouttheuseriden=ty whichisrequiredforloggingpurposes ▪ Currentlyafeatureisunderdiscussiontomake:current_useravailablethrough sys_contextsimilartoapex. Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 60 UseCases EnableRESTinSchema EnableRESTcapabili=esforatableorview ► UsingtheGUI(right-clickonthetable/view) ▪ „EnableRESTService“ ► Usingthecommandline/API BEGIN ORDS.ENABLE_OBJECT(p_enabled=>TRUE, p_schema=>'ORDSTEST', p_object=>'DEPT', p_object_type=>'TABLE', p_object_alias=>'dept', p_auto_rest_auth=>FALSE); COMMIT; END; Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 61 Demo Oracle ORDS – Quickstart für Entwickler 26.04.2016 Page 62