tag:blogger.com,1999:blog-5215551487816981140.post4035577766723267321..comments2024-01-23T06:10:31.184-08:00Comments on ORA-00001: Unique constraint violated: Creating a REST web service with PL/SQL (and making pretty URLs for your Apex apps)Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.comBlogger33125tag:blogger.com,1999:blog-5215551487816981140.post-46815053084657100632021-05-11T08:25:26.592-07:002021-05-11T08:25:26.592-07:00Hi,
I tried to follow your example to create a pl...Hi, <br />I tried to follow your example to create a plsql REST webservice routine that select all tables from user_tables and generates a webservice to each table using, by example: <br />SELECT DBURIType(''/HR/DEPARTMENTS').getXML() FROM DUAL<br />but it didn't work.<br /><br />Do you have any suggestion or one example in this direction?bloghttps://www.blogger.com/profile/04955443165370942656noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-64746185049391228742017-06-21T08:07:12.605-07:002017-06-21T08:07:12.605-07:00I found out that POST variables are mapped to inpu...I found out that POST variables are mapped to input parameters in handle_request.<br />Example:<br />curl -s -k -D- -X POST --data 'a=b' URL<br /><br />You use procedure handle_request(p_path IN VARCHAR2 DEFAULT NULL, p_json_payload in varchar2 default '{}')Peter Veprekhttps://www.blogger.com/profile/17462900651506682445noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-90716001217406091322015-04-16T11:45:56.531-07:002015-04-16T11:45:56.531-07:00@Chopped4Skin: If you are using the Embedded PL/SQ...@Chopped4Skin: If you are using the Embedded PL/SQL Gateway (DBMS_EPG), that does not support https as far as I know.<br /><br />If you are using Apache and mod_plsql, then you would just set up an SSL certificate as normal, no changes to the PL/SQL code.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-41994852254580046562015-04-11T13:27:17.056-07:002015-04-11T13:27:17.056-07:00Thanks for this example Morten - looks excellent a...Thanks for this example Morten - looks excellent and it works. What happens if you want to make it https (as opposed to http). What changes does one need to make? I'm on Oracle 11.2.0.4<br /><br />ThanksAnonymoushttps://www.blogger.com/profile/10309485876512526229noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-20891651241050044762015-04-09T03:20:24.110-07:002015-04-09T03:20:24.110-07:00Often, I read "how call web service with pl/s...Often, I read "how call web service with pl/sql", but never "how extract parameters...".<br />I created the restful web service with pl/sql and embededd gateway apex, and i need to extract parameter written via UTL_HTTP.WRITE_TEXT....<br />Can you suggest me ?Davidnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-64341058963187045482014-03-03T11:02:59.707-08:002014-03-03T11:02:59.707-08:00The link for documentation refers to Oracle 9i. H...The link for documentation refers to Oracle 9i. Has anything changed ( advanced) in Oracle 11g? I am on 11g release 2 ( 11.2.0.3) <br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-58101528180617948482014-01-27T10:03:39.152-08:002014-01-27T10:03:39.152-08:00@Mikle: As described in the last part of my post, ...@Mikle: As described in the last part of my post, this will not work for PUT and POST requests, since mod_plsql does not send the posted data to the PathAliasProcedure.<br /><br />If you use my Thoth Gateway (mod_plsql for IIS), there is a configuration option you can enable to get access to the POST data.<br /><br />And of course, if you use Oracle's Apex Listener with Apex 4.2, you can use true RESTful Services directly via the listener.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-25678065357657964582014-01-23T02:40:15.035-08:002014-01-23T02:40:15.035-08:00Hi,
Is it possible to use DAD's parameters:
...Hi,<br /><br />Is it possible to use DAD's parameters: <br /><br />'PlsqlDocumentTablename', <br />'PlsqlDocumentPath', ...<br /><br />for POST,PUT methods?<br /><br />Thanks,<br />Mikleanother Miklenoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-53513576754752972332012-11-06T13:26:37.208-08:002012-11-06T13:26:37.208-08:00@Vijay:
1. No, you don't need Apex, this work...@Vijay:<br /><br />1. No, you don't need Apex, this works just using the PL/SQL Web Toolkit (OWA) and the Embedded PL/SQL Gateway (DBMS_EPG).<br /><br />2. You should use the SYS user to set up the DAD settings, but use another schema (I've used a schema called "devtest") to install your PL/SQL code.<br /><br />To troubleshoot the "page not found" errors, you should enable the DebugStyle error reporting. See http://daust.blogspot.com/2008/04/troubleshooting-404-not-found-error-on.html for details.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-81749706793922296142012-11-05T08:22:36.158-08:002012-11-05T08:22:36.158-08:00Hi,
How can make it work without using APEX?
Tha...Hi,<br /><br />How can make it work without using APEX?<br /><br />Thanks,<br />VijayVijayhttps://www.blogger.com/profile/00703746263053933891noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-32118559166902685312012-11-05T08:16:53.500-08:002012-11-05T08:16:53.500-08:00Thanks for sharing this new concept.
I tried to r...Thanks for sharing this new concept.<br /><br />I tried to run the same procedure with some table name change and iam able to compile all the procedures without any errors.<br /><br />When i try to run the URL i am getting page not found error. <br /><br />I have few questions for you. <br /><br />1. Do i need to have APEX installed ?<br />2. I am running all these under system schema and using XE database. <br /><br /><br />Thanks,<br />VijayVijayhttps://www.blogger.com/profile/00703746263053933891noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-66611733951083957532012-10-24T02:45:50.990-07:002012-10-24T02:45:50.990-07:00@Learco: It depends on whether the Apex Listener s...@Learco: It depends on whether the Apex Listener supports the "PathAlias" and "PathAliasProcedure" configuration parameters. I don't think it does.<br /><br />However, since the Apex Listener now comes with extensive support for RESTful Web Services, you don't need the PathAliasProcedure for that anymore. Rewriting the URL is another matter, perhaps you can set up a RESTful service in the Apex Listener that executes PL/SQL that issues a redirect to the browser? <br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-7143904231436855242012-09-25T13:58:43.145-07:002012-09-25T13:58:43.145-07:00Hi Morten,
Do you think this kind of solution wor...Hi Morten,<br /><br />Do you think this kind of solution works with the latest version of APEX Listener?<br /><br />Regards,<br />LearcoLearcohttps://www.blogger.com/profile/01031286376196340101noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-88793462823593909012012-08-29T23:23:36.171-07:002012-08-29T23:23:36.171-07:00@Anonymous: "rest-demo" is the virtual f...@Anonymous: "rest-demo" is the virtual folder name that tells the gateway to forward the request to your handler procedure instead of executing the normal procedure.<br /><br />"rest_handler.handle_request" is the name of the procedure that will get called.<br /><br />You can change the names of both of those attributes (making sure to change your code accordingly).<br /><br />Have a look at the first screenshot in the blog post. The url is<br /><br />http://127.0.0.1:8080/devtest/rest-demo/employees/7839<br /><br />In that example, "devtest" is the DAD, "rest-demo" is the prefix that tells the gateway to forward everything after that prefix to the handler procedure, and "employees/7839" is what will actually get forwarded to the handler procedure.<br /><br />In your code (in the handler procedure) you can parse this string and do anything you like with it. You can make it handle multiple Apex applications if you want.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-4586732307235763692012-08-29T00:12:45.071-07:002012-08-29T00:12:45.071-07:00begin
dbms_epg.set_dad_attribute (dad_name => &...begin<br />dbms_epg.set_dad_attribute (dad_name => 'devtest', attr_name => 'path-alias', attr_value => 'rest-demo');<br />dbms_epg.set_dad_attribute (dad_name => 'devtest', attr_name => 'path-alias-procedure', attr_value => 'rest_handler.handle_request');<br />end;<br />/<br /><br />What does this mean if I am running multiple applications?<br /><br />How do i need to configure the DAD settings so i will get pretty URLs for all applications ?<br /><br />Do i have to add a line<br />dbms_epg.set_dad_attribute (dad_name => 'devtest', attr_name => 'path-alias', attr_value => 'rest-demo');<br /><br />for all applications??<br />what does rest-demo in this example mean?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-92012992359492627342012-05-20T00:18:59.639-07:002012-05-20T00:18:59.639-07:00Hello Morten,
1. Thank you for this great Blog.
...Hello Morten,<br /><br />1. Thank you for this great Blog.<br /><br />2. Small comment (I am not sure about it but it seems like) : Google recognizes this as a redirect call to a different URL (in our case apex application) and lower ranking.<br /><br />3. I have tried once to implement the following mod_rewrite code, which was very good , except some notification message : https://forums.oracle.com/forums/thread.jspa?threadID=299891&tstart=0&start=15<br /><br />4. So my questions are : <br /> a. Referring to comment mentioned in point 2.<br /> b. Are you familiar with any intentions from Oracle to allow native Url friendly built in option ?<br /><br />Much appreciated.<br /><br />Regards<br />Etay GEtay Gudai - Oracle Israelhttps://www.blogger.com/profile/14316771717388275941noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-85181143460776861672012-05-18T00:07:26.077-07:002012-05-18T00:07:26.077-07:00@Mike: As mentioned at the end of the blog post, &...@Mike: As mentioned at the end of the blog post, "The problem is that the webserver/gateway only sends the URL to the PathAliasProcedure. Any data that is POSTed to the URL is simply discarded by the gateway. Ideally, the names and values of the request should be sent to the PathAliasProcedure in name/value arrays (just like the gateway does when using flexible parameter passing). If anyone from Oracle is reading this, it can be considered an enhancement request for the next version of mod_plsql and the embedded gateway!"<br /><br />To my knowledge there has been no change in this regard for mod_plsql (and I haven't checked if/how the Apex Listener handles this), but I did add support for this in my own Thoth Gateway (a mod_plsql port for IIS).<br /><br />See http://code.google.com/p/thoth-gateway/ and the section called "Features in Thoth that are not in mod_plsql".<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-33894999248689935762012-05-16T11:17:56.408-07:002012-05-16T11:17:56.408-07:00Great writeup. I have this working as you describe...Great writeup. I have this working as you described. I was wondering if you had any updates on using the POST method. I can currently only post if I include p_path= in the body and it essentially ignores my URL after the alias. Ideally it would use the URL and also allow an XML document in the body without the p_path=.Mikenoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-7203442040785952992012-01-20T07:31:07.285-08:002012-01-20T07:31:07.285-08:00@Rohitha: The link you provided is an example of c...@Rohitha: The link you provided is an example of calling (or "consuming") a REST web service on an external server from PL/SQL.<br /><br />This blog post is about creating (or "exposing") a REST web service from PL/SQL. It's the opposite of calling a service.<br /><br />Also, the meat of the example is really the handle_request procedure, which is just a dozen or so lines of code.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-11348267903978982912012-01-10T04:29:54.144-08:002012-01-10T04:29:54.144-08:00Hello Oracle Gurus,
Pardon me for my ignorance. ...Hello Oracle Gurus, <br /><br />Pardon me for my ignorance. I am new to this Web Services thing. I am trying to create a REST WS which handles data in JSON format.<br /><br />I went through this page. Very interesting but seems pretty complicated to me. <br /><br />I also bumped into this page http://blog.warp11.nl/2011/07/calling-a-rest-webservice-with-plsql/<br /><br />which looks very simple.<br /><br />So, what's the difference between these 2 approaches??? Why is one very long than the other??<br /><br />Any advantages / disadvantages with these 2 methods (i.e. one over the other)???Rohithanoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-91151519535862238832011-03-17T04:00:36.711-07:002011-03-17T04:00:36.711-07:00Also had to add this function to get rid of an err...Also had to add this function to get rid of an error I was getting...<br /><br />-- "Set define off" turns off substitution variables. <br />Set define off; <br /><br />CREATE OR REPLACE FUNCTION APEX_030200.wwv_flow_epg_include_mod_local(<br /> procedure_name in varchar2)<br />return boolean<br />is<br />begin<br /> --return false; -- remove this statement when you modify this function<br /> --<br /> -- Administrator note: the procedure_name input parameter may be in the format:<br /> --<br /> -- procedure<br /> -- schema.procedure<br /> -- package.procedure<br /> -- schema.package.procedure<br /> --<br /> -- If the expected input parameter is a procedure name only, the IN list code shown below<br /> -- can be modified to itemize the expected procedure names. Otherwise you must parse the<br /> -- procedure_name parameter and replace the simple code below with code that will evaluate<br /> -- all of the cases listed above.<br /> --<br /> if upper(procedure_name) in (<br /> 'REST_HANDLER.HANDLE_REQUEST') then<br /> return TRUE;<br /> else<br /> return FALSE;<br /> end if;<br />end wwv_flow_epg_include_mod_local;<br />/Tim Wardhttps://www.blogger.com/profile/05630060400817813200noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-17065282717319989872011-03-14T06:54:59.656-07:002011-03-14T06:54:59.656-07:00Also for Mod_plsql, I also had to add the followin...Also for Mod_plsql, I also had to add the following to the dads.conf:<br /><br />PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize<br />Allow from allTim Wardhttps://www.blogger.com/profile/05630060400817813200noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-68743823853331440452011-03-10T01:47:56.844-08:002011-03-10T01:47:56.844-08:00Thanks Morten - I was sure that was the case, but ...Thanks Morten - I was sure that was the case, but I thought maybe I was reading it wrong.<br /><br />I'll have a play with my Glassfish laptop - but when I define our EC2 instance we will have flexibility.<br /><br />Thanks - this information is great.Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-76192285379364256812011-03-10T00:05:58.907-08:002011-03-10T00:05:58.907-08:00@Scott: DBMS_EPG is, as the name implies, used to ...@Scott: DBMS_EPG is, as the name implies, used to configure the Embedded PL/SQL Gateway (EPG).<br /><br />If you use Apache with mod_plsql, configuration is done using the .conf file.<br /><br />If you use the Java-based Apex Listener, then configuration is done using the built-in administration interface. However, I do not believe that the Apex Listener supports the PathAliasProcedure feature (at least not yet -- I suggest you submit an enhancement request to Oracle if you need it).<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-26624244796959517322011-03-09T18:16:40.610-08:002011-03-09T18:16:40.610-08:00I'm a little confused at the applicability of ...I'm a little confused at the applicability of this using a HTTP Server such as Apache.<br /><br />I'm using Glassfish to serve an 11g instance on my laptop with Apex4. <br /><br />My typical URL would start as<br />http://localhost:8080/apex40/f?p=<br /><br />However when I try to set the dad attributes, I receive this error:<br /> 1 begin<br /> 2 dbms_epg.set_dad_attribute<br /> 3 (dad_name => 'apex40'<br /> 4 ,attr_name => 'PlsqlPathAlias'<br /> 5 ,attr_value => 'sage');<br /> 6* end;<br />SQL> /<br />begin<br />*<br />ERROR at line 1:<br />ORA-24231: database access descriptor (DAD) apex40 not found<br />ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86<br />ORA-06512: at "SYS.DBMS_EPG", line 67<br />ORA-06512: at "SYS.DBMS_EPG", line 274<br />ORA-06512: at line 2<br /><br />Any ideas?Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.com