Home » SQL & PL/SQL » SQL & PL/SQL » compilation errors in stored procedure (oracle 10g)
compilation errors in stored procedure [message #405468] Wed, 27 May 2009 23:39 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,
i have written aprocedure but i am getting compilation errors with this

CREATE OR REPLACE PROCEDURE ezEMRxMigrate AS
       A   NUMBER:=1;
       V2  NUMBER:=10000;
       CURSOR C1 IS SELECT SCREEN_IMMUN_ID FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID=0 ORDER BY SCREEN_IMMUN_ID;
       CURSOR C11(T1 NUMBER) IS SELECT SCREENING_REFERENCE_ID FROM  EMRPATIENTSCREENINGAUDIT WHERE SCREENING_REFERENCE_ID=T1;
       CURSOR C12(T2 NUMBER) IS SELECT SCREENING_REFERENCE_ID FROM  EMRPATIENTSCREENINGDETAILS WHERE SCREENING_REFERENCE_ID=T2;
       CURSOR C13(T3 NUMBER) IS SELECT IMMUNIZATION_REFERENCE_ID FROM  EMRPATIENTIMMUNIZATIONAUDIT WHERE IMMUNIZATION_REFERENCE_ID=T3;
       CURSOR C14(T4 NUMBER) IS SELECT IMMUNIZATION_REFERENCE_ID FROM  EMRPATIENTIMMUNIZATIONDETAILS WHERE IMMUNIZATION_REFERENCE_ID=T4;
       CURSOR C2 IS SELECT SCREEN_IMMUN_ID FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID>0 ORDER BY SCREEN_IMMUN_ID;
 BEGIN
      FOR I IN C1 LOOP
        UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID=A,MASTER_REFERENCE_ID=A;
        FOR I IN C11(I.SCREEN_IMMUN_ID) LOOP
          UPDATE EMRPATIENTSCREENINGAUDIT SET SCREENING_REFERENCE_ID=A;
        END LOOP;
        FOR I IN C12(I.SCREEN_IMMUN_ID) LOOP
           UPDATE EMRPATIENTSCREENINGDETAILS SET SCREENING_REFERENCE_ID=A;
        END LOOP;
        FOR I IN C13(I.SCREEN_IMMUN_ID) LOOP
           UPDATE  EMRPATIENTIMMUNIZATIONAUDIT SET IMMUNIZATION_REFERENCE_ID=A;
        END LOOP;
        FOR I IN C13(I.SCREEN_IMMUN_ID) LOOP
           UPDATE EMRPATIENTIMMUNIZATIONDETAILS SET IMMUNIZATION_REFERENCE_ID=A;
        END LOOP;
                A:= A+1;
      END LOOP;
         FOR I IN C2 LOOP
            UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID=V2,MASTER_REFERENCE_ID=V2;
           FOR I IN C11(I.SCREEN_IMMUN_ID) LOOP
              UPDATE EMRPATIENTSCREENINGAUDIT SET SCREENING_REFERENCE_ID=A;
           END LOOP;
           FOR I IN C12(I.SCREEN_IMMUN_ID) LOOP
              UPDATE EMRPATIENTSCREENINGDETAILS SET SCREENING_REFERENCE_ID=A;
           END LOOP;
           FOR I IN C13(I.SCREEN_IMMUN_ID) LOOP
               UPDATE  EMRPATIENTIMMUNIZATIONAUDIT SET IMMUNIZATION_REFERENCE_ID=A;
          END LOOP;
           FOR I IN C13(I.SCREEN_IMMUN_ID) LOOP
              UPDATE EMRPATIENTIMMUNIZATIONDETAILS SET IMMUNIZATION_REFERENCE_ID=A;
           END LOOP;
	       V2:= V2+1;
        END LOOP;
             UPDATE EMRIDS SET EZEMRXID=V2-1 WHERE PROPERTY_NAME='SCREEN_IMMUN_ID';
 END;
 /

errors are:

Error(15,9): PL/SQL: Statement ignored
Error(15,22): PLS-00364: loop index variable 'I' use is invalid


Re: compilation errors in stored procedure [message #405469 is a reply to message #405468] Wed, 27 May 2009 23:41 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
what is I?
Re: compilation errors in stored procedure [message #405471 is a reply to message #405469] Wed, 27 May 2009 23:41 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i is a variable i am using here
Re: compilation errors in stored procedure [message #405472 is a reply to message #405471] Wed, 27 May 2009 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
rajasekhar857 wrote on Wed, 27 May 2009 21:41
i is a variable i am using here

Variable?
No, unknown.
Where is "I" defined as what data type?
Re: compilation errors in stored procedure [message #405475 is a reply to message #405472] Wed, 27 May 2009 23:48 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
sorry to say I is a number;
CREATE OR REPLACE PROCEDURE ezEMRxMigrate AS
       A   NUMBER:=1;
       V2  NUMBER:=10000;
       I   NUMBER;
       CURSOR C1 IS SELECT SCREEN_IMMUN_ID FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID=0 ORDER BY SCREEN_IMMUN_ID;
       CURSOR C11(T1 NUMBER) IS SELECT SCREENING_REFERENCE_ID FROM  EMRPATIENTSCREENINGAUDIT WHERE SCREENING_REFERENCE_ID=T1;
       CURSOR C12(T2 NUMBER) IS SELECT SCREENING_REFERENCE_ID FROM  EMRPATIENTSCREENINGDETAILS WHERE SCREENING_REFERENCE_ID=T2;
       CURSOR C13(T3 NUMBER) IS SELECT IMMUNIZATION_REFERENCE_ID FROM  EMRPATIENTIMMUNIZATIONAUDIT WHERE IMMUNIZATION_REFERENCE_ID=T3;
       CURSOR C14(T4 NUMBER) IS SELECT IMMUNIZATION_REFERENCE_ID FROM  EMRPATIENTIMMUNIZATIONDETAILS WHERE IMMUNIZATION_REFERENCE_ID=T4;
       CURSOR C2 IS SELECT SCREEN_IMMUN_ID FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID>0 ORDER BY SCREEN_IMMUN_ID;
 BEGIN
      FOR I IN C1 LOOP
        UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID=A,MASTER_REFERENCE_ID=A;
	END LOOP;
        FOR I IN C11(I.SCREEN_IMMUN_ID) LOOP
          UPDATE EMRPATIENTSCREENINGAUDIT SET SCREENING_REFERENCE_ID=A;
        END LOOP;
        FOR I IN C12(I.SCREEN_IMMUN_ID) LOOP
           UPDATE EMRPATIENTSCREENINGDETAILS SET SCREENING_REFERENCE_ID=A;
        END LOOP;
        FOR I IN C13(I.SCREEN_IMMUN_ID) LOOP
           UPDATE  EMRPATIENTIMMUNIZATIONAUDIT SET IMMUNIZATION_REFERENCE_ID=A;
        END LOOP;
        FOR I IN C13(I.SCREEN_IMMUN_ID) LOOP
           UPDATE EMRPATIENTIMMUNIZATIONDETAILS SET IMMUNIZATION_REFERENCE_ID=A;
        END LOOP;
                A:= A+1;
      END LOOP;
         FOR I IN C2 LOOP
            UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID=V2,MASTER_REFERENCE_ID=V2;
           FOR I IN C11(I.SCREEN_IMMUN_ID) LOOP
              UPDATE EMRPATIENTSCREENINGAUDIT SET SCREENING_REFERENCE_ID=A;
           END LOOP;
           FOR I IN C12(I.SCREEN_IMMUN_ID) LOOP
              UPDATE EMRPATIENTSCREENINGDETAILS SET SCREENING_REFERENCE_ID=A;
           END LOOP;
           FOR I IN C13(I.SCREEN_IMMUN_ID) LOOP
               UPDATE  EMRPATIENTIMMUNIZATIONAUDIT SET IMMUNIZATION_REFERENCE_ID=A;
          END LOOP;
           FOR I IN C13(I.SCREEN_IMMUN_ID) LOOP
              UPDATE EMRPATIENTIMMUNIZATIONDETAILS SET IMMUNIZATION_REFERENCE_ID=A;
           END LOOP;
	       V2:= V2+1;
        END LOOP;
             UPDATE EMRIDS SET EZEMRXID=V2-1 WHERE PROPERTY_NAME='SCREEN_IMMUN_ID';
 END;
 /
AFTER CHANGING THE CODE LIKE THIS IAM GETTING ERRORS LIKE

Error(29,13): PLS-00113: END identifier 'LOOP' must match 'EZEMRXMIGRATE' at line 1, column 11
Error(30,8): PLS-00103: Encountered the symbol "FOR" 
Error(46,14): PLS-00103: Encountered the symbol "UPDATE" 

Re: compilation errors in stored procedure [message #405477 is a reply to message #405468] Wed, 27 May 2009 23:54 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
If you can not solve simple syntax errors, perhaps you should seek other areas where you can succeed on your own, without depending upon the kindness of strangers.

Your answer is just a few clicks beyond the URL below.
http://tahiti.oracle.com
Let us know when you have found your answer.
Re: compilation errors in stored procedure [message #405478 is a reply to message #405477] Thu, 28 May 2009 00:24 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
create or replace PROCEDURE ezEMRxMigrate AS
       A   NUMBER:=1;
       V2  NUMBER:=10000;
       CURSOR C1 IS SELECT SCREEN_IMMUN_ID FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID=0 ORDER BY SCREEN_IMMUN_ID;
       CURSOR C11(T1 NUMBER) IS SELECT SCREENING_REFERENCE_ID FROM  EMRPATIENTSCREENINGAUDIT WHERE SCREENING_REFERENCE_ID=T1;
       CURSOR C12(T2 NUMBER) IS SELECT SCREENING_REFERENCE_ID FROM  EMRPATIENTSCREENINGDETAILS WHERE SCREENING_REFERENCE_ID=T2;
       CURSOR C13(T3 NUMBER) IS SELECT IMMUNIZATION_REFERENCE_ID FROM  EMRPATIENTIMMUNIZATIONAUDIT WHERE IMMUNIZATION_REFERENCE_ID=T3;
       CURSOR C14(T4 NUMBER) IS SELECT IMMUNIZATION_REFERENCE_ID FROM  EMRPATIENTIMMUNIZATIONDETAILS WHERE IMMUNIZATION_REFERENCE_ID=T4;
       CURSOR C2 IS SELECT SCREEN_IMMUN_ID FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID>0 ORDER BY SCREEN_IMMUN_ID;
 BEGIN
      FOR I IN C1 LOOP
        UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID=A,MASTER_REFERENCE_ID=A;
	END LOOP;
        FOR I IN C11(I.SCREEN_IMMUN_ID) LOOP
          UPDATE EMRPATIENTSCREENINGAUDIT SET SCREENING_REFERENCE_ID=A;
        END LOOP;
        FOR I IN C12(I.SCREEN_IMMUN_ID) LOOP
           UPDATE EMRPATIENTSCREENINGDETAILS SET SCREENING_REFERENCE_ID=A;
        END LOOP;
        FOR I IN C13(I.SCREEN_IMMUN_ID) LOOP
           UPDATE  EMRPATIENTIMMUNIZATIONAUDIT SET IMMUNIZATION_REFERENCE_ID=A;
        END LOOP;
        FOR I IN C14(I.SCREEN_IMMUN_ID) LOOP
           UPDATE EMRPATIENTIMMUNIZATIONDETAILS SET IMMUNIZATION_REFERENCE_ID=A;
	END LOOP;
	      A:= A+1;
      END LOOP;
         FOR I IN C2 LOOP
            UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID=V2,MASTER_REFERENCE_ID=V2;
           FOR I IN C11(I.SCREEN_IMMUN_ID) LOOP
              UPDATE EMRPATIENTSCREENINGAUDIT SET SCREENING_REFERENCE_ID=A;
           END LOOP;
           FOR I IN C12(I.SCREEN_IMMUN_ID) LOOP
              UPDATE EMRPATIENTSCREENINGDETAILS SET SCREENING_REFERENCE_ID=A;
           END LOOP;
           FOR I IN C13(I.SCREEN_IMMUN_ID) LOOP
               UPDATE  EMRPATIENTIMMUNIZATIONAUDIT SET IMMUNIZATION_REFERENCE_ID=A;
          END LOOP;
           FOR I IN C14(I.SCREEN_IMMUN_ID) LOOP
              UPDATE EMRPATIENTIMMUNIZATIONDETAILS SET IMMUNIZATION_REFERENCE_ID=A;
	   END LOOP;
                 V2:= V2+1;
        END LOOP;
             UPDATE EMRIDS SET EZEMRXID=V2-1 WHERE PROPERTY_NAME='SCREEN_IMMUN_ID';
 END;
 /


EXCEPTIONS:

Error(28,11): PLS-00113: END identifier 'LOOP' must match 'EZEMRXMIGRATE' at line 1, column 11

Error(29,10): PLS-00103: Encountered the symbol "FOR" 

Error(45,14): PLS-00103: Encountered the symbol "UPDATE" 






Re: compilation errors in stored procedure [message #405479 is a reply to message #405468] Thu, 28 May 2009 00:25 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Rajsekhar,

Do you really work on SQL/PL-SQL or something else,Which tool you use to write Stored Procedures (If you do write !!)

See the format below, and the one you sent ..Compare yourself

CREATE OR REPLACE PROCEDURE ezemrxmigrate
AS
   a    NUMBER := 1;
   v2   NUMBER := 10000;
   i    NUMBER;

   CURSOR c1
   IS
      SELECT   screen_immun_id
          FROM emrscreenimmunlkup
         WHERE GROUP_ID = 0
      ORDER BY screen_immun_id;

   CURSOR c11 (t1 NUMBER)
   IS
      SELECT screening_reference_id
        FROM emrpatientscreeningaudit
       WHERE screening_reference_id = t1;

   CURSOR c12 (t2 NUMBER)
   IS
      SELECT screening_reference_id
        FROM emrpatientscreeningdetails
       WHERE screening_reference_id = t2;

   CURSOR c13 (t3 NUMBER)
   IS
      SELECT immunization_reference_id
        FROM emrpatientimmunizationaudit
       WHERE immunization_reference_id = t3;

   CURSOR c14 (t4 NUMBER)
   IS
      SELECT immunization_reference_id
        FROM emrpatientimmunizationdetails
       WHERE immunization_reference_id = t4;

   CURSOR c2
   IS
      SELECT   screen_immun_id
          FROM emrscreenimmunlkup
         WHERE GROUP_ID > 0
      ORDER BY screen_immun_id;
BEGIN
   FOR i IN c1
   LOOP
      UPDATE emrscreenimmunlkup
         SET screen_immun_id = a,
             master_reference_id = a;

      FOR i IN c11 (i.screen_immun_id)
      LOOP
         UPDATE emrpatientscreeningaudit
            SET screening_reference_id = a;
      END LOOP;

      FOR i IN c12 (i.screen_immun_id)
      LOOP
         UPDATE emrpatientscreeningdetails
            SET screening_reference_id = a;
      END LOOP;

      FOR i IN c13 (i.screen_immun_id)
      LOOP
         UPDATE emrpatientimmunizationaudit
            SET immunization_reference_id = a;
      END LOOP;

      FOR i IN c13 (i.screen_immun_id)
      LOOP
         UPDATE emrpatientimmunizationdetails
            SET immunization_reference_id = a;
      END LOOP;

      a := a + 1;
   END LOOP;

   FOR i IN c2
   LOOP
      UPDATE emrscreenimmunlkup
         SET screen_immun_id = v2,
             master_reference_id = v2;

      FOR i IN c11 (i.screen_immun_id)
      LOOP
         UPDATE emrpatientscreeningaudit
            SET screening_reference_id = a;
      END LOOP;

      FOR i IN c12 (i.screen_immun_id)
      LOOP
         UPDATE emrpatientscreeningdetails
            SET screening_reference_id = a;
      END LOOP;

      FOR i IN c13 (i.screen_immun_id)
      LOOP
         UPDATE emrpatientimmunizationaudit
            SET immunization_reference_id = a;
      END LOOP;

      FOR i IN c13 (i.screen_immun_id)
      LOOP
         UPDATE emrpatientimmunizationdetails
            SET immunization_reference_id = a;
      END LOOP;

      v2 := v2 + 1;
   END LOOP;

   UPDATE emrids
      SET ezemrxid = v2 - 1
    WHERE property_name = 'SCREEN_IMMUN_ID';
END;
/


Now don't tell me that you have the logical/syntactical errors which you want us to solve
Re: compilation errors in stored procedure [message #405480 is a reply to message #405468] Thu, 28 May 2009 00:27 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I'm talking about the original post.
Quote:
FOR I IN C1 LOOP
UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID=A,MASTER_REFERENCE_ID=A;
FOR I IN C11(I.SCREEN_IMMUN_ID) LOOP
Don't you know any variable name other than "I". Why can't you give "J" for inner for loops?
By the way, the cursor variable in the cursor for loop doesn't need a declaration.

By
Vamsi

[Updated on: Thu, 28 May 2009 00:29]

Report message to a moderator

Re: compilation errors in stored procedure [message #405505 is a reply to message #405471] Thu, 28 May 2009 02:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rajasekhar857 wrote on Thu, 28 May 2009 06:41
i is a variable i am using here

Quote:
sorry to say I is a number;
CREATE OR REPLACE PROCEDURE ezEMRxMigrate AS
       A   NUMBER:=1;
       V2  NUMBER:=10000;
       I   NUMBER;
       CURSOR C1 IS SELECT SCREEN_IMMUN_ID FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID=0 ORDER BY SCREEN_IMMUN_ID;
       CURSOR C11(T1 NUMBER) IS SELECT SCREENING_REFERENCE_ID FROM  EMRPATIENTSCREENINGAUDIT WHERE SCREENING_REFERENCE_ID=T1;
       CURSOR C12(T2 NUMBER) IS SELECT SCREENING_REFERENCE_ID FROM  EMRPATIENTSCREENINGDETAILS WHERE SCREENING_REFERENCE_ID=T2;
       CURSOR C13(T3 NUMBER) IS SELECT IMMUNIZATION_REFERENCE_ID FROM  EMRPATIENTIMMUNIZATIONAUDIT WHERE IMMUNIZATION_REFERENCE_ID=T3;
       CURSOR C14(T4 NUMBER) IS SELECT IMMUNIZATION_REFERENCE_ID FROM  EMRPATIENTIMMUNIZATIONDETAILS WHERE IMMUNIZATION_REFERENCE_ID=T4;
       CURSOR C2 IS SELECT SCREEN_IMMUN_ID FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID>0 ORDER BY SCREEN_IMMUN_ID;
 BEGIN
      FOR I IN C1 LOOP
        UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID=A,MASTER_REFERENCE_ID=A;
	END LOOP;
        FOR I IN C11(I.SCREEN_IMMUN_ID) LOOP
          UPDATE EMRPATIENTSCREENINGAUDIT SET SCREENING_REFERENCE_ID=A;
        END LOOP;



1) I is NOT a number, it is a cursor record
2) Do you know of any coding language that allows this reuse of variables? How would you ever refer to the "outer" I?
3) Your logic is either waaaaay beyond me, or flawed. 100 dollars on that last option. Why would you do this:
      FOR i IN c12 (i.screen_immun_id)
      LOOP
         UPDATE emrpatientscreeningdetails
            SET screening_reference_id = a;
      END LOOP;

Since there is no where-clause on the update, every iteration of the cursor-loop, the same set of records will be updated to the same new value over and over again.

One of the most important lessons in coding: Use meaningful names. "I" is not a meaningful name, nor are C1, C11, a, v2 etc
Re: compilation errors in stored procedure [message #405518 is a reply to message #405505] Thu, 28 May 2009 03:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is closely related to one of the OP's other posts, where he was trying to get us to help him write a cursor based procedure for something that should rightfuly be done as a series of single SQL statements.

He's yet to show any ability to understand what people mean when they tell him that he's making mistakes - the update statements he's using here are the same as the ones he was trying to use in the other thread, and we told him there that he needed to add WHERE clauses to his queries.

Does anyone feel like writing his code for him and putting him out of our misery.

Re: compilation errors in stored procedure [message #405523 is a reply to message #405518] Thu, 28 May 2009 04:25 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
  CREATE OR REPLACE PROCEDURE ezemrxmigrate AS
  a    NUMBER := 1;
  v2   NUMBER := 10000;
  
  CURSOR c1 IS 
    SELECT screen_immun_id 
    FROM   emrscreenimmunlkup 
    WHERE  GROUP_ID=0 
    ORDER BY screen_immun_id
    FOR UPDATE OF screen_immun_id;
  
  CURSOR c11(t1 NUMBER) IS
    SELECT screening_reference_id 
    FROM   emrpatientscreeningaudit 
    WHERE  screening_reference_id=t1
    FOR UPDATE OF screening_reference_id;
  
  CURSOR c12(t2 NUMBER) IS 
    SELECT screening_reference_id 
    FROM   emrpatientscreeningdetails 
    WHERE  screening_reference_id=t2
    FOR UPDATE OF screening_reference_id;
  
  CURSOR c13(t3 NUMBER) IS 
    SELECT immunization_reference_id 
    FROM   emrpatientimmunizationaudit 
    WHERE  immunization_reference_id=t3
    FOR UPDATE OF immunization_reference_id;
  
  CURSOR c14(t4 NUMBER) IS 
    SELECT immunization_reference_id 
    FROM   emrpatientimmunizationdetails 
    WHERE  immunization_reference_id=t4
    FOR UPDATE OF immunization_reference_id;
  
  CURSOR c2 IS 
    SELECT screen_immun_id 
    FROM   emrscreenimmunlkup 
    WHERE  group_id>0 
    ORDER  BY screen_immun_id
    FOR UPDATE OF screen_immun_id;
BEGIN
  FOR i IN c1 LOOP
    UPDATE emrscreenimmunlkup 
    SET    screen_immun_id=a,
           master_reference_id=a
    WHERE CURRENT OF c1;

    FOR j IN c11(i.screen_immun_id) LOOP
      UPDATE emrpatientscreeningaudit 
      SET    screening_reference_id=a
      WHERE CURRENT OF c11;
    END LOOP;
 
    FOR j IN c12(i.screen_immun_id) LOOP
       UPDATE emrpatientscreeningdetails 
       SET    screening_reference_id=a
       WHERE CURRENT OF c12;
    END LOOP;
    
    FOR j IN c13(i.screen_immun_id) LOOP
       UPDATE emrpatientimmunizationaudit 
       SET    immunization_reference_id=a
       WHERE CURRENT OF c13;
    END LOOP;
    
    FOR j IN c14(i.screen_immun_id) LOOP
       UPDATE emrpatientimmunizationdetails 
       SET    immunization_reference_id=a
       WHERE CURRENT OF c14;
    END LOOP;
    a := a+1;
  END LOOP;

  FOR i IN c2 LOOP
    UPDATE emrscreenimmunlkup 
    SET    screen_immun_id=v2,
           master_reference_id=v2
    WHERE CURRENT OF c2;
    
    FOR j IN c11(i.screen_immun_id) LOOP
      UPDATE emrpatientscreeningaudit 
      SET    screening_reference_id=a
      WHERE CURRENT OF c11;
    END LOOP;
    
    FOR j IN c12(i.screen_immun_id) LOOP
       UPDATE emrpatientscreeningdetails 
       SET    screening_reference_id=a
       WHERE CURRENT OF c12;
    END LOOP;
    
    FOR j IN c13(i.screen_immun_id) LOOP
       UPDATE emrpatientimmunizationaudit 
       SET    immunization_reference_id=a
       WHERE CURRENT OF c13;
    END LOOP;
    
    FOR j IN c14(i.screen_immun_id) LOOP
       UPDATE emrpatientimmunizationdetails 
       SET    immunization_reference_id=a
       WHERE CURRENT OF c14;
    END LOOP;
    v2:= v2+1;
  END LOOP;
  UPDATE emrids 
  SET    ezemrxid = v2-1 
  WHERE  property_name = 'SCREEN_IMMUN_ID';
END;
/

hi at execution time i am unable to see o/p.it looks like never ending.comiltion seems ok.bye execution in this procedure is not happening .why?

[Updated on: Thu, 28 May 2009 04:41] by Moderator

Report message to a moderator

Re: compilation errors in stored procedure [message #405525 is a reply to message #405468] Thu, 28 May 2009 04:30 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member


Quote:
hi at execution time i am unable to see o/p.it looks like never ending.comiltion seems ok.bye execution in this procedure is not happening .why?



You know the business, you know DDL and DML ( that's why you keep it with you and won't share with anybody), you know what output you needed, Then tell me how would we know anything about it ??

Rajsekhar,

You will get answer/suggestions only when you post the correct requirement, we cannot predict why its never ending.
Re: compilation errors in stored procedure [message #405526 is a reply to message #405523] Thu, 28 May 2009 04:37 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
hi at execution time i am unable to see o/p.it looks like never ending.comiltion seems ok.bye execution in this procedure is not happening .why?


I think I get it now...... This is cryptic message challenge, a person who can deciphers it wins !!!!
Re: compilation errors in stored procedure [message #405528 is a reply to message #405468] Thu, 28 May 2009 04:44 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I see you've discovered where clauses for updates - that helps.
Still think you've got a bug though:
The updates inside the c2 loop - are you really sure you want to be setting all those ids to a and not v2.

You've spent far too much time worrying about stuff other than whether or not you're updating the right records to the right values - get that right first.

If you want to know why it's taking so long trace the session, but the way you're doing it is the slow way (lots of cursors, updating one row at a time), if you've got a lot of data in those tables it's going to take a while.
Re: compilation errors in stored procedure [message #405550 is a reply to message #405528] Thu, 28 May 2009 06:22 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
apologies for asking this question once again.
if i give my query like this
select * from EMRSCREENIMMUNLKUP where group_id = 0 order by screen_immun_id
screen-immun_id   group_id           m_r_id
1501               0                  null
1502               0                  null
1503               0                  null
-                  -                    -
1568               0                  null are there

now i want to insert in the same table as
1                 1501                   1
2                 1501                   2
- 
68                1501                   68
69                1502                   1
70                 1502                   1
-----------
x                  1568                  1
how is it possible through the sam eprocedure which i am using

Re: compilation errors in stored procedure [message #405552 is a reply to message #405468] Thu, 28 May 2009 06:27 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Quote:

how is it possible through the sam eprocedure which i am using


Do you really think that we have understood the Procedure (??) fully ?? There are quite a replies exchanged , still you haven't posted the correct reply of what is needed.

I believe you think that we all your subordinates and we all know what is the business,logic and all


Re: compilation errors in stored procedure [message #405555 is a reply to message #405550] Thu, 28 May 2009 06:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It looks like you've changed your requirements.

If you can explain to us the relation between
screen-immun_id   group_id           m_r_id
1501               0                  null
1502               0                  null
1503               0                  null
-                  -                    -
1568               0                  null are there


and this
1                 1501                   1
2                 1501                   2
- 
68                1501                   68
69                1502                   1
70                 1502                   1
-----------
x                  1568                  1
covering key points like why the first 68 records in the After group all get the same group id, and why there are more records in the second group than the first, then we may be able to help.

If you continue making poorly formatted incoherrent posts, then people will get bored and stop trying to help.
Re: compilation errors in stored procedure [message #405666 is a reply to message #405555] Thu, 28 May 2009 23:43 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
SELECT * FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID=0;

SCREEN_IMMUN_ID        GROUP_ID               MASTER_REFERENCE_ID    
---------------------- ---------------------- ---------------------- 
1501                   0                                             
1502                   0                                             
1503                   0                                             
1504                   0                                             
1505                   0                                             
1506                   0                                             
1507                   0                                             
1508                   0                                             
1509                   0                                             
1510                   0                                             
1511                   0                                             
1512                   0                                             
1513                   0                                             
1514                   0                                             
1515                   0                                             
1516                   0                                             
1517                   0                                             
1518                   0                                             
1519                   0                                             
1520                   0                                             
1521                   0                                             
1522                   0                                             
1523                   0                                             
1524                   0                                             
1525                   0                                             
1526                   0                                             
1527                   0                                             
1528                   0                                             
1529                   0                                             
1530                   0                                             
1531                   0                                             
1532                   0                                             
1533                   0                                             
1534                   0                                             
1535                   0                                             
1536                   0                                             
1537                   0                                             
1538                   0                                             
1539                   0                                             
1540                   0                                             
1541                   0                                             
1542                   0                                             
1543                   0                                             
1544                   0                                             
1545                   0                                             
1546                   0                                             
1547                   0                                             
1548                   0                                             
1549                   0                                             
1550                   0                                             
1551                   0                                             
1552                   0                                             
1553                   0                                             
1554                   0                                             
1555                   0                                             
1556                   0                                             
1557                   0                                             
1558                   0                                             
1559                   0                                             
1560                   0                                             
1561                   0                                             
1562                   0                                             
1563                   0                                             
1564                   0                                             
1565                   0                                             
1566                   0                                             
1567                   0                                             
1568                   0                                             

68 rows selected


NOW I WNAT TO MAKE TABLE RECORDS AS 

SCREEN_IMMUN_ID        GROUP_ID               MASTER_REFERENCE_ID    
---------------------- ---------------------- ---------------------- 
1                   0                                             
2                   0                                             
3                   0                                             
--------------------------------------------
67                  0                                             
68                  0     
69                  1501                         1
70                  1501                         2
-------------------
136                 1501                         68
137                 1502                          1
138                 1502                          2
-------------------
204                  1502                        68
-------

XXXXXX               1568                       68




I WANT TO USE THIS IN BELOW ONES
CREATE OR REPLACE PROCEDURE ezemrxmigrate AS
  a    NUMBER := 1;
  v2   NUMBER := 10000;
  
  CURSOR c1 IS 
    SELECT screen_immun_id 
    FROM   emrscreenimmunlkup 
    WHERE  GROUP_ID=0 
    ORDER BY screen_immun_id
    FOR UPDATE OF screen_immun_id;
  
  CURSOR c11(t1 NUMBER) IS
    SELECT screening_reference_id 
    FROM   emrpatientscreeningaudit 
    WHERE  screening_reference_id=t1
    FOR UPDATE OF screening_reference_id;
  
  CURSOR c12(t2 NUMBER) IS 
    SELECT screening_reference_id 
    FROM   emrpatientscreeningdetails 
    WHERE  screening_reference_id=t2
    FOR UPDATE OF screening_reference_id;
  
  CURSOR c13(t3 NUMBER) IS 
    SELECT immunization_reference_id 
    FROM   emrpatientimmunizationaudit 
    WHERE  immunization_reference_id=t3
    FOR UPDATE OF immunization_reference_id;
  
  CURSOR c14(t4 NUMBER) IS 
    SELECT immunization_reference_id 
    FROM   emrpatientimmunizationdetails 
    WHERE  immunization_reference_id=t4
    FOR UPDATE OF immunization_reference_id;
  
  CURSOR c2 IS 
    SELECT screen_immun_id 
    FROM   emrscreenimmunlkup 
    WHERE  group_id>0 
    ORDER  BY screen_immun_id
    FOR UPDATE OF screen_immun_id;
BEGIN
  FOR i IN c1 LOOP
    UPDATE emrscreenimmunlkup 
    SET    screen_immun_id=a,
           master_reference_id=a
    WHERE CURRENT OF c1;

    FOR j IN c11(i.screen_immun_id) LOOP
      UPDATE emrpatientscreeningaudit 
      SET    screening_reference_id=a
      WHERE CURRENT OF c11;
    END LOOP;
 
    FOR j IN c12(i.screen_immun_id) LOOP
       UPDATE emrpatientscreeningdetails 
       SET    screening_reference_id=a
       WHERE CURRENT OF c12;
    END LOOP;
    
    FOR j IN c13(i.screen_immun_id) LOOP
       UPDATE emrpatientimmunizationaudit 
       SET    immunization_reference_id=a
       WHERE CURRENT OF c13;
    END LOOP;
    
    FOR j IN c14(i.screen_immun_id) LOOP
       UPDATE emrpatientimmunizationdetails 
       SET    immunization_reference_id=a
       WHERE CURRENT OF c14;
    END LOOP;
    a := a+1;
  END LOOP;
END;

Re: compilation errors in stored procedure [message #405667 is a reply to message #405468] Thu, 28 May 2009 23:45 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>I WANT TO USE THIS IN BELOW ONES
Please proceed to do so.
Nobody here is stopping you from achieving your desires.
Re: compilation errors in stored procedure [message #405709 is a reply to message #405666] Fri, 29 May 2009 03:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your requirements have changed yet again.

Do you actually understand what you're trying to achieve?

Here's a solution that meets your current requirement, although I've thoughtlessly writen it in the form of a single piece of SQL, rather than an inefficient and slow cursor based procedure.
with src as (select level+1500  screen_immum_id
                   ,0 group_id
                   ,to_number(null) master_reference_id 
             from dual connect by level <= 68)
select (screen_immum_id -1500)  screen_immum_id
      ,group_id
      ,master_reference_id
from   src
union all
select (s1.screen_immum_id - 1500) + (s2.screen_immum_id - 1500)*68 
       ,s2.screen_immum_id
       ,(s1.screen_immum_id - 1500)
from    src s1, src s2
order by 1;

Re: compilation errors in stored procedure [message #405712 is a reply to message #405709] Fri, 29 May 2009 03:41 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
my table structure is
desc emrscreenimmunlkup
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------
SCREEN_IMMUN_ID                NOT NULL NUMBER(20)    
RECORD_TYPE                    NOT NULL VARCHAR2(2)   
GENDER                         NOT NULL NUMBER(20)    
SUB_GENDER                              NUMBER(20)    
DESCRIPTION                    NOT NULL VARCHAR2(500) 
STARTING_AGE_OPERATOR          NOT NULL VARCHAR2(255) 
STARTING_AGE_VALUE                      NUMBER(20)    
STARTING_AGE_UNITS                      VARCHAR2(255) 
FREQUENCY                               VARCHAR2(500) 
STARTING_AGE_LIMIT_RANGE                NUMBER(20)    
STARTING_AGE_LIMIT_UNITS                VARCHAR2(255) 
AGE_END_LIMIT                           NUMBER(20)    
AGE_END_LIMIT_UNITS                     VARCHAR2(255) 
REPEAT                                  NUMBER        
REPEAT_AGE_VALUE                        NUMBER(20)    
REPEAT_AGE_UNITS                        VARCHAR2(255) 
FREQUENCY_LIMIT                         NUMBER(20)    
REPEAT_CONDITION                        VARCHAR2(255) 
GROUP_ID                                NUMBER(20)    
STATUS                                  NUMBER(1)     
CDC_CODE                                VARCHAR2(50)  
SCREEN_COMMENTS                         VARCHAR2(255) 
MASTER_REFERENCE_ID                     NUMBER(20) 




what about the rest of values then?

[Trimmed very long lines]

[Updated on: Fri, 29 May 2009 05:18] by Moderator

Report message to a moderator

Re: compilation errors in stored procedure [message #405726 is a reply to message #405712] Fri, 29 May 2009 04:12 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Mr.JRowbottom

ok that is the way i have to proceed how can i use that in procedure and rest of the columns also repeated the same way for insertion.my tables structure is i mentoed in above reply
Re: compilation errors in stored procedure [message #405745 is a reply to message #405726] Fri, 29 May 2009 05:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How, exactly, do you think that I know what you want to do with the rest of the columns?

The sql I provided is a solution in that it produces the output that you requested - at no point have managed to, or even attempted to provide a description of what you're trying to achieve.

I would guess that you'll want to extend my query to include the rest of the columns from the S1 data source in the second half of the query, and from the main data source in the first half.
Something like:
select (screen_immum_id -1500)  screen_immum_id
      ,group_id
      ,master_reference_id
      ,<more columns>
from   src
union all
select (s1.screen_immum_id - 1500) + (s2.screen_immum_id - 1500)*68 
       ,s2.screen_immum_id
       ,(s1.screen_immum_id - 1500)
       ,<more columns from s1>
from    src s1, src s2
but that is really just a guess.

Re: compilation errors in stored procedure [message #405751 is a reply to message #405745] Fri, 29 May 2009 05:43 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
INSERT INTO EMRSCREENIMMUNLKUP (SCREEN_IMMUN_ID,RECORD_TYPE,GENDER,SUB_GENDER,DESCRIPTION,STARTING_AGE_OPERATOR,STARTING_AGE_VALUE,
                                STARTING_AGE_UNITS,FREQUENCY,STARTING_AGE_LIMIT_RANGE,STARTING_AGE_LIMIT_UNITS,AGE_END_LIMIT,
				AGE_END_LIMIT_UNITS,REPEAT,REPEAT_AGE_VALUE,REPEAT_AGE_UNITS,FREQUENCY_LIMIT,REPEAT_CONDITION,GROUP_ID,
				STATUS,CDC_CODE,SCREEN_COMMENTS,MASTER_REFERENCE_ID)
	SELECT (SCREEN_IMMUN_ID-1500) SCREEN_IMMUN_ID,RECORD_TYPE,GENDER,SUB_GENDER,DESCRIPTION,STARTING_AGE_OPERATOR,STARTING_AGE_VALUE,
                                STARTING_AGE_UNITS,FREQUENCY,STARTING_AGE_LIMIT_RANGE,STARTING_AGE_LIMIT_UNITS,AGE_END_LIMIT,
				AGE_END_LIMIT_UNITS,REPEAT,REPEAT_AGE_VALUE,REPEAT_AGE_UNITS,FREQUENCY_LIMIT,REPEAT_CONDITION,GROUP_ID,
                                STATUS,CDC_CODE,SCREEN_COMMENTS,MASTER_REFERENCE_ID FROM EMRSCREENIMMUNLKUP
			UNION ALL

         SELECT (S1.SCREEN_IMMUN_ID-1500)+(S2.SCREEN_IMMUN_ID-1500)*68,S2.SCREEN_IMMUN_ID,(S1.SCREEN-IMMUN_ID-1500),RECORD_TYPE,GENDER,SUB_GENDER
,DESCRIPTION,STARTING_AGE_OPERATOR,STARTING_AGE_VALUE,
                                STARTING_AGE_UNITS,FREQUENCY,STARTING_AGE_LIMIT_RANGE,STARTING_AGE_LIMIT_UNITS,AGE_END_LIMIT,
				AGE_END_LIMIT_UNITS,REPEAT,REPEAT_AGE_VALUE,REPEAT_AGE_UNITS,FREQUENCY_LIMIT,REPEAT_CONDITION,GROUP_ID,
                                STATUS,CDC_CODE,SCREEN_COMMENTS,MASTER_REFERENCE_ID FROM EMRSCREENIMMUNLKUP S1,EMRSCREENIMMUNLKUP S2


HI, iam not able to insert in the table these values directly

[split long line]

[Updated on: Fri, 29 May 2009 07:10] by Moderator

Report message to a moderator

Re: compilation errors in stored procedure [message #405770 is a reply to message #405751] Fri, 29 May 2009 06:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
"I am not able" is not an Oracle error message.

Since you don't tell us the error, we have to start troubleshooting at the beginning. So the first question:

Is the computer turned on?
Re: compilation errors in stored procedure [message #405861 is a reply to message #405770] Sat, 30 May 2009 10:41 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ThomasG wrote on Fri, 29 May 2009 13:47
"I am not able" is not an Oracle error message.

Since you don't tell us the error, we have to start troubleshooting at the beginning. So the first question:

Is the computer turned on?


Whoaa.. You're making assumptions here.

One or two steps back:

"Do you have a computer?"
Previous Topic: How to delete duplicate records? (splitted topic)
Next Topic: Need to display all column's name and value of a table in a query
Goto Forum:
  


Current Time: Fri Dec 02 12:32:37 CST 2016

Total time taken to generate the page: 0.09690 seconds