Sign in Join

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Mailing lists / fpc-pascal / 2008-04

fcl-db: oracle SP

19 messages · 5 people · –

Reply by email Subscribe to this list Your subscription

Read-only archive. Use j and k to move between messages.

Leonardo M. Ram�
to 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

Reply by email

Bee
to Leonardo M. Ram�
Show quoted text 9 lines
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;

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

Reply by email

Joost van der Sluis
to Bee: Using fcl-db aka sqldb, how to call oracle's stored procedure through toracleconnection?…

Op vrijdag 04-04-2008 om 17:19 uur [tijdzone +0700], schreef Bee:

Show quoted text 5 lines
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.

Reply by email

Bee
to 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

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.

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. :)

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.

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

Reply by email

Michael Van Canneyt
to Bee
Bee wrote 6 lines
> 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.

Show quoted text 10 lines
-Bee- has Bee.ography at: http://beeography.wordpress.com _______________________________________________ fpc-pascal maillist - http://lists.freepascal.org/mailman/listinfo/fpc-pascal

Reply by email

Michael Van Canneyt
to Bee
Bee wrote 5 lines
> 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

Reply by email

Bee
to Michael Van Canneyt: override TSQLConnection.StrToStatementType in TOracleCOnnection, and add it there.…
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

Reply by email

Inoussa OUEDRAOGO
to 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.

Inoussa O.

Reply by email

Bee
to Inoussa OUEDRAOGO

I'm new to Oracle.

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

Reply by email

Inoussa OUEDRAOGO
to Bee

2008/4/7, Bee :

Show quoted text 10 lines
I'm new to Oracle. > 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() )

Inoussa O.

Reply by email

Bee
to Inoussa OUEDRAOGO
Below is a simple Oracle sample :

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

Reply by email