Help with setting a cursor to be used for a loop
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Help with setting a cursor to be used for a loop

  1. #1
    Join Date
    Mar 2005
    Posts
    143

    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.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by jayjabour View Post
    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.

  3. #3
    Join Date
    Mar 2005
    Posts
    143
    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.
    Last edited by jayjabour; 09-15-2010 at 04:13 PM.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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>
    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.

  5. #5
    Join Date
    Mar 2005
    Posts
    143
    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.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by jayjabour View Post
    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.
    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.

  7. #7
    Join Date
    Mar 2005
    Posts
    143
    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.

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