parsing plsql source file for object name

I'm a newbie to awk.  I'm trying to develop some logic to parse out
the object name of a plsql file, the thing that is installed into the
db after "create or replace".  Has anyone already done this?

In article <6f4eb08c.0403051036.1d636e69@posting.google.com>,
Chad Cedotal <ccedotal@gradkell.com> wrote:

% I'm a newbie to awk.  I'm trying to develop some logic to parse out
% the object name of a plsql file, the thing that is installed into the
% db after "create or replace".  Has anyone already done this?

Many people have done it many times. What works best depends on how your
files are structured. If you use gawk, it might be helpful to use IGNORECASE
to create case-insensitive matches. Otherwise, you might want to convert
the input to lower-case. You could try setting the record separator to ";",
but this will cause problems if you have comments.

That aside, you need to deal with two basic command formats

create type name
create or replace type name

where type is the object type and name is the thing you're looking for,
and the spaces could be any amount of white space, possibly interspersed
with comments.

You could just look for what I've written above:

/create or replace/ { print $5; next }
/create/ { print $3; next }

But it won't work if the statements are split over two lines, or if
there are comments with the word `create' in them. Doing a more robust
job can be fiddly and possibly not worthwhile.

You know that create will always be the first non-whitespace thing on the
line, so you can look for that then look at each ensuing field to see
which of the above formats you have encountered. Ignoring the case issues,
this would give you something like

$1 == "create" {
name = ""
lookingfor = "or"
i = 2
while (name == "") {
while (i <= NF && name == "") {
if ($i ~ /^--/) {        # ack -- sqlplus comment to end of line
i = 1
if (getline <= 0) {
print "end of file without finding the name"
exit 1
}
}
else if ($i ~ /^\/\*/) { # ack -- SQL comment
print "not handling sql comment in the middle of create statement"
exit 1
}
else if ($i == lookingfor) {
if (lookingfor == "or")
lookingfor = "replace"
else if (lookingfor == "replace")
lookingfor = "*type*"
}
# from here on, we haven't matched the text we're looking for
# if the text is `or', it means we don't have `or replace'
# so we must have got the object type, and we look for name
# if the text is *type* or *name*, we weren't really looking
# for that text, and we assume we got what we wanted.
# if the text is `replace', we assume it was a syntax error
else if (lookingfor == "or" || lookingfor == "*type*")
lookingfor = "*name*"
else if (lookingfor == "*name*")
name = $i
else if (lookingfor == "replace") {
print "expected `replace', got `" $i "'. deal with it"
exit 1
}
i++
}
# reached the end of that line, so try the next one
if (name == "") {
i = 1
if (getline <= 0) {
print "end of file without finding the name"
exit 1
}
}
}

print name
}
--

Patrick TJ McPhee
East York  Canada
ptjm@interlog.com