deletion issue

[umesh049]

Hello,

I want to delete data from all the tables made in a schema.

but i got error.

"object REPORTS does not exist"

but when i search this table from user objects it does not exists.

can any body help me.

thanks

DECLARE
 query varchar2(700);
 CURSOR cu_idx IS
        SELECT TABLE_NAME FROM USER_TABLES where table_name not like 'REPORT%' AND TABLE_NAME NOT LIKE 'Sheet1%' ;
 BEGIN
  FOR cur_rec IN cu_idx LOOP
query:='delete from ' || cur_rec.TABLE_NAME ;
execute immediate query;
END LOOP;
 END;
 

DECLARE
*
ERROR at line 1:
ORA-06564: object REPORTS does not exist
ORA-06512: at line 8



SQL> SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME = 'REPORTS';

no rows selected

[amitpatel66]

Hello,

I want to delete data from all the tables made in a schema.

but i got error.

"object REPORTS does not exist"

but when i search this table from user objects it does not exists.

can any body help me.

thanks

DECLARE
 query varchar2(700);
 CURSOR cu_idx IS
        SELECT TABLE_NAME FROM USER_TABLES where table_name not like 'REPORT%' AND TABLE_NAME NOT LIKE 'Sheet1%' ;
 BEGIN
  FOR cur_rec IN cu_idx LOOP
query:='delete from ' || cur_rec.TABLE_NAME ;
execute immediate query;
END LOOP;
 END;
 

DECLARE
*
ERROR at line 1:
ORA-06564: object REPORTS does not exist
ORA-06512: at line 8



SQL> SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME = 'REPORTS';

no rows selected

Your issue seems to be strange. If you dont have the reports table then why are you putting that in WHERE condition of the cursor. That is not required and PATTERN matching again slow down the peroformance of the query!!