|
-
 Originally Posted by jayjabour
Here is my problem. I need to insert a row into a table called form for each row in a table called patient. there are 86 thousand rows in the patient table. So I figured instead of 86 thousand insert statements, write 1 that does a loop. I figured I needed to set a cursor that holds the value from the patient table I need. here is what I have come up with so far:
create or replace procedure RESEARCH
is
begin
cursor p1 is
select "Patient" from PATIENT where "Patient" NOT IN ( select a."Patient" from PATIENT A, PFORM B where a."Patient"=b."Patient");
for PFORM in p1
loop
INSERT into PFORM values (p.p1, 'RESRCH', trunc(sysdate), '20240', PFORM_PFORM_SEQ.nextVal, 'Research Consent & Info', '0',p.p1, trunc(sysdate), '1', '20240', trunc(sysdate), '0','0','', ' ', ' ', '0', ' ');
end loop;
but when I go to create the package It fails because it doesn't like the p1 in the cursor line.
Not sure why a cursor is needed when a simple "insert into FORM from (select blah, blah, blah from PATIENT where wathever-condition-if-any) would solve it.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
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
|