Solved

Analyze Stored Procedures in Oracle and SQL Server

Posted on 2008-10-30
19
1,055 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:hbash
  • 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 73

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 73

Expert Comment

by:sdstuber
ID: 22845475
prior to 11g, these views did not exist
0
 
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 73

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:hbash
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Expert Comment

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

Author Comment

by:hbash
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:hbash
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:hbash
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 73

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:hbash
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now