How to bind to a cursor on oracle

jvillasantegomez · 2013-11-05 20:58:39

Hello

How can I bind my params to a cursor for calling a stored proc of oracle. Here is a simple use case:

PROCEDURE dummy (P_cursor IN OUT Globales.T_c_weak_cursor, P_id IN Integer) IS
BEGIN
  OPEN P_cursor FOR
     SELECT * FROM whatever
END dummy;

Here's a simple oracle procedure, now let's see what's going on in mormot:

query :=  'call dummy (P_cursor => ?, P_id => ?)';
stmt := Props.NewThreadSafeStatementPrepared(query, false); // or true, don't know how to do it
stmt.Bind(1, myCursorVariable, paramOut);           // <------------- HOW I CAN DO THIS????????
stmt.BindTextU(2, id, paramIn);
stmt.ExecutePrepared;
// Then how I can get the cursor variable and return all that as JSON.

I'm using mormot with an old project at work and I would like to be capable of reusing the oracle procedures and functions, but i can't find a way of binding to a cursor from an oracle procedure.

Any help will be much appreciated!!!!

ab · 2013-11-06 12:28:53

Personally, I do not use such stored procs or cursors with Oracle at all.

AFAIK it is out of scope of our SynDB classes: there is no such notion as "DB cursor" within our managed types...
Our SynDBOracle unit does not handle the SQLT_CUR nor SQLT_RSET types.

What may be your "myCursorVariable" defined? As a result from anothe stored proc?
I'm still not able to find a way to handle such kind of "DB cursor" value in JSON... storing the pointer address as hexadecimal with a prefix (like dates or blobs)?

jvillasantegomez · 2013-11-06 14:00:43

Hello

It seems that you can't have an oracle stored proc that just returns a select. You must always return something, by a cursor out variable or doing a select ... into. Here's some code:

procedure dummy(p_id integer in) is
begin
  Select * from whatever;
end;

You can't make this to work on oracle, you must return a cursor for it to work.

My question then is, if I have to declare this types of procedures like this:

procedure dummy(P_cursor IN OUT Globales.T_c_weak_cursor, p_id IN integer) IS
  BEGIN
    OPEN P_cursor FOR
      Select * from whatever;		  
END

How can i use that with mormot, meaning, how can I call that last stored proc inside mormot?

Any help would be much appreciated.

ab · 2013-11-06 14:19:16

Sadly, it sounds like if it was out of scope of our SynDB classes: there is no such notion as "DB cursor" within our managed types...

And our SynDBOracle unit does not handle the SQLT_CUR nor SQLT_RSET types, even at lowest level...

You are free to propose some code patch, and do the testing.
We will check it and put it in the trunk version.

jvillasantegomez · 2013-11-06 14:49:12

It is sad indeed.

Oracles diverges in this respect to any other major database systems, so, by now we can't just use a cursor nor in an out parameter nor in a return value from a function. It is sad indeed, i will end up sending the sql string from delphi to oracle.

Thaks for your response.

ab · 2013-11-06 15:09:24

Note that thanks to features like array DML (e.g. SynDB BATCH mode), you can achieve amazing results in pure Delphi code.

Most of the time, using a stored procedure has no benefit, if its use was mainly to speed up the inserts.

jvillasantegomez · 2013-11-06 17:13:57

The use of stored procs is not just about to speed up the inserts, i'm using them to get data back from oracle with pretty convulated queries that would be a headache to write in a delphi string and because i'm working with an existing codebase that has a lot of the domain logic on oracle pl/sql. All the data retrieve procedures return a cursor and is a shame that i can't use that with mormot.

There seems not to be a solution for this wich, as i said, is a shame because every oracle developer out there knows that they has to return a cursor for any client (java, .NET, php) to work with the result set.

Anyways, i'm investigating converting the cursor to JSON on oracle and returning the json string to the caller. If i can get it working i'll post my solution here.

Mormot is indeed a great framework but it has its shortcomings.

ab · 2013-11-06 19:26:21

Such cursors is a Oracle-typical process of the 90s AFAIR...

Pretty powerful, but not standard.
Yes, we try to support all DBs, with common specificity support (like ArrayDML/Batch), but with the most common use cases.
I do not find out how we may use Oracle cursors in an ORM, for instance...

I understand it is a real shortcoming in your case.

jvillasantegomez · 2013-11-06 20:01:07

You're right, Oracle dosn't use standard stuff, it is the Microsoft of the database universe....

Now, Another related question...
Is it posible to call an oracle stored proc with 3 params in and one param out but also returning something... here's an example function spec on oracle:

FUNCTION dummy (
  param1 IN integer, 
  param2 IN integer,
  param3 IN string,
  param4 OUT string
) RETURN INTEGER;

