Help with setting a cursor to be used for a loop

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.

Quote:

---

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.

Sorry I am not explaining it well. I only need one fields value from the patient table. for example the form table has these fileds

form-
patient
form_code
date
pform

and the pateint table has these fields
patient
first
last
address

my insert for one patient is
insert into form values ('123', 'RESRCH', sysdate, '181281')
that would be for the patient who's number is 123 in the patient table. I have to insert a row in the form table for each patient in the patient table and the first value needs to be the patient number.

if I try :
insert into PFORM values ((select "Patient" p1 from PATIENT where "Patient" NOT IN ( select a."Patient" from PATIENT A, PFORM B where a."Patient"=b."Patient")), 'RESRCH', trunc(sysdate), '20240', PFORM_PFORM_SEQ.nextVal, 'Research Consent & Info', '0','783285', trunc(sysdate), '1', '20240', trunc(sysdate), '0','0','', ' ', ' ', '0', ' ')
which is what I think you sugested I get:
ORA-01427: single-row subquery returns more than one row
does this make more sense?
sorry for the confusion.

Lets go to the basics, is this what you are looking for?

Code:

---

SQL>
SQL> create table PATIENT(
  2  patient_nbr number(10));

Table created.

SQL> insert into PATIENT select 1 from dual;

1 row created.

SQL> insert into PATIENT select 2 from dual;

1 row created.

SQL> insert into PATIENT select 3 from dual;

1 row created.

SQL> insert into PATIENT select 4 from dual;

1 row created.

SQL> insert into PATIENT select 5 from dual;

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> create table FORM(
  2  patient_nbr number(10),
  3  some_date date);

Table created.

SQL> insert into FORM select 2,NULL from dual;

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from PATIENT;

PATIENT_NBR
-----------
          1
          2
          3
          4
          5

SQL>
SQL> select * from FORM;

PATIENT_NBR SOME_DATE
----------- ---------
          2

SQL>
SQL> insert
  2  into    FORM
  3          select a.patient_nbr, sysdate
  4          from  PATIENT a
  5          where (a.patient_nbr not in (select b.patient_nbr
  6                                      from  FORM b))
  7  ;

4 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from FORM;

PATIENT_NBR SOME_DATE
----------- ---------
          2
          1 15-SEP-10
          3 15-SEP-10
          4 15-SEP-10
          5 15-SEP-10

SQL>

---

haha, yup that works. However before I heard back from you I figured another way to do it. Much more involved. this is the script I used:
create table AXIUM.RESRCHTMP
(
"Patient" NUMBER(10)
);

insert into AXIUM.RESRCHTMP ("Patient") SELECT "Patient" FROM PATIENT WHERE "Patient" NOT IN ( SELECT a."Patient" FROM PATIENT A, PFORM B WHERE a."Patient"= b."Patient" and "FormCode"='RESRCH' );

ALTER TABLE AXIUM.RESRCHTMP
ADD (
"FormCode" CHAR(6 BYTE),
"Date" DATE,
"User" NUMBER(10),
"PForm" NUMBER(10),
"Description" VARCHAR2(30 BYTE),
"OwnerType" NUMBER(5),
"OwnerId" NUMBER(10),
"CreatedDate" DATE,
"Approved" NUMBER(5),
"LastAppUser" NUMBER(10),
"LastAppDate" DATE,
"Inactive" NUMBER(5),
"InactiveAppUser" NUMBER(10),
"InactiveAppDate" DATE,
"InactiveReason" VARCHAR2(50 BYTE),
"FStatus" CHAR(6 BYTE),
"AssignedUser" NUMBER(10),
"AssignedUGroup" CHAR(6 BYTE)
);



update RESRCHTMP set "FormCode"='RESRCH',
"Date"=trunc(sysdate),
"User"='20240',
"PForm"=PFORM_PFORM_SEQ.nextVal,
"Description"='Research Consent & Info',
"OwnerType"='0',
"OwnerId"="Patient",
"CreatedDate"=trunc(sysdate),
"Approved"='1',
"LastAppUser"='20240',
"LastAppDate"=trunc(sysdate),
"Inactive"= '0',
"InactiveAppUser"='0',
"AssignedUser"= '0';

INSERT INTO PFORM SELECT * FROM RESRCHTMP;

DROP TABLE RESRCHTMP;

It did what I needed, just more involved.

Quote:

---

Originally Posted by jayjabour

haha, yup that works. However before I heard back from you...

---

Who's fault is that?
I solved within the hour, you let the ticket unattended for an entire week.

it wasn't a dig on you. I came up with my method seeing as it worked (although not the best way) I didn't go back to the forum, for the solution. I got busy, today had some down time was looking around here saw the post and thought rather than not responding, maybe show another way. I guess next time I'll just say thanks and nothing more.