Solved

Analyze Stored Procedures in Oracle and SQL Server

Posted on 2008-10-30
19
1,097 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 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
migration MS SQL database to Oracle 30 62
SqlServer no dupes 25 35
SQL Error - Query 6 26
Checking a checkbox based on SQL DataReader boolean value in ASP.NET(VB) 2 21
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

831 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