Analyze Stored Procedures in Oracle and SQL Server
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
prior to 11g, these views did not exist
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
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
oops, yes, 10g is when they were both introduced, not just 11g. good catch
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.
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
And besides, I thought that "information_schema.routin
ASKER
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.
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.
information_schema.routine
ASKER
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.
Also this is not available in Oracle 10.2.
Plus the SQL return does not give me back aliased return columns.
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.
Since you are using oracle 11, does that matter?
No. You are right. It can't for the reasons I gave in http:#22850031.
ASKER
Plus, I do not see this returning return columns and input parameters as well as aliased columns.
ASKER
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.
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.
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.
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.
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.
ASKER
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
thanks,
Howard
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.