Re: Extract SQL statements from Database/File

In the subject you mention database or file. If you've got files that contain the PL/SQL code, the following perl snippet might be of help to you:

$ perl -e '$prt=0; open F,"your_file_name" or die "cannot open file: $!\n"; while (<F>) { chomp;
/(?:select|insert|delete|update)\b[^;\/]*/igo && do { $prt = 1; }; ( $prt ) && print $_, "\n"; /^[^;\/]*[;\/]/ && do { $prt = 0; }; } close F; '

This will print all lines that have select/insert/update/delete statements in them, and any that follow up to and including the next line that contains a ';' or a '/'. It relies on the fact that all statements start with a defined keyword and end with a ';' or a '/'. There may be leading or trailing code snippets, depending on the formatting of the file. You can add any other keywords that you need to the (?:select|...) section, separated by a '|'.

Of course, this will only catch the pieces of dynamic sql that contain the starting keyword.

Hope this helps,
Tony

Around 16/09/2009 12:22 AM, Rajesh Rao said:
>
> Hello All, any tools/scripts out there that you are aware of, to
> extract all select/insert/update/delete statements from within the
> packages for a schema? The schema in question has very large number of
> packages. Executing the packages themselves with tracing enabled was
> one way I could think of, but that might not hit all SQL’s within the
> package (conditional flows, etc).
>
> Regards
>
> Raj
>
> This communication is for informational purposes only. It is not
> intended as an offer or solicitation for the purchase or sale of any
> financial instrument or as an official confirmation of any
> transaction. All market prices, data and other information are not
> warranted as to completeness or accuracy and are subject to change
> without notice. Any comments or statements made herein do not
> necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
> and affiliates. This transmission may contain information that is
> privileged, confidential, legally privileged, and/or exempt from
> disclosure under applicable law. If you are not the intended
> recipient, you are hereby notified that any disclosure, copying,
> distribution, or use of the information contained herein (including
> any reliance thereon) is STRICTLY PROHIBITED. Although this
> transmission and any attachments are believed to be free of any virus
> or other defect that might affect any computer system into which it is
> received and opened, it is the responsibility of the recipient to
> ensure that it is virus free and no responsibility is accepted by
> JPMorgan Chase & Co., its subsidiaries and affiliates, as applicable,
> for any loss or damage arising in any way from its use. If you
> received this transmission in error, please immediately contact the
> sender and destroy the material in its entirety, whether in electronic
> or hard copy format. Thank you. Please refer to
> http://www.jpmorgan.com/pages/disclosures for disclosures relating to
> European legal entities.
>

--
http://www.freelists.org/webpage/oracle-l