Stored Procedure

[ba543]

I am currently working on a stored procedure that allows me to select errors within a certain table take those errors from that table and be able to update those errors with another select statement. After it is done updating the table then it deletes the old select statement. I just recently started using the function rowid to be able to hold the new select statement value so that I would be able to delete the old one.I just recently got two errors while trying to run the procedure code.
ORA-02291 integrity constraint parent key not found
ORA-06512: at line 7

I would appreciate and help anyone could give thanks.


PROCEDURE Error_Checking_Update
IS
v_sql_main VARCHAR2(1000);
v_sql_where VARCHAR2(2500);
v_date_ind VARCHAR2(1);
v_rec_eff_date_ind VARCHAR2(1);
v_pk_value VARCHAR2(100);
v_index INTEGER;
v_epk_curr_pos INTEGER;
v_epk_last_pos INTEGER;

cursor_name number;
cursor_rows number;

CURSOR c_get_errors IS
SELECT error_pk_ident, error_table, error_field, error_com_value , rowid
FROM RPT210ERROR_HAND
WHERE error_com_value = 'Karen'

v_get_errors_rec c_get_errors%ROWTYPE;

CURSOR c_get_pk_columns IS
SELECT DCC.COLUMN_NAME
FROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS DCC
WHERE DCC.OWNER = 'RPT'
AND DC.owner = DCC.OWNER
AND DC.constraint_type = 'P'
AND DCC.CONSTRAINT_NAME = DC.constraint_name
AND dc.table_name = v_get_errors_rec.error_table
ORDER BY DCC.POSITION;

v_get_pk_columns_rec c_get_pk_columns%ROWTYPE;

BEGIN
--Get error records with a new COM value
OPEN c_get_errors;
LOOP --c_get_errors
FETCH c_get_errors INTO v_get_errors_rec;
--Have one error field, table, new value, all pk column values
EXIT WHEN (c_get_errors%NOTFOUND);

--Build the list of WHERE clause columns and values
--Initialize variables
v_sql_where := '';
v_epk_curr_pos := 1;
v_epk_last_pos := 1;
--Get the column name(s) of the primary key
OPEN c_get_pk_columns;
LOOP --c_get_pk_columns
FETCH c_get_pk_columns INTO v_get_pk_columns_rec;
EXIT WHEN (c_get_pk_columns%NOTFOUND);
--Check if PK column is REC_EFF_DATE
IF v_get_pk_columns_rec.column_name = 'REC_EFF_DATE' THEN
v_rec_eff_date_ind := 'Y';
ELSE
v_rec_eff_date_ind := 'N';
END IF;

--Check if PK column is any DATE field
IF v_get_pk_columns_rec.column_name LIKE '%DATE%' THEN
v_date_ind := 'Y';
ELSE
v_date_ind := 'N';
END IF;

--Add the PK column name to the WHERE clause variable
IF v_sql_where IS NULL THEN
v_sql_where := v_get_pk_columns_rec.column_name || ' = ';
ELSE
v_sql_where := v_sql_where || ' AND ' || v_get_pk_columns_rec.column_name || ' = ';
END IF;

--Parse error_pk_ident field to get the PK column value
IF v_rec_eff_date_ind = 'Y' THEN
v_index := 3;
ELSE
v_index := 1;
END IF;

--Get the position of the colon
v_epk_curr_pos := instr(v_get_errors_rec.error_pk_ident, ':', v_epk_last_pos, v_index);

--Get the PK column value
v_pk_value := substr(v_get_errors_rec.error_pk_ident, v_epk_last_pos, v_epk_curr_pos-v_epk_last_pos);

--Add the PK column value to the WHERE clause variable
IF v_rec_eff_date_ind = 'Y' THEN
v_sql_where := v_sql_where || 'to_date(''' || v_pk_value || ''', ''YYYY-MM-DD HH:MI:SS'')';
ELSIF v_date_ind = 'Y' THEN
v_sql_where := v_sql_where || 'to_date(''' || v_pk_value || ''', ''YYYY-MM-DD'')';
ELSE
v_sql_where := v_sql_where || '''' || v_pk_value || '''';
END IF;

--Increment the variables
v_epk_last_pos := v_epk_curr_pos + 1;
END LOOP; --c_get_pk_columns
CLOSE c_get_pk_columns;

--Update the record with the new COM value
v_sql_main := 'UPDATE '||v_get_errors_rec.error_table||' SET ' || v_get_errors_rec.error_field || ' = ''' || v_get_errors_rec.error_com_value || ''' WHERE ';
-- sys.dbms_output.put_line(v_sql_main || v_sql_where);
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, v_sql_main || v_sql_where, dbms_sql.native);
cursor_rows:=dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name);
COMMIT;

--Delete the record from the error table
IF cursor_rows > 0 THEN
DELETE FROM RPT210ERROR_HAND
WHERE table.rowid = c_get_errors.rowid
--and error_pk_ident = v_get_errors_rec.error_pk_ident
--AND error_table = v_get_errors_rec.error_table
--AND error_field = v_get_errors_rec.error_field
--AND error_com_value = v_get_errors_rec.error_com_value;
END IF;
COMMIT;

--Update the record status in the Policy History table
--rec_status = F in RPT002POLICY_HIST
END LOOP; --c_get_errors
CLOSE c_get_errors;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
RAISE;
END Error_Checking_Update;
Enter further code below as specified in the Package spec.
END;

 

[p27br]

try posting this in the sql server forums, you'll get an answer faster.

 

[Craig0201]

try posting this in the sql server forums, you'll get an answer faster."

No it won't. He's reporting Oracle errors! ;->

Craig

 

[p27br]

craig

I wasn't being arrogant. I waas just trying to redirect you to the best forum, apologies if you were upset.
even though, I don't think the Access Tables and relationships has anything to do with oracle

 

[Craig0201]

p27br,

Just found it funny!

Craig

 

[p27br]

you're correct,
I should have read the entire post first. When I saw stored procedure, I jumped to the conclusion that it was about SQL Server.
Anyway, hope you get to fix the problem, once you find the right place to post it

 

[Craig0201]

p27br,

You're still missing bits. I didn't post the original question!

Craig

 

[ba543]

Craig0201, p27br
Sorry if my procedure stirred up such an argument. I have figured out the answer through playing with the code. I appreciate you both trying to direct me in the right direction. Have a nice memorial day weekend.
Thanks again.