I can't seem to get this to work. I think I'm declaring things properly and don't think I have invalid identifiers. I get errors at the following locations:
Error report:
ORA-06550: line 4, column 14:
PLS-00201: identifier 'MGE.KEYWORD' must be declared
ORA-06550: line 4, column 14:
PL/SQL: Item ignored
ORA-06550: line 5, column 13:
PLS-00201: identifier 'MGE.CHARTS' must be declared
ORA-06550: line 5, column 13:
PL/SQL: Item ignored
ORA-06550: line 6, column 13:
PLS-00201: identifier 'MGE.BOOK_ID' must be declared
ORA-06550: line 6, column 13:
PL/SQL: Item ignored
ORA-06550: line 7, column 13:
PLS-00201: identifier 'MGE.CHAPTER_ID' must be declared
ORA-06550: line 7, column 13:
PL/SQL: Item ignored
ORA-06550: line 14, column 14:
PLS-00201: identifier 'GD.FEATURE_ID' must be declared
ORA-06550: line 14, column 14:
PL/SQL: Item ignored
ORA-06550: line 27, column 36:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 27, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 33, column 34:
PLS-00225: subprogram or cursor 'C_MGE_NULL_FEATURES' reference is out of scope
ORA-06550: line 33, column 34:
PL/SQL: ORA-00904: "C_MGE_NULL_FEATURES"."C_KEYWORD": invalid identifier
ORA-06550: line 31, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 39, column 34:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 39, column 34:
PL/SQL: ORA-00904: "C_KEYWORD": invalid identifier
ORA-06550: line 37, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 43, column 111:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 43, column 111:
PL/SQL: ORA-00904: "C_CHARTS": invalid identifier
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
CODE:
DECLARE
message varchar2(20):= 'All Done!';
/*set up the variables to hold the values fetched from the cursor*/
c_keyword mge.keyword%type;
c_charts mge.charts%type;
c_bookid mge.book_id%type;
c_chapid mge.chapter_id%type;
/*need a unique identifier for the cursor row*/
uniqueid varchar2(50);
/*an individual chart string from the inner cursor*/
/*c_chart varchar2(10);
c_featureid gd.FEATURE_ID%type;
*/
featureid gd.FEATURE_ID%type;
keywordCount number(1);
/*need to have a cursor for the MAP_GNIS_ELEMENTS data (where FEATURE_ID is null)*/
CURSOR c_MGE_null_Features IS
select mge.KEYWORD, mge.CHARTS, mge.BOOK_ID, mge.CHAPTER_ID
from CP_OCSPRD.MAP_GNIS_ELEMENTS mge
where mge.FEATURE_ID is null
order by KEYWORD;
BEGIN
/*Get the cursor ready for work*/
OPEN c_MGE_null_Features;
/*loop through all the elements of the cursor*/
LOOP
FETCH c_MGE_null_Features INTO c_keyword, c_charts, c_bookid, c_chapid;
EXIT WHEN c_MGE_null_Features%notfound;
/*set up a cursor to help verify if the keyword is in the GNIS_DATA table*/
/*TODO: does a cursor have to be used here or is there another way?*/
select count(*) into keywordCount
from CP_OCSPRD.GNIS_DATA gd
where gd.FEATURE_NAME like c_mge_null_features.c_keyword;
/*if keyword count returns exactly one, assign feature id to the MGE_feature_id for that keyword, and mark feature_updated 'Y'*/
IF (keywordCount = 1) THEN
select gd.FEATURE_ID into featureid
from cp_ocsprd.GNIS_DATA gd
where gd.FEATURE_NAME like c_keyword;
update cp_ocsprd.MAP_GNIS_ELEMENTS mge
set mge.FEATURE_ID = featureid, mge.FEATURE_UPDATED = 'Y'
where mge.KEYWORD = c_keyword and mge.BOOK_ID = c_bookid and mge.CHAPTER_ID = c_chapid and mge.CHARTS = c_charts;
END IF;
END LOOP;
dbms_output.put_line(message);
CLOSE c_MGE_null_Features;
END;
/