This discussion is archived
11 Replies Latest reply: Mar 11, 2013 9:37 AM by user4556868 RSS

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

user4556868 Newbie
Currently Being Moderated
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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    >
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points