Retrieve PL/SQL parameters 2

[Diancecht]

Hi all.

I'm trying to build a generic program to execute stored procedures.

What I want to achieve is to get the name of the procedure, manage to get the parameters and show a form to the user to get the input values.

Now I'm stuck on how to dynamically retrieve the parameter names and types. Looks like you need to know them from the procedure declaration.

What I'm trying to do is a kind of reflection with stored prodecures.

Does anyone know any hint to achieve this?

Thanks in advance,
Dian

 

[Diancecht]

I've found the sollution for Oracle by querying the ALL_ARGUMENTS system table.

I don't think there's a proper Visual Basic way to do it.

Cheers,
Dian

 

[SBerthold]

>I don't think there's a proper Visual Basic way to do it.

Now, that is a misstatement. It has nothing to do with Visual Basic being able to properly do this, but rather the Data Access method returning the information, which relys on what the DBMS provides through it's interface.

If you cannot get this information from a stored proceedure\recordset created for this purpose, using DBMS specific syntax and functions, then you could try using the OpenSchema method under ADO, that is, if the OLEDB Provider for the DBMS has built into it the methods to expose these properties.
Look up "OpenSchema" under ADO in the help files or the MSDN Data Access site.

 

[Diancecht]

Thank you for for comments.

I use to assume the functionalities on the database connectors (OLEDB Providers) as language functionalities. I sometimes forget they're external features.

Anyway, thank you for the idea, I'll check de docs, maybe I overlooked something there.

Cheers,
Dian

 

[SBerthold]

>If you cannot get this information from a stored proceedure\recordset created for this purpose, using DBMS specific syntax and functions ...

But it sounds like you already found a specific dbms method.
OpenSchema was just another possible option I was offering.
When I said "Now, that is a misstatement." that was to be taken lightly, and after reading my post again, I hope it didn't offend you.
Hope you'll find the

solution you are looking for.


​

 

[Diancecht]

No offense at all.

The method I've found is an SQL query yo the ALL_ARGUMENTS Oracle table, that's what I mean with no Visual Basic sollution.

Cheers,
Dian

 

[Diancecht]

Finally, I overlooked the documentation. There's an option on OpenSchema to retrieve the info I need.

Just in case anyone else needs it

MSDN documentation:

http://msdn2.microsoft.com/en-us/library/ms945095.aspx

Open Schema tutorial:

http://www.w3schools.com/ado/met_conn_openschema.asp

Nice article on database metadata usinf OpenSchema

http://www.ftponline.com/archives/premier/mgznarch/VBPJ/1999/06jun99/dd0699.pdf

Example project:

http://www.nigelrivett.net/VB/ADOScema.html

Cheers,
Dian

 

[SBerthold]

Thank you for the star and posting all the information - this information may also help others.
Nice!