Hi
Is there any way one can query for a specific procedure's source code,
rather than the entire package body that it's within?

I know I can query user_source for the package body, or for stand-alone
procs, but I'm struggling to find a nice way of pulling out just the code
for a specific proc within the package.

I suppose I can pull out everything between the start of the proc I want
through to the start of the next proc, but I'd've thought there'd be a more
elegant way of doing it than that?

I'm looking @ Oracle 9i at present, but I'd be keen to know about solutions
for other releases too. Any thoughts @ all, really.

--
Adam

Adam Cameron wrote:

when posting an issue such as this, it is helpful if you include the
full version (ie: 9.2.0.?)

Check out:
Summary of DBMS_METADATA Subprograms
http://download.oracle.com/docs/cd/B...d2.htm#1028659

search for "procedure" on that page...

Michael Austin schrieb:
Afaik, there is no such method in Oracle.
You can get the whole package source (either from *_source or with
dbms_metadata ), but then you have to parse it to obtain a code for
particular packaged procedure/function.
I personally would avoid to write a plsql parser in plsql.
Perl,python,java,whatever else are much better suitable for that (it is
not as trivial as to search for "procedure foo", even not with
"regexp_like(text,"procedure\s+foo") etc).

Best regards

Maxim

Cheers for your response, Michael: I'll look into the link you metioned.


Like I suggested, I was not after a version specific solution, just wanting
to know about techniques in general.

But if we *were* considering just 9i, do you think there's much likelihood
that the answer to my question would have been any different had I said
9.2.0.1 or 9.2.0.8, for argument's sake?

--
Adam

Hey, cheers again Michael. I didn't know about DBMS_METADATA, and whilst I
don't think it's going to be much more use to me for my current issue than
just querying USER_SOURCE, I can see how it will be useful for other things
in future.

For my current situation, DBMS_METADATA doesn't have any greater
granularity than USER_SOURCE, unfortunately. One can extract the code from
a whole package, but one cannot dig down into it (without DIY). One can
use an OBJECT_TYPE of "PROCEDURE", but that only refers to stand-alone
procs, just like USER_SOURCE does. I tried to pass it object_type
"PROCEDURE" and a name of "mypkg.myproc" but it barfed at that.

Not to worry.

Cheers.

--
Adam

Cheers Maxim. Yeah, that's how it's beginning to seem to me too.

Thanks for the confirmation.

--
Adam

On Mar 1, 3:33*am, Adam Cameron <adam_j... (AT) hotmail (DOT) com> wrote:
Well, since you are asking to argue, I'd suggest going to metalink and
searching the bug database for dbms_metadata. You may notice a lot of
bugs, especially for newish features - one could even argue that
dbms_metadata is a newish feature in the early 9 timeframe, so you may
run across multiplicative issues using a newish feature on other
newish features.. So we can argue endlessly as to whether it is
suitable to use dbms_metadata in 9201. The correct answer is, of
course, don't use 9201. You'd be amazed at how many people do, and
are offended when a volunteer or oracle support says their problem may
magically disappear if they patch up.

That said, I think it ought to be ok to ask about techniques in
general. It _is_ more difficult to answer such questions though.
That's why you might check out asktom for such things, and not feel
too abused if any volunteer forum wants more specificity. You might
elicit an answer from someone who wants to answer such a question, you
might not. You might also get people to speak up as you saw because
they have been abused by too many homework or otherwise clueless
questions; such is second life.

jg
--
@home.com is bogus.
That darn gmail. http://www.basexblog.com/2009/02/26/...line-services/