Calling Oracle stored procedures and functions

jvillasantegomez · 2013-11-07 19:44:58

Hello

Can anyone post some code or example of calling a stored procedure and/or function from oracle pl/sql.

I can't make it work and this is basic stuff...

I want to get values by out parameters or by return function of oracle.

Any Help would be much appreciated!!!

Here's some code that sadly doesn't work AT ALL!!!

query :=  'BEGIN TEST_PKG.DUMMY(?, ?, ?, ?, ?); END;';
stmt := Props.NewThreadSafeStatementPrepared(query, false);

stmt.Bind(1, param1, paramIn);
stmt.BindTextU(2, param2, paramIn);
stmt.BindTextU(3, param3, paramIn);
stmt.BindTextS(4, param4, paramOut);
stmt.Bind(5, param5, paramOut);
stmt.ExecutePrepared;

In here param1, param2 and param3 are parameters that go to the procedure for it to work. This are effectively IN bound parameters.
param4 and param5 are the response for the procedure call. They are effectively OUT bound parameters.
Inside the procedure the last 2 params (param4 and param5) are filled up with some data, but back in Delphi i doesn't work at all, I always get back a wrong answer in param4 and param5.

The blog is full of benchmarks but no so many code samples....

PLEASE HELP!!!

Last edited by jvillasantegomez (2013-11-07 20:47:34)

ab · 2013-11-10 21:02:36

The input parameters are not changed in-place.

To retrieve an output parameter value, you have to use the following method:

    {/ retrieve the parameter content, after SQL execution
     - the leftmost SQL parameter has an index of 1
     - to be used e.g. with stored procedures
     - the parameter should have been bound with IO=paramOut or IO=paramInOut
       if CheckIsOutParameter is TRUE }
    function ParamToVariant(Param: Integer; var Value: Variant;
      CheckIsOutParameter: boolean=true): TSQLDBFieldType; virtual;

jvillasantegomez · 2013-11-11 13:13:16

Hello

The problem is that the statement Self.Props.NewThreadSafeStatementPrepared returns a ISQLDBStatement that doesn't have any method called ParamToVariant, so, do i have to cast or something??

Regards...

jvillasantegomez · 2013-11-11 14:19:18

Never Mind

Here's how to doit:

  stmt := Self.Props.NewThreadSafeStatement;
  stmt.Prepare(query, false);
  stmt.Bind(1, ..., paramIn);
  stmt.BindTextU(2, ..., paramIn);
  stmt.BindTextU(3, ..., paramIn);
  stmt.BindTextS(4, '', paramOut);
  stmt.Bind(5, 0, paramOut);
  stmt.ExecutePrepared;
  stmt.ParamToVariant(4, out1, true);
  stmt.ParamToVariant(5, out2, true);

You can't use Props.NewThreadSafeStatementPrepared(query, false); because that doesn't returns a TSQLDBStatement.

Thks for your response.

ab · 2013-11-11 15:00:15

Did you check the new BindCursor() and BoundCursor() methods, introduced yesterday?
See http://synopse.info/fossil/info/e9611a83ef

I did not make any test about it, so perhaps you may give some feedback.

jvillasantegomez · 2013-11-11 21:29:00

I have a lot of work to do, but as soon as i get a chance i will try the cursor functionality. Maybe tomorrow or the day after.

Thaks for contributing that much to the community.

ab · 2013-11-12 08:44:27

We have added ParamToVariant() method to ISQLDBStatement interface.

It is indeed very useful for stored procedure result access, and ISQLDBStatement is needed for proper statement caching.

See http://synopse.info/fossil/info/f8fee1974b

jvillasantegomez · 2013-11-12 14:35:10

Hello

First of all, thaks very much for your contributions to the community. You are doing a great work with mORmot.

Now, i'm trying the cursor functionality but it doesn't work so far.

here's a simple procedure on oracle:

PROCEDURE dummy (
    P_cursor OUT Globales.T_c_weak_cursor,
    P_id     IN Number
  ) IS
  BEGIN
    OPEN P_cursor FOR
		    SELECT * FROM some_table T WHERE T.Id = P_id
  END dummy;

And here's the calling delphi code:

var
  id: RawUTF8;
  query: RawUTF8;
  stmt: ISQLDBStatement;
  cursor: ISQLDBRows;
begin
  id := '2000';
	
  query :=  'BEGIN SOME_PKG.DUMMY(P_cursor => ?, P_id => ?); END;';
  stmt := Props.NewThreadSafeStatementPrepared(query, false);
  stmt.BindCursor(1);
  stmt.BindTextU(2, id, paramIn);
  stmt.ExecutePrepared;
  cursor := stmt.BoundCursor(1);     
  Result := cursor.FetchAllAsJSON(true);
end;

If Your execute that, the line cursor := stmt.BoundCursor(1); will throw this error: 'No BindCursor() on Param #2'.
Now, if you change the line cursor := stmt.BoundCursor(1); to cursor := stmt.BoundCursor(0); it will show this error: 'Prepare called with wrong ExpectResults', wich is odd because as the cursor is an out parameter is ok to call prepare with false.
Now, if you then change the line stmt := Props.NewThreadSafeStatementPrepared(query, false); to stmt := Props.NewThreadSafeStatementPrepared(query, true); Now that same line gives the error: Prepare called with wrong ExpectResults', wich is odder.

So, what gives??
The error raises on line 2603 on unit SynDBOracle because this line is true: if fExpectResults<>(StatementType=OCI_STMT_SELECT);
I haven't worked with OCI before, but it seems that is waiting for a select when in reality i'm just returning a cursor as an OUT parameter, so fExpectResults is false and different from OCI_STMT_SELECT.

Best regards...

ab · 2013-11-12 18:52:36

Oups... some obvious issue...
Please try http://synopse.info/fossil/info/18ade64d4a

jvillasantegomez · 2013-11-12 19:23:20

It now works like a charm.

Now ISQLDBStatement has the ParamToVariant() method and you can bind to oracle cursor.
For the sake of completness it would be great if one could call oracle functions, but I can live without it.

Thks for your quick commits to the great mORmot framework.

Last edited by jvillasantegomez (2013-11-13 14:47:12)

jvillasantegomez · 2013-11-28 16:35:55

Sorry

After some tests i found out that cursors are returning some empty records that don't belong to my dataset as stored in the db.
Whe you do Result := cursor.FetchAllAsJSON(true); it donsn't return my db records as expected, but it returns them mixin in some empty records.

best regards...

mORMot Open Source

#1 2013-11-07 19:44:58

Calling Oracle stored procedures and functions

#2 2013-11-10 21:02:36

Re: Calling Oracle stored procedures and functions

#3 2013-11-11 13:13:16

Re: Calling Oracle stored procedures and functions

#4 2013-11-11 14:19:18

Re: Calling Oracle stored procedures and functions

#5 2013-11-11 15:00:15

Re: Calling Oracle stored procedures and functions

#6 2013-11-11 21:29:00

Re: Calling Oracle stored procedures and functions

#7 2013-11-12 08:44:27

Re: Calling Oracle stored procedures and functions

#8 2013-11-12 14:35:10

Re: Calling Oracle stored procedures and functions

#9 2013-11-12 18:52:36

Re: Calling Oracle stored procedures and functions

#10 2013-11-12 19:23:20

Re: Calling Oracle stored procedures and functions

#11 2013-11-28 16:35:55

Re: Calling Oracle stored procedures and functions

Board footer