Board index » delphi » Delphi 4 C/S and Oracle 7.3 stored procedures problem

Delphi 4 C/S and Oracle 7.3 stored procedures problem

Hello All,

If I try to use Oracle 7.3 stored procedures with a cursor as parameter
in Delphi 4 C/S (TStoredProc) I have the following problem:

If try to open the stored procedure designtime (set active true) it is
possible to open it once. The Stored procedure returns the result set.
If I set active to false and then to true I receive an Oracle error
message 'Invalid cursor'.
The only way to open it again is to retype the StoredProcName and define
the Params again. After that it is possible to open it once.

In delphi 3 I have no problems with the stored procedure !!!

If I compare the output of the SQL monitor in delphi 3 and delphi 4
I see that delphi 4 tries to assign a null value to the cursor
parameter. In delphi 3 not.
I have set the parameter value to Unassigned in the delphi 4 parameter
editor.

I can reproduce the problem in delphi 3 when I check the 'null' checkbox
of the cursor parameter
in the delphi 3 parameter editor.
Now I receive the same error message 'Invalid cursor' because delphi
tries to assign a value
to the cursor parameter.

How can I prevent delphi 4 to assign a null value to the cursor
parameter ?

Additional information:
- In the params editor I defined the cursorparam as IN/OUT Parameter and
of type ftCursor
- Runtime I receive the same errors.
- In the BDE I use SQLORA32.DLL.

Thanks in advance.

Bas Wiebinga

 

Re:Delphi 4 C/S and Oracle 7.3 stored procedures problem


Quote
>If I try to use Oracle 7.3 stored procedures with a cursor as parameter
>in Delphi 4 C/S (TStoredProc) I have the following problem:

>If try to open the stored procedure designtime (set active true) it is
>possible to open it once. The Stored procedure returns the result set.
>If I set active to false and then to true I receive an Oracle error
>message 'Invalid cursor'.

We have found two other bugs with Delphi4 and Oracle stored
procedures. I think this area was not tested particularly well. We
have succeeded in getting Delphi 3 + BDE 5 to work with Oracle 8.04
packaged and standalone procedures, but only with workarounds.

What version of the BDE are you using? Is there any reason you are not
upgrading to Oracle 8.04?

Re:Delphi 4 C/S and Oracle 7.3 stored procedures problem


Try to onfig your BDE :

