Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    9

    Unanswered: SQL flow control

    Hello!

    I'm wondering if there is a way to control the execution of procedures within SQL? For example, I have a procedure "START_ALL" that kicks off two other procedures "GET_LAST_UPDATE_ID" and "GET_ORG_ID" respectively. How can I make sure procedure "GET_LAST_UPDATE_ID" finishes before procedure "GET_ORG_ID" begins? Here is what I have so far:


    PROCEDURE START_ALL
    (
    out1 OUT VARCHAR2,
    out2 OUT VARCHAR2,
    Client_ID IN VARCHAR2,
    )
    IS

    BEGIN
    Short_Name := upper(Client_ID);
    GET_LAST_UPDATE_ID(out1,out2);
    GET_ORG_ID(out1,out2);
    END;


    PROCEDURE GET_LAST_UPDATE_ID
    (
    out1 VARCHAR2,
    out2 VARCHAR2
    )
    IS
    BEGIN
    SELECT fnd_logins_s.NEXTVAL
    INTO Last_Update_ID
    FROM dual;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    error_type := 'A';
    error_message := SQLERRM;
    AR_PROFILE_ERROR_HANDLER(proc_name,error_message,e rror_type);
    WHEN others THEN
    error_type := 'C';
    error_message := SQLERRM;
    AR_PROFILE_ERROR_HANDLER(proc_name,error_message,e rror_type);
    END;


    PROCEDURE GET_ORG_ID
    (
    out1 VARCHAR2,
    out2 VARCHAR2
    )
    IS
    BEGIN
    SELECT organization_id
    INTO Org_ID
    FROM hr_all_organization_units
    WHERE NAME = Short_Name;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    error_type := 'A';
    error_message := SQLERRM;
    AR_PROFILE_ERROR_HANDLER(proc_name,error_message,e rror_type);
    WHEN others THEN
    error_type := 'C';
    error_message := SQLERRM;
    AR_PROFILE_ERROR_HANDLER(proc_name,error_message,e rror_type);
    END;


    Thank you in advance for the suggestions.

    -Don-

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Exactly as you coded it, in the order executed by 'PROCEDURE START_ALL'.



    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2003
    Posts
    9

    Smile

    Thank you LKBrwn_DBA.

    I did not know that for each procedure that the next one does not start before the previous one is finished.

    -Don-

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    If you need to execute independently, you would need to 'SUBMIT' as job using the DBMS_JOB package.


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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