Help with procedure that must insert
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help with procedure that must insert

  1. #1
    Join Date
    Mar 2001
    Posts
    7
    Hi, I desparately need some help with this procedure, I haven't worked with Oracle in while, and I'm all in a fluster about how to do this.
    I need to get the client_ref (the id) from the client table check that it does not exist in the client_activities table, if it does not exist then I want to insert a row into the client_activities table for each client_ref that does not exist. Below is the procedure code, a listing of the errors, and the table definition for client_activities. The sql statement used for the cursor does work correctly. Any ideas or pointers? I would be most grateful.

    CREATE PROCEDURE register_clients IS
    CURSOR id_crsr IS
    select client_ref
    from client
    where client_ref not in
    (select client_ref
    from client_activities);
    varCommCode VARCHAR2(5) := 'HLTH';
    varProgCode VARCHAR2(5) := 'TO';
    varActCode VARCHAR2(7) := 'REG';
    varUserOrg NUMBER(10) := 100;
    varVersion NUMBER(5) :=1;
    BEGIN
    FOR client_ref in id_crsr LOOP
    INSERT INTO CLIENT_ACTIVITIES
    (CLIENT_ACT_ID, CLIENT_REF, COMMUNITY_CODE, PROGRAM_CODE,
    ACTIVITY_CODE, USER_ORG_ID, EFF_DATE, VERSION)
    VALUES
    (select max(CLI.CLIENT_ACT_ID+1) from CLIENT_ACTIVITIES CLI), client_ref, varCommCode, varProgCode, varActCode, varUserOrg, select sysdate from dual, varVersion);
    END LOOP;
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUT.PUT_LINE(SQLERRM);
    END;
    /

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    19/2 PLS-00103: Encountered the symbol "SELECT" when expecting one of
    the following:
    ( - + mod not null others
    avg
    count current exists max min prior sql stddev sum variance
    execute cast trim forall



    19/61 PLS-00103: Encountered the symbol "," when expecting one of the
    following:
    set

    19/125 PLS-00103: Encountered the symbol "SELECT" when expecting one of
    the following:
    return

    context sqlcode sqlstate sqlname

    19/162 PLS-00103: Encountered the symbol ";" when expecting one of the
    following:
    set


    SQL> desc client_activities
    Name Null? Type
    ------------------------------- -------- ----
    CLIENT_ACT_ID NOT NULL NUMBER
    CLIENT_REF NOT NULL NUMBER(10)
    COMMUNITY_CODE NOT NULL VARCHAR2(5)
    PROGRAM_CODE NOT NULL VARCHAR2(5)
    ACTIVITY_CODE NOT NULL VARCHAR2(7)
    USER_ORG_ID NOT NULL NUMBER(10)
    EFF_DATE NOT NULL DATE
    VERSION NOT NULL NUMBER(5)

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Where do I start...
    First of all, you have got maybe 50 syntax errors. You should look at http://technet.oracle.com/docs/produ...a77069/toc.htm and start on page 1.

    Second, there are things called sequences in oracle for populating id number columns. Check out http://technet.oracle.com/docs/produ...5_ora.htm#1002 for starters.

    Lastly, you can't reference SQLERRM directly. See http://technet.oracle.com/docs/produ..._errs.htm#1147 for details.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Mar 2001
    Posts
    7
    Got it!

    Heres the final procedure.

    In reply to marist89:
    I am well aware of sequences and use them often. This is not my system, it was developed by someone else, and the client required an update procedure. I have been spending alot of time immersed in using postgresql and java, and my oracle stored procedure skills had gotten a little rusty.

    CREATE PROCEDURE register_all_clients IS
    CURSOR id_crsr IS
    select client_ref
    from client
    where client_ref not in
    (select client_ref
    from client_activities);
    varCommCode VARCHAR2(5) := 'HLTH';
    varProgCode VARCHAR2(5) := 'TO';
    varActCode VARCHAR2(7) := 'REG';
    varUserOrg NUMBER(10) := 100;
    varVersion NUMBER(5) :=1;
    max_act_id NUMBER;
    BEGIN
    FOR client_ref in id_crsr LOOP

    select max(CLIENT_ACT_ID) + 1
    into max_act_id
    from CLIENT_ACTIVITIES;

    INSERT INTO CLIENT_ACTIVITIES
    (CLIENT_ACT_ID, CLIENT_REF, COMMUNITY_CODE, PROGRAM_CODE,
    ACTIVITY_CODE, USER_ORG_ID, EFF_DATE, VERSION)
    values (
    max_act_id,client_ref.client_ref, varCommCode, varProgCode, varActCode, varUserOrg, sysdate, varVersion);
    END LOOP;
    END;
    /

Posting Permissions

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



Click Here to Expand Forum to Full Width