This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Jun 5, 2012 1:36 AM by 933651 RSS

Calling Shell Script from Oracle Procedure

640547 Newbie
Currently Being Moderated
I am supposed to call a shell script from oracle procedure. I have to also pass 5 paramters to the shell script and check in oracle if the shell script executed successfully or not. If not then raise application error esle continue. Please help
  • 1. Re: Calling Shell Script from Oracle Procedure
    686309 Newbie
    Currently Being Moderated
    sqlplus -s $USER/$PWD@DBNAME
    set serveroutput on size 100000;

    exec Pck_Name.Proc_Name(param1,2,3,4,5);

    And exec the shell by the name Name.ksh...
  • 2. Re: Calling Shell Script from Oracle Procedure
    OrionNet Guru
    Currently Being Moderated
    Hello,

    What's your oracle version? if you are using 10g then you can use dbms_scheduler. You can pass parameter as argument to your shell script.

    #myscipt.sh
    #!/bin/ksh
    echo "Calling shell script from Oracle usign dbms scheduler `date`" >> /u01/oradata/log/test.log
    Oracle job calls shell script using dbms_scheduler.
    BEGIN
       DBMS_SCHEDULER.CREATE_JOB (
          job_name          => 'TEST_SHELL',
          job_type          => 'EXECUTABLE',
          job_action        => '/u01/oradata/myscript.sh',
          start_date        => SYSDATE,
          repeat_interval   => 'FREQ=MINUTELY; INTERVAL=1',   
          enabled           => TRUE,
          comments          => 'Calling shell script from Oracle'
       );
    END;
    /
    Regards

    Edited by: OrionNet on Feb 26, 2009 11:42 PM
  • 3. Re: Calling Shell Script from Oracle Procedure
    640547 Newbie
    Currently Being Moderated
    how do i pass parameters to the shell script and how do i check the return code of the shell script in the procedure
  • 4. Re: Calling Shell Script from Oracle Procedure
    Kamran Agayev A. Oracle ACE Director
    Currently Being Moderated
    There's an example in my blog, you can check it. I create a shell script and pass parameter from it to stored procedure

    http://kamranagayev.wordpress.com/2009/02/23/using-oracle-utl_file-utl_smtp-packages-and-linux-shell-scripting-and-cron-utility-together-2/

    - - - - - - - - - - - - - - - - - - - - -
    Kamran Agayev A. (10g OCP)
    http://kamranagayev.wordpress.com
  • 5. Re: Calling Shell Script from Oracle Procedure
    640547 Newbie
    Currently Being Moderated
    I am supposed to log invalid records while doing insert into table... select from table2. I thought i can use DML_error_logging in 10g but it does not catch primary key violation. Can anyone tell me how to catch invalid records with the ORA exception into an error table? Also, there are a lot of records, so i would prefer if the method does not use cursor
    Thanks
  • 6. Re: Calling Shell Script from Oracle Procedure
    Kamran Agayev A. Oracle ACE Director
    Currently Being Moderated
    Use PL/SQL code to INSERT your data into table and write an exceptin, in this exception INSERT the row into error_log table

    - - - - - - - - - - - - - - - - - - - - -
    Kamran Agayev A. (10g OCP)
    http://kamranagayev.wordpress.com
  • 7. Re: Calling Shell Script from Oracle Procedure
    640547 Newbie
    Currently Being Moderated
    sorry, wrong thread.
    1) How to catch the exception causing record? [i don't want to use for loop to insert]
    2) How to call sqlldr from Oracle PL/SQL
    3) How to check return code of shell script which was called from Oracle PL/SQL using DBMS_SCHEDULER

    Thanks
  • 8. Re: Calling Shell Script from Oracle Procedure
    Kamran Agayev A. Oracle ACE Director
    Currently Being Moderated
    Can you please open new topic for these questions? This topic's subjec it "Calling Shell Script from Oracle Procedure"

    - - - - - - - - - - - - - - - - - - - -
    Kamran Agayev A. (10g OCP)
    http://kamranagayev.wordpress.com
  • 9. Re: Calling Shell Script from Oracle Procedure
    640547 Newbie
    Currently Being Moderated
    in my shell script, if there is an error i am returning 255 [exit 255], esle 0. How can i check if the shell script ran correctly or not?
  • 10. Re: Calling Shell Script from Oracle Procedure
    OrionNet Guru
    Currently Being Moderated
    Hello,
    In your script or command line do this
    ret=echo $?

    if ret is greater than 0 ,command failed and take apprpriate action .

    what is your intent here?
    Regards
  • 11. Re: Calling Shell Script from Oracle Procedure
    640547 Newbie
    Currently Being Moderated
    i need a dbms_scheduler script which will immediately call a shell script and pass two parameters to it. The shell script should be executed immediately. After the shell script has executed, i need to check the return code of it in the procedure which called it.
  • 12. Re: Calling Shell Script from Oracle Procedure
    OrionNet Guru
    Currently Being Moderated
    Hello,
    What you need is to do is check status of each command as i mentioned in my previous post and if it fails exits the script with a return value and handle it in anonymous pl/sql block.

    If you already tried or wrote something post it here so we can help you better.

    Regards
  • 13. Re: Calling Shell Script from Oracle Procedure
    640547 Newbie
    Currently Being Moderated
    #!/usr/bin/ksh

    #test_dbms_scheduler.ksh

    echo $1

    echo "I am in Unix"

    exit 0
    #####################################################

    chmod 755 test_dbms_scheduler.ksh

    #####################################################


    Create or replace procedure test_dbms_scheduler


    as

    v_text varchar2(255) := 'Parameter passed from Oracle to Unix';


    Begin


    dbms_output.put_line("I am in Procedure");

    dbms_scheduler.create_job

    (job_name=>'test_dbms_scheduler',


    job_action=>'/usr/bin/test_dbms_scheduler.ksh',


    number_of_arguments=>1,


    job_type=>'executable',


    start_date => SYSDATE,


    repeat_interval => 'FREQ=SECONDLY; INTERVAL=1',


    enabled=>false,


    auto_drop => TRUE,


    comments=> 'Run shell-script test_dbms_scheduler.ksh');


    dbms_scheduler.set_job_argument_value(job_name =>'test_dbms_scheduler', argument_position => 1, argument_value => v_text);

    dbms_scheduler.enable('test_dbms_scheduler');


    dbms_output.put_line("I am back in Procedure");


    Exception


    when others then

    dbms_output.put_line(sqlcode||sqlerrm);

    end;

    /



    ##################################################



    set serveroutput on

    exec test_dbms_scheduler;
  • 14. Re: Calling Shell Script from Oracle Procedure
    OrionNet Guru
    Currently Being Moderated
    Hello,

    Is there specific reason you want to do this? Generally most of the time its other way.

    Regards
1 2 3 Previous Next

Legend

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