2

TASK:

Move all the functions and procedures in packages to the current Oracle schema. (you can imagine a case when you could need that, if not - take it like a challenge!)

QUESTION:

How can I read the functions/procedure "body" while they are in the package? I know that I can use all_source, dba_source and others to get the package body lines, but this means that I have to parse all those rows/strings - it should be an easier way. Isn't it?

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Michael
  • 4,786
  • 11
  • 45
  • 68

2 Answers2

3

If you have access to Toad, it does this very well.

Also, look at DBMS_METADATA package, specifically, the GET_DDL procedure.

Hope that helps.

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
  • DBMS_METADATA with GET_DLL method gets the entire package body. I will take a look at the other suggestion. Thanks! – Michael Nov 10 '11 at 12:19
2

Why exactly do you need this?

Are you just trying to execute the functions and procedures as if they were defined in your schema? If so, then invoker's rights may help.

Are you doing this for testing? If so, take a look at this answer: Is there a way to access private plsql procedures for testing purposes? (summary: use conditional compilation to optionally make functions and procedures public)

If you really need to break the packages down to functions and procedures you'll need to do it manually if you want to be 100% accurate.

There are many potential problems with just reading the source and trying to do it automatically. What about package variables, types, initialization, security (can every function be public?), procedures within procedures, duplicate names, wrapped source, etc.

Community
  • 1
  • 1
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Hi, jonearles! Searching for an answer to my problem i found this - http://bypsoft.blogspot.com/2008/05/list-all-functions-and-stored.html - witch seemed to do exactly what I need, but it doesn't really work. The ideea of this task would be to make all those procedures and functions portable, so when you want to reorganise your bussiness logic and you do not have other way of recompileing that kind o procedures/functions (basically moving them) - you might want to do that instead of manually process them. – Michael Nov 11 '11 at 16:40
  • I don't think there's going to be any generic way to solve this problem. If you remove a function from it's intended context you can't expect it to work correctly. It sounds like you're trying to make your environment too dynamic. What's wrong with saying "here are the packages, if you don't like all the functions in them, don't use them"? But if you still need to do this, it may help to always put the function name after the `end`, to make it easier to parse. – Jon Heller Nov 17 '11 at 06:32