Solved

Analyze Stored Procedures in Oracle and SQL Server

Posted on 2008-10-30
19
1,126 Views
Last Modified: 2013-11-11
I have been working on a tool to allow a  user to point it at a database (Oracle or SQL Server) and then pass it the name of a stored procedure and have it return the input parameters and types and also the return columns and types.

I have used several methods, but at best only can retrieve either too many,  or not all of the columns which represent the resultset.  The problem is that the stored procedure may have aliased and/or constructed column names and none of the methods I have found and have been noodling around with wll retrieve these constructed column names.

Are there any tools or database objects that can get me the solution?  I have been using the command builder object to return the input parameters and executing  the stored proc and then reading the returned column names.  However,  even if I do this and get the aliased column names,  the db analyzer does not know what the name of the aliased columns are.

0
Comment
Question by:Howard Bash
  • 7
  • 5
  • 4
  • +1
19 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 350 total points
ID: 22845237
Unlike Views, which will always return a single structure, stored procedures can execute in various code paths which yield different results.  Sometimes it may not yield any results.  Sometimes the results will vary depending upon what parameters are supplied.

Put simply, unless it's the most basic of basic procedures, this can't be done.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 150 total points
ID: 22845468
Since you are using 11g  for Oracle

you can query dba_procedures and dba_arguments to get a list of procedures, functions and sub-programs inside packages as well as the arguments of them.  If the procedure is overloaded, that's indicated in dba_arguments as well.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22845475
prior to 11g, these views did not exist
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 27

Expert Comment

by:sujith80
ID: 22848033
Adding to the above post

dba_arguments exists in Oracle 10g as well.
Probably this view is the one you have to look at.

see the documentation( only user_/all_arguments are documented though)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1014.htm#i1573843
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22849725
oops, yes,  10g is when they were both introduced,  not just 11g.  good catch
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22850031
Ok.  I'm not oracle person, but wouldn't the same thing apply in oracle as in SQL?  Parameters, sure.... they don't change.  I mean some are optional, but they're always there.

But how can oracle reliably report on the output of a procedure.  Assume for a minute that the below procedure is in valid syntax for oracle, what would it display in these tables.



create procedure up_DoSomething
 @DoWhat char(1)
as
if @doWhat='a'
  select * from MyRealyWideTable
else
  select 'hello'
 
go

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22850043
And besides, I thought that "information_schema.routines" existed in oracle.  It does in SQL.
0
 
LVL 1

Author Comment

by:Howard Bash
ID: 22850044
I can get all the way there writing a .net application that gets the parameters,  modifies the parameters  collection with values for the parameters and executes the stored proc.  This will return the columns collection of the resultset which ALMOST gets me all the way to the solution.  

The problem is that the returned columns might be aliased and so the returned column name might not be the column name of the underlying table.

Also,  these system proc calls listed about, while they might get me to the same place, are only for Oracle and as the question states I am working with Oracle and with SQL Server and so am looking for a general solution,  which I have almost obtained with the alias problem remaining.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22850055
information_schema.routines
0
 
LVL 1

Author Comment

by:Howard Bash
ID: 22850388
You just repeated what you wrote about above...  

Also this is not available in Oracle 10.2.

Plus the SQL return does not give me back aliased return columns.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22850420
I repeated what I said above because it seemed as though http:#22850044 was written in response to http:#22850043 when in fact you were probably responding to what had been previously written.

Since you are using oracle 11, does that matter?

No.  You are right.  It can't for the reasons I gave in http:#22850031.
0
 
LVL 1

Author Comment

by:Howard Bash
ID: 22850586
Plus, I do not see this returning return columns and input parameters as well as aliased columns.
0
 
LVL 1

Author Comment

by:Howard Bash
ID: 22850612
Regarding return more than one resultset depending on some logic within the stored procedure, in that case I would want all the returned resultsets which would at least tell me were some values downstreamed were sourced from.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22850729
You will have to write that then.  There is no functionality in SQL Server, and I can't imagine how oracle could/would do it either, to output all possible output formats from the query.

Think about it:

* Different parameters can dictate what code block is executed.
* Depending upon what code block is executed, you may be... (running different selects, running different procedures which do direct output,
* Depending upon parameters, the procedure could be building different dynamic SQL statements.

So it's not just a matter of no one thinking of it, there is no REASONABLE way to RELIABLY do what you desire.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22851160
to generate a sql on the fly and then get information about that dynamic sql,  you'd have to use dbms_sql package in Oracle.

it will allow you to parse and describe the output of a sql string.  Which will be a collection of variable types.

I have no idea if something comparable exists in SQL Server or not.
0
 
LVL 1

Author Comment

by:Howard Bash
ID: 23162724
I would rather you did not close it.  I want to close it myself by rewarding all the hard work these folks have put to solving my problem.  Hopefully,  I will get back on this issue very soon.  If not,  I would prefer to award points.

thanks,
Howard
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23299307
Although the answers may not have given you what you wanted, the answers were accurate and complete and I don't think that a grade of B was fair.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question