| Information on Package Contents [message #569354] |
Thu, 25 October 2012 04:05  |
mjm22
Messages: 44
Registered: January 2010
Location: Singapore
|
Member |
|
|
Hi,
I am developing some automated test packages for my PL/SQL Packaged code. Going forward I can code the test package in conjunction with the code but I have some historic packages that I would like to develop these test packages for.
To save time I would like to employ oracle data dictionary views in order to construct the framework for my test package. This includes using SQL to get a list of procedures / functions within the package in order to create the test procedures (spec and body). I can do this in a basic way using the user_procedure view with something like...
SELECT 'PROCEDURE test_' || LOWER(procedure_name)
|| ' (p_result OUT VARCHAR2 IS BEGIN JTA.ACCOUNT_PROFILE_MAINT.' || procedure_name || ' END '
|| LOWER(procedure_name) || ';'
FROM user_procedures WHERE object_name = 'ACCOUNT_PROFILE_MAINT' AND subprogram_id != 0 ORDER BY subprogram_id;
However, the above only really works (in simplistic form.. without parameters) for procedures within the package. I would also like to be able to determine if the procedure listed is actually a function or procedure (so that I can alter the syntax accordingly to generate a correctly formatted string calling the program unit).
So, initially how do I determine the type of package program unit I have (Proc/Function)? Do I need to go to all_source to get this information or are there other views available I can join to?
Eventually I would like to extend this to be able to automatically include any parameters in the generated calling string.. again, is there any other option apart from all_source to get this information?
Thanks,
Mike
|
|
|
|
|
|
| Re: Information on Package Contents [message #569378 is a reply to message #569354] |
Thu, 25 October 2012 06:57  |
Solomon Yakobson
Messages: 1400
Registered: January 2010
|
Senior Member |
|
|
Besides what Michel mentioned you need to pay attention to USER/ALL/DBA_ARGUMENTS column SEQUENCE. USER/ALL/DBA_ARGUMENTS stores info even on package procedures without arguments. In such case POSITION = 1, SEQUENCE = 0 and ARGUMENT_NAME is NULL. Also, keep in mind data dictionary stores info on package public procedures/functions. Info on package private procedures/functions can't be obtained that easy - you'd need to parse package body.
SY.
P.S. I assume your package procedures/functions do not have composite type arguments. Otherwise it adds a bit more complexity to querying USER/ALL/DBA_ARGUMENTS.
|
|
|
|
| Re: Information on Package Contents [message #569384 is a reply to message #569378] |
Thu, 25 October 2012 08:04 |
mjm22
Messages: 44
Registered: January 2010
Location: Singapore
|
Member |
|
|
Thanks guys, exactly what I needed...
WRT Private procs/funs.. I am converting them to public where the proc/func needs a test(s) written for it. There are very few private ones anyway.
Some of the parameters are composite for a couple of the procs/funcs.. If the overhead of getting this data is too large then I will just code by hand. Really I am looking to develop a tool that will build 80% or so of the skeleton of a test package to save a lot of manual coding work. What you have provided above will certainly let me accomplish that.
Thanks both
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
