Oracle and stored function problem

[mdec]

Hi all,
There is my first experience with Oracle stored functions, and therefore i need a little help.
I did some examples on MySQL and it works, but for Oracle i have a big problem .

From Oracle developers I have got information about function prepared for my part of the project:

function GetLimit(CustCode in varchar2,
PlaceCode in out varchar2,
MoneyLimit out number,
MoneyTime out number,
rErr out varchar2 )return boolean;


Part of my Code:

Description:
Environment: ZeosDBO: 6.6.2, D7 Ent, Oracle 10g, Windows Vista Premium Basic and/or XP PRO, both legal and upgraded - the same effects.
Oracle Instant Client 11.1
zspKred is a type of TZStoredProc and connected to OraConn on OraTestMainForm.
Connection with Oracle works fine, I can read all other views without problems.


zspKred.Close;
zspKred.Params.Clear;
zspKred.Params.Add;
zspKred.Params[0].Name:='CustCode';
zspKred.Params[0].DataType:=ftString;
zspKred.Params[0].ParamType:=ptInput;
zspKred.Params.Add;
zspKred.Params[1].Name:='PlaceCode';
zspKred.Params[1].DataType:=ftString;
zspKred.Params[1].ParamType:=ptInputOutput;
zspKred.Params.Add;
zspKred.Params[2].Name:='MoneyLimit';
zspKred.Params[2].DataType:=ftInteger;
zspKred.Params[2].ParamType:=ptOutput;
zspKred.Params.Add;
zspKred.Params[3].Name:='MoneyTime';
zspKred.Params[3].DataType:=ftInteger;
zspKred.Params[3].ParamType:=ptOutput;
zspKred.Params.Add;
zspKred.Params[4].Name:='rErr';
zspKred.Params[4].DataType:=ftString;
zspKred.Params[4].ParamType:=ptOutput;

zspKred.StoredProcName:='GetLimit';

zspKred.Connection:=OraTestMainForm.OraConn;

zspKred.ParamByName('CustCode').AsString:='013137';
zspKred.ParamByName('PlaceCode').AsString:='WA';
zspKred.ParamCheck:=true;

Here comes execution and independent of the call there is the same error:
'Unsupported operation'

zspKred.ExecProc;

zspKred.Open;

SQL log (TZSQLMonitor) is in this moment empty but it's clear.

I am sure that I am doing something wrong, but what?

Thak you in advance for help.
Regards
Mariusz

[mdaems]

I think it means 'TZStoredProc isn't implemented for Oracle'.

So you'll have to use regular query components to get your results.

Mark

[mdec]

So you'll have to use regular query components to get your results.

Mark

I have tried a lot of queries in standard manner as well , but my experience with Oracle PL/SQL is about NULL .
Currently I am very regullary on the sites of Oracle, looking for ORA-NNNNN error and for samples/documentation.
I have tried EXECUTE, SELECT, USING parameters, but nothing. I have got only errors - bad query, wrong ended query and so on.

Maybe somebody will help me with preparing this first query?

Thank You in advance
Regards
Mariusz

[mdaems]

Unfortunately your function returns a boolean, a datatype oracle can't return from an sql query. The more they use an out parameter which can't used in a simple select either. Can you ask the developers to return a string (True/False or Y/N or Y/N/<error message) from their function? Eventually you could easily write a stored function for your own usage that's doing right this.

Maybe the worst news for you : we don't have active Oracle developers in the zeoslib team for the moment. So there's no way we can get the TZStoredProcedure component working for Oracle now.

As a side note : this is not exactly true as I'm a professional Oracle PL/SQL and Forms developer in 'real life'. I never programmed using the Oracle dll's, however. So I just don't know enough about it. Apart from this : I would have to set up an Oracle server to start doing this at home. Not very likely...

Mark

[mdec]

.... I would have to set up an Oracle server to start doing this at home. Not very likely...

Mark

Thx for answer.
Ok, in fact boolean is currently changed to number 0/1. I have used old document while preparing post - sorry.

My problem is how to prepare good query - as you know each comma, double comma, return etc. may be important when you are trying to use the new (for you) dialect of the SQL...

I am looking for solution by my friends here, so you haven't to install Oracle so soon
BTW:
I have had in the past connections with AGFA and I was often guest in Antwerp. Very nice town, I like it very much, for me very similar to Cologne in Germany. In my opinion belgian beers are the best in the Europe, next position is for polish breweries, next NL, DK... My first beer from Belgium is Hoegarden Vorbeedene Frucht - I like this kind of white beers.
Regards
Mariusz