13 Replies Latest reply: Dec 28, 2012 9:05 AM by John Spencer RSS

    UTL_FILE directory server side

    846231
      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
          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
            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
              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
                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
                  In principle, yes.

                  Cheers, APC
                  • 6. Re: UTL_FILE directory server side
                    846231
                    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
                      add new updates
                      • 8. Re: UTL_FILE directory server side
                        sb92075
                        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
                          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
                            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
                              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
                                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
                                  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