FAQ: How to call stored procedures?

[faq]

I have some stored procedures I need to call, but I don't see any TStoredproc equivalent for Direct Oracle Access. How can I call my stored procedures?

 

[support]

To call stored procedures and functions you can either use a TOracleQuery with a PL/SQL Block, or use the TOraclePackage component. If your function is called MyFunction that returns a string value, the PL/SQL Block would look like this:

begin
  :result := MyFunction(:param1, :param2);
end;

The :result, aram1 and aram2 variables need to be declared with a type that is compatible with the result and parameter types of the function. You can simply execute this query:

var
  Result: string;
begin
  with MyFunctionQuery do
  begin
    SetVariable('param1', 10);
    SetVariable('param2', 'SMITH');
    Execute;
    Result := GetVariable('result');
  end;
end;

or in C++Builder

{
  AnsiString Result;
  MyQuery->SetVariable("param1", 10);
  MyQuery->SetVariable("param2", "SMITH");
  MyQuery->Execute();
  Result = MyQuery->GetVariable("result");
}

If you are calling packaged procedures and functions, you can also use a TOraclePackage component. Set the Name and PackageName properties to 'MyPackage', and call the function like this (again assuming that MyPackage.MyFunction returns a string result):

Result := MyPackage.CallStringFunction('MyFunction', [10, 'SMITH']);

or in C++Builder

{
  AnsiString Result;
  Variant Params[] = {10, "SMITH"};
  Result = MyPackage->CallStringFunction("MyFunction",
    EXISTINGARRY(Params));
}

As a trick you can also leave the PackageName property empty to call functions and procedures that are not packaged, and still use one single TOraclePackage component.

[This message has been edited by support (edited 13 September 1999).]

 

[KaeOl]

An other way to call stored functions is to use an TOracleQuery like this:

VAR
  Query  : TOracleQuery;
  Param  : STRING;
  Result : STRING;
BEGIN
  Param          := 'MyParameter';
  Query.SQL.Text := 'select MyFunction ('''
                   + Param
                   + ''' from dual';
  Query.Execute;
  Result         := Query.Field (0);
END;

[This message has been edited by KaeOl (edited 10 September 1999).]

 

[shiv Kumar]

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure so that the stored procedure can act based on the parameter value(s) that is passed.

Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and click Execute Stored Procedure.

To know more about this visit on my page:http://www.cetpainfotech.com/technology/oracle-dba-Training