Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: Shell script to SQLPlust works - sometimes...

    Any advice would be a big help.

    First, let me say that I am not a unix or oracle person - but that it is now my duty to keep these systems up and running.

    7 unix shells calls a single oracle stored procedure. The procedure doesn't always respond when it is complete and the task continues to run on the unix box. On some days, all 7 of them complete. Other days, only 3 or 4 of them complete. That is, they all delete all the records every day, but they don't always go away on the unix side when they are finished.

    Stored procedure:

    CREATE OR REPLACE PROCEDURE NSF_OWNER_CON.nsf_user_con_table_purge
    (
    tbl_name in varchar,
    days_bk in integer,
    col_name in varchar,
    err_cde out number,
    name_tbl out varchar,
    err_mess out varchar,
    del_count out number,
    out_date out date
    )
    AS
    counter int;
    del_dte date;


    BEGIN
    counter := 0;
    del_count := 0;
    del_dte := '01-JAN-2000';
    name_tbl := tbl_name;

    SELECT (sysdate - days_bk) into del_dte from dual;

    out_date := del_dte;

    LOOP
    EXECUTE IMMEDIATE 'DELETE from ' || tbl_name || '
    where ' || col_name || ' < ''' || del_dte || '''and rownum < 10000 ';
    counter := SQL%ROWCOUNT;
    del_count := del_count + counter;
    COMMIT;
    EXIT WHEN counter < 9999;
    END LOOP;

    EXCEPTION
    WHEN OTHERS THEN
    err_cde := SQLCODE;
    err_mess := SQLERRM;
    END;

    1 of the 7 shells:

    #!/bin/ksh
    # nsf_table_purge_hist.ksh

    ORACLE_HOME=/opt/oclient/10.2.0
    export ORACLE_HOME
    LD_LIBRARY_PATH=/opt/oclient/10.2.0/lib:/usr/lib:/usr/local/lib
    export LD_LIBRARY_PATH

    /opt/oclient/10.2.0/bin/sqlplus <user id / pswd removed> @alns_prd <<ENDOFSQL > nsf_table_purge_hist.log

    SET SERVEROUTPUT ON FORMAT WRAPPED
    declare
    scode number;
    tbl_name varchar(29);
    deldte date;
    del_count number;
    err_mess varchar(80);
    begin

    nsf_owner_con.nsf_user_con_table_purge('eds_hist', 190,'record_date',scode,tbl_name,err_mess,del_coun t,deldte);
    DBMS_OUTPUT.PUT_LINE('tbl_name = ' || tbl_name);
    DBMS_OUTPUT.PUT_LINE('SCODE = ' || scode );
    DBMS_OUTPUT.PUT_LINE('deldte = ' || deldte);
    DBMS_OUTPUT.PUT_LINE('del_count = ' || del_count);
    DBMS_OUTPUT.PUT_LINE('err message = ' || err_mess);
    END;
    /
    exit;
    ENDOFSQL
    echo 'nsf_table_purge_hist.ksh complete - date = ' `date` >> nsf_table_purge_hist.log

    mv nsf_table_purge_hist.log /home/austinnsf/DataLoad/Reports/nsf_table_purge_hist.log.`date +%Y%m%d_%H.%M.%S`
    exit $?

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Are you saying these lines in the sql always get executed and thus the lines are written to the log file:
    Code:
    DBMS_OUTPUT.PUT_LINE('tbl_name = ' || tbl_name);
    DBMS_OUTPUT.PUT_LINE('SCODE = ' || scode );
    DBMS_OUTPUT.PUT_LINE('deldte = ' || deldte);
    DBMS_OUTPUT.PUT_LINE('del_count = ' || del_count);
    DBMS_OUTPUT.PUT_LINE('err message = ' || err_mess);

    And these last 2 lines in the shell script always get executed but the shell script hangs on the the "exit $?" statement:
    Code:
    echo 'nsf_table_purge_hist.ksh complete - date = ' `date` >> nsf_table_purge_hist.log
    mv  nsf_table_purge_hist.log /home/austinnsf/DataLoad/Reports/nsf_table_purge_hist.log.`date +%Y%m%d_%H.%M.%S`

  3. #3
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Has this always happened since the scripts were originally written, or has this just started recently?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •