General routine for stored procedures

[KitJackson]

I'm working on an application where all changes to data are done by means of stored procedures. This means that I have a lot of code of the form:-

with OracleQuery do
begin
Close;
SQL.Clear;
DeleteVariables;

SQL.Add('PLSQLstatement');
DeclareVariable('Variable1', otString);
DeclareVariable('Variable2', otInteger);
etc...
SetVariable('Variable1', Value1);
SetVariable('Variable1', Value2);
etc...

Execute;
etc...
end;

Where PLSQLstatement is

begin
StoredProcNameVariable1, :Variable2, etc...);
end;

I'd like to replace many such calls with a call to a single procedure

Procedure OracleUpdate(StoredProcName: string; ParamValues: array of const)

My problem is this. I need to get the names of the parameters and their types to save me having to extend the signature of this routine to include this information.

Is the routine used by DOA in the Variables Editor available to users to call or is it only available in the design time system for use inside the Delphi IDE? I only have a binary licence and don't have the source to DOA. Has anyone who has the source managed to extract this part of the design time system for use in their own routines? I could write something myself but I'd prefer the use the built in routine so that I always get the same results as DOA.

The parameter type I can get from ParamValues. It would be the callers responsibility to arrange ParamValues in the correct order.

An alternative scheme is to call something like this from within the new routine

select argument_name, data_type, data_length, data_precision, position
from all_arguments
where owner = 'MYOWNER'
and object_name = 'MYPROCEDURE'

Is the view sys.all_arguments always public so that this will work? Has anyone got any other ideas?

Thanks
Kit Jackson

 

[Marco Kalter]

The routine in the variables editor scans a SQL text for bind variables. It is publicly available, but it won't help you because you only have name of the stored procedure.

Querying the sys.all_arguments view once and caching the results might be the best way to go. As far as I know this view is granted to public by default.