Board index » delphi » Stored Procedure, TQuery and how to get the output-values

Stored Procedure, TQuery and how to get the output-values

Hello!

I have an Oracle Stored Procedure ( TEST_PROC ) containing two input
parameters and two outputparameters.
Through use of TStoredProc, I am able to retrieve the output-values like
this: It works fine.

  StoredProc1.Params[0].AsInteger := 21682;
  StoredProc1.Params[1].AsInteger := 155;
  StoredProc1.Prepare;
  StoredProc1.ExecProc;

  Edit1.Text := StoredProc1.Params[2].AsString;
  Edit2.Text := StoredProc1.Params[3].AsString;

After I have made an Oracle Package of my stored procedures (
MYPACKAGE.TEST_PROC ), the
TStoredProc is not able to find my stored procedure. Any ideas why?
( I have tried to use the syntax : USER.PACKAGE.PROCEDURE but still the
TStoredProc cannot find the stored
procedure )

One more thing.

How do I call an Oracle Stored Procedure with use of a TQuery component?

I have tried this, but the out-parametervalues returns blank or I get an
"invalid pointer operation"

  with Query1 do
  begin
    SQL.Clear;
    SQL.Add('BEGIN TEST_PROC( 100,200, :out1, :out2 ); END;');
    ParamByName('out1').ParamType := ptOutput;
    ParamByName('out1').DataType := ftFloat;
    ParamByName('out2').ParamType := ptOutput;
    ParamByName('out2').DataType := ftFloat;
    ExecSQL;

    Edit1.Text := ParamByName('out1').AsString;
    Edit2.Text := ParamByName('out2').AsString;
 end;

My Oracle Stored Procedure :
-------------------------------------------

Procedure TEST_PROC
   ( inn1 IN NUMBER,
     inn2 IN NUMBER,
     out1 OUT NUMBER,
     out2 OUT NUMBER )
   IS
BEGIN
   out1 := inn1;
   out2 := inn2;
END;

Environment : Oracle 8.1.6 + D5

Regards Bjarte :-)

 

Re:Stored Procedure, TQuery and how to get the output-values


TQuery cant return OUT parameter values. So, you should use
TStoredProc instead of TQuery. Also, when you will use TStoredProc
with packaged proc or func, you should assign manually value to
TStoredProc.StoredProcName. You cant see packaged procs and
funcs in design time in drop down list for property
TStoredProc.StoredProcName.
So, StoredProc1.StoredProcName := 'TEST.STRRETURN';

Re:Stored Procedure, TQuery and how to get the output-values


Thanks for your answer!

....but I still have the problem after I put my stored procedure in a
package.
If I only use package name + proc-name Delphi reports an
EAccessValidation 'Access validation at address 034DDE4C in module
ORA805.dll" .Read of Address 00000000. -  when Prepare is executed.
If I use USER.PACKAGE.PROCEDURE Delphi reports the same error message.

Any ideas?

Regards Bjarte :-)

some code:
  SP := TStoredProc.Create( nil );
  SP.DatabaseName := 'mydatabase';
  SP.StoredProcName := 'CUST_USER.MYPACKAGE.TEST_PROC';
  SP.Prepare;

  SP.Params[0].AsInteger := 21682;
  SP.Params[1].AsInteger := 155;
  SP.ExecProc;

  Edit1.Text := SP.Params[2].AsString;
  Edit2.Text := SP.Params[3].AsString;

Quote
sunlf wrote:
> TQuery cant return OUT parameter values. So, you should use
> TStoredProc instead of TQuery. Also, when you will use TStoredProc
> with packaged proc or func, you should assign manually value to
> TStoredProc.StoredProcName. You cant see packaged procs and
> funcs in design time in drop down list for property
> TStoredProc.StoredProcName.
> So, StoredProc1.StoredProcName := 'TEST.STRRETURN';

Re:Stored Procedure, TQuery and how to get the output-values


Delphi does not support Oracle 8.1.6, and you have just run into one of
the unsupported features.  Your best alternative is to use a third-party
product like Direct Oracle Access (www.allroundautomations.nl).

John Pierce

Other Threads