Home » SQL & PL/SQL » SQL & PL/SQL » Information on Package Contents (Oracle 10g2)
Information on Package Contents [message #569354] Thu, 25 October 2012 04:05 Go to next message
mjm22
Messages: 46
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 #569357 is a reply to message #569354] Thu, 25 October 2012 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query ALL_ARGUMENTS, if you have an argument with position 0 (and argument_name null) then it is a function.

Regards
Michel
Re: Information on Package Contents [message #569378 is a reply to message #569354] Thu, 25 October 2012 06:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
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 Go to previous message
mjm22
Messages: 46
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
Previous Topic: Converting utl_http.read_text to PDF file
Next Topic: Error while using sql merge
Goto Forum:
  


Current Time: Wed Oct 22 04:54:46 CDT 2014

Total time taken to generate the page: 0.05435 seconds