[fpc-pascal] fcl-db: oracle SP

[Bee]

Hi all,

Using fcl-db aka sqldb, how to call oracle's stored procedure through
toracleconnection? So far, google can't help me much. :( Or is there a
special component to handle stored procedure as in Delphi? TIA.

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Bee]

Post by Bee
Using fcl-db aka sqldb, how to call oracle's stored procedure through
toracleconnection? So far, google can't help me much. :( Or is there a
special component to handle stored procedure as in Delphi? TIA.

I'm using Oracle 10g, FPC 2.2.1, on Ubuntu 386.

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Leonardo M. Ramé]

Post by Bee
Using fcl-db aka sqldb, how to call oracle's stored procedure through
toracleconnection? So far, google can't help me much. :( Or is there a
special component to handle stored procedure as in Delphi? TIA.

Untested:

If your stored procedure returns a recordset simply do:

query.SQL.Text := 'select * from your_stored_sp';
query.Open;


If the SP does not returns a resultset do:

query.SQL.Text := 'execute your_stored_sp(''param1'', ''param2'')';
query.ExecSql;



Leonardo M. Ramé
http://leonardorame.blogspot.com


____________________________________________________________________________________
You rock. That's why Blockbuster's offering you one month of Blockbuster Total Access, No Cost.
http://tc.deals.yahoo.com/tc/blockbuster/text5.com

[Bee]

Post by Leonardo M. Ramé
query.SQL.Text := 'select * from your_stored_sp';
query.Open;
query.SQL.Text := 'execute your_stored_sp(''param1'', ''param2'')';
query.ExecSql;

That's what common db engines do. But not with oracle. Yes, I tested
them. :( For SP with no return value(s), simply do "call
sp_name<input_param_list>" is working perfectly. But if the SP involves
out param and/or cursor, none of "common" method is working. :(

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Joost van der Sluis]

Post by Bee
Hi all,
Using fcl-db aka sqldb, how to call oracle's stored procedure through
toracleconnection? So far, google can't help me much. :( Or is there a
special component to handle stored procedure as in Delphi? TIA.

Wow, so there is actually someone using the oracle-connection... I
thought I only made it for pr-purposes. ;)

Btw: fcl-db contains all the TDataset-based components. Also TDataset
itself is part of fcl-db. But also TMemDataset and tDbf. If you wanna
use some other db-framework like ZEOS you also need fcl-db.

sqldb is just one way to use TDataset, it's part of fcl-db, but it's not
an alias or something. Just to clear this up.

That sqldb doesn't return data from a stored procedure is probably
because it only tries to fetch data if the query starts with 'select' or
'show'. I'll have to add 'call' to that list for Oracle and do some
tests.

Can you submit a bug-report?

Joost.

[Bee]

Post by Joost van der Sluis
Wow, so there is actually someone using the oracle-connection... I
thought I only made it for pr-purposes. ;)

This is my first time experience with Oracle using FPC. I'm now using
FPC 2.2.1 and Lazarus 9.24.1. :-D

Post by Joost van der Sluis
Btw: fcl-db contains all the TDataset-based components. Also TDataset
itself is part of fcl-db. But also TMemDataset and tDbf. If you wanna
use some other db-framework like ZEOS you also need fcl-db.

I understand this.

Post by Joost van der Sluis
sqldb is just one way to use TDataset, it's part of fcl-db, but it's not
an alias or something. Just to clear this up.

Confirmed. :)

Post by Joost van der Sluis
That sqldb doesn't return data from a stored procedure is probably
because it only tries to fetch data if the query starts with 'select' or
'show'. I'll have to add 'call' to that list for Oracle and do some
tests.

I found that there are 2 kinds of server side code on Oracle. First, is
what called as "stored procedure" aka SP. Second, is what called as
"store function" aka SF.

For SP, Oracle returns the output as out param with cursor type. To call
SP, I'm using "call sp_name(:param)". The param is set accordingly
through OI. But, I have no idea how to obtain the cursor and read the
data within it. My code always raises an AV.

For SF, Oracle returns the output as row. To call SF, I'm using "call
sf_name from dual". But, I found the output of SF is not "compatible"
with TDataSource, and it raises an AV too.

I then digged deeper to the FCL's oracle binding codes and I found that
many Oracle's data types and features not yet wrapped. Transaction and
cursor are some of them. CMIIW.

For comparison, I look at PHP oracle examples. PHP has support for
cursor type. But, I don't understand why PHP requires two calls for
OCIExecute to get an SP result.

Post by Joost van der Sluis
Can you submit a bug-report?

I want to make sure my attempts are on the right track before I submit a
bug report. So, Joost... please help me here. If I found it's not too
difficult to help you fix fcl oracle, I can manage my time to help. TIA.

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Bee]

Post by Bee
For SF, Oracle returns the output as row. To call SF, I'm using "call
sf_name from dual". But, I found the output of SF is not "compatible"
with TDataSource, and it raises an AV too.

Sorry, my mistake. I mean "select sf_name from dual".

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Bee]

Post by Joost van der Sluis
That sqldb doesn't return data from a stored procedure is probably
because it only tries to fetch data if the query starts with 'select' or
'show'. I'll have to add 'call' to that list for Oracle and do some
tests.

Where can I add 'call' for Oracle? TIA.

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Michael Van Canneyt]

