7 Replies Latest reply: Mar 19, 2013 9:04 AM by APC RSS

    os_command.exec cannot cat file

    SevCon
      Hi,

      I am not sure which category this goes under so will move it if there is a better suggestion.

      I am using the os_command pl/sql and java packages from soruceforge to issue unix commands from pl/sql.

      I have it at the point where I can rm, touch files and issue commands such as ls but when I try and issue cat I get the message:

      begin
      select os_command.exec_clob('/bin/cat /home/oracle/rules/var.txt >/home/oracle/rules/var2.txt') into x from dual;
      end;

      I get:

      /bin/cat: /home/oracle/rules/var.txt: No such file or directory
      /bin/cat: >/home/oracle/rules/var2.txt: No such file or directory

      Those files are owned by and in group oracle. I can run the command from the terminal and get the correct result.

      If I put this command into a shell script and then execute that from pl/sql then I it succeeds.

      Is there anything in particular that cat would need?

      TIA

      Sev
        • 1. Re: os_command.exec cannot cat file
          APC
          So which privileges have you given the owner of {font:courier new}OS_COMMAND(){font} i.e. what did you pass to {font:courier new}DBMS_JAVA.grant_permission(){font} ?

          Cheers, APC

          P.S. For anybody else interested in this package you can find its homepage [url http://plsqlexecoscomm.sourceforge.net/plsqldoc/os_command.html]here.
          • 2. Re: os_command.exec cannot cat file
            SevCon
            Yep, sorry....

            I am pretty sure I ran the pl/sql block on the homepages. However, I know for certain that the other day I re-ran:

            call dbms_java.grant_permission('OSDEV', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
            call dbms_java.grant_permission('OSDEV', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
            call dbms_java.grant_permission('OSDEV', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');

            I will re-run the whole block as the RuntimePermission could be missing.

            Would that affect cat?

            S.

            p.s.
            I know this <<ALL FILES>> is a (dangerous) sledgehammer but I'm running on a private VM in order to provide a proof of concept.

            Edited by: SevCon on 18-Mar-2013 05:00
            • 3. Re: os_command.exec cannot cat file
              993606
              I guess it cant handle the > into another file

              When you issue

              set serveroutput on size 10000
              call dbms_java.set_output(10000);

              and then call only the cat command from sql plus do you see any output?
              • 4. Re: os_command.exec cannot cat file
                SevCon
                Output is as follows:


                OSDEV@orcl> set serveroutput on size 10000
                OSDEV@orcl> call dbms_java.set_output(10000)
                2 ;

                Call completed.

                OSDEV@orcl> select os_command.exec('/bin/cat foo.txt') from dual;

                OS_COMMAND.EXEC('/BIN/CATFOO.TXT')
                ----------------------------------
                1

                Good point about the redirect. My idea for a workaround was to echo the line into the file but the output is as if you had written echo "this into >that.txt" rather than creating a file called that.txt

                Any other input very welcome but I will implement my workaround extra script to move forwards for the time being.

                Thanks

                Sev
                • 5. Re: os_command.exec cannot cat file
                  SevCon
                  Apologies. I failed to qualify the file name. I have now qualified it with the full path. This is now the output and points more towards a redirect issue (I think):

                  OSDEV@orcl> l
                  1* select os_command.exec_clob('/bin/cat /home/oracle/rules/foo.txt') into :rcode from dual
                  OSDEV@orcl> /

                  OS_COMMAND.EXEC_CLOB('/BIN/CAT/HOME/ORACLE/RULES/FOO.TXT')
                  --------------------------------------------------------------------------------
                  This is the text in foo.txt
                  • 6. Re: os_command.exec cannot cat file
                    993606
                    The Java packeges you use certainly contain methods for reading and writing files on the os. I suggest you read the content of one file into a plsql variable with one Java method and then in a second step you write it back to the other file with the other Java method.

                    Why are you not using a directory and the utl_file package in your database? It also can read and write files.
                    • 7. Re: os_command.exec cannot cat file
                      APC
                      user7478310 wrote:
                      Why are you not using a directory and the utl_file package in your database? It also can read and write files.
                      I'm generally in favour of doing as much as possible inside the database. So if the file copying is part of a wider task then I think {font:courier new}UTL_FILE.FCOPY(){font} is the right approach. But if the task in hand is just to manipulate OS files (and nothing else) it makes a lot of sense to keep it in that layer and use OS commands.

                      The other thing is, the two different approaches use different mechanisms for permissions. The biggest limitation to database DIRECTORY objects is that they don't permit wildcards. We can't define a DIRECTORY for {font:courier new}\home\apc\*{font}. The ability to work with large numbers of sub-directories, especially directories ctreated on the fly, is a very useful feature of the Java-based approach.

                      Cheers, APC