synikaldemon
asked on
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,A CRO2)
values(XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,A CRO2);
EXIT WHEN c1%NOTFOUND;
-- process data record
END LOOP;
CLOSE testcursor;
END;
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,A
values(XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,A
EXIT WHEN c1%NOTFOUND;
-- process data record
END LOOP;
CLOSE testcursor;
END;
EXIT WHEN c1%NOTFOUND;
must be first line in the loop?
so far, you don't need any cursor actually, a plain "insert" would do
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:
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
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;
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'
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:
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
ASKER
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,A CRO2)
values(XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,A CRO2);
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:
--------------------------
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,A
values(XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,A
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:
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;
ASKER
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.
>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 :)
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 :)
ASKER
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,A CRO2)
values(XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,A CRO2);
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:
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,A
values(XXCE, XXAA,XXBA,XXA1,XXAG,ACRO,A
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:
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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
Open in new window