Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    93

    Unanswered: calling Oracle procedures in Unix

    I have created 3 Procedures all similar to this one:

    I then created 3 shell sripts which will call the sql? finally created a calling script to call the procedure. I am a bit unsure how to this all works, can someone check my code and I am doing this right? Also could I add my procedure (first one to the calling procedure?)

    Procedure:
    CREATE OR REPLACE PROCEDURE UPDATE_BLANK_RENEWAL_DATA AS
    CURSOR cur_BLANK IS
    SELECT P.PARTY_ID,
    p.attribute2, ---Incentive Level
    p.attribute9, ---card Issue date
    p.attribute11, ---card renewal date
    p.attribute7,
    p.attribute15, ---Internal Status
    p.attribute6, ---Card Status
    p.last_updated_by,
    P.LAST_UPDATE_DATE
    from hz_cust_accounts ca, hz_parties p
    where ca.party_id = p.party_id and
    (p.attribute15 <> 'ARC' or
    p.attribute15 is null) and ca.account_number is not null and
    (p.attribute9 is null and p.attribute11 is null)
    or
    (p.attribute9 is not null and p.attribute11 is null)
    or
    (p.attribute9 is null and p.attribute11 is not null)

    row_count number :=0;
    begin
    for i in cur_blank loop
    if i.attribute2 = 'Inactive' then

    if i.attribute9 is null and i.attribute11 is null then
    dbms_output.put_line('in inactive');
    update ar.hz_parties
    set attribute9 = to_char
    (sysdate, 'DDMMYYYY'),
    attribute11 = to_char(sysdate + 6, 'DDMMYYYY'),
    last_updated_by = '100000001',
    LAST_UPDATE_DATE = sysdate
    where PARTY_ID = i.party_id;
    end if;
    if i.attribute9 is null and i.attribute11 is not null then
    dbms_output.put_line('in inactive n-nn');
    update ar.hz_parties
    set attribute9 = to_char(sysdate, 'DDMMYYYY'),
    last_updated_by = '100000001',
    LAST_UPDATE_DATE = sysdate
    where PARTY_ID = i.party_id;
    end if;
    if i.attribute9 is not null and i.attribute11 is null then
    dbms_output.put_line('in inactive nn-n');
    update ar.hz_parties
    set attribute11 = to_char(to_date(i.attribute9,'DDMMYYYY') + 6, 'DDMMYYYY'),
    Last_updated_by = '100000001',
    LAST_UPDATE_DATE = sysdate
    where PARTY_ID = i.party_id;
    end if;
    end if;
    IF i.attribute2 = 'Lower Tier' then
    if i.attribute9 is null and i.attribute11 is null then
    dbms_output.put_line('in lower');
    update ar.hz_parties
    set attribute9 = to_char(sysdate, 'DDMMYYYY'),
    attribute11 = to_char(sysdate + 12, 'DDMMYYYY'),
    attribute6 ='RLL',
    Last_updated_by = '100000001',
    LAST_UPDATE_DATE = sysdate
    where PARTY_ID = i.party_id;
    end if;
    if i.attribute9 is null and i.attribute11 is not null then
    dbms_output.put_line('in lower n-nn');
    update ar.hz_parties
    set attribute9 = to_char(sysdate, 'DDMMYYYY'),
    attribute6 ='RLL',
    Last_updated_by = '100000001',
    LAST_UPDATE_DATE = sysdate
    where PARTY_ID = i.party_id;
    end if;
    if i.attribute9 is not null and i.attribute11 is null then
    dbms_output.put_line('in lower nn-n');
    update ar.hz_parties
    set attribute11 = to_char(to_date(i.attribute9,'DDMMYYYY') + 12, 'DDMMYYYY'),
    attribute6 ='RLL',
    Last_updated_by = '100000001',
    LAST_UPDATE_DATE = sysdate
    where PARTY_ID = i.party_id;
    end if;
    end if;
    if i.attribute2 = 'Upper Tier' then
    if i.attribute9 is null and i.attribute11 is null then
    dbms_output.put_line('in upper');
    update ar.hz_parties
    set attribute9 = to_char(sysdate, 'DDMMYYYY'),
    attribute11 = to_char(sysdate + 12, 'DDMMYYYY'),
    attribute6 ='RUU',
    Last_updated_by = '100000001',
    LAST_UPDATE_DATE = sysdate
    where PARTY_ID = i.party_id;
    end if;
    if i.attribute9 is null and i.attribute11 is not null then
    dbms_output.put_line('in upper n-nn');
    update ar.hz_parties
    set attribute9 = to_char(sysdate, 'DDMMYYYY'),
    attribute6 ='RUU',
    Last_updated_by = '100000001',
    LAST_UPDATE_DATE = sysdate
    where PARTY_ID = i.party_id;
    end if;
    if i.attribute9 is not null and i.attribute11 is null then
    dbms_output.put_line('in upper nn-n');
    update ar.hz_parties
    set attribute11 = to_char(to_date(i.attribute9,'DDMMYYYY') + 12, 'DDMMYYYY'),
    attribute6 ='RUU',
    Last_updated_by = '100000001',
    LAST_UPDATE_DATE = sysdate
    where PARTY_ID = i.party_id;
    row_count := row_count + 1;
    END IF;
    if row_count = 1000 then
    commit;
    row_count := 0;
    end if;
    end IF;
    END LOOP;
    COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('ERROR - Data Patch Error '||'Code:' || SQLCODE || 'Error ' || SQLERRM);
    END UPDATE_BLANK_RENEWAL_DATA;




    SHell script to call SQL:#********************************************* *********************************
    # Module - T5_Update_blank_cards_data_patch.sh
    # Description - Shell script to call SQL script to run Update customers who have no Card issue date or Card Expiry Date set.
    #
    # -----------------------------------------------------------------------------
    #
    # Version Author Update date Change Description
    # Req No.
    # -----------------------------------------------------------------------------
    # 0.1 ER 19-MAR-04 Initial Version
    #
    #************************************************* ****************************/
    #!/bin/ksh

    # Check parameters
    if [[ $# -ne 0 ]]
    then
    echo "USAGE - T5_Update_blank_cards_data_patch.sql"
    exit
    fi

    # SQL Plus - Process file
    if [[ -f $ES_PATH/code/other/Data_patches/T5_Update_blank_cards_data_patch.sql ]]
    then
    sqlplus $ES_DB_STRING @$ES_PATH/code/other/Data_patches/T5_Update_blank_cards_data_patch.sql
    EXIT_CODE=$?
    else
    echo "# ERROR - Script $ES_PATH/code/other/Data_patches/T5_Update_blank_cards_data_patch.sql not f
    ound!"
    exit 1
    fi

    ### CODE END ###

    and finally my Calling Procedure:

    spool $ES_LOGS/T4_Update_blank_cards_data_patch.log

    Whenever SQLERROR EXIT 1
    Whenever OSERROR EXIT 1




    -- The data Patch to give customers a renewal date for Customers with Blank CID and CRD
    --
    -----------------------------------------------------
    -- create variable for exception reporting
    -----------------------------------------------------
    VAR incexe_except NUMBER;
    -----------------------------------------------------
    DECLARE
    lc_success VARCHAR2(1) :=NULL;
    BEGIN

    T5_Update_blank_cards(lc_success);

    ---------------------------------------------------------
    -- if error returned then set
    -- variable for caller to check
    ---------------------------------------------------------



    END;
    /
    --*****************
    --* END OF PL/SQL *
    --*****************
    --------------------------------------------------------------
    -- Check if Exception record was created, if so, set the
    -- exit code accordingly
    --------------------------------------------------------------
    set feedback off



    set feedback on

    spool off

    exit
    Cheers
    Etravels

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Did you run the shell script? What were the results? Also, what is the contents of your .SQL files you are trying to run? For example, what does "T5_Update_blank_cards_data_patch.sql" look like?

    JoeB

  3. #3
    Join Date
    Feb 2004
    Posts
    93
    Originally posted by joebednarz
    Did you run the shell script? What were the results? Also, what is the contents of your .SQL files you are trying to run? For example, what does "T5_Update_blank_cards_data_patch.sql" look like?

    JoeB
    hiya

    sorry.. the .sql script is basically the first procedure.....
    (create or replace part at the beginning)
    Cheers
    Etravels

Posting Permissions

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