-
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)
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|