Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Oracle SQL Cursor

Ok, so tell me why this is failing below.  



SET SERVEROUTPUT ON

DECLARE

      CURSOR testcursor IS
            SELECT *
            FROM m_old_test
            WHERE eff_date = '201003';
  i INTEGER
 
 
  OPEN testcursor;
     LOOP
        i := i + 1;

        insert into m_old_test1 (XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2)
        values(XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2);
        EXIT WHEN c1%NOTFOUND;
        -- process data record
     END LOOP;
     CLOSE testcursor;
  END;
0
synikaldemon
Asked:
synikaldemon
1 Solution
 
ajexpertCommented:
are you getting any error?

However, the cursor query needs some modification as it contains both date and time

I suspect you are looking only year and month in eff_date, if not, do let us know

SELECT *
            FROM m_old_test
            WHERE to_date(eff_date, 'YYYYMM') = '201003';

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
EXIT WHEN c1%NOTFOUND;
must be first line in the loop?


SET SERVEROUTPUT ON

DECLARE

      CURSOR testcursor IS
            SELECT *
            FROM m_old_test
            WHERE eff_date = '201003';
  i INTEGER
 
 
  OPEN testcursor;
     LOOP
        EXIT WHEN c1%NOTFOUND;

        -- process data record
        i := i + 1;

        insert into m_old_test1 (XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2)
        values(XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2);
     END LOOP;
     CLOSE testcursor;
  END; 

Open in new window


so far, you don't need any cursor actually, a plain "insert" would do
insert into m_old_test1 (XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2)
select XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2
  from  m_old_test
     WHERE eff_date = '201003'

Open in new window


and in case the "failure" is because eff_date is date field, you may need to use the proper syntax:

     WHERE eff_date >= TO_DATE('201003', 'YYYYMM')
         AND eff_date < TO_DATE('201004', 'YYYYMM')

read up:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0
 
synikaldemonAuthor Commented:
Below is the error message.  For the date, we call it eff_date, but it is actually a string.  I don't think that is the problem.  Basically we are just trying to grab a bunch of records from one table and move them to another with the same structure.  



----------------------------------------------------------------------------------


Error starting at line 3 in command:


DECLARE

      CURSOR testcursor IS
            SELECT *
            FROM m_old_test
            WHERE eff_date = '201103';
  i INTEGER
 
 
  OPEN testcursor;
     LOOP
        i := i + 1;
       
   
 insert into m_old_test1 (XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2)
        values(XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2);

        EXIT WHEN c1%NOTFOUND;
     
     END LOOP;
     CLOSE testcursor;
  END;


Error report:
ORA-06550: line 10, column 3:
PLS-00103: Encountered the symbol "OPEN" when expecting one of the following:

   := . ( @ % ; not null range default character
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you are missing the ; after INTEGER and the BEGIN keyword:

DECLARE
      CURSOR testcursor IS
            SELECT *
            FROM m_old_test
            WHERE eff_date = '201103';
  i INTEGER; 
 BEGIN
  OPEN testcursor;
     LOOP
        EXIT WHEN c1%NOTFOUND;

        i := i + 1;
       
        insert into m_old_test1 (XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2)
        values(XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2);

     
     END LOOP;
     CLOSE testcursor;
  END;

Open in new window

0
 
synikaldemonAuthor Commented:
Yep angelIII we don't need a cursor for this, but we need to do some other things with the data, so we are just trying to get a cursor set up correctly.  Thanks for your help, you too ajexpert.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>but we need to do some other things with the data
you must be doing something really "crazy" that you really need a cursor..
SQL is very powerful (though not almighty)
but that is another question :)
0
 
synikaldemonAuthor Commented:
What I am getting now

Error starting at line 3 in command:
DECLARE
      CURSOR testcursor IS
            SELECT *
            FROM m_old_test
            WHERE eff_date = '201103';
  i INTEGER;
 BEGIN
  OPEN testcursor;
     LOOP
        EXIT WHEN testcursor%NOTFOUND;

        i := i + 1;

    insert into m_old_test1 (XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2)
        values(XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2);

     
     END LOOP;
     CLOSE testcursor;
  END;
 
Error report:
ORA-06550: line 15, column 45:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 14, column 10:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sure... you also need a FETCH statement:
http://psoug.org/reference/control_struct.html

DECLARE
      CURSOR testcursor IS
            SELECT *
            FROM m_old_test
            WHERE eff_date = '201103';
  i INTEGER;
  rec m_old_tet%ROWTYPE;
 BEGIN
  OPEN testcursor;
     LOOP
        FETCH testcursor INTO rec;
        EXIT WHEN testcursor%NOTFOUND;

        i := i + 1;

    insert into m_old_test1 (XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,ACRO2)
        values(rec.XXCE, rec.XXAA,rec.XXBA,rec.XXA1,rec.XXAG,rec.ACRO,rec.ACRO2);

     
     END LOOP;
     CLOSE testcursor;
  END;

Open in new window

0
 
Mark GeerlingsDatabase AdministratorCommented:
Yes, the "fetch" line is required *IF* you want to do this in PL\SQL.  But, I don't see any reason here to use PL\SQL.  Why not just do:
Insert into m_oldtest1
select * from m_oldtest
where eff_date = '201103';

Also, I just noticed this problem in your procedure: you are using the column names from m_oldtest1 to provide the values for the insert into the same table!  That will never give you what you want.
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now