ORDS as plsql gateway and plugin for resources

paddy3k

    Hello !

     

    I have to migrate an old project from OHS & mod_plsql to ORDS. While the simple setting of setting up an plsql gateway works, I have

    troubles to make the resource loading work fine.

     

    Resources like images used on the websites are stored in the database, but ORDS doesn't have an equivalent of the

    "PlsqlDocumentProcedure" from mod_plsql.

     

    As a replacement for this missing functionality, I developed a java ORDS plugin which listens for requests with a specific URL path:

     

    @Provides
    @Dispatches(@PathTemplate("/docs/*"))
    class ORDSResourcePlugin extends HttpServlet
    

     

    It works fine as long as I don't have a ORDS url mapping created.

     

    With this url-mapping.xml :

     

    <?xml version="1.0" encoding="UTF-8"?>
    <pool-config xmlns="http://xmlns.oracle.com/apex/pool-config">
     <pool name="db1" base-path="/test" updated="2018-08-17T09:14:02.268Z"/>
    </pool-config>
    

     

    the following call of the plsql gateway works:

     

    http://localhost:8080/test/pkg.plsqlcall
    

     

    but the images are not shown (plugin is not invoked).

    Even when I test with an explicit image URL like this:

     

    http://localhost:8080/test/docs/image1.jpg
    

     

    the plugin doesn't get invoked, instead I get an URLMappingNotFoundException.

     

    If I remove the url-mapping.xml, the plugin works as expected and images are shown when I call any URL with /docs/ in the path.

     

    My question is, how to make both together work? The plsql gateway and the plugin?

     

     

    I set up the plugin like this :

     

    java -jar ords.war plugin ORDSResourcePlugin.jar
    

     

    I configured ORDS like this :

     

    BEGIN
      ords.enable_schema(p_enabled             => TRUE,
                         p_schema              => 'schema1',
                         p_url_mapping_type    => 'BASE_PATH',
                         p_url_mapping_pattern => 'test',
                         p_auto_rest_auth      => FALSE);
      COMMIT;
    END;
    

     

    java -jar ords.war setup --database db1
    

     

    java -jar ords.war map-url --type base-path /test db1
    

     

     

    The installed component versions are:

     

    Oracle DB 12.1.0.2

    Apex 5.1.4

    ORDS 3.0.12.263.15.32

     

    If there are further informations needed to give a hint I would be happy about a response

      • 1. Re: ORDS as plsql gateway and plugin for resources
        thatJeffSmith-Oracle

        stepping back from the plugin angle,

         

        Resources like images used on the websites are stored in the database, but ORDS doesn't have an equivalent of the

        "PlsqlDocumentProcedure" from mod_plsql.

         

        Why not build a REST Service that gets the image for you from the table? Then you can simply

        GET .../ords/hr/images/123

         

        which could do something like

          select ''image/png', pic from table where id = :id ?

        1 位用户发现它有用
        • 2. Re: ORDS as plsql gateway and plugin for resources
          paddy3k

          Hi Jeff,

           

          thanks for your suggestion. I already tried a REST service but couldn't find a way to define a dynamic path like

          with the path templates in the plugin (/docs/*) ... is that possible? Because image urls can be 1 to n levels deep.

           

          edit: ok I tried with a REST service, but it doesn't work either.

           

          When I have the url-mapping.xml with the base-url entry in it, the REST service doesnt work...

          when I remove the url-mapping entry the REST service is working but the plsql gateway stops working.

           

          I think it's all just a mapping pattern problem. My URLs look like :

           

          http://localhost:8080/ords/test/package.function
          

           

          and resources always resolve to a subpath of :

           

          http://localhost:8080/ords/test/docs/
          

           

          the curios thing is... if I have the base-path mapping active and call the website, it shows up but the images are not shown.
          If I then remove the base-path mapping from the url-mapping.xml the plsql gateway to the website still works and the images

          are shown... but after a while it stops working and will resolve to 404 url not found again and only direct calls to the

          images like :

           

          http://localhost:8080/ords/test/docs/test.png
          

           

          still work.

          • 3. Re: ORDS as plsql gateway and plugin for resources
            Kris Rice-Oracle

            Obviously change the sql to proc/function/whatever is needed but using the {} syntax gets you the full path passed in.

            Here's my table. The PATH has full path a/bb/c/file.png

             

            I'm using the old {} syntax but you can also use : syntax and add a * to the end. The full syntax is here:

            {route patterns}

             

             

             CREATE TABLE "KLRICE"."K" 
               (  "PATH" VARCHAR2(2000 BYTE), 
                  "BLOB_CONTENT" BLOB,   
                  "WHEN" TIMESTAMP (6)
               )
            

             

             

            Here's the ords module

             

            BEGIN
              ORDS.ENABLE_SCHEMA(
                  p_enabled            => TRUE,
                  p_schema              => 'KLRICE',
                  p_url_mapping_type    => 'BASE_PATH',
                  p_url_mapping_pattern => 'klrice',
                  p_auto_rest_auth      => FALSE);   
            
            
              ORDS.DEFINE_MODULE(
                  p_module_name    => 'docs',
                  p_base_path      => '/docs/',
                  p_items_per_page =>  25,
                  p_status        => 'PUBLISHED',
                  p_comments      => NULL);     
              ORDS.DEFINE_TEMPLATE(
                  p_module_name    => 'docs',
                  p_pattern        => '{path}',
                  p_priority      => 0,
                  p_etag_type      => 'HASH',
                  p_etag_query    => NULL,
                  p_comments      => NULL);
              ORDS.DEFINE_HANDLER(
                  p_module_name    => 'docs',
                  p_pattern        => '{path}',
                  p_method        => 'GET',
                  p_source_type    => 'resource/lob',
                  p_items_per_page =>  25,
                  p_mimes_allowed  => '',
                  p_comments      => NULL,
                  p_source        =>
            'select  ''image/png'',blob_content, substr(k.path,instr(k.path,''/'')+1) filename
            from k
            where path = ''/''||:path'
                  );
              COMMIT;
            END;
            

             

             

            Here's an example calling it.  Notice the /docs/* where the path includes 3 subdirs.

            Screen Shot 2018-08-21 at 10.48.51 AM.png

            2 位用户发现它有用
            • 4. Re: ORDS as plsql gateway and plugin for resources
              paddy3k

              Hello Kris,

               

              first, many thanks for your effort of posting a sample REST service. I implemented it nearly the same. It works fine if you

              use it without a plsql gateway mapping!

               

              My problem is that I have to provide the plsql gateway with the same url mapping pattern. So if I extend your example

              by adding a entry to the url-mapping.xml to make the plsql gateway work :

               

              <?xml version="1.0" encoding="UTF-8"?>
              <pool-config xmlns="http://xmlns.oracle.com/apex/pool-config">
                   <pool name="KLRICE" base-path="klrice" updated="2018-08-21T09:14:02.268Z"/>
              </pool-config>
              

               

              The REST service will not be callable anymore. URLMappingNotFoundException will show up.

              If I remove the url-mapping.xml, the REST service works fine, but I cannot call the website anymore.

               

              The call would look like (to match your example code):

               

              http://localhost:9090/ords/klrice/somePackage.someFunction
              
              • 5. Re: ORDS as plsql gateway and plugin for resources
                Kris Rice-Oracle

                Right we do a quick test if something 'looks' like it's a plsql gateway call.

                 

                Any chance you could add in a /files/ into the path which shortcuts the looking like a plsql gateway call?

                1 位用户发现它有用
                • 6. Re: ORDS as plsql gateway and plugin for resources
                  paddy3k

                  You mean for the resources URL ? So it looks like :

                   

                  http://localhost:9090/ords/klrice/docs/files/image1.png
                  

                   

                  Yes, I could change that and just tried it...  with the base-path mapping in place, the plsql call worked but no image is shown.

                  Without the base-path mapping the plsql call doesn't work.

                   

                  Did I miss something?

                  • 7. Re: ORDS as plsql gateway and plugin for resources
                    paddy3k

                    No further ideas? I don't know what else to try to have both use-cases (plsql call & REST call) working with the same

                    base url.

                     

                    The reason behind this demand is to migrate the old (mod_plsql) way of up/downloading resources to a document table

                    in the db. This is currently done with the PlsqlDocumentProcedure of mod_plsql but there is no equivalent in ORDS.

                    Are there any other options without having to rewrite our own code?

                     

                    edit: I found a solution to distinct between the pl/sql gateway call and the REST service for downloading resources.

                    I changed our procedure which generates some kind of "virtual res" urls to something which have another prefix.

                    (I think kris meant the same in his earlier post, but I misunderstood it).

                     

                    For example:

                     

                    1a (pl/sql gateway call)

                    http://localhost:9090/ords/prefix1/somePackage.someFunction

                     

                    with the mapping:

                     

                    <?xml version="1.0" encoding="UTF-8"?>
                    <pool-config xmlns="http://xmlns.oracle.com/apex/pool-config">
                     <pool name="db1" base-path="/prefix1" updated="2018-10-09T11:29:56.307Z"/>
                    </pool-config>
                    

                     

                    1b (REST call for resource download)

                    http://localhost:9090/ords/res/images/header1.png

                     

                    Now the calls are dispatched as they should and the embedded images are shown on each site. So pretty easy in the

                    end... don't know why I came up with that earlier. The REST solution is also a more elegant way than the ORDS plugin

                    I developed first. Thanks for the help!