11 Replies Latest reply: Mar 11, 2013 9:39 AM by Hussein Sawwan-Oracle RSS

    How to setup DAD to execute URL to get a document from fnd_lobs table

    user4556868
      Env: db 11.1.0
      Application 11.5.10 CU2
      OS: red hat linux

      I wanted to execute an URL
      http://dev-amer-ebs.abctest.net/pls/XXDB/get_attachment_doc?i_docid=1660967

      When I execute it I get security error:GET_ATTACHMENT_DOC

      Get_attachment_doc is a procedure owned by apps and what it does is

      get a blob ie file_data
      select file_id media_id, file_name, file_data from applsys.fnd_lobs where file_id = 1660967
      using...
      OWA_UTIL.mime_header (NVL (i_mimetype, 'application/octet'), FALSE);

      HTP.p ('Content-length: ' || DBMS_LOB.getlength (io_blob));

      HTP.p (
      'Content-Disposition: attachment; filename="' || i_filename || '"'
      );

      --

      OWA_UTIL.http_header_close;


      My question is how to configure DAD so that
      http://dev-amer-ebs.abctest.net/pls/XXDB/get_attachment_doc?i_docid=1660967

      is authenticated (get_attachment_doc) without any prompt and get the blob displayed on the browser..

      Any guidance on this will be highly appreciated

      Thanks
        • 1. Re: How to setup DAD to execute URL to get a document from fnd_lobs table
          Hussein Sawwan-Oracle
          What is the complete error message you get?

          Please see if these docs help.

          Showing PDF Document Through Forms, When the Document is Stored as Blob Column in Rdbms [ID 743296.1]
          Troubleshooting Export and Attachment issues in Oracle Applications 11i [ID 338651.1]

          Thanks,
          Hussein
          • 2. Re: How to setup DAD to execute URL to get a document from fnd_lobs table
            user4556868
            I think I need a little bit more refinment...
            We don't have fusion middle ware...
            we use oracle ias and sure I would like to know how to configure DAD on oracle ias

            Thanks so much...

            Currently this is how our DAD looks in wdbsvr.app

            [DAD_XXDB]
            connect_string = XXDB_BALANCE
            password = welcome123
            username = APPS
            default_page = fnd_web.ping
            document_table = APPS.fnd_lobs_document
            document_path = docs
            document_proc =
            upload_as_long_raw =
            upload_as_blob = *
            reuse = Yes
            connmax = 10
            pathalias = fndgfm
            pathaliasproc = fnd_gfm.dispatch
            enablesso = No
            cgi_env_list=SERVER_NAME=sand-xxx-ebs.abc123.net,REQUEST_PROTOCOL=https,SERVER_PORT=443,HOST=sand-xxx-ebs.abc123.net:443
            input_filtering = Yes
            stateful=STATELESS_RESET
            ;

            The question is how to make the DAD work for get_attachment_doc if we don't have oracle fusion middleware...


            Thanks

            Edited by: user4556868 on Mar 5, 2013 4:10 PM
            • 3. Re: How to setup DAD to execute URL to get a document from fnd_lobs table
              Hussein Sawwan-Oracle
              Please see the "Sample Output" file in (11i : Oracle Application Object Library Attachments and Export Data Collection Test [ID 395163.1]) for a DAD configuration that works with APPS.fnd_lobs_document.

              Thanks,
              Hussein
              • 4. Re: How to setup DAD to execute URL to get a document from fnd_lobs table
                user4556868
                Thanks..
                I read 165195.1 and 270519.1 and looked at managing customization in autoconfig..

                I did oracle diagnostics as suggested and got the info that I am using wdbsvr.appfile
                I am not getting any clue...
                I know that I have to have entries specific to my requiremnt co-hosted with the existing set up...
                Let me fine tune the question...

                My requirement is to execute and download the blob from fnd_lobs table using below link

                http://dev-amer-ebs.abctest.net/pls/XXDB/get_attachment_doc?i_docid=1660967

                Currently the attachment in ebs can be accessed through :
                http://dev-amer-ebs.abctest.net/pls/XXDB/fndgfm/fnd_gfm.get/1727465279/1660967/perc%20overview.ppt

                You may ask why we wanted to do it...
                We are poc-ing to store a blob in fnd_lobs table and without actually creating a login for the user can we access the blob through a browser giving specfic file_id

                1st question : Can it work ? if yes

                2nd question : do I have to create entries in Wdbsvr.app file ? If yes can I simply edit file and add entries for " a poc"

                3rd : if 2 is yes , the current file is as below.. do you mind adding what it looks like

                Thanks a bunch any help is appreciated..


                I am confused already the document_table is defined it as fnd_lobs_attachments and in my case it is fnd_lobs
                I am not using..
                pathalias = fndgfm ; in my case ????
                pathaliasproc = fnd_gfm.dispatch ; in my case it's get attachment_doc


                ;
                ; $Header: wdbsvr.app 115.25 2004/11/25 13:19:18 adan ship $
                ;
                ; ###############################################################
                ;
                ; This file is automatically generated by AutoConfig. It will be read and
                ; overwritten. If you were instructed to edit this file, or if you are not
                ; able to use the settings created by AutoConfig, refer to Metalink document
                ; 165195.1 for assistance.
                ;
                ; ###############################################################

                ;
                [WVGATEWAY]
                defaultDAD = XXDB
                administrators = system
                adminPath = /admin_/
                custom_auth = CUSTOM
                ;
                [DAD_XXDB_admin]
                connect_string = XXDB_BALANCE
                cgi_env_list=SERVER_NAME=dev-amer-ebs.abctest.net,REQUEST_PROTOCOL=https,SERVER_PORT=443,HOST=dev-amer-ebs.abctest.net:443
                input_filtering = Yes
                ;
                [DAD_XXDB]
                connect_string = XXDB_BALANCE
                password = welcome123
                username = APPS
                default_page = fnd_web.ping
                document_table = APPS.fnd_lobs_document
                document_path = docs
                document_proc =
                upload_as_long_raw =
                upload_as_blob = *
                reuse = Yes
                connmax = 10
                pathalias = fndgfm
                pathaliasproc = fnd_gfm.dispatch
                enablesso = No
                cgi_env_list=SERVER_NAME=dev-amer-ebs.abctest.net,REQUEST_PROTOCOL=https,SERVER_PORT=443,HOST=dev-amer-ebs.abctest.net:443
                input_filtering = Yes
                stateful=STATELESS_RESET
                ;
                [DAD_XXDB_portal30]
                connect_string = XXDB_BALANCE
                password = %s_portal_passwd%
                username = portal30
                default_page = portal30.home
                document_table = portal30.wwdoc_document
                document_path = docs
                document_proc = portal30.wwdoc_process.process_download
                upload_as_long_raw =
                upload_as_blob = *
                reuse = Yes
                connmax = 10
                enablesso = Yes
                pathalias = url
                pathaliasproc = portal30.wwpth_api_alias.process_download
                cgi_env_list=SERVER_NAME=dev-amer-ebs.abctest.net,REQUEST_PROTOCOL=https,SERVER_PORT=443,HOST=dev-amer-ebs.abctest.net:443
                ;name_prefix =
                ;always_describe =
                ;after_proc =
                ;before_proc =
                stateful=STATELESS_RESET
                ;
                [DAD_XXDB_portal30_sso]
                connect_string = XXDB_BALANCE
                password = %s_portalsso_passwd%
                username = portal30_sso
                default_page = portal30_sso.wwsso_home.home
                document_table = portal30_sso.wwdoc_document
                document_path = docs
                document_proc = portal30_sso.wwdoc_process.process_download
                upload_as_long_raw =
                upload_as_blob = *
                reuse = Yes
                connmax = 10
                enablesso = Yes
                pathalias = url
                pathaliasproc = portal30_sso.wwpth_api_alias.process_download
                cgi_env_list=SERVER_NAME=dev-amer-ebs.abctest.net,REQUEST_PROTOCOL=https,SERVER_PORT=443,HOST=dev-amer-ebs.abctest.net:443
                ;name_prefix =
                ;always_describe =
                ;after_proc =
                ;before_proc =
                stateful=STATELESS_RESET
                ;

                Edited by: user4556868 on Mar 6, 2013 10:30 AM
                • 5. Re: How to setup DAD to execute URL to get a document from fnd_lobs table
                  Hussein Sawwan-Oracle
                  We are poc-ing to store a blob in fnd_lobs table and without actually creating a login for the user can we access the blob through a browser giving specfic file_id

                  1st question : Can it work ? if yes
                  I am not sure since I have not tried it myself.
                  2nd question : do I have to create entries in Wdbsvr.app file ? If yes can I simply edit file and add entries for " a poc"
                  It will be overwritten each time you run AutoConfig so please see (Customizing an AutoConfig Environment [ID 270519.1]).
                  3rd : if 2 is yes , the current file is as below.. do you mind adding what it looks like
                  It looks OK to me.

                  Thanks,
                  Hussein
                  • 6. Re: How to setup DAD to execute URL to get a document from fnd_lobs table
                    Srini Chavali-Oracle
                    >
                    My question is how to configure DAD so that
                    http://dev-amer-ebs.abctest.net/pls/XXDB/get_attachment_doc?i_docid=1660967

                    is authenticated (get_attachment_doc) without any prompt and get the blob displayed on the browser..
                    >

                    >
                    You may ask why we wanted to do it...
                    We are poc-ing to store a blob in fnd_lobs table and without actually creating a login for the user can we access the blob through a browser giving specfic file_id

                    1st question : Can it work ? if yes
                    >

                    Are you asking if you can access a document stored in an EBS database by providing a URL, without actually signing on to EBS (i.e. bypassing EBS security completely) ? If so, then the answer is that it is not possible.

                    HTH
                    Srini
                    • 7. Re: How to setup DAD to execute URL to get a document from fnd_lobs table
                      Hussein Sawwan-Oracle
                      You are passing the apps username/password in the DAD configuration, so the database connectivity should not be an issue here.

                      Thanks,
                      Hussein
                      • 8. Re: How to setup DAD to execute URL to get a document from fnd_lobs table
                        user4556868
                        How about using anonymous, anonymous.

                        I think it's possible we may need to tweak DAD config..
                        That is what I am trying to poc..

                        This what current what I get...
                        when I put it ina browser

                        http://dev-amer-ebs.abctest.net/pls/XXDB/get_attachment_doc?i_docid=1660967

                        a window pops up with title with the following info
                        -------------------------------------------------------------------

                        window title :connect to dev-amer-ebs.abctest.net

                        SECURITY ERROR:GET_ATTACHMENT_DOC
                        username:
                        Password:

                        Remember my password (check box)

                        Click button (ok), Click button (cancel)

                        --------------------------------------------------------------------

                        Sorry , --I couldnot put a screen shot of the window

                        I tried all combination of username/password
                        it comesback with authentication failure

                        I am trying circumvent the issue...
                        is it a DAD issue or a firewall issue

                        Thanks
                        • 9. Re: How to setup DAD to execute URL to get a document from fnd_lobs table
                          Hussein Sawwan-Oracle
                          I tried all combination of username/password
                          Did you try with a valie application username/password?

                          What about GUEST/<GUEST Password>?

                          What is the exact error you get?

                          For uploading screenshots, please upload the file to any free hosting website and post the link here.

                          Can you find any errors in apache log files?

                          Please also make sure you bounce the application services after editing your DAD configuration.

                          Thanks,
                          Hussein
                          • 10. Re: How to setup DAD to execute URL to get a document from fnd_lobs table
                            user4556868
                            Thanks all..
                            It was a simple miss
                            I must register get_attachment_doc under sysadmin--> websql
                            and it's working fine...
                            • 11. Re: How to setup DAD to execute URL to get a document from fnd_lobs table
                              Hussein Sawwan-Oracle
                              user4556868 wrote:
                              Thanks all..
                              It was a simple miss
                              I must register get_attachment_doc under sysadmin--> websql
                              and it's working fine...
                              Thanks for the update and for sharing the solution!