Describe Stored Proc parameters

[David Best]

Using TOracleQuery.Describe for a stored proc seems to do nothing.

Query.Sql.Text := ':Result := MyProcarg1);';
Query.Describe;

Both FieldCount and VariableCount are 0. How do I get the parameter descriptions? I must be missing something obvious!

Thanks

 

[al0]

Describe procedure has nothing in common with variables . It obtains descriptions of select-list fields.

For variables you should use TQuery.DeclareVariable procedure (or DeclareAndSet to declare variable and setits value in one step).

 

[David Best]

That's disappointing. I have a helper library which works with queries and stored procs in a generic way. I am trying to implement a generic 'CallFunction' which returns the result as a variant.

I suppose I can query the system tables for the info, but I expected DOA to do it for me.

 

[al0]

No system tables can contains information of your anonymous PL/SQL block. In your first message you have written:

Query.Sql.Text := ':Result := MyProcarg1);';

First of all, this is invalid, it shall be:

Query.Sql.Text := 'begin :Result := MyProcarg1); end;';

Secondly, your PL/SQL block may be much more complicated and may contain a lot of variables, which can be not just obtained as stored function results, but calculated directly inside the block, so there is absolutely no way for database which variables and of which type you plan to obtain from this block, you only are able to know!

So when you construct that block you shall supply information concerning those variables with DeclareVariable call.

Your concern is that you have to call arbitrary function, not known in advance. You can query its parameter types as well as result type from USER_ARGUMNTS (or ALL_ARGUMENTS if it is located in different schema) system view, then call DeclareVariable.

 

[David Best]

I will be limiting this to simple functions with elementary data types, so querying USER_ARGUMENTS might just do the trick.

Thanks!