This discussion is archived
7 Replies Latest reply: Mar 19, 2013 7:04 AM by APC RSS

os_command.exec cannot cat file

850905 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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
    850905 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    850905 Newbie
    Currently Being Moderated
    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
    850905 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

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