This discussion is archived
13 Replies Latest reply: Dec 28, 2012 7:05 AM by John Spencer RSS

UTL_FILE directory server side

846231 Newbie
Currently Being Moderated
Hi all,

Maybe the expert developers can help me?

DB=11.2.0.3

Is there a way that the utl_file dir be located at the client laptop output folder? Since the server side is in CLOUD and for security reasons users are not allowed to access the cloud servers.

Someone had advised to use a "java stored proc"? Have you heard of this one? It seems this the industry standard and is the solution being used by the EBS Suite product developers.

Any ideas?


Thanks a lot,

Kinz
  • 1. Re: UTL_FILE directory server side
    APC Oracle ACE
    Currently Being Moderated
    KinsaKaUy? wrote:
    Is there a way that the utl_file dir be located at the client laptop output folder?
    No.

    Well, maybe. That is, if you can persuade your user to share their desktop, and then get the network administrator to map that share to a drive which is accessible by the database server. Except that you go on to say ...
    Since the server side is in CLOUD and for security reasons users are not allowed to access the cloud servers.
    So that's a probable "No" then.
    Someone had advised to use a "java stored proc"? Have you heard of this one?
    Did they say why you should use a JSP? Or is it just because Java is so much more magical than PL/SQL?

    Most likely your ESB devs are using a Java Stored Procedure which executes an FTP call to move files from the desktop to the database server. You could do something similar using PL/SQL. Tim Hall has an implementation [url http://www.oracle-base.com/articles/misc/ftp-from-plsql.php]on his Oracle-Base site.

    Cheers, APC
  • 2. Re: UTL_FILE directory server side
    846231 Newbie
    Currently Being Moderated
    Thanks APC,
    Did they say why you should use a JSP? Or is it just because Java is so much more magical than PL/SQL?
    It is because of PL/SQL limitation not to be able to write to the client folder? or am i lost?

    Ok I will try Tim's program sample.


    Thanks,

    Edited by: KinsaKaUy? on 27-Dec-2012 02:24
  • 3. Re: UTL_FILE directory server side
    APC Oracle ACE
    Currently Being Moderated
    KinsaKaUy? wrote:
    It is because of PL/SQL limitation not to be able to write to the client folder? or am i lost?
    The database server literally cannot see your desktop. This is nothing to do with PL/SQL, it is to do with networking and security protocols. The client connects to the server, not the other way round.

    Java in the database has the same limitation. It's just that at your site somebody has knocked up a JSP which uses a low level protocol such as FTP to work around this. They could have done the same thing with PL/SQL but chose not to (or didn't realise that they could), or more likely they just re-purposed an existing piece of Java code.

    Cheers, APC
  • 4. Re: UTL_FILE directory server side
    846231 Newbie
    Currently Being Moderated
    Thanks a lot apc...

    By the way will this also work for my EXPDP such that my output dump will be saved on local client laptop?
  • 5. Re: UTL_FILE directory server side
    APC Oracle ACE
    Currently Being Moderated
    In principle, yes.

    Cheers, APC
  • 6. Re: UTL_FILE directory server side
    846231 Newbie
    Currently Being Moderated
    Hi ALL,

    Is it a breach of database security law or a bad security setup if you create a TEMP folder on your database server for developers UTL_FILE output dir. and share this folder to developers for them to see their output?

    Thanks,
  • 7. Re: UTL_FILE directory server side
    846231 Newbie
    Currently Being Moderated
    add new updates
  • 8. Re: UTL_FILE directory server side
    sb92075 Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    Hi again apc,

    Is it a breach of database security law or a bad security setup if you create a TEMP folder on your database server for developers UTL_FILE output dir. and share this folder to developers for them to see their output?

    Thanks,
    for me, only DBA can log onto Production DB Server; no developers & no System Administrators.
    You can't break what you can't even touch!
  • 9. Re: UTL_FILE directory server side
    846231 Newbie
    Currently Being Moderated
    Thanks Sb,
    for me, only DBA can log onto Production DB Server; no developers & no System Administrators.
    Not necessarily thay have to login, but the folder can be shared as readonly folder.
    You can not break what you can not write :)


    So does this mean that UTL_FILE tool is never used in your setup?

    So I will advise the developer to "never" use utl_file option? (well except when using what apc had advise to use tim's code).


    Thanks
  • 10. Re: UTL_FILE directory server side
    EdStevens Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    add new updates
    Patience, Grasshopper

    You posted this follow-up a mere ONE MINUTE after your previous post.

    This forum is not a chat line, and it is not paid support.

    Everyone here has a job for which they are paid, and this forum is not it.

    No one is responsible for monitoring it and giving a quick response.

    Furthermore, it is a global forum. The person with the information you seek may very well live 20 time zones away from you and was going to bed just as you posted. He will not even see your post for several more hours.

    Your original post went up in the middle of the night for half the world.

    No one with the information you seek is deliberately withholding it until you sound sufficiently desperate.
  • 11. Re: UTL_FILE directory server side
    BluShadow Guru Moderator
    Currently Being Moderated
    EdStevens wrote:
    KinsaKaUy? wrote:
    add new updates
    Patience, Grasshopper

    You posted this follow-up a mere ONE MINUTE after your previous post.

    This forum is not a chat line, and it is not paid support.

    Everyone here has a job for which they are paid, and this forum is not it.

    No one is responsible for monitoring it and giving a quick response.

    Furthermore, it is a global forum. The person with the information you seek may very well live 20 time zones away from you and was going to bed just as you posted. He will not even see your post for several more hours.

    Your original post went up in the middle of the night for half the world.

    No one with the information you seek is deliberately withholding it until you sound sufficiently desperate.
    Not to mention the Christmas and new year holidays... far less people about.
  • 12. Re: UTL_FILE directory server side
    EdStevens Guru
    Currently Being Moderated
    BluShadow wrote:
    EdStevens wrote:
    KinsaKaUy? wrote:
    add new updates
    Patience, Grasshopper

    You posted this follow-up a mere ONE MINUTE after your previous post.

    This forum is not a chat line, and it is not paid support.

    Everyone here has a job for which they are paid, and this forum is not it.

    No one is responsible for monitoring it and giving a quick response.

    Furthermore, it is a global forum. The person with the information you seek may very well live 20 time zones away from you and was going to bed just as you posted. He will not even see your post for several more hours.

    Your original post went up in the middle of the night for half the world.

    No one with the information you seek is deliberately withholding it until you sound sufficiently desperate.
    Not to mention the Christmas and new year holidays... far less people about.
    Yes, I'd guess that half of Europe and the entire Western Hemisphere is barely working until after 1 Jan. Skeleton staffs and even those who are at the office are just keeping the lights on.
  • 13. Re: UTL_FILE directory server side
    John Spencer Oracle ACE
    Currently Being Moderated
    EdStevens wrote:
    BluShadow wrote:
    EdStevens wrote:
    KinsaKaUy? wrote:
    add new updates
    Patience, Grasshopper

    You posted this follow-up a mere ONE MINUTE after your previous post.

    This forum is not a chat line, and it is not paid support.

    Everyone here has a job for which they are paid, and this forum is not it.

    No one is responsible for monitoring it and giving a quick response.

    Furthermore, it is a global forum. The person with the information you seek may very well live 20 time zones away from you and was going to bed just as you posted. He will not even see your post for several more hours.

    Your original post went up in the middle of the night for half the world.

    No one with the information you seek is deliberately withholding it until you sound sufficiently desperate.
    Not to mention the Christmas and new year holidays... far less people about.
    Yes, I'd guess that half of Europe and the entire Western Hemisphere is barely working until after 1 Jan. Skeleton staffs and even those who are at the office are just keeping the lights on.
    Ed:

    You get lights while working over the holidays? Must be nice ;-)

    John

Legend

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