Need help with stored procedure that inserts

[stressball]

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 <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute cast trim forall
<a string literal with character set specification>
<a number> <a single-quoted SQL string>

19/61 PLS-00103: Encountered the symbol &quot;,&quot; when expecting one of the
following:
<an identifier> <a double-quoted delimited-identifier> set

19/125 PLS-00103: Encountered the symbol &quot;SELECT&quot; when expecting one of
the following:
return <an identifier> <a double-quoted delimited-identifier>
context sqlcode sqlstate sqlname

19/162 PLS-00103: Encountered the symbol &quot;;&quot; when expecting one of the
following:
<an identifier> <a double-quoted delimited-identifier> 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)

 

[lewisp]

Try this:

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;
max_act_id NUMBER;
BEGIN
FOR client_ref in id_crsr LOOP

select max(CLI.CLIENT_ACT_ID) + 1
into max_act_id
from CLIENT_ACTIVITIES
where client_ref = cleint_ref.client_ref;

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;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUT.PUT_LINE(SQLERRM);
END;
/

 

[stressball]

Thank you! I made a couple of small changes and this is the final procedure.

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;
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;
/