DLL32 = SQLORA32.DLL (Don't use OCI.DLL)
VENDOR INIT = ORA804.DLL <- Use it !
...
NET PROTOCOL = TNS (Don't uses TCP/IP)
...
SCHEMA CACHE DIR = 'C:\TEMP' or other
SCHEMA CACHE DIR = 256
...
SQLQRYMODE = SERVER
SERVER NAME = DESENV.WORLD (Connect string to Oracle)

---------------------------------
Jucimar Junior (FUCAPI)

Bas Wiebinga escreveu na mensagem <35EF9BC7.672A0...@terbit.nl>...

Quote
>Hello All,

>If I try to use Oracle 7.3 stored procedures with a cursor as parameter
>in Delphi 4 C/S (TStoredProc) I have the following problem:

>If try to open the stored procedure designtime (set active true) it is
>possible to open it once. The Stored procedure returns the result set.
>If I set active to false and then to true I receive an Oracle error
>message 'Invalid cursor'.
>The only way to open it again is to retype the StoredProcName and define
>the Params again. After that it is possible to open it once.

>In delphi 3 I have no problems with the stored procedure !!!

>If I compare the output of the SQL monitor in delphi 3 and delphi 4
>I see that delphi 4 tries to assign a null value to the cursor
>parameter. In delphi 3 not.
>I have set the parameter value to Unassigned in the delphi 4 parameter
>editor.

>I can reproduce the problem in delphi 3 when I check the 'null' checkbox
>of the cursor parameter
>in the delphi 3 parameter editor.
>Now I receive the same error message 'Invalid cursor' because delphi
>tries to assign a value
>to the cursor parameter.

>How can I prevent delphi 4 to assign a null value to the cursor
>parameter ?

>Additional information:
>- In the params editor I defined the cursorparam as IN/OUT Parameter and
>of type ftCursor
>- Runtime I receive the same errors.
>- In the BDE I use SQLORA32.DLL.

>Thanks in advance.

>Bas Wiebinga

Re:Delphi 4 C/S and Oracle 7.3 stored procedures problem


Quote
Jucimar Junior wrote:

> Try to onfig your BDE :

> DLL32 = SQLORA32.DLL (Don't use OCI.DLL)
> VENDOR INIT = ORA804.DLL <- Use it !

But what if ORA804.DLL is not availaible on the client
and I have to use ORA73.DLL ?

Re:Delphi 4 C/S and Oracle 7.3 stored procedures problem


Quote
> But what if ORA804.DLL is not availaible on the client and I have to use

ORA73.DLL ?

Opsss ... :) Use like D3

DLL32 = SQLORA32.DLL
VENDOR INIT = ORA73.DLL <- Use it !
...
NET PROTOCOL = TNS (Don't uses TCP/IP)
...
SCHEMA CACHE DIR = 'C:\TEMP' or other
SCHEMA CACHE DIR = 256
...
SQLQRYMODE = SERVER
SERVER NAME = DESENV.WORLD (Connect string to Oracle)

---------------------------------
Jucimar Junior (FUCAPI)

Re:Delphi 4 C/S and Oracle 7.3 stored procedures problem


I have the same problem as described below. But the solution does not
work for me.
If anybody knows other solutions, please get back to me as soon as
possible.
Thank you very much.

I've read this solution:
***************************************************
Try to onfig your BDE :

DLL32 = SQLORA32.DLL (Don't use OCI.DLL)
VENDOR INIT = ORA804.DLL <- Use it !
...
NET PROTOCOL = TNS (Don't uses TCP/IP)
...
SCHEMA CACHE DIR = 'C:\TEMP' or other
SCHEMA CACHE DIR = 256
...
SQLQRYMODE = SERVER
SERVER NAME = DESENV.WORLD (Connect string to Oracle)

---------------------------------
Jucimar Junior (FUCAPI)

Bas Wiebinga escreveu na mensagem <35EF9BC7.672A0...@terbit.nl>...

Quote
>Hello All,

>If I try to use Oracle 7.3 stored procedures with a cursor as parameter
>in Delphi 4 C/S (TStoredProc) I have the following problem:

>If try to open the stored procedure designtime (set active true) it is
>possible to open it once. The Stored procedure returns the result set.
>If I set active to false and then to true I receive an Oracle error
>message 'Invalid cursor'.
>The only way to open it again is to retype the StoredProcName and
define
>the Params again. After that it is possible to open it once.

>In delphi 3 I have no problems with the stored procedure !!!

>If I compare the output of the SQL monitor in delphi 3 and delphi 4
>I see that delphi 4 tries to assign a null value to the cursor
>parameter. In delphi 3 not.
>I have set the parameter value to Unassigned in the delphi 4 parameter
>editor.

>I can reproduce the problem in delphi 3 when I check the 'null'
checkbox
>of the cursor parameter
>in the delphi 3 parameter editor.
>Now I receive the same error message 'Invalid cursor' because delphi
>tries to assign a value
>to the cursor parameter.

>How can I prevent delphi 4 to assign a null value to the cursor
>parameter ?

>Additional information:
>- In the params editor I defined the cursorparam as IN/OUT Parameter
and
>of type ftCursor
>- Runtime I receive the same errors.
>- In the BDE I use SQLORA32.DLL.

>Thanks in advance.

>Bas Wiebinga

Re:Delphi 4 C/S and Oracle 7.3 stored procedures problem


I'm just guessing, but try

StoredProc1.ParamByName('parmname').Clear;

before opening the storedproc.

Hope this helps.

TKJ

Quote
Eugene Vatnik wrote in message <35F6CDFC.67958...@clarendon-ins.com>...
>I have the same problem as described below. But the solution does not
>work for me.
>If anybody knows other solutions, please get back to me as soon as
>possible.
>Thank you very much.

>I've read this solution:
>***************************************************
>Try to onfig your BDE :

>DLL32 = SQLORA32.DLL (Don't use OCI.DLL)
>VENDOR INIT = ORA804.DLL <- Use it !
>...
>NET PROTOCOL = TNS (Don't uses TCP/IP)
>...
>SCHEMA CACHE DIR = 'C:\TEMP' or other
>SCHEMA CACHE DIR = 256
>...
>SQLQRYMODE = SERVER
>SERVER NAME = DESENV.WORLD (Connect string to Oracle)

>---------------------------------
>Jucimar Junior (FUCAPI)

>Bas Wiebinga escreveu na mensagem <35EF9BC7.672A0...@terbit.nl>...
>>Hello All,

>>If I try to use Oracle 7.3 stored procedures with a cursor as parameter

>>in Delphi 4 C/S (TStoredProc) I have the following problem:

>>If try to open the stored procedure designtime (set active true) it is
>>possible to open it once. The Stored procedure returns the result set.
>>If I set active to false and then to true I receive an Oracle error
>>message 'Invalid cursor'.
>>The only way to open it again is to retype the StoredProcName and
>define
>>the Params again. After that it is possible to open it once.

>>In delphi 3 I have no problems with the stored procedure !!!

>>If I compare the output of the SQL monitor in delphi 3 and delphi 4
>>I see that delphi 4 tries to assign a null value to the cursor
>>parameter. In delphi 3 not.
>>I have set the parameter value to Unassigned in the delphi 4 parameter
>>editor.

>>I can reproduce the problem in delphi 3 when I check the 'null'
>checkbox
>>of the cursor parameter
>>in the delphi 3 parameter editor.
>>Now I receive the same error message 'Invalid cursor' because delphi
>>tries to assign a value
>>to the cursor parameter.

>>How can I prevent delphi 4 to assign a null value to the cursor
>>parameter ?

>>Additional information:
>>- In the params editor I defined the cursorparam as IN/OUT Parameter
>and
>>of type ftCursor
>>- Runtime I receive the same errors.
>>- In the BDE I use SQLORA32.DLL.

>>Thanks in advance.

>>Bas Wiebinga

Other Threads