Member Avatar for vasuv

Hi,

I am currently working on Oracle 11.2.0.3. Below is my requirement.

1.I would like to scan the database ( Eg. 1 entire schema) and need to find the DB object either Procedure/Funcation which is having DROP statement.
( some of the procedures having " DROP PROCEDURE <PROCEDURE_NAME> " statements.

  1. Need to take the list of those objects ( having the DROP statements). (in Excel/CSV)

  2. Then need to drop only those objects from the schema.

Can any one please advise.

Thanks,
Vas

Edited by vasuv
  • 2 Contributors
  • 2 Replies
  • 379 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by vasuv
Member Avatar for jwenting

According to an old Stackoverflow post (which you could have easily found yourself, it's as simple as running a single PL/SQL statement SELECT * FROM ALL_source WHERE UPPER(text) LIKE '%BLAH%'

Click Here

No need to export it to Excel and drop manually, the stored procedure you write to execute this statement can loop over the results and drop directly (of course you need to make sure to skip the procedure itself in that loop, as it will be one of the returned database objects).

Member Avatar for vasuv

I didn't not write any procedure. Can you please advise any stored procdure to satisfy the above requirement. I have written the query as follows to get the information. But didn't get the complete required information.

SELECT * FROM ALL_source WHERE UPPER(text) LIKE '%BLAH%'
AND TYPE='PROCEDURE'
AND OWNER='XXXX';
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.