Post by Bee

Post by Joost van der Sluis
That sqldb doesn't return data from a stored procedure is probably
because it only tries to fetch data if the query starts with 'select' or
'show'. I'll have to add 'call' to that list for Oracle and do some
tests.

Where can I add 'call' for Oracle? TIA.

override TSQLConnection.StrToStatementType in TOracleCOnnection, and add it
there.

Michael.

Post by Bee
-Bee-
http://beeography.wordpress.com
_______________________________________________
http://lists.freepascal.org/mailman/listinfo/fpc-pascal

[Bee]

Post by Michael Van Canneyt
override TSQLConnection.StrToStatementType in TOracleCOnnection, and add it
there.

If I simply replace 'execute' with 'call' of StatementTokens constant
within sqldb unit, then recompile FPC and Lazarus, would it work?
It's just for testing to make sure it works before going further.

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Michael Van Canneyt]

Post by Michael Van Canneyt
override TSQLConnection.StrToStatementType in TOracleCOnnection, and add it
there.

If I simply replace 'execute' with 'call' of StatementTokens constant within
sqldb unit, then recompile FPC and Lazarus, would it work?

No, because you would break other connection types.

Michael

[Bee]

Post by Michael Van Canneyt
No, because you would break other connection types.

As I said, it's just for testing purpose and I don't use other
connection type. But as far as I understand, it indeed doesn't work
since stExecProcedure is never checked anyway. :(

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Bee]

Post by Michael Van Canneyt
override TSQLConnection.StrToStatementType in TOracleCOnnection, and add it
there.

I added this to oracleconnection.pp unit

function TOracleConnection.StrToStatementType(s : string) : TStatementType;
begin
S:=Lowercase(s);
if s = 'call' then exit(stSelect);
result := inherited StrToStatementType(s);
end;

Recompiled my fpc 2.2.1, compilation went ok.

Compile my test program, fine. Run the test program, it crashed. But
now, instead of unknown AV, it raised a runtime error (EDatabaseError)
with message: "EDatabaseError : : Oracle returned error 24333:".

The number means: "ORA-24333: zero iteration count error". It's the same
exact error message when I tried Zeos to access the same SP. I don't
know what it means. I'm stucked, again. :(

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Bee]

Post by Michael Van Canneyt
override TSQLConnection.StrToStatementType in TOracleCOnnection, and add it
there.

BTW... 'select sf from dual' despite the syntax very common is still not
working, raising an unhandled AV. I think it's more than just syntax or
parsing problem. :(

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Inoussa OUEDRAOGO]

Post by Bee
BTW... 'select sf from dual' despite the syntax very common is still not
working, raising an unhandled AV. I think it's more than just syntax or
parsing problem. :(

For a pipelined function, the syntax is : select * from table( sf() )
The other syntax ( 'select sf from dual' ) is for simple function ,
which returns single value.

[Bee]

I'm new to Oracle.

Post by Inoussa OUEDRAOGO
For a pipelined function, the syntax is : select * from table( sf() )
The other syntax ( 'select sf from dual' ) is for simple function ,
which returns single value.

What's "pipelined function"? "select * from table(sf())" seems to
requires a table. Does an SF belong to a table?

Ah, I need to dig more to Oracle manuals, obviously. :)

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Inoussa OUEDRAOGO]

Post by Bee
I'm new to Oracle.

Post by Inoussa OUEDRAOGO
For a pipelined function, the syntax is : select * from table( sf() )
The other syntax ( 'select sf from dual' ) is for simple function ,
which returns single value.

What's "pipelined function"? "select * from table(sf())" seems to requires
a table. Does an SF belong to a table?

A pipeline function is an Oracle way to create "selectable" SQL
function , like the ones in
MS SQL SERVER ( 2000+ ), or Firebird selectable stored procedure.
Below is a simple Oracle sample :

CREATE OR REPLACE TYPE TYPE_REC_A AS OBJECT(
INT_FIELD NUMBER(6),
STR_FIELD Varchar2(60) )
/

CREATE OR REPLACE TYPE TYPE_TABLE_REC_A AS TABLE OF TYPE_REC_A
/

CREATE OR REPLACE FUNCTION FN_GET_LIST_A
RETURN TYPE_TABLE_REC_A PIPELINED IS
REC_ROW TYPE_REC_A ;
BEGIN

REC_ROW := TYPE_REC_A( 1, 'line 1' );
PIPE ROW ( REC_ROW );

REC_ROW := TYPE_REC_A( 2, 'line 2' );
PIPE ROW ( REC_ROW );

REC_ROW := TYPE_REC_A( 3, 'line 3' );
PIPE ROW ( REC_ROW );

RETURN;

END;
/

select * from table( FN_GET_LIST_A() )

[Bee]

Thanks, your advice is indeed working using TOracleConnection and
TSQLQuery. But, I got performance overhead using this trick. Pipelined
function took 16 ms while default SF/SP for same result took less than 1 ms.

So, the thing we should fix is to make sqlDB supports Oracle's default
SP/SF and get the best performance out of it. ;)

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

[Bee]

Post by Bee
So, the thing we should fix is to make sqlDB supports Oracle's default
SP/SF and get the best performance out of it. ;)

http://bugs.freepascal.org/view.php?id=11117

-Bee-

has Bee.ography at:
http://beeography.wordpress.com