In here i want to call this procedure and get back an integer result, also get back some string on param4 wich is an out param
Is that posible on mormot?? and if so who would i do it??

Here's what i got so far but it doesn't work

query :=  'call dummy (?, ?, ?, ?)';
  stmt := Props.NewThreadSafeStatementPrepared(query, false);  // it is false here??, remember, it is a function with returns some integer
  stmt.Bind(1, param1, paramIn);
  stmt.BindTextU(2, param2, paramIn);
  stmt.BindTextU(3, param3, paramIn);
  stmt.BindTextS(4, param4, paramOut);
  stmt.ExecutePrepared;                        // How do i get the returned integer from the function??

In here, param4 is a variable declared as string and will be filled by the function call, but i also want to get a handle to the function return variable.

Is this possible at all??

This is really important for me, it is ok if i can't call a procedure or function returning a cursor, after all it is not standard sql, but i must be able to call other kinds of procedures and/or functions from oracle. Please, any help would be much appreciated.

Last edited by jvillasantegomez (2013-11-06 21:40:38)

ab · 2013-11-07 07:48:46

Could you not create a stored procedure to return the FUNCTION dummy() returned value as an OUT parameter?

Sounds like the standard way of doing it.
http://asktom.oracle.com/pls/asktom/ASK … 3078805685

In this very same link, there is the Brett Rosen sample code, using plain OCI commands to retrieve a cursor content from a stored proc.
I suspect I may use it as reference to add cursor support in SynDBOracle.

Could you please create a feature request for cursor support in http://synopse.info/fossil/reportlist ? (with a link to the "AskTOM" site and also this forum thread).
We may be able to match such cursor fields with a JSON array.

jvillasantegomez · 2013-11-07 14:27:55

I've addedd a feature request for cursor support, it seems like a very good addition to the mormot framework.

Now, I'm still having problems trying to call a funtion from oracle... How do you bind the return value of the function?
Here's what i've got so far:

FUNCTION dummy (
  param1 IN integer, 
  param2 IN integer,
  param3 IN string,
  param4 OUT string
) RETURN INTEGER;

query :=  'BEGIN ? := dummy(?, ?, ?, ?); END;';
stmt := Props.NewThreadSafeStatementPrepared(query, false);
stmt.Bind(1, value, paramOut);
stmt.Bind(2, param1, paramIn);
stmt.BindTextU(3, param2, paramIn);
stmt.BindTextU(4, param3, paramIn);
stmt.BindTextS(5, param4, paramOut);
stmt.ExecutePrepared;

In here, param4 is a variable declared as string and will be filled by the function call.
value is declared as an integer and will be the return value of the function itself.

after the call, i want this two variables to be filled by the stored proc call
value equals the return value of the funcion
param4 equals the OUT param of the function wich is filled inside the function.

Saddly i don't get it to work yet.

My problem with oracle and all this is that i'm evolving a big codebase and I want to reuse the functions/procedures written in oracle, but so far i'm having trouble with it. Can anyone please help??

ab · 2013-11-07 15:40:28

As far as I understood the problem, you may need:

1. Simply use SELECT to return the value as a regular statement.

2. Or create a stored PROCEDURE wrapping the FUNCTION to return the value not with a RETURN but as an OUT parameter:

PROCEDURE dummyWrapped (
  param1 IN integer, 
  param2 IN integer,
  param3 IN string,
  param4 OUT string,
  param5 OUT integer
) ..........

jvillasantegomez · 2013-11-07 16:58:33

WoW

I will do that, wrap all the functions into procedures with out parameters, but again, it is a shame that SynDBOracle doesn't supports calling functions from oracle.

Thks for your response.

mORMot Open Source

#1 2013-11-05 20:58:39

How to bind to a cursor on oracle

#2 2013-11-06 12:28:53

Re: How to bind to a cursor on oracle

#3 2013-11-06 14:00:43

Re: How to bind to a cursor on oracle

#4 2013-11-06 14:19:16

Re: How to bind to a cursor on oracle

#5 2013-11-06 14:49:12

Re: How to bind to a cursor on oracle

#6 2013-11-06 15:09:24

Re: How to bind to a cursor on oracle

#7 2013-11-06 17:13:57

Re: How to bind to a cursor on oracle

#8 2013-11-06 19:26:21

Re: How to bind to a cursor on oracle

#9 2013-11-06 20:01:07

Re: How to bind to a cursor on oracle

#10 2013-11-07 07:48:46

Re: How to bind to a cursor on oracle

#11 2013-11-07 14:27:55

Re: How to bind to a cursor on oracle

#12 2013-11-07 15:40:28

Re: How to bind to a cursor on oracle

#13 2013-11-07 16:58:33

Re: How to bind to a cursor on oracle

Board footer