Hi Expertise,
I am trying to drop the $ tables which I am getting while running my Interfaces, procedures and packages.
I am using this code which i found in google.
I have created an option named tempSchema with value ODI ( My schema ) which I am working
DECLARE
V_COUNT NUMBER;
V_TABLE VARCHAR2(300);
stmt varchar2(300);
BEGIN
SELECT COUNT(DISTINCT TABLE_NAME)INTO V_COUNT FROM ALL_TABLES WHERE
OWNER='<%odiRef.getOption("tempSchema")%>'AND TABLE_NAME LIKE '%$_%';
WHILE(V_COUNT>0)LOOP
SELECT DISTINCT TABLE_NAME INTO V_TABLE FROM ALL_TABLES WHERE
OWNER='<%=odiRef.getOption("tempSchema")%>'AND TABLE_NAME LIKE '%$_%' AND ROWNUM=1;
stmt:='DROP TABLE'||'<%=odiRef.getOption("tempSchema")%>'||'-'||V_TABLE||'cascade constraints PURGE';
EXECUTE IMMEDIATE stmt;
commit;
V_COUNT:=V_COUNT -1;
END LOOP;
END;
please find the screenshot of my option
screenshot of my procedure
While I am executing the procedure, I am getting Invalid SQL statement error.
Please help me in dropping the tables.
Thanks,
Santhosh.