I am getting the below error after executing the code given below :
"Error1: Inside Delete Block: ORA-00904: "AJAPPDAMEAAD6HPAAF": invalid identifier"
Its not identifying the rowid value in the delete statement, the block is getting inside the refcursor and fetching the rowid for the table and then its not able to execute the delete statement for the same rowid, Please help me how to achieve this using rowid. Thanks In Advance...
CREATE OR REPLACE PROCEDURE PROCESS_TEST (V_SCHEMA VARCHAR2, V_TAB VARCHAR2) AS
l_sql VARCHAR2 (2000);
CURSOR c_x
IS
SELECT *
FROM TEST_TAB
WHERE UPPER (SCHEMA) = UPPER (V_SCHEMA)
AND UPPER (TABLE_NAME) = UPPER (V_TAB)
AND STATUS IS NULL;
TYPE tab_names_type IS TABLE OF TEST_TAB%ROWTYPE;
tab_names tab_names_type;
l_cursor SYS_REFCURSOR;
TYPE c_1 IS TABLE OF UROWID;
tab_row c_1;
l_sql_errm VARCHAR2 (2000);
V_STATUS VARCHAR2 (1) := 'N';
BEGIN
OPEN c_x;
LOOP
FETCH c_x BULK COLLECT INTO tab_names LIMIT 1000;
EXIT WHEN tab_names.COUNT = 0;
FOR id IN tab_names.FIRST .. tab_names.LAST
LOOP
l_sql :=
'SELECT a.ROWID FROM '
|| tab_names (id).SCHEMA
|| '.'
|| tab_names (id).TABLE_NAME
|| ' a';
OPEN l_cursor FOR l_sql;
LOOP
FETCH l_cursor BULK COLLECT INTO tab_row LIMIT 1000;
EXIT WHEN tab_row.COUNT = 0;
FOR i IN tab_row.FIRST .. tab_row.LAST
LOOP
BEGIN
EXECUTE IMMEDIATE
'DELETE '
|| tab_names (id).SCHEMA
|| '.'
|| tab_names (id).TABLE_NAME
|| ' a WHERE a.ROWID = '
|| tab_row (i);
COMMIT;
V_STATUS := 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_sql_errm := 'Inside Delete Block: ' || SQLERRM;
V_STATUS := 'N';
ROLLBACK;
END;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_sql_errm := 'Inside Others Block: ' || SQLERRM;
V_STATUS := 'N';
ROLLBACK;
END;
CodePudding user response：
You are concatenating your rowid value into the delete statement as a string. If you're going to do that you need to at least quote it, and more correctly convert it via chartorowid() (though you're still implicitly converting your urowid value to a string):
EXECUTE IMMEDIATE
'DELETE FROM '
|| tab_names (id).SCHEMA
|| '.'
|| tab_names (id).TABLE_NAME
|| ' a WHERE a.ROWID = chartorowid('''
|| tab_row (i)
|| ''')';
It would be better to use a bind variable:
EXECUTE IMMEDIATE
'DELETE FROM '
|| tab_names (id).SCHEMA
|| '.'
|| tab_names (id).TABLE_NAME
|| ' a WHERE a.ROWID = chartorowid(:r)'
USING tab_row (i);
or just:
EXECUTE IMMEDIATE
'DELETE FROM '
|| tab_names (id).SCHEMA
|| '.'
|| tab_names (id).TABLE_NAME
|| ' a WHERE a.ROWID = :r'
USING tab_row (i);
db<>fiddles of your version and my version, with debugs added so you can actually see the error (it would be better to throw or log it, depending on whether you want to carry on; the handling and transaction control suggest you want to carry on, but you still need to report/record the problem reliably - which means not using dbms_output!).
CodePudding user response：
ROWID is a string, not a number. You have to enclose it into single quotes (chr(39)):
EXECUTE IMMEDIATE 'DELETE FROM '
|| tab_names (id).SCHEMA
|| '.'
|| tab_names (id).TABLE_NAME
|| ' a WHERE a.ROWID = '
|| CHR (39)
|| tab_row (i)
|| CHR (39);
SQL> CREATE OR REPLACE PROCEDURE PROCESS_TEST (V_SCHEMA VARCHAR2, V_TAB VARCHAR2)
2 AS
3 l_sql VARCHAR2 (2000);
4
5 CURSOR c_x IS
6 SELECT *
7 FROM TEST_TAB
8 WHERE UPPER (SCHEMA) = UPPER (V_SCHEMA)
9 AND UPPER (TABLE_NAME) = UPPER (V_TAB)
10 AND STATUS IS NULL;
11
12 TYPE tab_names_type IS TABLE OF TEST_TAB%ROWTYPE;
13
14 tab_names tab_names_type;
15
16
17 l_cursor SYS_REFCURSOR;
18
19 TYPE c_1 IS TABLE OF UROWID;
20
21 tab_row c_1;
22
23 l_sql_errm VARCHAR2 (2000);
24 V_STATUS VARCHAR2 (1) := 'N';
25 BEGIN
26 OPEN c_x;
27
28 LOOP
29 FETCH c_x BULK COLLECT INTO tab_names LIMIT 1000;
30
31 EXIT WHEN tab_names.COUNT = 0;
32
33 FOR id IN tab_names.FIRST .. tab_names.LAST
34 LOOP
35 l_sql :=
36 'SELECT a.ROWID FROM '
37 || tab_names (id).SCHEMA
38 || '.'
39 || tab_names (id).TABLE_NAME
40 || ' a';
41
42 OPEN l_cursor FOR l_sql;
43
44 LOOP
45 FETCH l_cursor BULK COLLECT INTO tab_row LIMIT 1000;
46
47 EXIT WHEN tab_row.COUNT = 0;
48
49 FOR i IN tab_row.FIRST .. tab_row.LAST
50 LOOP
51 BEGIN
52 EXECUTE IMMEDIATE 'DELETE FROM '
53 || tab_names (id).SCHEMA
54 || '.'
55 || tab_names (id).TABLE_NAME
56 || ' a WHERE a.ROWID = '
57 || CHR (39)
58 || tab_row (i)
59 || CHR (39);
60
61 COMMIT;
62 V_STATUS := 'Y';
63 EXCEPTION
64 WHEN OTHERS
65 THEN
66 l_sql_errm := 'Inside Delete Block: ' || SQLERRM;
67 V_STATUS := 'N';
68 ROLLBACK;
69 END;
70 END LOOP;
71 END LOOP;
72 END LOOP;
73 END LOOP;
74 EXCEPTION
75 WHEN OTHERS
76 THEN
77 l_sql_errm := 'Inside Others Block: ' || SQLERRM;
78 V_STATUS := 'N';
79 ROLLBACK;
80 END;
81 /
Procedure created.
Testing:
SQL> SELECT * FROM test_tab;
SCHEM TABLE_ S
----- ------ -
SCOTT MOVIES
SQL> SELECT title_id, title FROM movies;
TITLE_ID TITLE
---------- ------------------------------
1 Titanic
SQL> EXEC process_test('SCOTT', 'MOVIES');
PL/SQL procedure successfully completed.
SQL> SELECT title_id, title FROM movies;
no rows selected
SQL>