1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Using Linux command line commands through PL/SQL

Discussion in 'SQL PL/SQL' started by ac.arijit, Nov 14, 2012.

  1. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260

    There can be many different RPM packges that could include these files. As an example:

    http://rpm.pbone.net/index.php3/sta...suse/com/processing-1.0.1-8.3.x86_64.rpm.html

    So if your system administrator has (on the installation DVD or Redhat marketplace) some processing-... RPM it should do the job.
    But again, it is his word that is the key

    Regards,
    Dariyoosh
     
  2. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    221
    Likes Received:
    23
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi dariyoosh,

    The system administrator just got the file location, but is hesitant on making any changes.
    The file location is: /d02/clone-appl/apps/tech_st/10.1.2/jdk/jre/lib/i386/client/libjvm.so
    My $ORACLE_HOME= /d02/clone-appl/apps/tech_st/10.1.2
    Also, I don't think my JAVA_HOME is defined as when i tried command 'cd $JAVA_HOME' its not navigating anywhere.

    So, as you defined the LD_LIBRARY_PATH and CLASSPATH ...
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
    CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

    I believe the paths you provided, in these locations the libjvm.so is not existing.
    So, what should be done?
    Should we copy the file libjvm.so into any of these locations which are defined as LD_LIBRARY_PATH or CLASSPATH?
    Or
    Should i define the path of the file libjvm.so in any of the variable?

    Regards
    Ari
     
  3. harish

    harish Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Simplest way to run this using DBMS_pipe package . Though this package you can run host command in you pl/sql blok.
    For more details you have to read details about DBMS_PIPE package.

    Please note that for this you need execute permission on DBMS_PIPE package.

    I hope this will help you.
     
  4. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    221
    Likes Received:
    23
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi harish,

    Can you provide me any working example? .. I'll be thankful. I got all the execute permissions on DBMS_PIPE, and tried this earlier but failed to achieve what i intended to.

    So, if u can provide any such working example, it'll be really great for me.

    Regards
    Ari
     
  5. harish

    harish Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi Arijit

    Long time back i tried. Please try below code . I hope this will work.

    =====================================


    CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 )
    RETURN INTEGER IS
    STATUS NUMBER;
    errormsg VARCHAR2(80);
    pipe_name VARCHAR2(30);
    BEGIN
    pipe_name := 'HOST_PIPE';
    dbms_pipe.pack_message( cmd );
    STATUS := dbms_pipe.send_message(pipe_name);
    RETURN STATUS;
    END;

    ==================================

    declare

    status1 number;
    status number;
    command varchar2(255);
    begin

    status:=host_command('ls -l');

    status1:=dbms_pipe.receive_message( 'HOST_PIPE' );

    dbms_output.put_line('status:'||status);
    dbms_output.put_line('status1:'||status1);
    dbms_pipe.unpack_message( command );

    end;
     
  6. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    221
    Likes Received:
    23
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi harish,

    Thanks for the reply and the example too.

    I got the priviledges on the package and executed the script u provided. This return the status as 0.
    But didn't created the .pdf file in the location which was the objective.

    Regards
    Ari
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It did create the pdf file, correct? Show us the command you used and the location of the pdf file. It may be as simple as providing the complete path for the pdf file.
     
  8. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    221
    Likes Received:
    23
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi David,

    Thanks a ton for the reply. :)

    1. If your're referring the dbms_pipe method then, these are the steps:
    I used the script provided by harish above and this is the function call:
    Code (SQL):

    SET ServerOutput ON;
    DECLARE
        status1 NUMBER;
        STATUS  NUMBER;
        command varchar2(255);
    BEGIN
        STATUS  :=  host_command('/d02/clone-appl/apps/tech_st/10.1.2/bin/rwrun report=/d02/clone-appl/apps/apps_st/appl/gl/12.0.0/reports/US/AMRIT_BS_TFRM.rdf userid=apps/apps desformat=pdf destype=file desname=/d02/clone-appl/apps/apps_st/appl/gl/12.0.0/reports/US/AMRIT_BS_TFRM.pdf PERIOD_NAME=''APR-11-12'' P_ACCESS_SET_ID=1000 P_ORG_ID=82 P_UNIT=11');
        status1 :=  dbms_pipe.receive_message( 'HOST_PIPE' );
        dbms_output.put_line('status:'||STATUS);
        dbms_output.put_line('status1:'||status1);
        dbms_pipe.unpack_message( command );
    END;
     
    P.S: The OS script which is passed to host_command is working fine from toad network utilities and also the execute priviledge on dbms_pipe is granted to the database user before execution.
    After Execution the output is:
    Status: 0
    Status1: 0
    PL/SQL Procedure successfully completed


    **But when i check the location manually i don't see the .pdf file.

    2. If you're referring the java source method, then you need to check the above discussions with dariyoosh where i've mentioned the steps precisely, containing the Java source compilation, publishing of java call and granting of priviledges.

    Later, when i execute the below script:
    Code (SQL):

    SET ServerOutput    ON;
    DECLARE
        POutput    Dbms_Output.Chararr;
        PLines     INTEGER := 1000;
    BEGIN
        Dbms_Output.Enable(1000000);
        Dbms_Java.Set_Output(1000000);
        Amrit_Developers_Toolkit.ExecuteCmd(p_command => '/d02/clone-appl/apps/tech_st/10.1.2/bin/rwrun report=/d02/clone-appl/apps/apps_st/appl/gl/12.0.0/reports/US/AMRIT_BS_TFRM.rdf userid=apps/apps desformat=pdf destype=file desname=/d02/clone-appl/apps/apps_st/appl/gl/12.0.0/reports/US/AMRIT_BS_TFRM.pdf PERIOD_NAME=''APR-11-12'' P_ACCESS_SET_ID=1000 P_ORG_ID=82 P_UNIT=11');
        Dbms_Output.Get_Lines(POutput,PLines);
        FOR i IN 1 .. PLines
        Loop
            Dbms_Output.Put_Line(POutput(i));
        END Loop;
    END;
     
    I got an output:
    Process err :/d02/clone-appl/apps/tech_st/10.1.2/bin/rwrun: error while loading shared libraries: libjvm.so: cannot open shared object file: No such file or directory
    PL/SQL procedure successfully completed.


    **Here also, as expected the .pdf file isn't created.

    Thanks once again, plz reply ASAP
    Regards
    Ari
     
  9. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    221
    Likes Received:
    23
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi guyz,

    I need some solution & fast, .. my boss is on my head :(
    Plz save my life.

    Regards
    Ari
     
  10. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    I think I already gave you the solution to the problem. What you're talking about now is another problem. If you run rwrun on a Linux terminal and you get libjvm.so: cannot open shared object file: No such file or directory as error message, this is a software problem. Who installed the oracle software on that server? Either your oracle DBA or your system administrator. So if a component is missing, that is not a PL/SQL developer problem, that is their problem and it is up to them to search and find how exactly and according to what parameters they installed the software and why the installed software is not fully operational? I think you may want to say that to your boss. Particularly, in my previous example I'm sure that I showed that the Java code compiled correctly without any problem and I managed to run a Linux command which gave the desired result.

    So far, how much have you Googled for that (if any)?

    Regards,
    Dariyoosh
     
  11. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    221
    Likes Received:
    23
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Dariyoosh,

    So good to hear u back .. :)
    Well i got ur point by..
    But their is a bit of circumstantial problem, our DBA unfortunately is out of town on another project and i'm the only one who has to make everything working for now.

    Although, the component ain't missing, its there, i mentioned it above the libjvm.so file is existing and also my LD_LIBRARY_PATH is defined.. so kindly help me in setting up the config required. Plzz plzz plzzz .... I beg .. :D Lol sounds crazzy but .. plzz

    Yepp !! I've, plenty of it!! But can't figure out the appropriate one, as its oceans of info n i'm just a novice.

    Regards
    Ari
     
  12. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Personally I don't know rwrun command. I'll ask colleague, if I found something I'll post it here.


    Regards,
    Dariyoosh
     
  13. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260